Re: Multiple extents in system tablespace

From: <jl34778_at_corp02.d51.lilly.com>
Date: 4 Oct 94 17:58:13 EST
Message-ID: <1994Oct4.175813.1_at_corp02.d51.lilly.com>

In article <1994Oct1.131545.1321_at_gtewd.mtv.gtegsc.com>, davidsonj_at_gtewd.mtv.gtegsc.com writes:
> I have noticed that my system tablespace has a lot of segments with multiple
> extents (some as many as 20 extents). Will this impact performance of stored
> procedures? Or anything else? Should I compress the system tablespace (I think
> that is done by exporting and reimporting the tablespace - but don't I have to
> eliminate the current tables before import will actually compress). Is this
> worth the effort?
> thanks in advance for any advice you may have,
> jim

Compressing the system tables is not as simple as doing regular tables. You need to drop a table to compress it, and it is not a good idea to try to drop a data dictionary table.

You have to do a full database export, drop the database, and import the data back in. However, it is not that simple, since a full export does not export the data dictionary. It gets rebuilt during the import, through the DDL that creates the other objects. This leaves you in pretty much the same situation you were in before. So, you need to modify the SQL.BSQ file that creates the data dictionary tables during database creation. Modify it to specify the initial, next, and pctincrease parameters for the objects that you are having trouble with.

Be VERY careful.

When I have done this, I do the following:

Look at DBA_SEGMENTS to identify the tables and clusters that need non-default

        storage parameters.
Do a full database export, when no one is on the system. Generate a list of your tablespaces and datafiles. Shutdown the database
backup the database files (just in case) delete the database files.
Modify the SQL.BSQ file. (keep a copy of the original)

	VMS location: ora_rdbms:sql.bsq
	Unix location: $ORACLE_HOME/dbs/sql.bsq

recreate the database (I use the ORACLE install procedure to do this) create any tablespaces not created by the install process do a full database import
restore the old version of sql.bsq, so that any additional databases that

        you create will have the defaults.

Hope this helps. Looks like fun, eh?

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285

Received on Tue Oct 04 1994 - 23:58:13 CET