RE: RE: Urgent - how to make SYSTEM tbls smaller ?

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Mon, 03 Feb 2003 08:00:26 -0800
Message-ID: <F001.00542288.20030203080026@fatcity.com>

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 <richard.ji_at_mobilespring.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Feb 03 2003 - 10:00:26 CST