(Fwd) Re: Reducing table initial extent size for subset of pro

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Fri, 16 Jun 2000 15:42:37 -0700
Message-Id: <10530.109625@fatcity.com>

• Forwarded message follows -------

--Message-Boundary-25011

Linda,

see the attached files for an example run (Oracle 7.3). There is a redundant .zip file in case the text attachments don't go through right.

i didn't have time to look at doc#61949.1, i hope this isn't duplicate info.

i think there are freeware utilities to generate dll from the data dictionary (maybe on Jared's web site?), but using the "easier" built in oracle utilities, this is a "stupid oracle trick" #1 from the old days:

export an empty table (like joe said rows=n), then import with "indexfile" to get the ddl in a text file (the result file is in a pretty ugly format, you may have to edit it a lot).

since you have to edit the ddl, it is labor intensive, and I'm not sure how you would create subsets when importing. also, the space calculations pretty much have to be done by hand as far as I know. you can of course approximate by chopping down the space figures in the ddl text file proportionately.

(try "exp help=y" and "imp help=y" for the paramter list. the exp/imp parameters are also in the documentation, which used to be called "utility manual" or somesuch, don't know since I haven't looked at it for a *long* time!).

After you have edited the ddl, then run it to create table/etc statements in the dev/test instance, make a "real" export file (rows=y) from the prod instance, and import that into the smaller tables you precrated in the test/dev instance(s).

i'm sure you will need to do some extra stuff besides what i said here, but this gives you the basic idea.

best wishes,
ep

(i'll also fwd to the list without attachments to stimulate discussion)

Date sent:      	Fri, 16 Jun 2000 10:31:07 -0800
To:             	Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Send reply to:  	ORACLE-L_at_fatcity.com
From:           	Linda Hagedorn <Linda_at_pets.com>
Subject:        	Reducing table initial extent size for subset of production



> I inherited an export/import process to create a subset of production data

> for development and QA use.  It's consistently running out of space.      

...

> To reach my goal of smaller tables, do I have to 1) export without
> compression, 2) preallocate all 200+ tables, and 3) import, or is there
> another way? If I have to preallocate the tables, does someone have SQL to
> generate the DDL statements, including the size math to recalucluate the
> tables smaller?

--Message-Boundary-25011

Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'TEST_DDL.TXT'

REM CREATE TABLE "USERNAME"."LINDA_DDL_TEST" ("COLUMN1" CHAR(1), "COLUMN2" REM NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL REM 512000 NEXT 253952 MINEXTENTS 1 MAXEXTENTS 299 PCTINCREASE 0 REM FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" ; --Message-Boundary-25011

Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file '16JUN00A.LOG'

Connected to: Oracle7 Server Release 7.3.2.3.0 - Production Release With the distributed, replication and parallel query options PL/SQL Release 2.3.2.3.0 - Production
Export done in WE8ISO8859P1 character set Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...

. . exporting table                 LINDA_DDL_TEST

Export terminated successfully without warnings.

--Message-Boundary-25011

Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'afiedt.buf'

create table 
       linda_ddl_test
     ( 
       column1 char(1),
       column2 number
     )

/

--Message-Boundary-25011

Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'exp_test.bat'

exp username/password_at_db-alias file=16jun00a.dmp rows=n log=16jun00a.log tables=linda_ddl_test

--Message-Boundary-25011

Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Text from file 'imp_test.bat'

imp username/password_at_db-alias file=16jun00a.dmp log=16ju00a2.log indexfile=test_ddl.txt full Received on Fri Jun 16 2000 - 17:42:37 CDT