Adding new temporary tablespace on primary.
create temporary tablespace temp2 tempfile '+data(tempfile)' SIZE 10M AUTOEXTEND ON NEXT 1M;on standby the tablespace is listed in v$tablespace
SQL> select name from v$tablespace; TS# NAME ---------- ----------------------------------- 0 SYSTEM 1 SYSAUX 2 UNDOTBS1 3 TEMP 4 USERS 5 TEMP2But there's no temp file associated with this tablespce
select ts#,name from v$tempfile; TS# NAME ---------- -------------------------------------------------- 3 +DG_DATA/ent11g2s/tempfile/temp.267.787771139To add a temp file stop the log apply on standby and open the db in read only mode.
SQL> alter database open read only; Database altered. SQL> alter tablespace temp2 add tempfile '+DG_DATA' size 10m; Tablespace altered. select ts#,name from v$tempfile; TS# NAME ---------- ---------------------------------------------_ 3 +DG_DATA/ent11g2s/tempfile/temp.267.787771139 5 +DG_DATA/ent11g2s/tempfile/temp2.260.877620677
If the temporary tablesapce is dropped on the primary then both tablespace and temp file is dropped on the standby.
SQL> select name from v$tempfile; NAME ---------------------------------------------- +DG_DATA/ent11g2s/tempfile/temp.267.787771139 SQL> select name from v$tablespace; NAME ----------- SYSTEM SYSAUX UNDOTBS1 USERS TEMPOn alert log
Recovery deleting tempfile #2:'+DG_DATA/ent11g2s/tempfile/temp2.260.877620677' Recovery dropped temporary tablespace 'TEMP2'Useful Metalink Notes
DDL Operations (Alter and Drop) on Temporary Tablespace Hang [ID 1911167.1]
Drop Temp Tablespace Command Hangs [ID 214371.1]
Drop Of A Huge LMT Temporary Tablespace Hangs [ID 1070665.1]