전체 테이블 복사 Procedure

원문: https://blog.naver.com/qoxmfaktmxj/222810409487

CREATE OR REPLACE PROCEDURE SYS.프로시저명

IS

v_script VARCHAR2(2000);

CURSOR V_T_NAME IS

select

replace(dbms_metadata.get_ddl(‘TABLE’,’A계정의테이블명’,’A계정’),’“A계정명”.’,’“B계정명”.’)script

from all_users

WHERE USERNAME=‘A계정명’

;

---

BEGIN

FOR TN IN V_T_NAME LOOP

BEGIN

v_script := TN.script;

EXECUTE IMMEDIATE v_script;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(‘ QUERY ‘|| v_script);

DBMS_OUTPUT.PUT_LINE(‘ error ‘|| TN.script||’ : ‘||sqlerrm);

END;

END LOOP;

end;

---

특정 계정 전체 테이블 복사 -> sys계정 권한 필요