Re: Cloning database structure
- From: Kurt Van Meerbeeck <kurtvm@xxxxxxxxxx>
- To: nigel@xxxxxxxxxxxxxx, prabhu_adam@xxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 14 Oct 2006 16:01:05 +0200
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
>
>-----
>--
>//www.freelists.org/webpage/oracle-l
>
>
--
Kurt Van Meerbeeck
kurtvm@xxxxxxxxxx
kurt_van_meerbeeck@xxxxxx
dude@xxxxxxxxxx
http://www.ora600.org
Imagination is more important than knowledge... (A.Einstein)
You can have many different jobs and still be lazy... (H.Simpson)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
