Copy all tables from a schema to a new schema using PLSQL

الجمعة, مارس 02, 2018

Hello, before we got into the CODE, I want to apologize for my English. I'm CS student, so this code (and what I explain) may not be the correct way to do it, if you think you can make this code better, please share your ideas with me in the comments

last week the professor in my university asked us to make procedure to copy all the tables from a schema, with all constraints and data to a new schema

At first I had no idea where to start, searching on google, no result, just some incomplete information here and there without any examples or clear explanations that a student can understand.

so After 3 days of continuous searching and trying the commands here is the result:

Make sure  that owner of the source Schema having all privileges.

CREATE OR REPLACE
PROCEDURE copy_all( s_user VARCHAR2, d_user VARCHAR2 ) IS
  v_array apex_application_global.vc_arr2;
  code2 VARCHAR2(30000);
  user_exist pls_integer;
  table_exist EXCEPTION;
  pragma EXCEPTION_INIT(table_exist, -00955);
BEGIN
  --Create user owner of the scheme if not exist and grant privileges
  SELECT COUNT(*) INTO user_exist FROM all_users WHERE USERNAME=d_user;
  IF user_exist = 0 THEN
    EXECUTE immediate 'create user '||d_user||' identified by '||d_user;
    EXECUTE immediate 'grant connect to '||d_user;
    EXECUTE immediate 'grant all privileges to '||d_user;
  END IF;
  
  -- define some parameters to the function DBMS_METADBMS_METADATA.GET_DDL
  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', false); -- no ';' at the end of the instruction
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false); -- remove some grabage at the end
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false); -- no foreign keys
  
  -- Create tables without foreign keys
  FOR code_line IN (SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name, s_user)AS tab_code FROM USER_TABLES u ) LOOP
    code2 := code_line.tab_code;
    code2 := REPLACE(code2, q'{"}'||s_user||q'{"}', q'{"}'||d_user||q'{"}');
    --dbms_output.put_line(code2);
    EXECUTE immediate code2;
  END LOOP;
  
  --Inserting the data
  FOR table_record IN (SELECT table_name FROM all_tables WHERE owner=s_user ) LOOP
    EXECUTE immediate 'insert into '||d_user||'.'||table_record.table_name||'  select * from '||s_user||'.'||table_record.table_name;
  END LOOP;
  
  ---get back the sql terminator ';' this will be needid the split the statment, since EXECUTE immediate can run one command only at a time
  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
  
  -- Insertion des cles etrangers
  FOR i IN (SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, s_user) DDL
  FROM user_tables t WHERE EXISTS (SELECT 1 FROM ALL_CONSTRAINTS WHERE table_name    = t.table_name AND constraint_type = 'R')) LOOP
    v_array := apex_util.string_to_table(i.ddl, ';');
    FOR i IN 1..v_array.count LOOP
      v_array(i)            := REPLACE(v_array(i), q'{"}'||s_user||q'{"}', q'{"}'||d_user||q'{"}');
      IF LENGTH(v_array(i)) != 0 THEN
        EXECUTE immediate(v_array(i));
      END IF;
      --dbms_output.put_line(v_array(i));
    END LOOP;
  END LOOP;
  
EXCEPTION
WHEN table_exist THEN
  dbms_output.put_line('table exist');
END;

شارك المقال

مقالات مشابهة

0 comments