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 >>> [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] 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: [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).