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]>: > 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 <[EMAIL PROTECTED]<dodytanjung79%40yahoo.com>> > wrote: > From: dody osmon <[EMAIL PROTECTED] <dodytanjung79%40yahoo.com>> > Subject: Re: [indo-oracle] resize tablepsace system > To: [email protected] <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]
