Home » RDBMS Server » Server Administration » Table Script
Table Script [message #61278] Sat, 10 April 2004 04:09 Go to next message
MUHAMMAD SAFHAN GHAURI
Messages: 6
Registered: November 2000
Junior Member
HI,
I WANT TO GENERATE SQL SCRIPT OF TABLE CREATION.
PLEASE HELP ME.

MUHAMMAD SAFHAN GHAURI
Re: Table Script [message #61287 is a reply to message #61278] Mon, 12 April 2004 04:50 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
--
-- IF YOU ARE using 9i you can use this method.
--
dbadmin@constitution_lawd2 > get dummy
  1  set long 500000000000000
  2  set pagesize 1000
  3  set linesize 1000
  4  set trimspool on
  5  SET HEAD off
  6  SELECT DBMS_METADATA.GET_DDL('TABLE',D.TABLE_NAME)||';' FROM DBa_tables D
  7* where OWNER='DBADMIN' and TABLE_NAME='EMP';
dbadmin@constitution_lawd2 > @dummy

  CREATE TABLE "DBADMIN"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 ;

--
-- If using 8i, do this
-- export the table
-- import using indexfile=somefile
-- the file created will have your ddl ...

bash-2.03$ exp dbadmin@lawd file=dummy.dmp tables=emp statistics=none

Export: Release 9.2.0.4.0 - Production on Mon Apr 12 08:48:47 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
Export terminated successfully without warnings.

--
-- do the import ... it would do any impoort..instead will write to a fille u 
-- u have specified in indexfile option

bash-2.03$ imp dbadmin@lawd file=dummy.dmp indexfile=empddl.sql full=y

Import: Release 9.2.0.4.0 - Production on Mon Apr 12 08:49:38 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . skipping table "EMP"

Import terminated successfully without warnings.

--
-- here is the ddl of the table.
-- 
bash-2.03$ cat empddl.sql

REM  CREATE TABLE "DBADMIN"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"
REM  VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE,
REM  "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
REM  FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS ;
REM  ... 14 rows
REM  ALTER TABLE "DBADMIN"."EMP" ADD PRIMARY KEY ("EMPNO") USING INDEX
REM  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
REM  FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING ENABLE ;

[Updated on: Fri, 30 July 2010 09:22]

Report message to a moderator

Previous Topic: Why the Oracle should be start every time?
Next Topic: change the name of orapwd.ora file on HP UX
Goto Forum:
  


Current Time: Fri Sep 20 12:34:48 CDT 2024