YesXYZ

아딸이 함께 만드는 세상

[Oracle] 스키마 삭제 시 반드시 체크해야 할 10가지 핵심 항목

by · 2025년 12월 19일

회사에서 사용하는 데이터베이스에 Locked and/or Expired Schema가 너무 많아 데이터베이스를 Full Backup받은 이후에 삭제하기로 하였습니다. 데이터베이스의 Migration과 신규 시스템 구축을 반복하면서 서비스가 종료되었거나 이관하지 않은 시스템에서 사용하던 스키마를 정리하지 않고 그대로 사용한 결과, “Rolling a snowball”처럼 감당할 수 없을 만큼 쌓여버렸습니다. 

set verify off
set escape '\';
col schema_type for a25 heading "Schema Type";
col locked for 9999999 heading "Locked";
col expired for 9999999 heading "Expired";
col locked_expired for 9999999 heading "Locked|Expired";
col active for 9999999 heading "Active";
SELECT 
    CASE 
        WHEN username IN (
        -- 핵심 관리자 및 보안
        'SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM',
        -- XML 및 검색 엔진
        'XDB', 'ANONYMOUS', 'CTXSYS',
        -- 멀티미디어 및 공간 데이터
        'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
        -- 워크플로우 및 데이터 관리
        'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT',
        -- 분석 및 OLAP
        'OLAPSYS', 'MGMT_VIEW'
        ) 
        THEN 'INTERNAL SCHEMA' 
        ELSE 'USER SCHEMA' 
    END AS schema_type,
    SUM(CASE WHEN account_status = 'LOCKED' THEN 1 ELSE 0 END) AS locked,
    SUM(CASE WHEN account_status = 'EXPIRED' THEN 1 ELSE 0 END) AS expired,
    SUM(CASE WHEN account_status = 'EXPIRED \& LOCKED' THEN 1 ELSE 0 END) AS locked_expired,
    SUM(CASE WHEN account_status = 'OPEN' THEN 1 ELSE 0 END) AS active
FROM dba_users
GROUP BY 
    CASE 
        WHEN username IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS',
                          'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 
                          'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW')
        THEN 'INTERNAL SCHEMA' 
        ELSE 'USER SCHEMA' 
    END;

                                              Locked
Schema Type                 Locked  Expired  Expired   Active
------------------------- -------- -------- -------- --------
INTERNAL SCHEMA                  0        0        0        2
USER SCHEMA                     ??        ?        0       14


SELECT USERNAME, ACCOUNT_STATUS, DEFAULT_TABLESPACE, CREATED
FROM   DBA_USERS
WHERE  USERNAME NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS',
                        'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
                        'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW')
ORDER BY USERNAME;

