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
- 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; - 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; - Untuk melihat datafile dan size dari tablespace UNDOTBS2
SQL> select file_name,bytes from dba_data_files
where tablespace_name='UNDOTBS2'; - 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;
- Untuk melihat undo tablespace yang aktif saat ini gunakan
SQL> show parameter undo_tablespace
Untuk mengubah undo_tablespace ke tablespace yang baru saja kita buatSQL> alter system set undo_tablespace=UNDOTBS2;
TEMPORARY TABLESPACE
- 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; - 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; - 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';
- 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;
- Untuk melihat temporary tablespace yang digunakan sebagai DEFAULT di database adalah
SQL> select PROPERTY_VALUE from database_properties
Untuk mengubah default temporary tablespace menjadi tablespace yang baru saja kita buat
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';SQL> alter database default temporary tablespace temp2;
PERMANENT TABLESPACE
- 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; - 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; - 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.
- 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
- 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. - 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 expectedSedangkan 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; - 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
- Pertanyaan
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
Rohmad, tambahin donk caranya lihat tablespace (terdiri dari data files apa saja, lokasinya dimana, size nya berapa) sebelum kita merubah ukuran tablespace 🙂
Sudah ditambahkan ya 🙂
Pak Rohmad,apa bila menambahkan datafile ke tablespace tapi tidak pada satu partisi(harddisk) apakah bisa?
Bisa. Bahkan sebaiknya begitu. Filosofinya, sebaiknya datafile itu disebar ke partisi yang berbeda. Untuk bagi-bagi I/O
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.
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 );
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 … :-).
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
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..
pak saya mau tanya ..
bisakah data di rerecover hanya dengan dbf file nya ?
karen dmp file nya gak ada ?
terima kasih
Pingback: Arsitektur Database Oracle « e-computer system
gmn caranya membuat sebuah tablespace dngan nama tertentu misal “abc_1234” ???
utuk medapatkan ini (‘/oradata/oracle/ts_bak/undotbs201.dbf’ size 10m;) gmna caranya pak? terimakasih
-masuk ke sysdba
– ketik @datafile
– akan muncul permintaan mengisi nama tablespace