Reorganize System Tablespace

From: Jayaraman Ashok <ashok_jayaraman_at_yahoo.com>
Date: 9 Sep 2002 02:08:21 -0700
Message-ID: <7ca2852.0209090108.6f087ca7@posting.google.com>

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 Received on Mon Sep 09 2002 - 04:08:21 CDT