I am trying to spool the below SQL to copy schema objects to another, but its not working.

select 'CREATE TABLE INTDTA.' ||table_name|| '(AS SELECT * FROM all_tables where owner ='ZRTDTA' and 1=2;)' 

Expected Output: create table INTDTA.test1 as select * from zrtdta.test1 where 1=2;

Did I miss anything?

2 Answers 2


If you are trying generate SQL to create empty tables for all tables then the SQL you have written might be okay. There is couple of errors which I have corrected as:

select 'CREATE TABLE INTDTA.' ||table_name|| ' AS SELECT * FROM ZRTDTA.' || table_name || ' where 1=2;' from all_tables where owner='ZRTDTA';

If you want to use single quotation mark(') inside another single quotation mark then you have to type it twice or for other options, see Literals.

According to the title of the question you wish to copy all tables including the rows to another schema. If you want so then you have two options.

  • If it is within the same database then you can use CTAS.
  • If it is between two different databases then you can either use EXPDP/IMPDP or CTAS with Database Link.

  • 2
    • @JSapkota- Hi, your query is working but its giving all tables details. but i need the output only for ZRTDTA tables as create table INTDTA.test1 as select * from zrtdta.test1 where 1=2; but your query is giving CREATE TABLE INTDTA.SYS_IOT_OVER_95547 AS SELECT * FROM all_tables where owner ='ZRTDTA' and 1=2;*
      – Naveen
      Commented Jul 7, 2016 at 3:31
    • @Naveen: I have edited the SQL.
      – atokpas
      Commented Jul 7, 2016 at 3:37

    CTAS (Create Table AS) will work as long as you don't need triggers or indexes or constraints. EXPDP/IMPDP with REMAP_SCHEMA will bring along all of these other objects. Use CONTENT=METADATA_ONLY if you don't want to include the data.

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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