Re: Cloning database structure

From: Kurt Van Meerbeeck <kurtvm_at_pandora.be>
Date: Sat, 14 Oct 2006 16:01:05 +0200
Message-Id: <4.1.20061014155331.01f43158@in.pandora.be>

Hi,

You could use Pretoria for this. It will :

- pretty print the indexfile, 
- manipulate the storage clauses
- seperate table/index/constraint ddl in different files

See http://pretoria.sourceforge.net
http://sourceforge.net/projects/pretoria

Make a no-rows export as explained before - and create the indexfile (imp indexfile=myddl.sql)
Then you can create a storage parameter file for example : "DWH"."CUSTOMERS" INITIAL 100M NEXT 100M PCTFREE 5 TABLESPACE CUST

DEFAULT_TABLE("DWH") INITIAL 1M NEXT 1M TABLESPACE DWH_DATA
DEFAULT_TABLE ("DWHADMIN") INITIAL 512K NEXT 512K TABLESPACE DWH_ADMIN_DATA
DEFAULT_TABLE INTIAL 128K NEXT 128K TABLESPACE DATA

"DWH"."CUSTOMERS_IDX" INITIAL 20M NEXT 20M TABLESPACE CUST_IDX DEFAULT_INDEX("DWH") TABLESPACE INDEX DWH_INDX DEFAULT_INDEX TABLESPACE INDX Using the above as a storagefile for Pretoria, the following things will happen (Read - DDL will be created):
· the table DWH.CUSTOMER will get equal initial & next extents of 100M, pct free of 5, and will be placed in tablespace CUST · all other tables of owner DWH will get equal initial & next extents of 1M, and will be placed in tablespace DWH_DATA · all tables of owner DWHADMIN will get equal initial & next extents of 512K, and will be placed in tablespace DWH_ADMIN_DATA · all tables not owned by DWH or DWHADMIN will get equal initial & next extents of 128K, and will be placed in tablespace DATA · the index DWH.CUSTOMER_IDX will get equal initial & next extents of 20M,
and will be placed in tablespace CUST_IDX

·       all other indexes of owner DWH will be put in tablespace DWH_INDX 
·       all other indexes not owned by DWH will be put in tablespace INDX

Allthough DBMS_METADATA exists for some time - now and then, Pretoria is still handy.
Oh - it can also strip the storage clauses from the ddl. Anyways - you could write it yourself with awk/sed or perl - this one is in java and runs pretty well on every platform ;-)

Cheers,
Kurt

At 06:29 14/10/2006 -0700, Nigel Thomas wrote:
>Prabhu
>>Is there any way to import the object with the default storage clause or any
>>other methed we can do for this. We can't clone db due to the space issue.
>
>You can make a no-rows export of your warehouse and then use the imp option
>indexfile=myddl.sql to generate a script with the create table / index /
>constraint statements, then edit that file. If you are on *nix it is pretty
>easy to sed or awk the results to adjust tablespace names, initial extents,
>etc. The CREATE TABLEs are all REM'd out, so the first thing to do is to
>remove those comments.
>
>See
>http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch02.htm
>#1005500
>
>HTH
>
>Regards Nigel
>
>-----
>--
>http://www.freelists.org/webpage/oracle-l
>
>

-- 
Kurt Van Meerbeeck
kurtvm_at_pandora.be
kurt_van_meerbeeck_at_axi.be
dude_at_ora600.org
http://www.ora600.org

Imagination is more important than knowledge... (A.Einstein)

You can have many different jobs and still be lazy... (H.Simpson)




--
http://www.freelists.org/webpage/oracle-l

Received on Sat Oct 14 2006 - 09:01:05 CDT