1. periksa besaran TABLESPACE TEMP
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
—————————— ———- ———-
TEMP 3.4193E+10 165675008
2. dimana TABLESPACE TEMP
SQL> col tablespace_name format a20
SQL> col file_name format a45
SQL> SELECT tablespace_name, file_name FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;
TABLESPACE_NAME FILE_NAME
——————– ———————————————
TEMP +DATA/rac/tempfile/temp.266.674566177
TEMP +DATA/rac/tempfile/temp04032011
3. create TABLESPACE TEMP2
SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
‘+DATA2/rac/tempfile/temp001.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ”
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
SQL> SELECT tablespace_name, file_name FROM dba_temp_files WHERE tablespace_name = ‘TEMP2′;
TABLESPACE_NAME FILE_NAME
——————– ———————————————
TEMP2 +DATA2/rac/tempfile/temp001.dbf
4. move DEFAULT TEMPORARY TABLESPACE
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
5. delete TABLESPACE TEMP
SQL> drop tablespace TEMP including contents and datafiles;
6. create TABLESPACE TEMP
SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
‘+DATA2/rac/tempfile/temp01.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ”
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
7. move DEFAULT TEMPORARY TABLESPACE
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
8. delete TABLESPACE TEMP2
drop tablespace TEMP including contents and datafiles;
Tags: MOVE TABLESPACE TEMP, RENAME TABLESPACE TEMP, TABLESPACE TEMP, TEMP
setijoagus
