Administrasi Tablespace

Tablespace merupakan bagian dari arsitektur logic database Oracle [secara sekilas, struktur logik database Oracle adalah tablespace, segment, extent, dan block]. Tablespace digunakan sebagai tempat (storage) bagi segment. Segment adalah object database yang mempunyai data. Yang termasuk segment adalah table, index, cluster, rollback (undo), lobsegment, lobindex, table partition, index partition, lob partition, temporary segment, dll. Gunakan query berikut untuk melihat type-type segment yang ada di database kita SQL> select distinct SEGMENT_TYPE from dba_segments;

Secara fisik, tablespace terdiri atas satu atau lebih datafile. Informasi tentang tablespace ada di view v$tablespace , dba_tablespaces, dba_data_files, dba_temp_files, dll.

Gunakan command berikut untuk melihat tipe-tipe tablespace SQL> select distinct CONTENTS from dba_tablespaces; Berdasarkan hasil query tersebut, berikut ini 3 tipe tablespace:

• UNDO. Untuk menyimpan rollback (undo) segment
• TEMPORARY. Untuk menyimpan temporary segment
• PERMANENT. Untuk menyimpan segment selain dua di atas (contoh tabel, index)

UNDO TABLESPACE

1. Contoh membuat Undo Tablespace dengan nama undotbs2, datafile /oradata/oracle/ts_bak/undotbs201.dbf, ukuran file sebesar 10M. Jangan lupa tambahkan option undo sesudah create. SQL> create undo tablespace undotbs2
datafile '/oradata/oracle/ts_bak/undotbs201.dbf' size 10m;
2. Untuk menambah (menaikkan size/space) dapat dilakukan dengan manaikkan size dari datafile atau menambah datafile SQL> alter database
datafile '/oradata/oracle/ts_bak/undotbs201.dbf' resize 20m; SQL> alter tablespace undotbs2 add
datafile '/oradata/oracle/ts_bak/undotbs202.dbf' size 10m;
3. Untuk melihat datafile dan size dari tablespace UNDOTBS2
   SQL> select file_name,bytes from dba_data_files
where tablespace_name='UNDOTBS2';

4. Untuk melihat free space tiap-tiap datafile dari tablespace UNDOTBS2
   SQL> select a.name, sum(b.bytes) from v$datafile a, dba_free_space b where a.file#=b.file_id and b.TABLESPACE_NAME='UNDOTBS2' group by a.name;

5. Untuk melihat undo tablespace yang aktif saat ini gunakan SQL> show parameter undo_tablespaceUntuk mengubah undo_tablespace ke tablespace yang baru saja kita buat SQL> alter system set undo_tablespace=UNDOTBS2;

TEMPORARY TABLESPACE

1. Contoh membuat temporay tablespace dengan nama TEMP2, tempfile /oradata/oracle/ts/temp21.dbf, ukuran file sebesar 10M. Jangan lupa tambahkan option temporary sesudah create, dan gunakan tempfile bukan datafile. SQL> create temporary tablespace temp2
tempfile '/oradata/oracle/ts/temp21.dbf' size 10m;
2. Untuk menambah (menaikkan size/space) dapat dilakukan dengan manaikkan size dari tempfile atau menambah tempfile SQL> alter database
tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m; SQL> alter tablespace temp2 add
tempfile '/oradata/oracle/ts/temp22.dbf' size 10m;
3. Untuk melihat temp file (file-file milik TEMPORARY tablespace) dan sizenya. Contoh, misalkan nama TEMPORARY tablespace tersebut adalah TEMP:
   SQL> select file_name,bytes from dba_temp_files where tablespace_name='TEMP';

4. Untuk melihat free spacenya
   SQL> select a.name, sum(b.BYTES_FREE) from v$tempfile a, V$TEMP_SPACE_HEADER b where a.file#=b.file_id and b.TABLESPACE_NAME='TEMP' group by a.name;

5. Untuk melihat temporary tablespace yang digunakan sebagai DEFAULT di database adalah SQL> select PROPERTY_VALUE from database_properties
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; Untuk mengubah default temporary tablespace menjadi tablespace yang baru saja kita buat SQL> alter database default temporary tablespace temp2;

PERMANENT TABLESPACE

