특정 시점으로 테이블 데이터 복원

-- 특정 시간대의 데이터 조회
SELECT * FROM target_table AS OF TIMESTAMP TO_TIMESTAMP('2009-12-11 20:53:57', 'YYYY-MM-DD HH24:MI:SS');

-- 특정 시점의 데이터로 새 테이블 생성
CREATE TABLE restored_table AS 
SELECT * FROM target_table AS OF TIMESTAMP TO_TIMESTAMP('2009-12-11 20:53:57', 'YYYY-MM-DD HH24:MI:SS');

락된 테이블 정보 조회

SELECT 
    sess.sid,
    sess.serial#,
    lo.oracle_username,
    lo.os_user_name,
    ao.object_name,
    lo.locked_mode  
FROM v$locked_object lo
JOIN dba_objects ao ON ao.object_id = lo.object_id
JOIN v$session sess ON lo.session_id = sess.sid;

세션 종료를 통한 락 해제

ALTER SYSTEM KILL SESSION '597,5388';

삭제된 테이블 데이터 일괄 복구

SELECT 'FLASHBACK TABLE ' || original_name || ' TO BEFORE DROP;' AS recovery_command
FROM recyclebin 
WHERE original_name LIKE '%GJBB%' 
AND TO_DATE(droptime, 'YYYY-MM-DD HH24:MI:SS') > TO_DATE('2021-07-10:00:00:00', 'YYYY-MM-DD HH24:MI:SS');

테이블스페이스 사용 현황 확인

SELECT 
    a.tablespace_name AS "테이블스페이스명",
    total AS "총 크기",
    free AS "남은 공간",
    (total - free) AS "사용량",
    ROUND(total / (1024 * 1024 * 1024), 2) AS "총 크기(GB)",
    ROUND(free / (1024 * 1024 * 1024), 2) AS "남은 공간(GB)",
    ROUND((total - free) / (1024 * 1024 * 1024), 2) AS "사용량(GB)",
    ROUND((total - free) / total * 100, 2) AS "사용률(%)"
FROM (
    SELECT tablespace_name, SUM(bytes) AS free
    FROM dba_free_space
    GROUP BY tablespace_name
) a
JOIN (
    SELECT tablespace_name, SUM(bytes) AS total
    FROM dba_data_files
    GROUP BY tablespace_name
) b ON a.tablespace_name = b.tablespace_name;

테이블 컬럼 및 주석 정보 조회

SELECT * FROM (
    SELECT 
        t1.table_name,
        t1.column_name,
        t2.comments
    FROM user_tab_columns t1
    LEFT JOIN user_col_comments t2 
        ON t1.table_name = t2.table_name 
        AND t1.column_name = t2.column_name
) 
WHERE table_name LIKE '%T_BYYTKCGL%';

락을 발생시킨 SQL 문장 추적

SELECT 
    l.session_id,
    sid,
    s.serial#,
    l.locked_mode,
    l.oracle_username,
    s.user#,
    l.os_user_name,
    s.machine,
    s.terminal,
    a.sql_text
FROM v$sqlarea a
JOIN v$session s ON s.prev_sql_addr = a.address
JOIN v$locked_object l ON l.session_id = s.sid
ORDER BY sid, s.serial#;

테이블 일괄 삭제 명령어 생성

SELECT REPLACE(TO_CHAR(WM_CONCAT('DROP TABLE ' || table_name)), ',', ';' || CHR(10)) AS drop_commands
FROM user_tab_comments 
WHERE table_name LIKE '%T_WTHDGL%';

PostgreSQL 락 정보 조회

SELECT
    lock.locktype,
    lock.pid,
    lock.mode,
    lock.granted,
    stat.usename,
    stat.query,
    stat.query_start,
    NOW() - stat.query_start AS duration,
    stat.state
FROM pg_locks lock
JOIN pg_stat_activity stat ON lock.pid = stat.pid
WHERE lock.relation = 'T_ZG_QYDS_DSJBXX'::regclass
   OR lock.relation::regclass::text = 'T_ZG_QYDS_DSJBXX'
ORDER BY duration DESC;

PostgreSQL 프로세스 종료

SELECT pg_terminate_backend(70427225441856);

데이터베이스 연결 제한 설정 확인

SELECT name, value, display_value 
FROM v$parameter 
WHERE name IN ('processes', 'sessions', 'transactions');

현재 세션 및 프로세스 수 확인

SELECT '현재 세션 수' AS type, COUNT(*) AS count 
FROM v$session
UNION ALL
SELECT '현재 프로세스 수', COUNT(*) 
FROM v$process;

미해제된 연결 세션 조회

SELECT 
    sid,
    serial#,
    username,
    status,
    machine,
    program,
    osuser,
    logon_time,
    ROUND(last_call_et/60, 2) AS idle_minutes,
    'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd
FROM v$session
WHERE username NOT IN ('SYS', 'SYSTEM', 'DBSNMP') 
    AND username IN ('USR_YJSZCM', 'USR_YJSZJ')
    AND status = 'INACTIVE'
    AND last_call_et > 600
ORDER BY last_call_et DESC;

연결 세션 일괄 종료

BEGIN
    FOR session_rec IN (
        SELECT sid, serial#, username
        FROM v$session
        WHERE username NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
            AND username IN ('USR_YJSZJ')
            AND status = 'INACTIVE'
            AND last_call_et > 600
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || session_rec.sid || ',' || session_rec.serial# || ''' IMMEDIATE';
        DBMS_OUTPUT.PUT_LINE('종료됨: ' || session_rec.username || ' - SID=' || session_rec.sid);
    END LOOP;
END;