BEGIN
-- 시노님 삭제
BEGIN
FOR C IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'NWISDBA'
) LOOP
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '|| C.TABLE_NAME || ';
END LOOP;
END;
-- 테이블삭제
BEGIN
FOR C IN (SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'NWISDBA'
) LOOP
EXECUTE IMMEDIATE 'DROP TABLE '|| C.OWNER || '.' || C.TABLE_NAME || ' CASCADE CONSTRAINTS PURGE';
END LOOP;
END;
-- 시퀀스 삭제
BEGIN
FOR C IN (SELECT SEQUENCE_OWNER,SEQUENCE_NAME FROM ALL_SEQUENCES
WHERE SEQUENCE_OWNER = 'NWISDBA'
) LOOP
EXECUTE IMMEDIATE 'DROP SEQUENCE '|| C.SEQUENCE_OWNER || '.' || C.SEQUENCE_NAME;
END LOOP;
END;
-- 뷰 삭제
BEGIN
FOR C IN (
SELECT OWNER,VIEW_NAME FROM ALL_VIEWS
WHERE OWNER = 'NWISDBA'
) LOOP
EXECUTE IMMEDIATE 'DROP VIEW '|| C.OWNER || '.' || C.VIEW_NAME;
END LOOP;
END;
-- 트리거 삭제
BEGIN
FOR C IN (SELECT OWNER, TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER = 'NWISDBA' )
LOOP
EXECUTE IMMEDIATE 'DROP TRIGGER '|| C.OWNER || '.' || C.TRIGGER_NAME;
END LOOP;
END;
--인덱스 삭제
BEGIN
FOR C IN (SELECT OWNER, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = 'NWISDBA' AND UNIQUENESS <> 'UNIQUE')
LOOP
EXECUTE IMMEDIATE 'DROP INDEX '|| C.OWNER || '.' || C.INDEX_NAME;
END LOOP;
END;
--프로시저 삭제
BEGIN
FOR C IN (SELECT OWNER, OBJECT_NAME FROM ALL_PROCEDURES WHERE OWNER = 'NWISDBA' AND OBJECT_TYPE = 'PROCEDURE')
LOOP
EXECUTE IMMEDIATE 'DROP PROCEDURE '|| C.OWNER || '.' || C.OBJECT_NAME;
END LOOP;
END;
--펑션 삭제
BEGIN
FOR C IN (SELECT OWNER, OBJECT_NAME FROM ALL_PROCEDURES WHERE OWNER = 'NWISDBA' AND OBJECT_TYPE = 'FUNCTION')
LOOP
EXECUTE IMMEDIATE 'DROP FUNCTION '|| C.OWNER || '.' || C.OBJECT_NAME;
END LOOP;
END;
END;
'DataBase > Oracle 11g - 12c' 카테고리의 다른 글
오라클 계정의 비밀번호 만료일 변경 (0) | 2020.06.24 |
---|---|
오라클 STARTUP, SHUTDOWN (0) | 2020.06.24 |
[Oracle] 오라클 각종 조회쿼리 및 테이블 설명 (0) | 2020.06.24 |
[Oracle] 테이블 및 인덱스 ANALYZE (0) | 2020.06.24 |
[Oracle] 테이블 정의서 추출 쿼리 (0) | 2020.06.24 |