Dictionary를 조회할 때 매우 유용한 SQL문
select * from dict
where lower(comments) like lower('%&dictionary_comments%');
--------------------------------------
select * from dict
where lower(table_name) like lower('%&dictionary_name%');
--------------------------------------
column table_name format a30
column column_name format a30
column comments format a60 word_wrapped
select * from dict_columns
where lower(table_name) like lower('&dictionary_name')
and lower(column_name) like lower('&column_name');
--------------------------------------
select * from dict_columns
where lower(table_name) like lower('&dictionary_name');
View 모음
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS;
-----------------------------------
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, VALIDATED, DEFERRABLE, DEFERRED
FROM USER_CONSTRAINTS;
-----------------------------------
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM USER_CONS_COLUMNS;
-----------------------------------
SELECT A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE, A.R_CONSTRAINT_NAME, A.SEARCH_CONDITION
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME;
-----------------------------------
SELECT T.TABLE_NAME, T.CONSTRAINT_NAME, C.COLUMN_NAME,
T.DEFERRABLE, T.DEFERRED, T.VALIDATED
FROM DBA_CONSTRAINTS T, DBA_CONS_COLUMNS C
WHERE T.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND T.OWNER LIKE '%&OWNER%'
AND T.TABLE_NAME LIKE '%&TABLE_NAME%';
-----------------------------------
SELECT OBJECT_NAME, OBJECT_TYPE, OBJECT_ID, DATA_OBJECT_ID, CREATED, TIMESTAMP, LAST_DDL_TIME
FROM USER_OBJECTS
ORDER BY OBJECT_TYPE;
----------------------------------
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;
-----------------------------------
SELECT *
FROM USER_SEQUENCES;
-----------------------------------
SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM USER_SYNONYMS;
-----------------------------------
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, ALLOCATION_TYPE
FROM DBA_TABLESPACES;
----------------------------------
롤백세그먼트의 일반적인 정보
SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME,
INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, STATUS
FROM DBA_ROLLBACK_SEGS;
----------------------------------
현재 인스턴트가 사용하고 있는 롤백세그먼트에 대한 통계 검색
SELECT N.NAME, S.EXTENTS, S.RSSIZE, S.OPTSIZE, S.HWMSIZE, S.XACTS, S.STATUS
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
----------------------------------
롤백세그먼트에 대한 경합
SELECT N.NAME, ROUND(100*S.WAITS/S.GETS)
FROM V$ROLLNAME N, V$ROLLSTAT S
WHERE N.USN = S.USN;
----------------------------------
블록킹 트랜잭션
SELECT S.SID, S.SERIAL#, T.START_TIME, T.XIDUSN, S.USERNAME
FROM V$SESSION S, V$TRANSACTION T, V$ROLLSTAT R
WHERE S.SADDR = T.SES_ADDR
AND T.XIDUSN = R.USN
AND ((R.CUREXT = T.START_UEXT-1)
OR ((R.CUREXT = R.EXTENTS-1) AND T.START_UEXT = 0));
----------------------------------
인덱스에 대한 유효성 확인
SELECT BLOCKS, BTREE_SPACE, USED_SPACE, PCT_USED, LF_ROWS, DEL_LF_ROWS
FROM INDEX_STATS;
----------------------------------
SELECT USERNAME, USER_ID, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE
FROM DBA_USERS;
----------------------------------
SELECT TABLESPACE_NAME, BYTES, MAX_BYTES, BLOCKS, MAX_BLOCKS
FROM USER_TS_QUOTAS;
-----------------------------------
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES,
AUTOEXTENSIBLE, INCREMENT_BY, MAXBYTES
FROM DBA_DATA_FILES;
-----------------------------------
SELECT FILE#, STATUS, RFILE#, BYTES, BLOCKS, CREATE_BYTES, BLOCK_SIZE, NAME
FROM V$DATAFILE;
-----------------------------------
SELECT FILE#, STATUS
FROM V$BACKUP;
-----------------------------------
SELECT *
FROM DBA_FREE_SPACE
ORDER BY BLOCK_ID;
-----------------------------------
SELECT A.TABLESPACE_NAME, A.BYTES, A.STATUS, B.STATUS, B.ENABLED, B.NAME
FROM DBA_DATA_FILES A, V$DATAFILE B
WHERE A.FILE_ID = B.FILE# AND A.TABLESPACE_NAME LIKE '%&TABLESPACE_NAME%';
-----------------------------------
SELECT TABLESPACE_NAME, EXTENTS_COALESCED, TOTAL_BYTES, BYTES_COALESCED
FROM DBA_FREE_SPACE_COALESCED;
-----------------------------------
SELECT A.TABLESPACE_NAME "TABLESPACE",
B.FILE_NAME "FILE",
B.BYTES "TOTAL SIZE",
C.BYTES "SIZE LEFT"
FROM DBA_TABLESPACES A, DBA_DATA_FILES B, DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND
A.TABLESPACE_NAME = C.TABLESPACE_NAME AND
A.TABLESPACE_NAME LIKE '%&TABLESPACE_NAME%';
-----------------------------------
SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT
FROM USER_SEGMENTS;
-----------------------------------
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS, INDEX_TYPE, TABLESPACE_NAME
FROM USER_INDEXES;
-----------------------------------
SELECT LINE, POSITION, TEXT
FROM USER_ERRORS
ORDER BY SEQUENCE;
-----------------------------------
SELECT *
FROM USER_SOURCE;
-----------------------------------
SELECT SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS
FROM USER_EXTENTS
ORDER BY SEGMENT_NAME, EXTENT_ID;
-----------------------------------
SELECT TABLESPACE_NAME, SEGMENT_NAME, FILE_ID, EXTENT_ID, BLOCK_ID, BLOCKS
FROM DBA_EXTENTS
ORDER BY EXTENT_ID;
-----------------------------------
SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID, POSITION
FROM PLAN_TABLE
ORDER BY ID;
-----------------------------------
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
FROM USER_TABLES;
-----------------------------------
SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY
FROM USER_TAB_COLUMNS;
-----------------------------------
SELECT TABLE_NAME, TABLE_TYPE_NAME, PARENT_TABLE_NAME, PARENT_TABLE_COLUMN
FROM USER_NESTED_TABLES;
-----------------------------------
SELECT *
FROM DBA_PROFILES;
-----------------------------------
SELECT *
FROM USER_RESOURCE_LIMITS;
-----------------------------------
SELECT *
FROM USER_PASSWORD_LIMITS;
-----------------------------------
SELECT *
FROM V$OPTION;
-----------------------------------
SELECT *
FROM V$PARAMETER;
-----------------------------------
SELECT DISTINCT OBJECT_TYPE
FROM DBA_OBJECTS;
-----------------------------------
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE, CREATED
FROM DBA_OBJECTS
WHERE OWNER LIKE '%&OWNER%'
AND OBJECT_TYPE LIKE '%&OBJECT_TYPE%'
ORDER BY OBJECT_NAME;
-----------------------------------
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME LIKE '%&TABLE_NAME%'
ORDER BY OWNER, TABLE_NAME;
-----------------------------------
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS
WHERE VIEW_NAME LIKE '%&VIEW_NAME%';
-----------------------------------
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE '%&ROLE%';
-----------------------------------
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_SYNONYMS
ORDER BY 2, 1, 3;
-----------------------------------
<클러스터>
클러스터의 일반적인 정보(블록파라미터..)
SELECT * FROM DBA_CLUSTERS
WHERE OWNER LIKE '%&OWNER%';
클러스터 테이블 및 클러스터 키 조회
SELECT OWNER, CLUSTER_NAME, CLU_COLUMN_NAME, TABLE_NAME
FROM DBA_CLU_COLUMNS
WHERE OWNER LIKE '%&OWNER%';
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
FROM DBA_TAB_COLUMNS
WHERE OWNER LIKE '%&OWNER%';
-----------------------------------
IOT 조회
SELECT T.TABLE_NAME AS "IOT", O.TABLE_NAME AS "OVERFLOW",
I.INDEX_NAME AS "INDEX", O.TABLESPACE_NAME AS "OVERFLOW TBS",
I.TABLESPACE_NAME AS "INDEX TBS", I.PCT_THRESHOLD
FROM DBA_TABLES T, DBA_TABLES O, DBA_INDEXES I
WHERE T.OWNER = O.OWNER
AND T.TABLE_NAME = O.IOT_NAME
AND T.OWNER = I.OWNER
AND T.TABLE_NAME = I.TABLE_NAME
AND T.OWNER LIKE '%&OWNER%';
-----------------------------------
SELECT USERNAME, TIMESTAMP, ACTION_NAME
FROM DBA_AUDIT_TRAIL;
-----------------------------------
SELECT USERID, OBJ$NAME, SES$ACTIONS, TIMESTAMP#
FROM SYS.AUD$
WHERE OBJ$NAME LIKE '%&OBJECT_NAME%';
-----------------------------------
SELECT SESSIONID, STATEMENT, TIMESTAMP#, USERID, TERMINAL, ACTION#,
OBJ$CREATOR, OBJ$NAME, SES$ACTIONS, COMMENT$TEXT, SPARE1
FROM SYS.AUD$;
