Home » RDBMS Server » Backup & Recovery » Clone large db to DDL-only db - EXP/IMP issue with init extents
| Clone large db to DDL-only db - EXP/IMP issue with init extents [message #506999] |
Thu, 12 May 2011 04:01  |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
Hi,
I am looking to create a DDL-only clone of a 2TB database. The purpose of this clone is to provide developers a preprod-like environment where they can test the syntax of their code changes before deploying to production. The database will reside on a seperate, small server.
As I only need the structure of the objects and not the data, my first port of call was EXP using rows=n. However, when I IMP to an indexfile I see that the initial extents have been defined on the tables (in some cases 1GB!). As stated my clone environment is small, so I would be looking to have these set at 1mb. Does anyone have any suggestions on how I can achieve this? I will want to be able to automate the cloning of the database as much as possible. Perhaps there's some other way other than EXP/IMP?
Thanks all.
F.
|
|
|
|
|
|
| Re: Clone large db to DDL-only db - EXP/IMP issue with init extents [message #507035 is a reply to message #506999] |
Thu, 12 May 2011 05:07  |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
Thanks Michel.
However when I specify COMPRESS=N the DDL for the table reverts to the same DDL as the original table definition, ie it gives me the same initial extent as when obtained via
select dbms_metadata.get_ddl('TABLE', table_name, owner)
In this case the initial extent on some tables is very large.
What I am looking for is a way to reduce the initial extent down to 1mb per table. Obviously I can edit the indexfile, but I am looking for a way to automate the clone so that it can be run nightly/ad hoc.
Thanks,
Firefly.
|
|
|
|
|
|
|
|
| Re: Clone large db to DDL-only db - EXP/IMP issue with init extents [message #507047 is a reply to message #507038] |
Thu, 12 May 2011 05:31 |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
Think I was able to answer this myself!
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
and then
select dbms_metadata.get_ddl('TABLE', table_name, owner) x
from dba_tables
where owner NOT IN
('ANONYMOUS', 'CSMIG', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'MDSYS', 'MGMT_VIEW', 'ORACLE_OCM', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WK_TEST', 'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'XS$NULL')
order by owner, table_name;
Sample output is then:
CREATE TABLE "USER"."TABLE1"
( "AC_ACCOUNT_ID" NUMBER(15,0),
"AC_ACCOUNT_DESC" VARCHAR2(50),
"AC_ATTRIBUTE1_DESC" VARCHAR2(150),
"AC_CHART_OF_ACC_DESC" VARCHAR2(30),
"AC_ACCOUNT_TYPE_ID" CHAR(1),
"AC_TEMPLATE_ID" NUMBER(15,0),
"AC_TEMPLATE_DESC" VARCHAR2(50),
"GE_SECURITY_KEY_ID" NUMBER(8,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PROD_DATA"
Firefly
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 07:24:54 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
