Oracle - Clone Table - Structure, Data Constraints and All

Oracle - Clone table - Structure, data constraints and all

Take a look into dbms_metadata, especially its procedure dbms_metadata.get_ddl function (see this tahiti link).

So, in your case, you would first do a

select dbms_metadata.get_ddl('TABLE', 'SOURCETABLE') from dual;

As per be here now's comment: dont forget the dbms_metadata.get_dependent_ddl:

select dbms_metadata.get_dependent_ddl('TABLE', 'SOURCETABLE') from dual;

And then work from the given output.

How to duplicate a table with all its constrains in SQL*Plus?

I'd start with something like

set long 100000
select dbms_metadata.get_ddl('TABLE', 'TAB1', '<schemaname'>) from dual

This returns a create table statement for TAB1 (in schema <schemaname>). You can
then copy that statement and change the identfier TAB1 to TAB2. You should make sure that
you also change the names of all constraints since they must be unique in Oracle.

Finally, you'll want to do a insert into TAB2 select * from TAB1

How can I create a copy of an Oracle table without copying the data?

Just use a where clause that won't select any rows:

create table xyz_new as select * from xyz where 1=0;

Limitations

The following things will not be copied to the new table:

• sequences
• triggers
• indexes
• some constraints may not be copied
• materialized view logs

This also does not handle partitions

---

create full copy of existing table in oracle

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.

Creating Duplicate Table From Existing Table

Use this query to create the new table with the values from existing table

CREATE TABLE New_Table_name AS SELECT * FROM Existing_table_Name; 

Now you can get all the values from existing table into newly created table.

How to duplicate a table with constraints, indexes, etc. in APEX?

You could Generate DDL file. Especially effective if required coping multiple tables

With in Apex go to SQL Workshop > Utilities > Generate DDL > create script

Select the schema.

Select objects required (table,trigger etc)

Next then select objects names (table name, trigger name etc)

Also see Using Oracle Application Express Utilities

How can I create a copy of an Oracle table include trigger,sequence & index

Yes, there is a way. You should use DBMS_METADATA package (http://docs.oracle.com/cd/E11882_01/server.112/e22490/metadata_api.htm#SUTIL3619)

Some tutorial here: http://www.orafaq.com/node/807

You could also use SQL Developer and there is tab "SQL" for every table. Inside "SQL" is sql which contains all dependencies to recreate your table

---

Related Topics