There may be some cases like if you recreate a control file of your database manually in that case you will need to recreate a temporary tablespace.
To delete the old temp tablespace you need to create the new one and then make the newly one is the default temp tablespace.
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
To delete the old temp tablespace you need to create the new one and then make the newly one is the default temp tablespace.
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
SQL> CREATE TEMPORARY TABLESPACE TEMP1
2 TEMPFILE '/u01/app/oracle/oradata/db1/temp02.dbf' SIZE 200M;
Tablespace created.
Elapsed: 00:00:00.57
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
Database altered.
Elapsed: 00:00:00.13
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Elapsed: 00:00:01.33
SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;
NAME
--------------------------------------------------------------------------------
BYTES STATUS
---------- -------
/u01/app/oracle/oradata/db1/temp02.dbf
209715200 ONLINE
Elapsed: 00:00:00.00
Shrinking the Temp Tablespace.
SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;
NAME
--------------------------------------------------------------------------------
BYTES STATUS
---------- -------
/u01/app/oracle/oradata/db1/temp02.dbf
209715200 ONLINE
Elapsed: 00:00:00.00
SQL> SELECT * FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP1 209715200 2097152 207618048
Elapsed: 00:00:00.01
In the below case we tell the oracle explicitly to shrink the Temp tablespace to keep atleast some defined value.
SQL> ALTER TABLESPACE TEMP1 SHRINK SPACE KEEP 100M;
Tablespace altered.
Elapsed: 00:00:00.13
SQL> COL NAME FOR A40
SQL> SET LINE 100
SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;
NAME BYTES STATUS
---------------------------------------- ---------- -------
/u01/app/oracle/oradata/db1/temp02.dbf 105906176 ONLINE
Elapsed: 00:00:00.00
SQL> SELECT * FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP1 105906176 2097152 103809024
Elapsed: 00:00:00.01
SQL> ALTER TABLESPACE TEMP1 SHRINK SPACE;
Tablespace altered.
Elapsed: 00:00:00.03
SQL> SELECT NAME,BYTES,STATUS FROM V$TEMPFILE;
NAME BYTES STATUS
---------------------------------------- ---------- -------
/u01/app/oracle/oradata/db1/temp02.dbf 2088960 ONLINE
Elapsed: 00:00:00.00
In the below case Oracle will shrink the Temp Tablespace to the min possible value.
SQL> SELECT * FROM DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP1 2088960 2088960 0
Elapsed: 00:00:00.00
No comments:
Post a Comment