1

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!

  • Maybe search and replace for the tablename and then pass the result to execute immediate? Is that really so difficult? – Colin 't Hart Feb 25 '15 at 20:15
  • Difficulty is subjective to experience. Thanks for the advice though mate! – Blued00d Feb 25 '15 at 20:23
  • If you know how to do a bit of DBMS_METADATA.get_DDL, do you know enough PL/SQL to change the table name and do an execute immediate? I think that's what @Colin'tHart was eluding to. You appear to show enough knowledge in your question.... – Philᵀᴹ Feb 25 '15 at 23:38
2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.