2

Is that possible to write copy all table include (data ,pk , fk .....) scripts on same schema with all object ? When I run the code below
create table testmember as (select * from member);

I can only bring table data . When i executed this code i did not see table keys ( FK , PK ) please somebody show me a way

UPDATE---- Hı i create all tables with data you can test it but be carrefully :) In my question again how can i add pk with this table ? 

    declare 
      Procedure test_copy_table is 
       sqlString VARCHAR2(2000) := '';
          Cursor tables is
         select * from user_tables;
      begin
        for rec in tables
         loop
         dbms_output.put_line(rec.table_name);
       sqlString:='CREATE TABLE T_' ||rec.table_name || ' AS (SELECT * FROM ' ||rec.table_name|| ')';
  dbms_output.put_line(sqlString);
  execute immediate sqlString;
  end loop;
   end;
begin
  test_copy_table();
 end;`

In this code part create all tables with data as i said before please add some more clear information.UPDATE---

Hı ı can get all primery key you can see below code thx for helpinn. This scripts helps you Thx for helping resolved. 

 procedure copy_pk_same_schema is
put_semicolumn varchar2(1);
  begin

for uc in (select *
             from user_constraints
            where constraint_name not like 'BIN$%'
              and constraint_name not like 'SYS_%'
              and constraint_type = 'P') loop
  -- dbms_output.put_line('--table/pk: ' || uc.table_name || ' ' ||
  --     uc.constraint_name);

  --   dbms_output.put_line('alter table T_' || uc.table_name || ' drop constraint ' || uc.constraint_name||';');
  if LENGTH('T_' || uc.table_name) > 30 then
    dbms_output.put_line('alter table T_' ||
                         substr(uc.table_name,
                                1,
                                (length(uc.table_name) - 5)) ||
                         ' add constraint');
  else
    dbms_output.put_line('alter table T_' || uc.table_name ||
                         ' add constraint');
  end if;

  if LENGTH('T_' || uc.constraint_name) > 30 then
    dbms_output.put_line('T_' ||
                         substr(uc.constraint_name,
                                1,
                                (length(uc.constraint_name) - 5)) ||
                         ' primary key (');
  else
    dbms_output.put_line('T_' || uc.constraint_name ||
                         ' primary key (');
  end if;

  put_semicolumn := 'F';
  for pk in (select *
               from user_cons_columns
              where constraint_name not like 'BIN$%'
                and table_name = uc.table_name
                and constraint_name = uc.constraint_name
              order by constraint_name, position) loop
    if put_semicolumn = 'F' then
      dbms_output.put_line(pk.column_name);
      put_semicolumn := 'T';
    else
      dbms_output.put_line(',' || pk.column_name);
    end if;
  end loop;

  dbms_output.put_line(');');
end loop;
end;
rusty1119
  • 41
  • 1
  • 2
  • 5
  • Not as a function of the create table as select syntax. You would first have to create the tables based on DDL and then insert data into, not use `create table X as (Select)` syntax. Consider: http://stackoverflow.com/questions/233870/how-can-i-create-a-copy-of-an-oracle-table-without-copying-the-data or http://dba.stackexchange.com/questions/18058/copy-complete-structure-of-a-table-in-oracle-without-using-backup – xQbert Jan 27 '15 at 14:28

1 Answers1

0

Indexes can be created by using below query. You can use the query and write the procedure as written for creating tables.

    SELECT uc.constraint_name,
           uc.table_name,
           listagg(uc.column_name, ',') within GROUP(ORDER BY uc.column_name)
      FROM user_cons_columns uc
     GROUP BY uc.constraint_name;

But this is limited for creating normal or unique indexes not for Primary or Foreign Key constraint and their default index. For that you have to take the DDL and create the Script which can execute in one go.

PLSQL_007
  • 215
  • 2
  • 8