큰 맘을 먹고 정리하기로 했지만, 막상 정리하려니 겁이 덜컥. “전임자들도 하지 않는 일을 내가 굳이”라는 생각이 들더군요. 그래도 할 건 해야죠. 2026년 새해 목표 중의 하나로 설정해서 차근차근 하나하나 삭제해 나가야겠습니다.
먼저 삭제 대상 스키마의 객체를 참조하는 View, Synonyms, Stored procedures, Functions, Packages 또는 Database links 와 같은 다른 스키마의 종속 객체를 확인해야 합니다. CASCADE 옵션을 사용하면 이러한 객체가 무효화되거나 삭제된 스키마의 테이블을 가리키는 참조 무결성 제약 조건(다른 스키마의 Foreign keys)이 삭제되어 다른 애플리케이션에서 오류가 발생할 수 있기 때문입니다. 그리고 스키마를 바로 삭제하지 말고 ALTER USER 삭제할_스키마_명 LOCK; 을 먼저 수행하여 며칠간 애플리케이션 오류가 발생하는지 지켜보는 게 좋습니다.
데이터 정합성과 서비스 영향도를 최소화하기 위해 Query data dictionary views(예: DBA_DEPENDENCIES, DBA_OBJECTS)를 쿼리하여 이러한 종속성을 식별하는 등 오라클에서 특정 스키마를 삭제(DROP USER 삭제할_스키마_명 CASCADE;)하기 전에 반드시 체크해야 할 10가지 핵심 항목을 정리하고자 합니다.
참고로 스키마 삭제만으로는 OS의 디스크 여유 공간이 즉시 늘어나지 않습니다. 스키마를 삭제한 후, 물리적인 디스크 용량을 늘리고 싶다면 해당 스키마가 전용으로 사용하던 테이블스페이스를 통째로 삭제하거나 데이터 파일의 크기를 축소해야 합니다.

  1. 객체 권한 (Grants)
  2. 외래 키 참조 (Foreign Key)
  3. 공용 시노님 (Public Synonym)
  4. 데이터베이스 링크 (Database Link)
  5. 스케줄러 및 잡 (Scheduler Jobs)
  6. 컴파일 의존성 (Object Dependencies)
  7. 소스코드 확인 (Source Code)
  8. 테이블스페이스 확인 (Tablespace)
  9. 디렉토리 객체 확인 (Directory)
  10. 트리거 이벤트 확인 (Trigger)
1. 객체 권한 (Grants)
  • 해당 스키마의 오브젝트(테이블, 뷰 등)를 다른 누가 사용하고 있는지 확인합니다. 삭제 시 이 모든 권한이 사라져 관련 프로그램이 중단될 수 있습니다.
  • 체크 대상: DBA_TAB_PRIVS, ROLE_TAB_PRIVS, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_COL_PRIVS 
    SELECT GRANTEE, TABLE_NAME, PRIVILEGE 
FROM DBA_TAB_PRIVS 
WHERE OWNER = '삭제할_스키마_명';

SELECT ROLE, TABLE_NAME, PRIVILEGE 
FROM ROLE_TAB_PRIVS 
WHERE OWNER = '삭제할_스키마_명';

SELECT GRANTEE, GRANTED_ROLE 
FROM DBA_ROLE_PRIVS 
WHERE GRANTED_ROLE IN (SELECT ROLE FROM ROLE_TAB_PRIVS WHERE OWNER = '삭제할_스키마_명');

SELECT *
FROM DBA_SYS_PRIVS 
WHERE GRANTEE = '삭제할_스키마_명';

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE 
FROM DBA_COL_PRIVS 
WHERE OWNER = '삭제할_스키마_명';
2. 외래 키 참조 (Foreign Key)
  • 삭제하려는 스키마의 테이블을 다른 스키마에서 외래 키(Foreign Key)로 참조하고 있는지 확인해야 합니다. CASCADE 옵션을 쓰면 제약 조건은 끊기지만, 참조하던 쪽의 데이터 정합성에 문제가 생길 수 있습니다.
  • 체크 대상: DBA_CONSTRAINTS (Constraint Type = ‘R’) 
    SELECT LPAD(' ', 2 * (LEVEL - 1)) || CHILD_OWNER AS CHILD_OWNER,
       CHILD_TABLE, CONSTRAINT_NAME, PARENT_OWNER, PARENT_TABLE
FROM (
    SELECT
        A.OWNER AS CHILD_OWNER, 
        A.TABLE_NAME AS CHILD_TABLE,
        A.CONSTRAINT_NAME, 
        B.OWNER AS PARENT_OWNER,
        B.TABLE_NAME AS PARENT_TABLE
    FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
    WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
      AND A.R_OWNER = B.OWNER
      AND A.CONSTRAINT_TYPE = 'R'
)
CONNECT BY NOCYCLE PRIOR CHILD_OWNER = PARENT_OWNER 
               AND PRIOR CHILD_TABLE = PARENT_TABLE
