Manage your temporary datafile

Hari ini coba memanage temporary tablespace untuk siskadb.
Kondisi eksisting terdapat 2 datafile temporary :

1. temp01.dbf
2. temp02.dbf

Posisi tablespace temp file ada di : /data2/oradata/siscc/

Untuk melihat tablespace existing :

SQL> SELECT tablespace_name, file_name, bytes

2 FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME BYTES

----------------- -------------------------------- ---------------TEMP /data2/oradata/siscc/temp01.dbf 500,000,000

/data2/oradata/siscc/temp02.dbf 13,107,200,000

Yang akan dilakukan adalah mendrop tablespace dan recreate tempfile

SQL> ALTER DATABASE TEMPFILE '/data2/oradata/siscc/temp02.dbf'

DROP INCLUDING DATAFILES;

Database altered.

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/data2/oradata/siscc/temp02.dbf' SIZE 512m 2 AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED

SQL> SELECT tablespace_name, file_name, bytes 2 FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME BYTES

----------------- -------------------------------- --------------TEMP /data2/oradata/siscc/temp02.dbf 536,870,912