The following query may help locating the high water mark. 1 select f1.file_name, round(f1.bytes / 1024 / 1024) ALLOCATED_MB, 2 round(f2.block_size * (1 + max(u.block# + u.length)) / 1024 / 1024, 2) HWM_MB 3 from dba_data_files f1, 4 v$datafile f2, 5 sys.uet$ u 6 where f1.relative_fno = f2.rfile# 7 and f1.file_id = f2.file# 8 and f2.ts# = u.ts# 9 and f2.file# = u.file# 10 group by f1.file_name, 11 round(f1.bytes / 1024 / 1024), 12* f2.block_size
>----- ------- Original Message ------- ----- >From: Richard Ji <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Sun, 02 Feb 2003 21:13:59 > >Check dba_free_space to find out how much free >space you have >freed up for the SYSTEM tablespace. > >Then you can do: > >SQL>alter database datafile >'path_to_system_tablespace' resize 800m; > >800m is just an example. > >This will work if the space you freed up are >towards the end of the >datafile, not in the middle. > >Richard > >-----Original Message----- >Sent: Sunday, February 02, 2003 11:14 PM >To: Multiple recipients of list ORACLE-L > > >guys, > >one of my instances here... > >has SYSTEM tablespace of size 25G (with one >datafile). >it has lot of indexes used by an application. >the total size of indexes in SYSTEM tablespace is >21G. > >this was done by someone else and now they want me >to : > >1)drop some un-used indexes in SYSTEM tablespace. >2)move the remaining indexes to some other >tablespace. >3)and make the SYSTEM tablespace smaller. > >all they want is to free disk space. > >i can move the index to some other tablespace >by <alter index ......rebuild>. > >even if drop the indexes ....the space occupied >by the index in the SYSTEM tablespace is cleared >but the datafile does not get smaller. > >but how do i make the file smaller ? > >should i exp and imp the whole DB ? > >the ENV is 8.1.6/win2k . >this is quite urgent.plz respond quickly. > >Regards, >Prem. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