START WITH PARENT_OWNER = UPPER('삭제할_스키마_명');
3. 공용 시노님 (Public Synonym)
  • 삭제할 스키마의 오브젝트를 가리키는 Public Synonym이 있는지 확인하세요. 스키마가 삭제되면 이 시노님들은 ‘껍데기(Invalid)’만 남게 되어, 이를 사용하는 다른 쿼리들이 모두 실패하게 됩니다. DROP PUBLIC SYNONYM '삭제할_시니님_명'; 명령어를 실행하여 공용 시노님을 삭제합니다.
  • 체크 대상: DBA_SYNONYMS 
    SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_SYNONYMS
WHERE TABLE_OWNER = '삭제할_스키마_명';
4. 데이터베이스 링크 (Database Links)
  • 해당 사용자가 생성한 DB Link가 있는지 확인해야 합니다. 특히 다른 사용자들이 이 유저가 만든 DB Link를 통해 외부 데이터를 가져오고 있었다면 서비스 장애로 이어집니다.
  • Private DB Link는 스키마 삭제와 함께 삭제되지만, SYS/SYSTEM 스키마에 의해 생성된 Public DB Link는 DROP PUBLIC DATABASE LINK '삭제한_DB링크_명'; 명령어를 실행하여 삭제합니다.
  • 체크 대상: DBA_DB_LINKS 
    SELECT OWNER, DB_LINK, USERNAME, HOST 
FROM DBA_DB_LINKS 
WHERE OWNER = '삭제할_스키마_명' OR USERNAME = '&v_owner' OR DB_LINK LIKE '%삭제할_스키마_명%' ;
5. 스케줄러 잡 (Scheduler Jobs)
  • 해당 스키마 소유로 돌아가고 있는 자동화 작업이 있는지 확인하세요. DBMS_SCHEDULER나 DBMS_JOB에 등록된 작업은 사용자가 삭제되면 함께 사라지거나 중단됩니다.
  • 체크 대상: DBA_SCHEDULER_JOBS 
    SELECT OWNER, JOB_NAME, STATE, LAST_START_DATE
FROM DBA_SCHEDULER_JOBS
WHERE OWNER = '삭제할_스키마_명';
  • 삭제할 스키마의 현재 실행 중인 스케줄러 잡이 있는지 아래와 같이 확인 및 삭제합니다. DROP USER를 하기 전에 DBMS_SCHEDULER.DROP_JOB으로 먼저 지워주는 것이 Best Practice라고 하네요! 
    -- 현재 실행 중인 잡 확인
SELECT OWNER, JOB_NAME, SESSION_ID 
FROM DBA_SCHEDULER_RUNNING_JOBS 
WHERE OWNER = '삭제할_스키마';

-- 실행 중인 잡 강제 중지
EXEC DBMS_SCHEDULER.STOP_JOB('스키마명.잡이름');

-- 잡 비활성화
EXEC DBMS_SCHEDULER.DISABLE('스키마명.잡이름');

-- 잡 삭제
EXEC DBMS_SCHEDULER.DROP_JOB('스키마명.잡이름');
  • 데이터의 이동이나 외부 자원 접근 시에 Credential을 확인합니다. 7단계 소스코드 확인과 동일한 절차로 Credential이 사용되는 Procedure나 Function 등을 검색합니다. 
    SELECT OWNER, CREDENTIAL_NAME, USERNAME, WINDOWS_DOMAIN
FROM   DBA_SCHEDULER_CREDENTIALS
WHERE  OWNER = '삭제할_스키마';
6. 컴파일 의존성 (Object Dependencies, Invalid Objects)
  • 다른 스키마의 프로시저, 함수, 뷰가 삭제될 스키마의 테이블을 사용하고 있는지 확인해야 합니다. 이 경우 스키마 삭제 후 다른 유저의 프로그램이 INVALID 상태가 됩니다.
  • 스키마 삭제에 확신이 없으시다면 Audit Trail 기능을 사용해서 의심스러운 객체의 사용 여부를 일정 기간 모니터링 한 후 삭제할 것을 추천드립니다.
  • 체크 대상: DBA_DEPENDENCIES 
    SELECT OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_TYPE
FROM   DBA_DEPENDENCIES
WHERE  REFERENCED_OWNER = '삭제할_스키마_명'
AND    OWNER != '삭제할_스키마_명';
7. 소스코드 확인 (Source Codes)
  • 스키마 삭제 전 영향도를 평가할 때, 관리자는 반드시 이 뷰를 봐야 합니다. 그래야 내가 모르는 다른 유저의 숨겨진 로직까지 다 찾아낼 수 있기 때문입니다.
  • 다른 스키마가 작성한 Procedure나 Function 등에 삭제하고자 하는 스키마의 오브젝트가 참조되어 있을 수 있습니다.
  • 체크 대상: DBA_SOURCE 
    SELECT DISTINCT OWNER, NAME, TYPE
FROM   DBA_SOURCE
WHERE  UPPER(TEXT) LIKE '%'||'삭제할_스키마_명'||'.%'
AND    OWNER <> '삭제할_스키마_명';
8. 테이블스페이스 확인 (Tablespace)
  • DROP TABLESPACE 테이블스페이스명을 사용하여 테이블스페이스를 삭제하기 전에 다른 스키마에서도 해당 테이블스페이스를 사용하고 있는지 확인해야 합니다.
  • DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES 명령를 실행하면 데이터파일까지 삭제됩니다.
  • 체크 대상: DBA_SEGMENTS, DBA_DATA_FILES 
    SELECT A.OWNER, A.TABLESPACE_NAME, A.TS_USED_MB,
       B.FILE_NAME, B.BYTES / 1024 / 1024 AS FILE_MB
FROM  (SELECT OWNER, TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) AS TS_USED_MB
       FROM   DBA_SEGMENTS
       WHERE  OWNER = '삭제할_스키마_명'
       GROUP BY OWNER, TABLESPACE_NAME) A,
       DBA_DATA_FILES B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1, 2, 4;
9. 디렉토리 객체 확인 (Directory)
  • 디렉토리는 특정 유저 소유가 아니라 DB 전체 공유 객체입니다. 특정 유저의 파일 입출력을 위해 생성된 디렉토리는 DROP DIRECTORY '삭제할_디렉토리_명'; 명령어를 실행하여 수동으로 지워줘야 합니다.
  • ls -d DIRECTORY_PATH를 실행하여 해당 디렉토리가 물리적으로 존재하는지 확인합니다.
  • 체크 대상: DBA_DIRECTORIES 
    SELECT OWNER, DIRECTORY_NAME, 'ls -d ' || DIRECTORY_PATH AS PATH_CHECK
FROM   DBA_DIRECTORIES
WHERE  OWNER = '삭제할_스키마_명';
10. 트리거 이벤트 확인 (Trigger)
  • 다른 유저(User_B)가 삭제 대상 스키마(User_A)의 테이블을 감시하는 트리거를 생성한 경우, User_A를 삭제하면 그 테이블이 사라지므로 User_B가 소유한 트리거는 INVALID가 되며 삭제되지 않고 찌꺼기로 남습니다.
  • 특히 SYS 유저가 소유한 트리거에 User_A의 테이블이 포함되되어 있는 경우, 이 시스템 트리거는 INVALID 상태가 되며, 최악의 경우 다른 모든 유저의 로그인이나 DDL 작업이 중단되는 장애가 발생할 수 있습니다.
  • ALTER TRIGGER SYS.트리거_이름 DISABLE; 명령어를 먼저 실행한 후 며칠간 모니터링 후 문제가 없다면, DROP TRIGGER SYS.트리거_이름; 명령어를 실행합니다.
  • 체크 대상: DBA_TRIGGERS 
    SELECT OWNER, TRIGGER_NAME, TABLE_OWNER, TABLE_NAME, TRIGGER_TYPE, TRIGGERING_EVENT
