Copy Structure of a Table


Usually, when we want to copy table, we use this command:

CREATE TABLE bck_customer AS
SELECT * FROM customers;

But I intended to copy a table, only copy the structure without data that already in the table.
So i have to modify that sql to do that, then the command:

CREATE TABLE bck_customer AS
SELECT * FROM customers
WHERE 1 = 2;

2 Responses

  1. I am working on Oracle 10g. I want copy a table with whole record and structure also and save to next location on another PC. give me command

  2. If you want to copy a table with whole record and structure to another PC that have another oracle schema, you can use oracle exp as the easiest way.

    EXP SCOTT/TIGER FILE=EMP.DMP TABLES=(EMP)

    but i you want to use some script to do that, there are two steps, ie:
    1. get create table script
    for example, we use table EMP
    SELECT DBMS_METADATA.GET_DDL( ‘TABLE’, ‘EMP’, ‘SCOTT’ ) FROM DUAL;
    ————————————————————————–
    CREATE TABLE “SCOTT”.”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),
    CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”)
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE “USERS” ENABLE,
    CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)
    REFERENCES “SCOTT”.”DEPT” (“DEPTNO”) 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 “USERS”

    2. generate insert statement
    use get_insert_script.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: