Resizing TableSpace on Oracle Dear all lagi......... Sesuai janji gw, gw bikin catatan kecil lagi...yang ini khusus ngebahas soal mengatasi ukuran tablespace yang kritis. Masih ingatkan sama catatan gw yang terdahulu. langsung aja maaaang.... Pertama2 kita check dengan script yang sudah kita bikin --> the code : ## Cek TABLESPACE By Command Line Sql ##: set linesize 128 echo off feedback on heading on verify off pagesize 35 col megs_alloc format 999,999,999 heading "MB ALLOC" col megs_free format 999,999,999 heading "MB FREE" col megs_used format 999,999,999 heading "MB USED" col pct_free format 999 heading "% FREE" col pct_used format 999 heading "% USED" --col bsize format 99999 new_value bsize heading "BLOCK SIZE" --set termout off --select value bsize from v$parameter --where name = 'db_block_size'; --set termout on break on report skip 1 compute sum label "TOTAL" of megs_alloc on report compute sum of megs_free on report compute sum of megs_used on report select c.tablespace_name, round(a.bytes/1048576,2) megs_alloc, round(b.bytes/1048576,2) megs_free, round((a.bytes-b.bytes)/1048576,2) megs_used, round(b.bytes/a.bytes * 100,2) pct_free, round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used from (select tablespace_name, sum(a.bytes) bytes, min(a.bytes) minbytes, max(a.bytes) maxbytes from sys.dba_data_files a group by tablespace_name) a, (select a.tablespace_name, nvl(sum(b.bytes),0) bytes from sys.dba_data_files a, sys.dba_free_space b where a.tablespace_name = b.tablespace_name (+) and a.file_id = b.file_id (+) group by a.tablespace_name) b, sys.dba_tablespaces c where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = c.tablespace_name union select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc, round(sum(bytes_free) / 1048576,2) megs_free, round(sum(bytes_used) / 1048576,2) megs_used, round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free, round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used from v$temp_space_header group by tablespace_name order by tablespace_name; terus kita running lewat sqlplus--> SQL> @ c:\Data\script\mccserver\tablespacecheck.sql Tablespace Size TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED ----------------- ------------ ------------ ------------ ------ ------ CWMLITE 20 3 18 13 88 DRSYS 20 10 10 52 48 EXAMPLE 149 0 149 0 100 ======================= potong ============ MAA_TRXP 12,048 255 11,793 2 98 MAA_TRXE 14,048 2,395 11,653 17 83 ======================= potong ============ SYSTEM 560 135 425 24 76 TEMP 8,099 0 8,099 0 100 TOOLS 10 10 0 99 1 UNDOTBS1 5,951 5,477 474 92 8 USERS 25 24 1 98 3 ======================= potong ============ XDB 38 38 0 100 0 ------------ ------------ ------------ TOTAL 121,338 51,027 70,311 34 rows selected. Disini terlihat ada 2 buah tablespace yang kritis(lebih dari 80%).... Sekarang kita akan menambah size dari tablespace tersebut, oh iyah...semua command ini full dari sqlplus yah langkah berikutnya adalah kita lihat dulu nama2 datafile dari tablespace yang akan kita resize (apa sih datafile? cari tahu aja yah digoogle..atau tanya lagi ntar insya allah gw jawab) SQL> select * from dba_data_files where tablespace_name = 'MAA_TRXP'; FILE_NAME --------------------------------------- /m02/oradata/maa/maa_trxp01.dbf /m02/oradata/maa/maa_trxp02.dbf /m02/oradata/maa/maa_trxp03.dbf 3 rows selected. hmmmm...ada yang kurang, apa yah? oh iya jumlah/size dari masing2 datafile belum tampak nih... sekarang kita lihat field2 pada object datafile biar hasilnya spesifik: SQL> desc dba_data_files; Name --------------------------------------- FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = 'MAA_TRXP' group by file_name; SUBSTR(FILE_NAME,1,50) SUM(BYTES) -------------------------------------------------- ---------- /m02/oradata/maa/maa_trxp01.dbf 5242880000 /m02/oradata/maa/maa_trxp02.dbf 5242880000 /m02/oradata/maa/maa_trxp03.dbf 2147483648 3 rows selected. Sip...sekarang kita resize tablespace dengan membuat datafile yang baru.... sebenarnya penentuan besaran kapasitas dari datafile ada analisa tersendiri tapi...seperti biasa akan dibahas pada catatan mengenai Database analysis...hehehe...ngeles. SQL> alter tablespace maa_trxp add datafile '/m02/oradata/maa/maa_trxp04.dbf' size 5000 M; Tablespace altered. Check lagi datafile pada tablespace yang bersangkutan: SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = 'MAA_TRXP' group by file_name; SUBSTR(FILE_NAME,1,50) SUM(BYTES) -------------------------------------------------- ---------- /m02/oradata/maa/maa_trxp01.dbf 5242880000 /m02/oradata/maa/maa_trxp02.dbf 5242880000 /m02/oradata/maa/maa_trxp03.dbf 2147483648 /m02/oradata/maa/maa_trxp04.dbf 5242880000 4 rows selected. Nah loooh...dah nambah kan??? siipp, mission succesed. Sekarang check tablespace-nya, dah ngga kritis lagi atau masih kritis. SQL> @ c:\Data\script\mccserver\tablespacecheck.sql Tablespace Size TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED ----------------- ------------ ------------ ------------ ------ ------ CWMLITE 20 3 18 13 88 DRSYS 20 10 10 52 48 EXAMPLE 149 0 149 0 100 ======================= potong ============ MAA_TRXP 17,048 5,245 11,803 31 69 MAA_TRXE 14,048 2,395 11,653 17 83 ======================= potong ============ SYSTEM 560 135 425 24 76 TEMP 8,099 0 8,099 0 100 TOOLS 10 10 0 99 1 UNDOTBS1 5,951 5,477 474 92 8 USERS 25 24 1 98 3 ======================= potong ============ XDB 38 38 0 100 0 ------------ ------------ ------------ TOTAL 121,338 51,027 70,311 34 rows selected. Ok deh kaka...sip kan, sekarang dah 69%, dan untuk tablespace lainnya ngga perlu deh gw jelasin lagi step2-nya, selain ngga perlu juga akan menambah pusing para pembaca sekalian...apalagi gw yang ngetik.... :) Alhamdulillah...akhirnya selesai juga tugas2 gw, selamat Hari Raya Idul Fitri 1428 H...Taqabalallahu mina wa minkum, Mohon Maaf Lair dan Batin.... dan SELAMAT BERLIBUR..... ASYIKKKKK GW CUTI....SEMOGA ngga ada masalah sama server2 gw.