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

This entry was posted in Administration and tagged , , , . Bookmark the permalink.

14 Responses to Administrasi Tablespace

  1. YanRa says:

    Rohmad, tambahin donk caranya lihat tablespace (terdiri dari data files apa saja, lokasinya dimana, size nya berapa) sebelum kita merubah ukuran tablespace 🙂

  2. rohmadne says:

    Sudah ditambahkan ya 🙂

  3. edfin says:

    Pak Rohmad,apa bila menambahkan datafile ke tablespace tapi tidak pada satu partisi(harddisk) apakah bisa?

  4. rohmadne says:

    Bisa. Bahkan sebaiknya begitu. Filosofinya, sebaiknya datafile itu disebar ke partisi yang berbeda. Untuk bagi-bagi I/O

  5. Adhi says:

    saya punya datafile yang sizenya membengkak 26 GB
    saya sudah coba
    – export database full
    – buat database baru
    – import database
    tetapi tetap saja datafile masih membengkak,
    setahu saya dtfile tsb membengkak karena saya sering create table dan drop table.
    apakah ada cara untuk resize datafile tersebut?
    karena jika saya export size file dmp nya cuma 300 MB.

  6. rohmadne says:

    Datafile bisa diresize (dikecilkan ukurannya) namun ada batasan yang berkaitan dengan high water mark http://rohmad.net/2008/07/01/high-water-mark/

    File besar bisa juga karena parameter PCTINCREASE tabel anda disetting > 0. Lihat PCTINCREASE :

    SQL> select OWNER,TABLE_NAME,PCT_INCREASE from dba_tables where TABLESPACE_NAME=’YOURTABLESPACE’;

    Kalau nilainya > 0, setting menjadi 0
    SQL> alter table TEST STORAGE (PCTINCREASE 0 );

  7. Wandi says:

    Untuk Adhi,

    Pada saat exportnya apakah sudah dicoba menggunakan option “compress=N” belom ya?
    klo tidak salah, option “compress = Y”, artinya pada saat import maka seluruh extent pada table
    akan di mampatkan menjadi 1 initial extent.

    Semoga membantu … :-).

  8. agnes says:

    mas rohmat saya agnes, mau tanya,,
    waktu saya mau add datafile di oracle 10g kok g bisa y??
    coding n hasilnya seperti ini..

    SQL> alter tablespace tbs1 add datafile ‘/data/ora_data/datafile1.dbf’ size 400Mautoextend on next 10M maxsize 10G;
    alter tablespace tbs1 add datafile ‘/data/ora_data/datafile1.autoextend on next 10M maxsize 10G
    *
    ERROR at line 1:
    ORA-01034: ORACLE not available

  9. agnes says:

    mas rohmat saya agnes, mau tanya,,
    waktu saya mau add datafile di oracle 10g kok g bisa y??
    coding n hasilnya seperti ini..

    SQL> alter tablespace tbs1 add datafile ‘/data/ora_data/datafile1.dbf’ size 400Mautoextend on next 10M maxsize 10G;
    alter tablespace tbs1 add datafile ‘/data/ora_data/datafile1.autoextend on next 10M maxsize 10G
    *
    ERROR at line 1:
    ORA-01034: ORACLE not available

    tu knp y mas,,
    apa yang harus saya lakukan??
    padahal memori yg tersedia masih 25GB.. terima kasih atas perhatian mas rohmat..
    jawabannya saya tunggu..

  10. badrulsyah says:

    pak saya mau tanya ..

    bisakah data di rerecover hanya dengan dbf file nya ?
    karen dmp file nya gak ada ?

    terima kasih

  11. Pingback: Arsitektur Database Oracle « e-computer system

  12. cuizt says:

    gmn caranya membuat sebuah tablespace dngan nama tertentu misal “abc_1234” ???

  13. yayuk says:

    utuk medapatkan ini (‘/oradata/oracle/ts_bak/undotbs201.dbf’ size 10m;) gmna caranya pak? terimakasih

Leave a Reply

Your email address will not be published. Required fields are marked *