RE: SYSTEM TABLESPACE IS SO HUGE ?
10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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).
Re: SYSTEM TABLESPACE IS SO HUGE ?
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).
RE: SYSTEM TABLESPACE IS SO HUGE ?
Yes Steve, System tablespace was fragmented heavily and that's why its taking 10 Gig . The database size is 75 gig , and would you suggest me to take Full database EXPORT and import it back after creating a new database with same structure ?? or is there any best or simple way ?? Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 1:51 PM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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).
RE: SYSTEM TABLESPACE IS SO HUGE ?
Check for 3rd party applications going in there. Peoplesoft was one that used to use System as it's default tablespace. Also in beginning in 11i apps 11.5.5 the patches now go in the database so System grows quite a bit. Kathy -Original Message- Sent: Thursday, August 15, 2002 11:51 AM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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). Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kathy Duret 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).
RE: SYSTEM TABLESPACE IS SO HUGE ?
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).
RE: SYSTEM TABLESPACE IS SO HUGE ?
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).
RE: SYSTEM TABLESPACE IS SO HUGE ?
To eliminate all confusion ( I came to know just now ) , somebody created this tablespace with 1 MB at the time of database creation MAY be , and only 150 MB of space is consumed , remaining all wasted space. would like to resize the SYSTEM tablespace datafile now !!! Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Check for 3rd party applications going in there. Peoplesoft was one that used to use System as it's default tablespace. Also in beginning in 11i apps 11.5.5 the patches now go in the database so System grows quite a bit. Kathy -Original Message- Sent: Thursday, August 15, 2002 11:51 AM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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). Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kathy Duret 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).
Re: SYSTEM TABLESPACE IS SO HUGE ?
You've already been given the best and simplest method to defragment the SYSTEM tablespace: export, recreate with locally managed tablespaces, do a full import. You _could_ take a look at which SYS objects have many extents and edit sql.bsq **caution**caution**caution** before recreating your new database. Before you do that, figure out if the tablespace is really fragmented or just heavily used. Each version of Oracle has required more SYSTEM tablespace. Oracle Apps could be filling it up. Or you could just have lots and lots of source code. Take a look at dba_extents and dba_free_space to see if you have lots of unusable space. It's pretty conceivable, since Oracle likes to put a 50% increase on many of its segment definitions. If the tablespace has lots of unusable free space, you could possibly try reclaiming the space the way I've often had to reclaim space in a live database with no downtime allowed. If Oracle will allow alter index rebuild and alter table move on the objects (I've never tried to move anything belonging to SYS), you could construct a tablespace map showing where each extent begins and ends. Hopefully you'll have an overextended index or table sitting at the bottom of a datafile. With nobody else able to access the database, do an alter index rebuild or alter table move, perhaps with new storage parameters. With luck, you'll now have a more compressed segment and space freed at the end of the file. Resize the data file to reclaim the space. Make sure your boss knows you might have to restore and do a point in time recovery if something goes wrong. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 2:18 PM Yes Steve, System tablespace was fragmented heavily and that's why its taking 10 Gig . The database size is 75 gig , and would you suggest me to take Full database EXPORT and import it back after creating a new database with same structure ?? or is there any best or simple way ?? Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 1:51 PM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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: Tom Pall
RE: SYSTEM TABLESPACE IS SO HUGE ? ( Cant shrink the datafile
Ahmed, The following is the o/p for your queries : OWNER TABLESPACE_NAMEAllocated MBytes -- -- SYSSYSTEM 148.570313 SYSTEM SYSTEM .6328125 TABLESPACE_NAMETotal MBytes -- SYSTEM1 High Water mark for the SYSTEM tablespace is 9.6 Gig and actual size is 10 gig , so Shirking the datafile may not help me , but is there any way to shrink the tablespace size ?? Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 4:59 PM To: Multiple recipients of list ORACLE-L 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
RE: SYSTEM TABLESPACE IS SO HUGE ?
Rogers thanks for the mail, I am trying to resize the datafile associated to SYSTEM and getting the ORA-03297 error , can , what should I do to resize the datafile, I do not want to see SYSTEM tablespace taking that much space Thanks, Madhu -Original Message- Sent: Thursday, August 15, 2002 3:21 PM To: [EMAIL PROTECTED] Madhu, From all that I understand about the system tablespace, There is no easy way to defragment the system tablespace. An Export, rebuild, Import ( under the direction of Oracle Support according to the metalink notes) is a way to defragment the system tablespace. #What was the initial size of the system tablespace when created? Can you explain the increase in size? Is your performance suffering and the users complaining? Be sure that you do a backup of everything before you attempt to work on defraging the system tablespace. Good luck, Ron ROR mª¿ªm Reddy, Madhusudana [EMAIL PROTECTED] 08/15/02 03:22PM 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).
RE: SYSTEM TABLESPACE IS SO HUGE ?
Paul , Yours is an excellent idea, but I have never implemented it in SYSTEM tablespace. I could see some Indexes Tables owned by SYSTEM and OUTLN users in the SYSTEM tablespace .. can I rebuild these indexes ?? Or can I move all objects ( tables indexes to SYSTEM OUTLN default tablespaces ).. I too do not want to touch any SYS objects ... Would like to know the possibilities with no downtime !!! Thanks again Madhu -Original Message- Sent: Thursday, August 15, 2002 4:44 PM To: Multiple recipients of list ORACLE-L You've already been given the best and simplest method to defragment the SYSTEM tablespace: export, recreate with locally managed tablespaces, do a full import. You _could_ take a look at which SYS objects have many extents and edit sql.bsq **caution**caution**caution** before recreating your new database. Before you do that, figure out if the tablespace is really fragmented or just heavily used. Each version of Oracle has required more SYSTEM tablespace. Oracle Apps could be filling it up. Or you could just have lots and lots of source code. Take a look at dba_extents and dba_free_space to see if you have lots of unusable space. It's pretty conceivable, since Oracle likes to put a 50% increase on many of its segment definitions. If the tablespace has lots of unusable free space, you could possibly try reclaiming the space the way I've often had to reclaim space in a live database with no downtime allowed. If Oracle will allow alter index rebuild and alter table move on the objects (I've never tried to move anything belonging to SYS), you could construct a tablespace map showing where each extent begins and ends. Hopefully you'll have an overextended index or table sitting at the bottom of a datafile. With nobody else able to access the database, do an alter index rebuild or alter table move, perhaps with new storage parameters. With luck, you'll now have a more compressed segment and space freed at the end of the file. Resize the data file to reclaim the space. Make sure your boss knows you might have to restore and do a point in time recovery if something goes wrong. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 2:18 PM Yes Steve, System tablespace was fragmented heavily and that's why its taking 10 Gig . The database size is 75 gig , and would you suggest me to take Full database EXPORT and import it back after creating a new database with same structure ?? or is there any best or simple way ?? Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 1:51 PM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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
Re: SYSTEM TABLESPACE IS SO HUGE ?
Other than the SYSTEM tablespace consuming 10Gb of storage, are there any indications at all that there a performance problem of any kind related to the purported fragmentation? Upshot: if it's not causing any problems, then just live with it; what's not hurting you isn't hurting you. Otherwise, if you can see problems related to data dictionary or if someone desperately wants that tablespace shrunk from 10Gb to 1Gb or less, then recreate the database... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 1:18 PM Yes Steve, System tablespace was fragmented heavily and that's why its taking 10 Gig . The database size is 75 gig , and would you suggest me to take Full database EXPORT and import it back after creating a new database with same structure ?? or is there any best or simple way ?? Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 1:51 PM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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: Tim Gorman 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).
RE: SYSTEM TABLESPACE IS SO HUGE ?
I did not see any performance issues , except some space issues on the box.. -Original Message- Sent: Thursday, August 15, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Other than the SYSTEM tablespace consuming 10Gb of storage, are there any indications at all that there a performance problem of any kind related to the purported fragmentation? Upshot: if it's not causing any problems, then just live with it; what's not hurting you isn't hurting you. Otherwise, if you can see problems related to data dictionary or if someone desperately wants that tablespace shrunk from 10Gb to 1Gb or less, then recreate the database... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 1:18 PM Yes Steve, System tablespace was fragmented heavily and that's why its taking 10 Gig . The database size is 75 gig , and would you suggest me to take Full database EXPORT and import it back after creating a new database with same structure ?? or is there any best or simple way ?? Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 1:51 PM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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: Tim Gorman 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:
Re: SYSTEM TABLESPACE IS SO HUGE ?
From a later posting on this thread, it sounds like SYSTEM was originally configured at that size? Unless that wasted space is vital, chalk it up to lessons learned, leave the database alone (on this issue), and I'm sure you'll find a more valid reason to recreate it in future (i.e. migrating to Oracle10i)... Just my $0.02... ...isn't that what 10Gb costs these days? :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 5:58 PM I did not see any performance issues , except some space issues on the box.. -Original Message- Sent: Thursday, August 15, 2002 5:54 PM To: Multiple recipients of list ORACLE-L Other than the SYSTEM tablespace consuming 10Gb of storage, are there any indications at all that there a performance problem of any kind related to the purported fragmentation? Upshot: if it's not causing any problems, then just live with it; what's not hurting you isn't hurting you. Otherwise, if you can see problems related to data dictionary or if someone desperately wants that tablespace shrunk from 10Gb to 1Gb or less, then recreate the database... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 15, 2002 1:18 PM Yes Steve, System tablespace was fragmented heavily and that's why its taking 10 Gig . The database size is 75 gig , and would you suggest me to take Full database EXPORT and import it back after creating a new database with same structure ?? or is there any best or simple way ?? Thanks Madhu -Original Message- Sent: Thursday, August 15, 2002 1:51 PM To: Multiple recipients of list ORACLE-L 10G? Must have a lot of objects not belonging to sys/system in there. (Unless someone turned auditing on and forgot about it.) I'd say the system tablespace must be so fragmented that it's best to create another database and recreate the users and import their data with default and quota set to locally managed tablespace(s). -Original Message- Sent: Thursday, August 15, 2002 12:07 PM To: Multiple recipients of list ORACLE-L 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: Orr, Steve 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: Tim Gorman 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
