Check temporary tablespace informations:
select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
------------------------ -----
DEFAULT_TEMP_TABLESPACE TEMP
select tablespace_name, contents, status from dba_tablespaces where tablespace_name like '%TEMP%';
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
TEMP TEMPORARY ONLINE
Create a new temporary tablespace TEMP_X and make it default temporary tablespace, drop the old TEMP tablespace:
CREATE TEMPORARY TABLESPACE TEMP_X
TEMPFILE '+MWDATA1' SIZE 4000M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_X;
SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ----------------
+JDATA1/JDB/tempfile/temp_x.274.804949525 TEMP_X
/u01/app/oracle/oradata/JDB/temp01.dbf TEMP
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Create new temporary tablespace TEMP,make it deault temporary tablespace and drop the old TEMP_X tablespace:
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '+MWDATA1' SIZE 4000M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP_X INCLUDING CONTENTS AND DATAFILES;
Resize a Temporary Tablespace:
alter tablespace temp resize 1000M;
alter database tempfile 'tempfile_name' resize 3000M;
Shrink a Temporary Tablespace:
alter tablespace temp shrink space;
alter tablespace temp shrink tempfile 'tempfile_name' keep 2000M;
RENAME a Temporary Tablespace:
alter tablespace temp rename to temp_x;
To view information about Temporary Tablespace:select * from dba_temp_files;
select * from v$tempfile;
select * from v$temp_space_header;
Monitor the tempoary tablspace usage:
select * from v$sort_usage;
select * from v$sort_segment;
If you try to drop the default temporary tablespace, you will get following error message:
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
No comments:
Post a Comment