Oracle SQL Creating Copy of Table Using DBMS_metadata

dbmsddloracletable

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!

Best Answer

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.

Related Question