Re: SQL Script for eliminating table extents.

From: Creach, Sylvan <sc00802%po1_at_COBE.COM>
Date: Wed, 31 Jan 1996 15:30:02 -0500
Message-Id: <9601312045.AA27614@alice.jcc.com>

Hi, David,

Your question.

   >First, I am assuming that the export/import will create tables with a
   >default size and extent size regardless of how much data will go into
   >that table.    YES / NO???

You have an option to compress or not to compress extents. If you compress the extents it will determine the initial size of the extent allocation based on the sum of all extents allocated to that object. It will not determine size on how much data the object is using. So if your primary purpose is to reduce the number of extents then export/import will do this.

>Second,
>Does anybody have an SQL script that will:
>1) look at all the tables/indexes in a tablespace
>2) formulate a drop table and create table/index script
>3) look at current space used by the table/index
>4) include in the create table/index command initial sizes in order to
> minimize the number of extents a table/index uses
>
>Thanks,
>David Priebe
>alphora7_at_renfrew.edu.on.ca

Maybe this will help.

I have been through an experience where the tables where over allocated and can share what I did to resize the database extents.In this situation the Oracle database was created by another DBA and the initial extents were set to 5M as the default. The result of this LARGE initial size was that most tables were way over allocated. So my task was to resize all 600+ tables and 800+ indexes. Here is quick overview of what I did:

1. To determine how much space each table and index was using I just ran an analyze on all Tables. (analyze table ????? estimate statistics )
2. Perform a full Exported of the database.
3. Created the (create table and create index scripts) using the import option INDEXFILE. Command: (imp usr/pass file=?.dmp indexfile=outfile.sql) This will generate a SQL script with the CREATE TABLE statments commented out and CREATE INDEX statements.
4. Wrote a sql script to spool the used size to a file. select table_name,blocks*2048 from dba_tables; select index_name,leaf_blocks*2048 from dba_indexes;
5. Wrote a ksh (UNIX korn) script to modify the SQL generated from (# 3. INDEXFILE) based on the values generated by the analyze statements.
6. Drop all tables except SYS and SYSTEM. Dropped the user defined Tablespaces and recreated ajusting the size as needed.
7. Precreated the tables and indexes using the modified SQL in #5.
8. Perform a full imported the database.

 This saved almost 1 gig on our database system. If you would like more information on scripts or question, feel free to email me.

Sylvan Creach, Oracle DBA                            sc00802_at_po1.cobe.com

Cobe Laboratories, Inc.
                                                  (303)239-2206
1185 Oak St                                              Platforms: HP-UX
9.04 (UNIX)
Lakewood, CO 80215-4407 USA                                OSF1  3.2

 (UNIX)          Windows NT
Oracle Releases: 7.0.16.4, 7.1.6 and 7.2.2.3 Received on Wed Jan 31 1996 - 15:45:58 CST