STEP BY STEP DEFRAG DATAFILE TABLESPACE


Tujuan : merapikan segment yang acak2an dan juga reclaim spaceūüôā. bisa impact juga ke performance pada sisi I/O

1. Cek Tablespace yang akan di defrag

–hitung selisih size datafile dikurangi size segment
select a.tablespace_name,b.datafile_size,a.segment_size,b.datafile_size-a.segment_size from(
select tablespace_name,round(sum(bytes/1024/1024/1024),2) segment_size from dba_segments group by tablespace_name) a,
(select tablespace_name,round(sum(bytes/1024/1024/1024),2) datafile_size from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by  2 desc  ;

–cek block-block yang kosong/free
select
file_id,
block_id,
block_id + blocks Р1   end_block,
owner,
segment_name,
partition_name,
segment_type
from
dba_extents
where
tablespace_name = ‘SOURCE_TABLESPACE’
union all
select
file_id,
block_id,
block_id + blocks Р1   end_block,
‘free’¬†¬†¬†¬†¬†¬†¬†¬†¬† owner,
‘free’¬†¬†¬†¬†¬†¬†¬†¬†¬† segment_name,
null            partition_name,
null            segment_type
from
dba_free_space
where
tablespace_name = ‘SOURCE_TABLESPACE’
order by
1,2

2. Create Penampungan data tablespace yang akan didefrag
– bisa create tablespace baru atau
– ke existing tablespace yang masih mempunyai free space yang cukup
##create tablespace
–select location datafile
select * from dba_data_files where tablespace_name like ‘SOURCE_TABLESPACE’
–create tablespace
create tablespace DATA_DEST_TABLESPACE datafile ‘/u01/EISDB11/DATA_DEST_TABLESPACE_01.DBF’ size 10m autoextend on next 100m maxsize unlimited;
create tablespace INDEX_DEST_TABLESPACE datafile ‘/u01/EISDB11/INDEX_DEST_TABLESPACE_01.DBF’ size 10m autoextend on next 100m maxsize unlimited;

3. Generate script defragment
–defragment untuk table
select ‘alter table ‘||owner||’.”‘||segment_name|| ‘” move tablespace DATA_DEST_TABLESPACE;’¬† from dba_segments where tablespace_name like ‘SOURCE_TABLESPACE’ and segment_type like ‘TABLE’;

–defragment untuk index
select ‘alter index ‘||owner||’.’||segment_name|| ‘ rebuild tablespace INDEX_DEST_TABLESPACE;’ from dba_segments where tablespace_name like ‘SOURCE_TABLESPACE’ and segment_type like ‘INDEX’

4. Generate script 4 resize datafile
select ‘alter database datafile ”’||file_name||”’ resize 2m;’ from dba_data_files where tablespace_name= ‘SOURCE_TABLESPACE’;

5. Drop old tablespace
–cek apakah masih ada object atau tidak
select count(0) from dba_segments where tablespace_name like ‘SOURCE_TABLESPACE’
–jika sudah tidak ada object maka tablespace dapat didrop
DROP TABLESPACE SOURCE_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

6. Rename Tablespace
–untuk konsistensi dan kerapian, nama tablespace dapat diubah / disesuaikan
alter TABLESPACE nama_awal rename to nama_baru;

#issue yang mungkin muncul
– recycle bin
SELECT * FROM RECYCLEBIN;

SELECT * FROM USER_RECYCLEBIN;
–solution : purge table yang bersangkutan
select ‘purge table ‘||owner||’.”‘||segment_name||'”;’ from dba_segments where tablespace_name like ‘COL_STG_DATA’ and segment_type like ‘TABLE’;
atau
PURGE RECYCLEBIN;
– CLOB BLOB object

Dengan kaitkata , , , , , ,

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: