Home » Developer & Programmer » Designer » Extracting only the tablescripts for a schema
Extracting only the tablescripts for a schema [message #90690] Sun, 08 August 2004 22:54 Go to next message
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
Re: Extracting only the tablescripts for a schema [message #90691 is a reply to message #90690] Mon, 09 August 2004 11:14 Go to previous messageGo to next message
andrew again
Messages: 2574
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
Re: Extracting only the tablescripts for a schema [message #90692 is a reply to message #90691] Tue, 10 August 2004 00:20 Go to previous messageGo to next message
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 ...
Re: Extracting only the tablescripts for a schema [message #90694 is a reply to message #90692] Tue, 10 August 2004 08:51 Go to previous message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
see this link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7490088329317
Previous Topic: Can I connect Designer 6 client with 9i db
Next Topic: Oracle design - S.A.M.E
Goto Forum:
  


Current Time: Tue Sep 02 11:00:01 CDT 2014

Total time taken to generate the page: 0.08525 seconds