create full copy of existing table in oracle

Question

I know that I can easily create a copy of a table with create table t1 select * from table2; But I need to copy:

• columns
• comments
• tablespace
• indexes
• grants
• triggers
• keys
• default values
• ...
• any other dependencies

Is there way to do it by pl/sql procedure? My Oracle version is 11R2.

The SQL of table:

-- Create table

    create table SCHEMA.MY_TABLE
    (
      id      number(1),
      name    varchar2(30),
      dat     date
    )
    tablespace MY_TS
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 10M
        next 1M
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the table 
    comment on table SCHEMA.MY_TABLE
      is 'MY TABLE';
    comment on column SCHEMA.MY_TABLE.ID
      is 'id';
    comment on column SCHEMA.MY_TABLE.NAME
      is 'name of operation';
    comment on column SCHEMA.MY_TABLE.DAT
      is 'date of operation';

    grant select on SCHEMA.MY_TABLE to PUBLIC;

Answer 1

You can use DBMS_REDEFINITION package, that thing can do a copy of whole table including comments etc. It will also "defragment" the new copy to occupy less space than the original table.

More info at https://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS042

Example by request:

So let's create a test schema, a table and fill it with some data:

CREATE USER "TEST_SCHEMA" IDENTIFIED BY "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST_SCHEMA";

CREATE TABLE "TEST_SCHEMA"."NAMES" ("ID" NUMBER, "NAME" VARCHAR2(25), PRIMARY KEY("ID"));

INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (1, 'joe');
INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (2, 'pete');
INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (3, 'mark');

Now we can check the content of the test table:

SELECT * FROM "TEST_SCHEMA"."NAMES";

Let's begin the copy process by creating empty table exactly as the source table looks like (using WHERE 1=0, which won't take any data).

CREATE TABLE "TEST_SCHEMA"."NAMES_COPY" AS SELECT * FROM "TEST_SCHEMA"."NAMES" "A1" WHERE 1=0;

Here you can setup paralellization etc, prepare data etc.. After everything is done, let's start the REDEFINITION process:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('TEST_SCHEMA','NAMES','NAMES_COPY');
END;
/

When the contents is copied, we need to copy every object that is dependent on the table (like triggers, indexes etc....)

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('TEST_SCHEMA',  'NAMES',    'NAMES_COPY',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

We can check if any errors occured during copying of dependencies with this query:

SELECT "OBJECT_NAME", "BASE_TABLE_NAME", "DDL_TXT" FROM DBA_REDEFINITION_ERRORS;

If everything is alright, we can finish the process:

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('TEST_SCHEMA', 'NAMES',    'NAMES_COPY');
END;
/

And voila, the new table is like the cloned sheep Dolly with everything in it:

SELECT * FROM "TEST_SCHEMA"."NAMES_COPY";

Now to cleaup the test schema, run these:

DROP TABLE "TEST_SCHEMA"."NAMES";
DROP TABLE "TEST_SCHEMA"."NAMES_COPY";
DROP USER "TEST_SCHEMA" CASCADE;

I hope it helped.

Answer 2

If you are using TOAD, SQL DEVELOPER you can access the Table SQL by using f4 in toad and by using search abject and then SQL in SQL developer. This will give you SQL script to create a table. This will have all Indexes, Default values, column structure, grants, columns comments, tablespace. For triggers, you can get this from DBA_triggers/Triggers section of the above method. Just change the name of the table and execute the statement and after that insert all the data from the base table to new table.