Oracle SQL Creating Copy of Table Using DBMS_metadata

Question

so I am trying to create a copy of a table using dbms_metadata. I know you can get the DDL of a table by executing DBMS_METADATA.get_DDL('Table','TABLENAME') so my question now is how can I use this to create a new identical table?

I know you can easily create a copy of a table with create table t1 select * from table2; etc. So how can I make use of dbms_metadata to create a new identical table? Any advice would be greatly appreciated. Thanks!

Answer 1

Instead of using simply text based replace, you can do this with the metadata API, which is a more robust solution. Lets say you have a table X.TABLE1:

create table x.table1 (id number, name varchar2(100), primary key(id));

You want to recreate it as Y.TABLE2, you could do the following:

set serveroutput on
declare
  metadata_handle number;
  transform_handle number;
  ddl_handle number;
  result_array sys.ku$_ddls;
begin
  metadata_handle := dbms_metadata.open('TABLE');
  transform_handle := dbms_metadata.add_transform(metadata_handle, 'MODIFY');
  dbms_metadata.set_remap_param(transform_handle, 'REMAP_NAME', 'TABLE1', 'TABLE2');
  dbms_metadata.set_remap_param(transform_handle, 'REMAP_SCHEMA', 'X', 'Y');

  ddl_handle := dbms_metadata.add_transform(metadata_handle, 'DDL');

  dbms_metadata.set_filter(metadata_handle, 'SCHEMA', 'X');
  dbms_metadata.set_filter(metadata_handle, 'NAME', 'TABLE1');

  loop
    result_array := dbms_metadata.fetch_ddl(metadata_handle);
    exit when result_array is null;
      for i in result_array.first..result_array.last loop
      dbms_output.put_line(result_array(i).ddltext);
    end loop;
  end loop; 
  dbms_metadata.close(metadata_handle);
end;
/

Which results in:

CREATE TABLE "Y"."TABLE2" 
   (    "ID" NUMBER, 
    "NAME" VARCHAR2(100), 
     PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" 

Obviously you can pass it to execute immediate, or just spool it.

Answer 2

If you want even more automation, then download SQLcl, then you can do fun things like

ctas current_table new_table

and it generates necessary code for you. e.g.,

SQL> ctas my_table new_table
  CREATE TABLE OWNER."NEW_TABLE"
   (    "OWNER",
        "OBJECT_NAME",
        "SUBOBJECT_NAME",
        "OBJECT_ID",
        "DATA_OBJECT_ID",
        "OBJECT_TYPE",
        "CREATED",
        "LAST_DDL_TIME",
        "TIMESTAMP",
        "STATUS",
        "TEMPORARY",
        "GENERATED",
        "SECONDARY",
        "NAMESPACE",
        "EDITION_NAME",
        "SHARING",
        "EDITIONABLE",
        "ORACLE_MAINTAINED"
   ) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
SQL>

all you need to do then is enter / and voila. Just remember, it is a cool tool but still in Beta but for most parts works very well.