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 tablespceselect 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]
