MOVE TABLESPACE TEMP

3 Jan

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: