====== **** 데이터사전(Data Dictionary) **** ======

 ---- **** ORACLE DATA DICTIONARY VIEW(오라클 데이터 사전 뷰) **** ---- 

    show user;
    -- USER이(가) "HR"입니다.
    
    select * 
    from dictionary;
    -- 또는
    select * 
    from dict;
    /*
      USER_CONS_COLUMNS
      ALL_CONS_COLUMNS
    */

 

 

    -----------------------------------------------
    --  ========= SYS 로 접속한 것 시작 ========== --
    -----------------------------------------------

 

    show user;
    -- USER이(가) "SYS"입니다.
    
    -- 먼저 index 공부를 할 때 trace(자동추적) 때문에 HR 에게 부여했던 SELECT ANY DICTIONARY 권한을 회수하기
    revoke SELECT ANY DICTIONARY from HR;
    -- Revoke을(를) 성공했습니다.
    
    revoke SELECT_CATALOG_ROLE from HR;
    
    select * 
    from dictionary;
    -- 또는
    select * 
    from dict;
    
    /*
       USER_CONS_COLUMNS
       ALL_CONS_COLUMNS
       DBA_CONS_COLUMNS
    */

 

 

 

     DBA_로 시작하는 것 
     ==> 관리자만 조회가능한 것으로 모든오라클사용자정보, 모든테이블, 모든인덱스, 모든데이터베이스링크 등등등 의 정보가 다 들어있는 것.
     
     USER_로 시작하는 것 
     ==> 오라클서버에 접속한 사용자 소유의 자신의오라클사용자정보, 자신이만든테이블, 자신이만든인덱스, 자신이만든데이터베이스링크 등등등 의 정보가 다 들어있는 것.
     
     ALL_로 시작하는 것 
     ==> 오라클서버에 접속한 사용자 소유의 즉, 자신의오라클사용자정보, 자신이만든테이블, 자신이만든인덱스, 자신이만든데이터베이스링크 등등등 의 정보가 다 들어있는 것
         과(와)
         자신의 것은 아니지만 조회가 가능한 다른사용자의오라클사용자정보, 다른사용자소유의테이블, 다른사용자소유의인덱스, 다른사용자소유의데이터베이스링크 등등등 의 정보가 다 들어있는 것. 
    */

 

    select *
    from dba_tables;
    
    select *
    from dba_tables
    where owner in ('HR', 'ORAUSER1');

 

    --  ========= SYS 로 접속한 것 끝 ========== --

 

 

---

 

--  ========= HR 로 접속한 것 시작 ========== --

 

    show user;
    -- USER이(가) "HR"입니다.
    
    select *
    from dba_tables;
    -- 
    
    select *
    from user_tables;
    
    select *
    from all_tables
    where owner = 'ORAUSER1';

 

 

 

---

 

- *** 자신이 만든 테이블에 대한 모든 정보를 조회하고 싶다. 어디서 보면 될까요? *** ---

 

    select *
    from dict
    where table_name like 'USER_%' and lower(comments) like '%table%';
    
    select *
    from USER_TABLES;

 

 

---

-- *** USER_TABLES 에서 보여지는 컬럼에 대한 설명을 보고 싶으면 아래와 같이하면 됩니다. *** --

    
    select *
    from dict_columns
    where table_name = 'USER_TABLES';

 

---

-- *** 자신이 만든 테이블의 컬럼에 대한 모든 정보를 조회하고 싶다. 어디서 보면 될까요? *** ---

 

    select *
    from dict
    where table_name like 'USER_%' and lower(comments) like '%column%';
    
    select *
    from USER_TAB_COLUMNS
    where table_name = 'EMPLOYEES';

 

 

---

-- *** USER_TAB_COLUMNS 에서 보여지는 컬럼에 대한 설명을 보고 싶으면 아래와 같이하면 됩니다. *** --

 

    select *
    from dict_columns
    where table_name = 'USER_TAB_COLUMNS';

 

 

---

 

-- *** 자신이 만든 테이블의 제약조건에 대한 모든 정보를 조회하고 싶다. 어디서 보면 될까요? *** ---

    
    select *
    from dict
    where table_name like 'USER_%' and lower(comments) like '%constraint%';
    
    select *
    from USER_CONSTRAINTS
    where table_name = 'EMPLOYEES';
    
    select *
    from USER_CONS_COLUMNS
    where table_name = 'EMPLOYEES';

 

 

---

-- *** 자신이 만든 데이터베이스 링크에 대한 모든 정보를 조회하고 싶다. 어디서 보면 될까요? *** ---

 

    select *
    from dict
    where table_name like 'USER_%' and lower(comments) like '%database link%';
    
    select *
    from USER_DB_LINKS;

 

 

---

 

-- *** 자신이 만든 시퀀스에 대한 모든 정보를 조회하고 싶다. 어디서 보면 될까요? *** ---

    select *
    from dict
    where table_name like 'USER_%' and lower(comments) like '%sequence%';
    
    select *
    from USER_SEQUENCES;

 

---

-- *** 자신이 만든 인덱스에 대한 모든 정보를 조회하고 싶다. 어디서 보면 될까요? *** ---

    select *
    from dict
    where table_name like 'USER_%' and lower(comments) like '%index%';
    
    select *
    from USER_INDEXES
    where table_name = 'EMPLOYEES';
    
    select *
    from USER_IND_COLUMNS
    where table name = 'EMPLOYEES';

 

 

---

-- *** 자신이 만든 테이블 'TBL_BOARD_TEST_1' 에서 어떤 컬럼에 default 값을 부여했는지 조회하고 싶다. 어디서 보면 될까요? *** ---
    

    select *
    from dict
    where table_name like 'USER_%' and lower(comments) like '%column%';
    
    
    select column_name, data_default
    from USER_TAB_COLUMNS
    where table_name = 'TBL_BOARD_TEST_1';