OraFAQ Forum: Designer » Extracting only the tablescripts for a schema

Home » Developer & Programmer » Designer » Extracting only the tablescripts for a schema

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extracting only the tablescripts for a schema [message #90690]

Sun, 08 August 2004 22:54

Aparna Venkatraman
Messages: 6
Registered: April 2002

Junior Member

Basically , I need a script that would extract the tablescripts �alone (creat table ) of a given schema ,from�a given database ,with the storage clause specifying the tablespaces alone.

And when i execute this script, after i drop all the tables in the schema, this should create the table structures without the data.

Please help

Regards,

Aparna

Report message to a moderator

Re: Extracting only the tablescripts for a schema [message #90691 is a reply to message #90690]

Mon, 09 August 2004 11:14

andrew again
Messages: 2577
Registered: March 2000

Senior Member

The most reliable way would be:

exp scott/tiger tables=t1,t2 rows=n file=tmp.dmp
imp scott/tiger file=tmp.dmp indexfile=tmp.sql

OR

exp scott/tiger owner=scott rows=n file=scott.dmp
imp scott/tiger file=scott.dmp show=y log=scott_ddl.txt

These 2 methods would give you other storage info too though.

You can try dbms_metadata in 9i. You'll need to read all the documentation to determine how to control the level of detail returned.

set long 10000
set pagesize 0

scott@ORA9I.WORLD>  select dbms_metadata.get_ddl( 'TABLE', 'EMP' ) from dual
  2  /

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO")
  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE,
         CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
  REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

For me - I would just use TOAD - it's much easier...
Database > Export > Table Scripts

Report message to a moderator

Re: Extracting only the tablescripts for a schema [message #90692 is a reply to message #90691]

Tue, 10 August 2004 00:20

Aparna Venkatraman
Messages: 6
Registered: April 2002

Junior Member

Thanks ,

I have tried this optionof Export with rows=n. But i have a problem with that.
The Table structure which gets created ,with this exp command,occupies larger inital extent even without the data. this is becos, it is trying to generate the script along with the storage structure as available in the oracle dictionary .
In toad we can actually control the storage clause ,and mention only the tablespace details.
Can we have such options with oracle itself?

Regards,
Aparna

Please help ...

Report message to a moderator

Re: Extracting only the tablescripts for a schema [message #90694 is a reply to message #90692]

Tue, 10 August 2004 08:51

andrew again
Messages: 2577
Registered: March 2000

Senior Member

see this link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7490088329317

Report message to a moderator

Previous Topic:	Can I connect Designer 6 client with 9i db
Next Topic:	Oracle design - S.A.M.E

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 02:15:40 CST 2024