모든 테이블의 rowcount 조회

SELECT table_name,
       num_rows
  FROM all_tables
 WHERE owner = 'xxx' ;

오라클 컬럼 찾기

--해당컬럼 사용하는 곳 찾기

1. PROC, FUNC, TRIGGER
 1) 해당 user에 대하여
SELECT *
FROM USER_SOURCE
WHERE TEXT LIKE '%TECHR_CD%'

 2) 모든 uesr에 대하여
SELECT *
FROM DBA_SOURCE
WHERE TEXT LIKE '%TECHR_CD%'

2. view
 1) 해당 user에 대하여
SELECT *
FROM USER_VIEWS
WHERE dbms_xmlgen.getxml('select text from user_views where view_name = ''' || view_name || '''') like '%TECHR_CD%';

 2) 모든 uesr에 대하여
SELECT *
FROM DBA_VIEWS
WHERE dbms_xmlgen.getxml('select text from DBA_views where view_name = ''' || view_name || '''') like '%TECHR_CD%';

오라클 object 내 특정문자열 찾기

SELECT *
  FROM ALL_SOURCE
 WHERE OWNER = '계정'
   --AND TYPE = 'PROCEDURE' --object
   AND TEXT LIKE '%com_member%';  --문자열

Lock 확인 및 해제

1. 확인 방법
SELECT DISTINCT t1.session_id AS session_id
   ,t2.serial# AS serial_no
   ,t1.os_user_name AS os_user_name
   ,t1.oracle_username AS oracle_username
   ,t2.status AS status
   ,t3.object_name
   ,DECODE( locked_mode
           ,2, 'ROW SHARE'
           ,3, 'ROW EXCLUSIVE'
           ,4, 'SHARE'
           ,5, 'SHARE ROW EXCLUSIVE'
           ,6, 'EXCLUSIVE'
           ,'UNKNOWN'
          ) lock_mode
FROM v$locked_object t1, v$session t2, dba_objects t3
WHERE t1.session_id = t2.SID
AND t1.object_id = t3.object_id;

2. 확인 방법

select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'
;

3. kill 하기

ALTER SYSTEM KILL SESSION s.sid,s.serial#;
ALTER SYSTEM KILL SESSION '121,21';