[Oracle] 데이터 딕셔너리 활용하기

[복습] 현재 alert log file 을 열어서 이슈를 확인하고 그 이슈에 대한 문제를 해결하시오

glogin.sql에 있는
set time on
set timing on 지우기

.bash_profile에 alert 추가하기

[oracle@ora19c ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@ora19c admin]$
[oracle@ora19c admin]$ ls -l glogin.sql
-rw-r--r--. 1 oracle oinstall 422  8월 26 13:51 glogin.sql
[oracle@ora19c admin]$
[oracle@ora19c admin]$ vi glogin.sql
[oracle@ora19c admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--

set sqlprompt "_user '@' _connect_identifier > "


[oracle@ora19c ~]$ sys

SQL*Plus: Release 19.0.0.0.0 - Production on 목 8월 28 09:52:52 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS @ ORA19 >
SYS @ ORA19 > exit;
[oracle@ora19c ~]$ tail -10 .bash_profile

alias sys='export ORACLE_SID=ORA19; sqlplus / as sysdba'
alias sysdw='export ORACLE_SID=ora19dw; sqlplus / as sysdba'
alias scott='export ORACLE_SID=ORA19; sqlplus scott/tiger'
alias scottdw='export ORACLE_SID=ora19dw; sqlplus scott/tiger'
alias net='cd /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/'
alias alert='cd /u01/app/oracle/diag/rdbms/ora19/ORA19/trace/'

[oracle@ora19c ~]$ source .bash_profile
[oracle@ora19c ~]$
[oracle@ora19c ~]$ alert
[oracle@ora19c trace]$ vi alert*
2025-08-28T09:21:35.642821+09:00
db_recovery_file_dest_size of 8256 MB is 20.63% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2025-08-28T09:26:19.564627+09:00
Running KSFV I/O slave I101 os pid=3061
2025-08-28T09:26:19.574075+09:00
Running KSFV I/O slave I102 os pid=3063
2025-08-28T09:26:19.581220+09:00
Running KSFV I/O slave I103 os pid=3065
"alert_ORA19.log" 28862L, 1351472C        

[oracle@ora19c trace]$ sys

SQL*Plus: Release 19.0.0.0.0 - Production on 목 8월 28 09:59:14 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS @ ORA19 > show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 8256M
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SYS @ ORA19 >
SYS @ ORA19 >
SYS @ ORA19 > select 8256*2 from dual;

    8256*2
----------
     16512

SYS @ ORA19 > alter system set db_recovery_file_dest_size=16512m scope=both;

시스템이 변경되었습니다.

SYS @ ORA19 >

---

[이론1] dba 와 dbe 는 데이터 사전을 조회할 일이 종종 있습니다.

갑자기 개발자가 emp 테이블에 sal의 길이를 늘려달라고 요청이 들어오면 데이터 사전을 조회해서 정확한 내용을 확인하고 늘려야합니다.

---

[실습] emp 테이블이 데이터베이스에 있는지 조회하시오

select *
 from dba_tables
 where table_name ='EMP';

---

[실습2] scott의 emp 테이블의 월급의 길이를 number(10,2) 로 늘립니다.

desc scott.emp;

alter  table  scott.emp
 modify sal  number(10,2);
 
desc scott.emp;

---

[문제1] emp 테이블의 deptno의 길이를 number(10)으로 변경하시오

alter table scott.emp
 modify deptno number(10);
 
desc scott.emp;

---

[실습3] hr계정이 소유한 테이블의 갯수가 몇 개인지 확인하시오

select username, account_status
 from dba_users;
 
 select *
  from dba_tables
  where owner='HR';

---

[실습4] hr 계정이 소유한 테이블 중에서 가장 건수가 많은 테이블의 건수는 몇 개인가?

 select  table_name, num_rows, last_analyzed
  from dba_tables
  where owner='HR'
  order by num_rows desc;
  
  --HR 계정이 가지고 있는 테이블들에 대해서 통계정보를 수집하는 명령어 
exec dbms_stats.gather_schema_stats('HR');

 select  table_name, num_rows, last_analyzed
  from dba_tables
  where owner='HR'
  order by num_rows desc;

---

[문제2] scott 이 가지고 있는 테이블들의 통계정보를 오늘날짜로 다시 수집하고 scott이 가지고 있는 테이블과 그 건수와 analyze를 한 날짜를 확인하시오

exec dbms_stats.gather_schema_stats('SCOTT');

 select  table_name, num_rows, last_analyzed
  from dba_tables
  where owner='SCOTT'
  order by num_rows desc;

---

[실습5] HR 계정이 가지고 있는 테이블중 employees 테이블에 인덱스가 무엇이 있는지 조회하시오

select *
 from dba_indexes
 where owner='HR' and table_name='EMPLOYEES';

💡 sql developer 에서 dba_in까지 쓰고 ctrl + space를 누르면 관련된 딕셔너리들을 볼 수 있음

---

[문제3] EMPLOYEES 테이블의 SQL을 튜닝하려고 합니다. 어느 컬럼에 인덱스가 걸려있는지 확인하고 싶습니다. 관련된 SQL을 작성하세요.

SELECT *
 FROM HR.employees
 where first_name='Donald';
 
select *
 from dba_ind_columns
 where index_owner='HR' and table_name='EMPLOYEES';

---

[실습6] 아래의 SQL을 튜닝할 수 있도록 hr 계정으로 따로 sqldeveloper로 접속하기 위해서 hr 계정의 락을 해제하고 hr 계정의 패스워드를 hr 로 변경하시오

SELECT *
 FROM HR.employees
 where first_name='Donald';

-- hr 계정의 락을 해제
alter user hr account unlock;

-- 패스워드를 hr로 바꿈
alter user hr identified by hr;

---

[실습7] 실제 실행계획을 편하게 볼 수 있게 statistics_level 이라는 파라미터를 all로 설정하시오

SYS @ ORA19 > show parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level              string      TYPICAL
statistics_level                     string      ALL
SYS @ ORA19 >
SYS @ ORA19 > alter system set statistics_level=all scope=both;

시스템이 변경되었습니다.

---

[실습8] hr 계정에서 아래의 SQL의 실제 실행계획을 확인하시오

먼저 sys에서 아래의 권한을 hr 에게 주고, hr에 재접속

GRANT SELECT ON V_$SQL TO HR;
GRANT SELECT ON V_$SQL_PLAN TO HR;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO HR;
GRANT SELECT ON V_$SESSION TO HR;
GRANT SELECT ON V_$SQL_SHARED_CURSOR TO HR;

SELECT /*+ full(e) */  *
 FROM employees  e
 where first_name='Donald';
 
SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

튜닝 후:

 SELECT /*+ index_ss(e EMP_NAME_IX) */  *
 FROM employees  e
 where first_name='Donald';
 
 SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

---

[문제4] 아래의 SQL을 튜닝하시오

SELECT * 
  From employees
  where to_char(hire_date,'RRRR') ='2008';

create index emp_hire_date_ix 
 on employees(hire_date);
 
select *
 from employees
 where hire_date between to_date('20080101','RRRRMMDD') and to_date('20090101','RRRRMMDD');
 
 SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

---

🔸 [실습9] 필수로 자주 조회하는 데이터 사전 보는 쿼리들

-- ===============================
-- Oracle 데이터 딕셔너리 조회 쿼리
-- ===============================

-- 1. 데이터 딕셔너리 뷰 목록 확인
-- 사용 가능한 모든 딕셔너리 뷰 보기
SELECT * FROM DICTIONARY ORDER BY TABLE_NAME;

-- 특정 패턴의 뷰 찾기 (예: 테이블 관련)
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE '%TABLE%' ORDER BY TABLE_NAME;

-- ===============================
-- DBA_ 뷰 (DBA 권한 필요)
-- ===============================

-- 모든 데이터베이스 사용자 조회
SELECT USERNAME, ACCOUNT_STATUS, CREATED, LAST_LOGIN 
FROM DBA_USERS 
ORDER BY USERNAME;

-- 모든 테이블 정보 조회
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, NUM_ROWS
FROM DBA_TABLES 
ORDER BY OWNER, TABLE_NAME;

-- 모든 인덱스 정보 조회
SELECT OWNER, INDEX_NAME, TABLE_NAME, INDEX_TYPE, STATUS
FROM DBA_INDEXES
ORDER BY OWNER, TABLE_NAME;

-- 테이블스페이스 정보 조회
SELECT TABLESPACE_NAME, STATUS, CONTENTS, EXTENT_MANAGEMENT
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;

-- 권한 부여 현황 조회
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS
WHERE GRANTEE NOT IN ('SYS', 'SYSTEM')
ORDER BY GRANTEE, PRIVILEGE;

-- ===============================
-- ALL_ 뷰 (접근 가능한 객체)
-- ===============================

-- 접근 가능한 모든 테이블 조회
SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM ALL_TABLES
ORDER BY OWNER, TABLE_NAME;

-- 접근 가능한 뷰 조회
SELECT OWNER, VIEW_NAME, TEXT_LENGTH
FROM ALL_VIEWS
ORDER BY OWNER, VIEW_NAME;

-- 접근 가능한 시퀀스 조회
SELECT SEQUENCE_OWNER, SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY
FROM ALL_SEQUENCES
ORDER BY SEQUENCE_OWNER, SEQUENCE_NAME;

-- 접근 가능한 프로시저/함수 조회
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS, CREATED
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

-- 테이블의 컬럼 정보 조회
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP'  -- 특정 테이블명으로 변경
ORDER BY COLUMN_ID;

-- ===============================
-- USER_ 뷰 (소유한 객체)
-- ===============================

-- 내가 소유한 테이블 조회
SELECT TABLE_NAME, NUM_ROWS, TABLESPACE_NAME, LAST_ANALYZED
FROM USER_TABLES
ORDER BY TABLE_NAME;

-- 내가 소유한 인덱스 조회
SELECT INDEX_NAME, TABLE_NAME, INDEX_TYPE, STATUS, UNIQUENESS
FROM USER_INDEXES
ORDER BY TABLE_NAME, INDEX_NAME;

-- 내가 소유한 뷰 조회
SELECT VIEW_NAME, TEXT_LENGTH, TEXT
FROM USER_VIEWS
ORDER BY VIEW_NAME;

-- 내가 소유한 시퀀스 조회
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER
FROM USER_SEQUENCES
ORDER BY SEQUENCE_NAME;

-- 내가 소유한 제약조건 조회
SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUS
FROM USER_CONSTRAINTS
ORDER BY TABLE_NAME, CONSTRAINT_TYPE;

-- 제약조건 상세 정보 (컬럼별)
SELECT c.CONSTRAINT_NAME, c.TABLE_NAME, cc.COLUMN_NAME, c.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
ORDER BY c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION;

-- ===============================
-- 실용적인 조합 쿼리들
-- ===============================

-- 1. 테이블 크기 정보 (세그먼트 포함)
SELECT 
    t.TABLE_NAME,
    t.NUM_ROWS,
    ROUND(s.BYTES/1024/1024, 2) AS SIZE_MB,
    s.SEGMENT_TYPE
FROM USER_TABLES t
LEFT JOIN USER_SEGMENTS s ON t.TABLE_NAME = s.SEGMENT_NAME
ORDER BY s.BYTES DESC NULLS LAST;

-- 2. 인덱스와 해당 컬럼 정보
SELECT 
    i.INDEX_NAME,
    i.TABLE_NAME,
    ic.COLUMN_NAME,
    ic.COLUMN_POSITION,
    i.UNIQUENESS
FROM USER_INDEXES i
JOIN USER_IND_COLUMNS ic ON i.INDEX_NAME = ic.INDEX_NAME
ORDER BY i.TABLE_NAME, i.INDEX_NAME, ic.COLUMN_POSITION;

-- 3. 외래키 관계 조회
SELECT 
    c.CONSTRAINT_NAME AS FK_NAME,
    c.TABLE_NAME AS CHILD_TABLE,
    cc.COLUMN_NAME AS CHILD_COLUMN,
    c.R_CONSTRAINT_NAME AS PK_NAME,
    r.TABLE_NAME AS PARENT_TABLE,
    rc.COLUMN_NAME AS PARENT_COLUMN
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
JOIN USER_CONSTRAINTS r ON c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
JOIN USER_CONS_COLUMNS rc ON r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'R'
ORDER BY c.TABLE_NAME, c.CONSTRAINT_NAME;

-- 4. 테이블별 권한 확인
SELECT 
    TABLE_NAME,
    PRIVILEGE,
    GRANTEE,
    GRANTABLE
FROM USER_TAB_PRIVS
ORDER BY TABLE_NAME, PRIVILEGE;

-- 5. 무효한 객체 찾기
SELECT 
    OBJECT_NAME,
    OBJECT_TYPE,
    STATUS,
    LAST_DDL_TIME
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OBJECT_TYPE, OBJECT_NAME;

-- ===============================
-- 성능 관련 조회 쿼리
-- ===============================

-- 1. 테이블 통계 정보 확인
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    BLOCKS,
    AVG_ROW_LEN,
    LAST_ANALYZED
FROM USER_TABLES
WHERE NUM_ROWS > 0
ORDER BY NUM_ROWS DESC;

-- 2. 인덱스 사용 통계 (11g 이상)
SELECT 
    INDEX_NAME,
    TABLE_NAME,
    MONITORING,
    USED
FROM USER_OBJECT_USAGE
ORDER BY TABLE_NAME, INDEX_NAME;

-- ===============================
-- 시스템 정보 조회 (DBA 권한 필요)
-- ===============================

-- 현재 세션 정보
SELECT 
    SID,
    SERIAL#,
    USERNAME,
    STATUS,
    MACHINE,
    PROGRAM
FROM V$SESSION
WHERE USERNAME IS NOT NULL
ORDER BY USERNAME;

-- 테이블스페이스 사용량
SELECT 
    TABLESPACE_NAME,
    ROUND(BYTES/1024/1024, 2) AS TOTAL_MB,
    ROUND(MAXBYTES/1024/1024, 2) AS MAX_MB
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;