1. Contoh membuat permanent tablespace dengan nama DATA, datafile /oradata/oracle/ts_bak/data01.dbf, ukuran file sebesar 10M. SQL> create tablespace DATA
datafile '/oradata/oracle/ts_bak/data01.dbf' size 10m;
2. Untuk menambah (menaikkan size/space) dapat dilakukan dengan manaikkan size dari datafile atau menambah datafile. Caranya sama persis seperti pada UNDO tablespace SQL> alter database
datafile '/oradata/oracle/ts_bak/data01.dbf' resize 20m; SQL> alter tablespace DATA add
datafile '/oradata/oracle/ts_bak/data02.dbf' size 10m;
3. Untuk melihat datafile, size, dan free size dari PERMANENT tablespace; caranya seperti untuk UNDO tablespace, yaitu gunakan view dba_data_files, v$datafile, dan dba_free_space.
4. Untuk melihat permanent tablespace yang digunakan sebagai DEFAULT di database adalah
   SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
   Untuk mengubah default permanent tablespace menjadi tablespace yang baru saja kita buat
   SQL> alter database default tablespace data;

MENGURANGI SIZE DARI TABLESPACE

1. Dilakukan dengan mengurangi size dari datafilenya. Perintah untuk mengurangi size adalah sama dengan perintah untuk menambah size, intinya adalah mengubah size (RESIZE). Jangan lupa, untuk temporary tablespace gunakan TEMPFILE; untuk PERMANENT dan UNDO tablespace sama, gunakan DATAFILE.
   SQL> alter database
tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m;
SQL> alter database
datafile '/oradata/oracle/ts/undotbs1.dbf' resize 20m;

   Catatan penting
   Penguranga size (resize) tidak bisa dilakukan pada block di bawah high water mark. High water mark adalah posisi block tertinggi yang pernah dipakai untuk extent. Nanti kapan-kapan saya bahas tentang high water mark ini. Eksekusi akan error kalau resize dilakukan di bawah High water mark:
   ORA-03297: file contains used data beyond requested RESIZE value
   Best practice-nya, kalau misalkan size datafile 4G, dan kita ingin menurunkan size-nya, lakukan secara gradual (diturunkan 100M – 100M) untuk menemukan size (high water mark) yang sesuai.

2. Dilakukan dengan menghapus temp file
   Untuk alasan keamanan, datafile tidak bisa dihapus. Ingat, yang dimaksud datafile adalah file-file milik tablespace PERMANENT dan UNDO.
   SQL> alter database
datafile '/oradata/oracle/ts/test02.dbf' drop;
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

   Sedangkan temp file bisa dihapus (file milik tablespace TEMPORARY) karena file ini tidak berisi data. Dengan catatan, paling tidak sisakan 1 tempfile.
   SQL> alter database
tempfile '/oradata/oracle/ts/temp02.dbf2' drop;

3. contoh kasus
   • Pertanyaan
     Bagaimana cara untuk resize tablepspace SYSTEM yang besar nya sudah 3G, padahal yang ke pakai cuma 500M, sudah di coba pake alter tablespace resize, tetapi tidak bisa .
   • Jawaban
     Resize tidak bisa dilakukan karena dulunya space 3G itu pernah kepakai. Mungkin dulu pernah sempat ada segment (table/index/temp segment) yang memakai tablespace SYSTEM, namun sekarang sudah dihapus.

     Konsep yang berkaitan dengan hal ini adalah “High Water Mark”.

     Kalau size tablespace (datafile) tidak bisa dikurangi dengan “alter database datafile ‘…’ resize” sementara itu free space-nya masih sangat banyak, satu-satunya solusi adalah recreate tablespace yang bersangkutan. Caranya:
     – export data-data yang ada di tablespace tsb
     – create tablespace baru
     – import data-data tsb ke tablespace baru
     – drop tablespace lama.

     Namun sayangnya, tablespace SYSTEM tidak bisa di-recreate. Kalau masih mau dipaksa, ya dengan recreate database:
     – export database full
     – buat database baru
     – import database
     – drop database lama

MENGHAPUS (drop) TABLESPACE
Perintahnya sama untuk ketiga jenis tablespace tersebut. Contoh SQL> drop tablespace DATA;

Referensi dari Oracle documentation:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm