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 Go to next message
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 #507009 is a reply to message #506999] Thu, 12 May 2011 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However, when I IMP to an indexfile I see that the initial extents have been defined on the tables (in some cases 1GB!).

Add "compress=n" to your exp command.

Better use Data Pump instead with CONTENT=METADATA_ONLY option.

Regards
Michel
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 Go to previous messageGo to next message
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 #507036 is a reply to message #507035] Thu, 12 May 2011 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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

Oracle does not do it, so you have to do it by yourself, for instance using "sed".
(By the way, how Oracle could know the "ad hoc" size?)

Regards
Michel
Re: Clone large db to DDL-only db - EXP/IMP issue with init extents [message #507038 is a reply to message #507036] Thu, 12 May 2011 05:12 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Thanks, I suppose I was looking for a way to extract the DDL without the storage parameters for the table. That way when I run the DDL on the clone database (with LMT) tablespaces the initial extents would be small.

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 Go to previous messageGo to next message
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
Re: Clone large db to DDL-only db - EXP/IMP issue with init extents [message #507103 is a reply to message #507047] Thu, 12 May 2011 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
>Clone large db to DDL-only db - EXP/IMP issue with init extents
>Think I was able to answer this myself!
I do not see anything involving EXTENT in result set?
Re: Clone large db to DDL-only db - EXP/IMP issue with init extents [message #507104 is a reply to message #507103] Thu, 12 May 2011 08:55 Go to previous message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
That's true, but I'm using Locally Managed Tablespace (with small init/next extents) so it creates the table fine.
Previous Topic: Restoring RMAN Backup on another server
Next Topic: RMAN
Goto Forum:
  


Current Time: Wed Jun 19 04:59:16 CDT 2019