Bong's Log

SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS, CREATED, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE '테이블_이름%'
ORDER BY OBJECT_TYPE;

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, COLUMN_ID
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = '테이블_이름'
ORDER BY COLUMN_ID;

-- 인덱스 목록 확인
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS, STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME = '테이블_이름';

SELECT OWNER, NAME, TYPE, SHARABLE_MEM, LOADS, EXECUTIONS
FROM V$DB_OBJECT_CACHE
ORDER BY LOADS DESC;

SELECT OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE LAST_DDL_TIME > SYSDATE - 7
ORDER BY LAST_DDL_TIME DESC;

SELECT S.SID, S.SERIAL#, C.USER_NAME, C.SQL_TEXT
FROM V$OPEN_CURSOR C
JOIN V$SESSION S
ON C.SID = S.SID
WHERE C.SQL_TEXT LIKE '%테이블_이름%';

-- 삭제된 테이블 목록 조회
SELECT OBJECT_NAME, ORIGINAL_NAME, DROPTIME FROM RECYCLEBIN;
-- 특정 테이블 복구
FLASHBACK TABLE 테이블_이름 TO BEFORE DROP;

-- 현재 실행 중인 세션 확인
SELECT SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM FROM V$SESSION WHERE STATUS = 'ACTIVE';

-- 실행 중인 SQL 조회
SELECT S.SID, S.SERIAL#, S.USERNAME, SQ.SQL_TEXT
FROM V$SESSION S
JOIN V$SQL SQ
ON S.SQL_ID = SQ.SQL_ID
WHERE S.STATUS = 'ACTIVE';

-- 락 걸린 세션 확인
SELECT L.SID, S.SERIAL#, S.USERNAME, L.TYPE, L.MODE_HELD
FROM V$LOCK L
JOIN V$SESSION S
ON L.SID = S.SID
WHERE L.BLOCK = 1;

-- 최근 1시간 동안 UNDO 사용량 조회
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT
FROM V$UNDOSTAT
WHERE BEGIN_TIME > SYSDATE - 1/24
ORDER BY BEGIN_TIME DESC;

-- 특정 SQL 실행 계획 조회
SELECT * FROM V$SQL_PLAN WHERE SQL_ID = 'SQL_ID_값';

-- 현재 락 걸린 세션 확인
SELECT * FROM V$LOCK;
-- 세션 강제 종료
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

-- TEMP 테이블스페이스 크기 확인

SELECT
 TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB 
FROM DBA_TEMP_FILES;