|
Re: Table Script [message #61287 is a reply to message #61278] |
Mon, 12 April 2004 04:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10708 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
|
|
|