From: mark.powell@eds.com (Mark D Powell) Newsgroups: comp.databases.oracle.server Subject: Re: Reorganize System Tablespace Date: 9 Sep 2002 18:43:40 -0700 Organization: http://groups.google.com/ Lines: 53 Message-ID: <178d2795.0209091743.501106d0@posting.google.com> References: <7ca2852.0209090108.6f087ca7@posting.google.com> NNTP-Posting-Host: 68.42.103.53 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1031622220 5296 127.0.0.1 (10 Sep 2002 01:43:40 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 10 Sep 2002 01:43:40 GMT Sybrand Bakker wrote in message news:... > On 9 Sep 2002 02:08:21 -0700, ashok_jayaraman@yahoo.com (Jayaraman > Ashok) wrote: > > >Hi > >The database is running on Oracle 8.1.6.3.0 on AIX 4.3.3. Whe i query > >on any dba* views it takes more than 30 minutes or so to get the > >result and sometimes it just hangs. Oracle says it is due to smon > >hanging from time to time and suggested using oradebug utility to dump > >the > >process(i think it spawns a new smon automatically and kills the hung > >process). After this all my queries on dba* views are > >better. I have just taken over this database which runs 11i and the > >pctincrease for system tablespace is 100 and the size > >of it is around 4.3 GB. I was horrified and i thought the first job > >for me will be to reorganize this. KIndly give your inputs. Some > >tablespaces are dictionary managed but most are LMTs.Will the > >following have performance impact? In case i have to rebuild these > >objects which is the best method? > > > >SUBSTR(SEGMENT_NAME,1,20) EXTENTS > >---------------------------------------- ---------- > >SOURCE$ 4572 > >IDL_UB1$ 5179 > >IDL_UB2$ 3370 > >I_SOURCE1 2166 > > > > > >Cheers, > >Ashok > > > There is only one way to reorganize the system tablespace > 1 full database export > 2 recreate the database > 3 full database import > > Hth > > > Sybrand Bakker, Senior Oracle DBA > > To reply remove -verwijderdit from my e-mail address Ashok, did support ask you to check to see if the base tables had been analyzed? If not then you may want to check to make sure no one did this; if you find statistics on the base tables then remove them and see if things improve. If support has identifed a known bug that you did not post then what did they say about upgrading? HTH -- Mark D Powell --