FROM   DBA_TRIGGERS
WHERE  OWNER <> '&v_owner'
AND    TABLE_OWNER = '&v_owner';
아래는 10가지 항목을 한꺼번에 점검할 수 있는 쉘 스크립트입니다. 많은 활용바랍니다.
#!/bin/bash

SCRIPT_NAME=$(basename "$0")
OUT_FILE="${SCRIPT_NAME%.*}"

read -p "(Input schema name you want delete): " target_schema
#upper case
#target_schema=$(echo "$target_schema" | tr '[:lower:]' '[:upper:]')
target_schema=${target_schema^^}

# Run the SQL script using sqlplus
sqlplus -s  "/as sysdba" <"${OUT_FILE}_${target_schema}_$(date +%Y%m%d_%H%M%S).log"
set sqlprompt ""
set feedback off
set linesize 9000
set pagesize 50000
set verify off
set escape '\'

DEFINE v_owner = '${target_schema}'

prompt
prompt *********************************************************
prompt [0] &v_owner Information(Schema DDL)
prompt *********************************************************
set long 20000;
COL SCHEMA_DDL FOR A150 WORD_WRAPPED;
SELECT DBMS_METADATA.GET_DDL('USER', '&v_owner') AS SCHEMA_DDL
FROM DBA_USERS 
WHERE USERNAME = '&v_owner';

prompt
COL ROLE_GRANT_DDL FOR A150 WORD_WRAPPED;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&v_owner') AS ROLE_GRANT_DDL
FROM DBA_ROLE_PRIVS 
WHERE GRANTEE = '&v_owner' AND ROWNUM = 1;

prompt
prompt *********************************************************
prompt [0] Active Sessions (Must be terminated before DROP)
prompt *********************************************************
COL MACHINE FOR A30
COL PROGRAM FOR A50
SELECT SID, SERIAL#, STATUS, MACHINE, PROGRAM 
FROM V\$SESSION 
WHERE USERNAME = '&v_owner';

prompt
prompt *********************************************************
prompt [1] Object Grants (Direct, Role, System, Column)
prompt *********************************************************
prompt 1-1. Direct Object Grants
prompt (SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE '%'||'\&table_name'||'%' AND OWNER <> '&v_owner';)
COL GRANTEE FOR A20
COL TABLE_NAME FOR A30
COL PRIVILEGE FOR A25
COL GRANTABLE FOR A10
SELECT GRANTEE, TABLE_NAME, PRIVILEGE, GRANTABLE
FROM DBA_TAB_PRIVS
WHERE OWNER = '&v_owner' AND GRANTEE != '&v_owner'
ORDER BY 1, 2;

