Oracle-L: RE: SYSTEM TABLESPACE IS SO HUGE ?

From: Muqthar Ahmed <Muqthar.Ahmed_at_decoratetoday.com>
Date: Thu, 15 Aug 2002 13:58:33 -0800
Message-ID: <F001.004B6F1C.20020815135833@fatcity.com>

Madhu,

Try these two queries to find out allocated space in SYSTEM tablespace:

select owner, tablespace_name, sum(bytes)/(1024*1024) "Allocated MBytes" from dba_segments
where tablespace_name = 'SYSTEM'
group by owner, tablespace_name;

select tablespace_name, sum(bytes)/(1024*1024) "Total MBytes" from dba_data_files
where tablespace_name = 'SYSTEM'
group by tablespace_name;

Muqthar Ahmed
DBA -----Original Message-----
Sent: Thursday, August 15, 2002 4:23 PM
To: Multiple recipients of list ORACLE-L

Rogers ,
If you mean to say Writing is Creating the segments , I did not see any other user except SYS and SYSTEM users in the SYSTEM tablespace. But some user IDs which were created for replication purpose have the Default_tablespace as SYSTEM , but they do not have any segments in SYSTEM tablespace . And there wont be any chance of creating a new object/segment under those users.

I believe the SYSTEM tablespace was fragmented and now I would like to resize it , would like to know best and fastest way

Thanks
Madhu

-----Original Message-----
Sent: Thursday, August 15, 2002 1:51 PM
To: Multiple recipients of list ORACLE-L

Madhu
some one or something is writing into your system tablespace. I would check to see if anyone has the system tablespace as their default tablespace.
SELECT username from dba_users where default_tablespace ='SYSTEM'; or SELECT username from dba_users where temporary_tablespace ='SYSTEM'; Ron
ROR mô¿ôm

>>> Madhusudana.Reddy_at_bestbuy.com 08/15/02 02:06PM >>> Hello Listers,
We have a database for which the SYSTEM tablespace size is nearly 10 gig ,
Now I have a plan to reduce the size of it . And database is running on
8.1.7.2.
What would be the best and faster way to do it. Your ideas will be very much
appreciated !!

Thank in advance,
Madhu

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Reddy, Madhusudana
INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Ron Rogers
INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Reddy, Madhusudana
INET: Madhusudana.Reddy_at_bestbuy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------