My CDB has DEFAULT TEMPORARY TABLESPACE named TEMP_0001.
My PDB PTST01P0 has DEFAULT TEMPORARY TABLESPACE named TEMP_TST01.
How can assign TEMP_0001 to a PDB as DEFAULT TEMPORARY TABLESPACE and then drop TEMP_TST01 ?
Take TEMP_TST01 offline. Then when it is no longer being used (transactions that were using it have ended) drop it.
New transactions will use TEMP_0001 after the other one is marked as offline.
I can't put tablespace TEMP_TST01 offline:
SQL> alter tablespace TEMP_TST01 offline;
alter tablespace TEMP_TST01 offline
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
(Oracle doc's) You cannot take the following tablespaces offline:
- The undo tablespace
- Temporary tablespaces
But, I can put tempfile offline:
SQL> alter database TEMPFILE '/database/oracle/PTST01P/temp01/TEMP_001_PTST01P_001.dbf' OFFLINE;
SQL> alter database TEMPFILE '/database/oracle/PTST01P/temp01/TEMP_001_PTST01P_001.dbf' drop;
finally, I can't drop temp tablespace
SQL> drop tablespace TEMP_TST01;
drop tablespace TEMP_TST01
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
Have you tried using ALTER DATABASE on the root to 'reset' the temp tablespace?
DATABASEstatements with the following clauses modify the root and set default values for the PDBs. In order to specify these clauses, the current container must be the root and you must have the commonly granted
What is it about that exception that you do NOT understand?
You have to provide a valid database name and the exception is telling you that you have NOT provided the actual name of the database.
See the doc I provided above for the ALTER DATABASE statement.
We can't see your machine.
We can't run queries on your system.
So we don't know what your database name is and can't query V$DATABASE to find out.
Post the database name the view shows.