prompt
COL SOURCE_CHECK FOR A100
SELECT 'SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE ''%' || TABLE_NAME || '%'' AND OWNER = ''' || GRANTEE || ''';' AS SOURCE_CHECK
FROM DBA_TAB_PRIVS
WHERE OWNER = '&v_owner' AND GRANTEE != '&v_owner'
GROUP BY GRANTEE, TABLE_NAME
ORDER BY 1;

prompt
prompt 1-2. Role Grants
COL ROLE FOR A20
SELECT ROLE, TABLE_NAME, PRIVILEGE 
FROM ROLE_TAB_PRIVS 
WHERE OWNER = '&v_owner';

prompt
prompt 1-3. System Grants
COL ADMIN_OPTION FOR A15
SELECT *
FROM DBA_SYS_PRIVS 
WHERE GRANTEE = '&v_owner';

prompt
prompt 1-4. Column Grants
SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE 
FROM DBA_COL_PRIVS 
WHERE OWNER = '&v_owner';

prompt
COL SOURCE_CHECK FOR A100
SELECT 'SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE ''%' || COLUMN_NAME || '%'' AND OWNER = ''' || GRANTEE || ''';' AS SOURCE_CHECK
FROM DBA_COL_PRIVS
WHERE OWNER = '&v_owner';

prompt
prompt *********************************************************
prompt [2] Foreign Keys
prompt *********************************************************
COL CHILD_OWNER FOR A20
COL CHILD_TABLE FOR A30
COL CONSTRAINT_NAME FOR A30
COL PARENT_OWNER FOR A20
COL PARENT_TABLE FOR A30
SELECT LPAD(' ', 2 * (LEVEL - 1)) || CHILD_OWNER AS CHILD_OWNER,
       CHILD_TABLE, CONSTRAINT_NAME, PARENT_OWNER, PARENT_TABLE
FROM (
    SELECT
        A.OWNER AS CHILD_OWNER, 
        A.TABLE_NAME AS CHILD_TABLE,
        A.CONSTRAINT_NAME, 
        B.OWNER AS PARENT_OWNER,
        B.TABLE_NAME AS PARENT_TABLE
    FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
    WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
      AND A.R_OWNER = B.OWNER
      AND A.CONSTRAINT_TYPE = 'R'
)
CONNECT BY NOCYCLE PRIOR CHILD_OWNER = PARENT_OWNER 
               AND PRIOR CHILD_TABLE = PARENT_TABLE
START WITH PARENT_OWNER = UPPER('&v_owner');

prompt
prompt *********************************************************
prompt [3] Synonym
prompt *********************************************************
COL OWNER FOR A20
COL SYNONYM_NAME FOR A30
COL TABLE_OWNER FOR A20
COL TABLE_NAME FOR A30
COL OBJECT_TYPE FOR A20
SELECT s.OWNER, s.SYNONYM_NAME, s.TABLE_OWNER, s.TABLE_NAME, o.OBJECT_TYPE
FROM DBA_SYNONYMS s, DBA_OBJECTS o
WHERE s.TABLE_OWNER = o.OWNER 
AND   s.TABLE_NAME = o.OBJECT_NAME
AND   s.TABLE_OWNER = '&v_owner';

prompt
prompt *********************************************************
prompt [4] Database Links
prompt *********************************************************
prompt 4-1. DB Links Owned by '&v_owner' (Will be dropped)
COL DB_LINK FOR A50
COL USERNAME FOR A20
COL HOST FOR A50
SELECT OWNER, DB_LINK, USERNAME, HOST 
FROM DBA_DB_LINKS 
WHERE OWNER = '&v_owner';

prompt
prompt 4-2. DB Links Using '&v_owner' as Credentials (Will be broken)
prompt (SELECT DBMS_METADATA.GET_DDL('DB_LINK', '\&db_link_name', '\&OWNER') FROM DUAL;)
prompt (DROP [PUBLIC] DATABASE LINK \&v_public_dblink;)
COL DB_LINK FOR A50
SELECT OWNER, DB_LINK, USERNAME, HOST
FROM DBA_DB_LINKS
WHERE OWNER <> '&v_owner' 
AND (USERNAME = '&v_owner' OR DB_LINK LIKE '%'||'&v_owner'||'%');

prompt
COL DBLINK_DDL FOR A100
SELECT 'SELECT DBMS_METADATA.GET_DDL(''DB_LINK'', ''' || DB_LINK || ''', ''' || OWNER || ''') AS GET_DDL FROM DUAL;' AS DBLINK_DDL
FROM DBA_DB_LINKS
WHERE OWNER <> '&v_owner'
AND (USERNAME = '&v_owner' OR DB_LINK LIKE '%'||'&v_owner'||'%');

prompt
SELECT 'SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE ''%' || DB_LINK || '%'' AND OWNER = ''' || OWNER || ''';' AS SOURCE_CHECK
FROM DBA_DB_LINKS
WHERE OWNER <> '&v_owner'
AND (USERNAME = '&v_owner' OR DB_LINK LIKE '%'||'&v_owner'||'%');

prompt
prompt *********************************************************
prompt [5] Scheduler Jobs
prompt *********************************************************
prompt 5-1. Scheduler Jobs Owned by '&v_owner' (Will be dropped)
COL JOB_NAME FOR A30
COL JOB_TYPE FOR A16
COL STATE FOR A15
COL LAST_START_DATE FOR A15
SELECT OWNER, JOB_NAME, STATE, TO_CHAR(LAST_START_DATE, 'YYYY-MM-DD') AS LAST_START_DATE
FROM DBA_SCHEDULER_JOBS
WHERE OWNER = '&v_owner';

prompt
prompt 5-2. SYS's Scheduler Jobs Using '&v_owner' as Credentials (Will be broken)
COL JOB_ACTION FOR A50
SELECT OWNER, JOB_NAME, JOB_TYPE, JOB_ACTION, STATE, TO_CHAR(LAST_START_DATE, 'YYYY-MM-DD') AS LAST_START_DATE
FROM DBA_SCHEDULER_JOBS
WHERE (
    UPPER(JOB_ACTION) LIKE '%' || UPPER('&v_owner') || '%'
    OR UPPER(PROGRAM_NAME) IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = UPPER('&v_owner'))
)
AND OWNER IN ('SYS', 'SYSTEM');

prompt
prompt 5-3. Credentials Owned by '&v_owner' (External Auth Info)
prompt (SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE '%'||'\&v_credential'||'%' AND OWNER <> '&v_owner';)
COL OWNER FOR A15
COL CREDENTIAL_NAME FOR A25
COL USERNAME FOR A20
COL WINDOWS_DOMAIN FOR A20
SELECT OWNER, CREDENTIAL_NAME, USERNAME, WINDOWS_DOMAIN
FROM DBA_SCHEDULER_CREDENTIALS
WHERE OWNER = UPPER('&v_owner');

prompt
prompt *********************************************************
prompt [6] Object Dependencies
prompt (SELECT DBMS_METADATA.GET_DDL('\&TYPE', '\&NAME', '\&OWNER') FROM DUAL;)
prompt *********************************************************
COL TYPE FOR A18
COL NAME FOR A30
COL REFERENCED_OWNER FOR A20
COL REFERENCED_NAME FOR A30
COL REFERENCED_TYPE FOR A18
COL REFERENCED_LINK_NAME FOR A50
SELECT OWNER, NAME, TYPE, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_LINK_NAME 
FROM DBA_DEPENDENCIES 
WHERE REFERENCED_OWNER = '&v_owner'
  AND OWNER != '&v_owner';

prompt
COL DEPENDENCY_DDL FOR A100
SELECT 'SELECT DBMS_METADATA.GET_DDL(''' || TYPE || ''', ''' || NAME || ''', ''' || OWNER || ''') AS GET_DDL FROM DUAL;' AS DEPENDENCY_DDL
FROM DBA_DEPENDENCIES
WHERE REFERENCED_OWNER = '&v_owner'
  AND OWNER != '&v_owner';

prompt
COL SOURCE_CHECK FOR A100
SELECT 'SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE ''%' || NAME || '%'' AND OWNER = ''' || OWNER || ''';' AS SOURCE_CHECK
FROM DBA_DEPENDENCIES
WHERE REFERENCED_OWNER = '&v_owner'
  AND OWNER != '&v_owner'
GROUP BY OWNER, NAME;

prompt
COL DROP_DDL FOR A100
SELECT 'DROP ' || TYPE || ' ' || OWNER || '.' || NAME || ' ;' AS DROP_DDL
FROM DBA_DEPENDENCIES
WHERE REFERENCED_OWNER = '&v_owner'
  AND OWNER != '&v_owner';

prompt
prompt *********************************************************
prompt [7] External Source Code References (Will be Invalidated)
prompt *********************************************************
COL NAME FOR A30
COL TYPE FOR A12
SELECT DISTINCT OWNER, NAME, TYPE
FROM DBA_SOURCE
WHERE UPPER(TEXT) LIKE '%'||'&v_owner'||'.%'
  AND OWNER <> '&v_owner';

prompt
SELECT 'SELECT DBMS_METADATA.GET_DDL(''' || TYPE || ''', ''' || NAME || ''', ''' || OWNER || ''') AS GET_DDL FROM DUAL;' AS REF_DDL
FROM (
SELECT DISTINCT OWNER AS OWNER, NAME, TYPE
FROM DBA_SOURCE
WHERE UPPER(TEXT) LIKE '%'||'&v_owner'||'.%'
  AND OWNER <> '&v_owner'
);

prompt
prompt *********************************************************
prompt [8] Storage Impact Analysis
prompt *********************************************************
prompt 8-1. Tablespaces and Datafiles occupied by '&v_owner'
prompt (SELECT DBMS_METADATA.GET_DDL('TABLESPACE', '\&ts_name') AS DDL_RESULT FROM DUAL;)
COL TABLESPACE_NAME FOR A20
COL TS_USED_MB FOR 999,999,990
COL FILE_NAME FOR A80
COL FILE_MB FOR 999,999,990
SELECT A.OWNER, A.TABLESPACE_NAME, A.TS_USED_MB,
       B.FILE_NAME, B.BYTES / 1024 / 1024 AS FILE_MB
FROM  (SELECT OWNER, TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) AS TS_USED_MB
       FROM   DBA_SEGMENTS
       WHERE  OWNER = '&v_owner'
       GROUP BY OWNER, TABLESPACE_NAME) A,
       DBA_DATA_FILES B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1, 2, 4;

prompt
COL TS_DDL FOR A100;
SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'', ''' || A.TABLESPACE_NAME || ''') AS GET_DDL FROM DUAL;' AS TS_DDL
FROM  (SELECT OWNER, TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024,0) AS TS_USED_MB
       FROM   DBA_SEGMENTS
       WHERE  OWNER = '&v_owner'
       GROUP BY OWNER, TABLESPACE_NAME) A,
       DBA_DATA_FILES B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1;

prompt
prompt 8-2. Owners sharing tablespaces with &v_owner
SELECT DISTINCT OWNER, TABLESPACE_NAME
FROM   DBA_SEGMENTS
WHERE  OWNER <> '&v_owner'
AND    TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER = '&v_owner')
ORDER BY 1, 2;

prompt
prompt *********************************************************
prompt [9] Check Directory objects and Delete invalid directories
prompt     (DROP DIRECTORY \&v_directory);)
prompt *********************************************************
COL DIRECTORY_NAME FOR A30
COL PATH_CHECK FOR A100
SELECT OWNER, DIRECTORY_NAME, 'ls -d ' || DIRECTORY_PATH AS PATH_CHECK
FROM   DBA_DIRECTORIES
WHERE  OWNER = '&v_owner';

prompt
prompt *********************************************************
prompt [10] Check Triggering Events
prompt     (ALTER TRIGGER SYS.\&v_trigger DISABLE;)
prompt     (DROP TRIGGER SYS.\&v_trigger;)
prompt *********************************************************
COL OWNER FOR A20
COL TRIGGER_NAME FOR A30
COL TABLE_OWNER FOR A20
COL TABLE_NAME FOR A30
COL TRIGGER_TYPE FOR A16
COL TRIGGERING_EVENT FOR A50
SELECT OWNER, TRIGGER_NAME, TABLE_OWNER, TABLE_NAME, TRIGGER_TYPE, TRIGGERING_EVENT
FROM   DBA_TRIGGERS
WHERE  OWNER <> '&v_owner'
AND    TABLE_OWNER = '&v_owner';

exit;
EOF1

Tags:

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다