Check this link http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php#online_segment_shrink
Lakukan SHRINK SPACE untuk table2 yang ada di tablespace untuk mereset HWM (cuman available di Oracle10g or higher). Sehingga bisa lebih me-reclaim wasted space. Tapi seperti dari awal saya katakan, ini tidak applicable untuk SYSTEM tablespace. 2008/7/8 dody osmon <[EMAIL PROTECTED]>: > yang jelas sih, saya bisa gunakan space lebih efisien, masalah itu di > atas HWM, saya tidak bisa komentar , oo yaa .., kalau emang bisa pakai > reset , emang model script reset pak yoel bagaimana ? > > thx, > Dody > > --- On Mon, 7/7/08, Yoel Susanto <[EMAIL PROTECTED]<yoel.susanto%40gmail.com>> > wrote: > From: Yoel Susanto <[EMAIL PROTECTED] <yoel.susanto%40gmail.com>> > > Subject: Re: [indo-oracle] resize tablepsace system > To: [email protected] <indo-oracle%40yahoogroups.com> > Date: Monday, July 7, 2008, 11:59 PM > > SELECT #4 akan menghasilkan command ALTER TABLESPACE.. . > > Tapi yang pak Dodi kasih hanya akan meresize datafile diatas HWM datafile, > > tidak akan mereset HWM datafile. > > Jadi misal datafile 3 GB, HWM ada di 2GB, actuall used space cuman 500MB. > > Jika HWM tidak di reset, Anda akan hanya akan bisa meresizenya ke 2GB. > > 2008/7/8 hendra chen <[EMAIL PROTECTED] com.sg>: > > > Pak Dodi, > > > > > > cara yang bapak berikan itu bisa untuk resize tablespace system? > > > command yang mana ya yang bisa untuk meresize tablespace system nya ? > > > yang bapak berikan setau saya itu cuma command select saja. > > > > > > > > > --- On Mon, 7/7/08, dody osmon <dodytanjung79@ yahoo.com<dodytanjung79% > 40yahoo.com> > > > > wrote: > > > From: dody osmon <dodytanjung79@ yahoo.com <dodytanjung79% 40yahoo.com> > > > > > Subject: Re: [indo-oracle] resize tablepsace system > > > To: indo-oracle@ yahoogroups. com <indo-oracle% 40yahoogroups. com> > > > > Date: Monday, 7 July, 2008, 1:41 PM > > > > > > > > > hai, > > > > > > terimakasih, tapi ini bisa di lakukan dan saya sudah mencoba , berikut > step > > > nya: > > > > > > 1. > > > > > > set verify off > > > > > > column file_name format a50 word_wrapped > > > > > > column smallest format 999,990 heading "Smallest|Size| Poss." > > > > > > column currsize format 999,990 heading "Current|Size" > > > > > > column savings format 999,990 heading "Poss.|Savings" > > > > > > break on report > > > > > > compute sum of savings on report > > > > > > column value new_val blksize > > > > > > 2. > > > > > > select file_name, > > > > > > ceil( (nvl(hwm,1)* &&blksize) /1024/1024 ) smallest, > > > > > > ceil( blocks*&&blksize/ 1024/1024) currsize, > > > > > > ceil( blocks*&&blksize/ 1024/1024) - > > > > > > ceil( (nvl(hwm,1)* &&blksize) /1024/1024 ) savings > > > > > > from dba_data_files a, > > > > > > ( select file_id, max(block_id+ blocks-1) hwm > > > > > > from dba_extents > > > > > > group by file_id ) b > > > > > > where a.file_id = b.file_id(+) > > > > > > / > > > > > > 3. > > > > > > column cmd format a75 word_wrapped > > > > > > 4. > > > > > > select 'alter database datafile '''||file_name| |''' resize ' || > > > > > > ceil( (nvl(hwm,1)* &&blksize) /1024/1024 ) || 'm;' cmd > > > > > > from dba_data_files a, > > > > > > ( select file_id, max(block_id+ blocks-1) hwm > > > > > > from dba_extents > > > > > > group by file_id ) b > > > > > > where a.file_id = b.file_id(+) > > > > > > and ceil( blocks*&&blksize/ 1024/1024) - > > > > > > ceil( (nvl(hwm,1)* &&blksize) /1024/1024 ) > 0 > > > > > > / > > > > > > thx, > > > > > > Dody > > > > > > --- On Mon, 6/30/08, Rudy <[EMAIL PROTECTED] com> wrote: > > > > > > From: Rudy <[EMAIL PROTECTED] com> > > > > > > Subject: Re: [indo-oracle] resize tablepsace system > > > > > > To: indo-oracle@ yahoogroups. com > > > > > > Date: Monday, June 30, 2008, 1:05 AM > > > > > > Hi, utk tablespace system yg sdh disetting sebesar 3 GB , sebaiknya > jangan > > > dikecilkan. > > > > > > Apabila memang kurang dan file system nya sdh mencapai 3 GB, sebaiknya > > > dibuatkan ke file lain dengan tablespace yg sama. > > > > > > Adapun langkahbnya sbb : > > > > > > Cek Space Masing-masing table space: > > > > > > select sum(bytes)/( 1024*1024) ,tablespace_ name,file_ name > > > > > > from dba_data_files > > > > > > where tablespace_name like '%SYS%' > > > > > > group by tablespace_name, file_name; > > > > > > Sedangkankan utk cek Free Space Tablespace sbb : > > > > > > select sum(bytes)/( 1024*1024) ,tablespace_ name > > > > > > from dba_free_space > > > > > > where tablespace_name like '%SYS%' > > > > > > group by tablespace_name > > > > > > Dan utk menambahkan size table space bisa mengunakan resize tablespace > sbb > > > : > > > > > > alter database datafile '/data/applmgr/ proddata/ fax01.dbf' resize 500m; > > > > > > Sedangkan utk menambahkan file utk table space sbb : > > > > > > alter tablespace GLD add datafile '/data/appltest/ testdata/ gld02.dbf' > > > size 10m; > > > > > > Regard's > > > > > > Rudy > > > > > > --- On Mon, 6/30/08, dody osmon <dodytanjung79@ yahoo.com> wrote: > > > > > > From: dody osmon <dodytanjung79@ yahoo.com> > > > > > > Subject: [indo-oracle] resize tablepsace system > > > > > > To: indo-oracle@ yahoogroups. com > > > > > > Date: Monday, June 30, 2008, 11:17 AM > > > > > > Hai, > > > > > > salam kenal semua, bagaimana cara untuk resize tablepspace system yg > besar > > > nya sudah 3 giga, padahal yang ke pakai cuma 500 M, sudah di coba pake > alter > > > tablespace resize, tetapi tidak bisa . > > > > > > Thx > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Yahoo! Toolbar is now powered with Search Assist.Download it now! > > > http://sg.toolbar. yahoo.com/ > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > -- > > Cheers, > > Yoel Susanto > > www.indo-oracletech .com > > [Non-text portions of this message have been removed] > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > -- Cheers, Yoel Susanto www.indo-oracletech.com [Non-text portions of this message have been removed]
