오라클 관리자용 SQL

<테이블스페이스 관련>

SELECT tablespace_name, file_name, bytes FROM dba_data_files

SELECT b.tablespace_name, b.file_name, b.bytes "TotalSize",

SUM(NVL(a.bytes,0)) "FreeSpace",(SUM(NVL(a.bytes,0))/b.bytes)*100 "UsageRate%"

FROM dba_free_space a, dba_data_files b

WHERE a.file_id(+) = b.file_id

GROUP BY b.tablespace_name, b.file_name, b.bytes

ORDER BY b.tablespace_name

SELECT u.username, u.default_tablespace, b.file_name, b.bytes "총크기",

SUM(NVL(a.bytes,0)) "여유공간",

(SUM(NVL(a.bytes,0)) / b.bytes) * 100 "사용률(%)"

FROM dba_free_space a, dba_data_files b, dba_users u

WHERE a.file_id(+) = b.file_id

AND u.default_tablespace = b.tablespace_name

AND u.username = 'AIS'

GROUP BY u.username, u.default_tablespace , b.file_name, b.bytes

ORDER BY u.username

select username, default_tablespace from dba_users where username <> 'SYSTEM'

and username <> 'SYS'

SELECT owner, table_owner, table_name, index_name, tablespace_name FROM dba_indexes

WHERE owner = 'AEIS'

<인덱스 관련>

select owner, table_owner, table_name, index_name, tablespace_name from dba_indexes

where owner ='AIS'

  

<사용자 관련>

SELECT * FROM dba_users WHERE username = 'ais'

<세션 강제 종료>

ALTER SYSTEM KILL SESSION '16,286' // sid, serial# 이 필요함

<접속중인 클라이언트 확인>

SELECT osuser, username, terminal, program, sid, serial#, audsid, USER#,

logon_time FROM v$session

select sid,serial#,audsid,user#,username from v$session;

SELECT ALL SYS.V_$SESSION.OSUSER, SYS.V_$SESSION.USERNAME,

SYS.V_$SESSION.TERMINAL,

SYS.V_$SESSION.PROGRAM, SYS.V_$PROCESS.SPID SERVER_PID

FROM SYS.V_$PROCESS, SYS.V_$SESSION

WHERE SYS.V_$PROCESS.ADDR=SYS.V_$SESSION.PADDR

<SQL 문장 재사용하는 비율 확인>

SELECT gets, gethitratio, pins, pinhitratio, reloads, invalidations

FROM V$librarycache

WHERE namespace = 'sql area'

<1%보다 작아야 함>

SELECT (SUM(reloads) / SUM(pins)) * 100 "Missing Rate"

FROM v$librarycache

<Dictionary Cache Tuning>

SELECT TO_CHAR(TRUNC(SUM(getmisses)/SUM(gets)* 100, 5), 0999.99) || '%(less than 15%)'

"Missing Rate" FROM v$rowcache

<데이터베이스의 버퍼 캐시 재사용율>

SELECT (1 - (phy.value / (cur.value + con.value))) * 100 "Cache Hit Ratio"

FROM v$sysstat cur, v$sysstat con, v$sysstat phy

WHERE cur.name = 'DB_BLOCK_GETS' AND con.name = 'CONSISTENT GETS'

AND phy.name = 'PHYSICAL READS' 

  

<PK 보기>

select table_name, column_name, 'PK' pk, index_name

from all_ind_columns

where table_name = 'ACM'

사용자별 유용한 쿼리

<테이블에 대한 인덱스 확인>

SELECT index_name, table_name FROM user_indexes WHERE table_name = 'APG'

  

<테이블에 대한 제약조건 확인>
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'ADLFV'

<테이블에 대한 부가정보 확인>
SELECT table_name, tablespace_name, partitioned, nested
FROM user_tables
WHERE table_name = 'S_USER'

<테이블 제약조건 제거>
ALTER TABLE PKIUSER DROP CONSTRAINT PK_PKIUSER;

<테이블 제약조건 추가>
ALTER TABLE PKIUSER ADD (
  CONSTRAINT PK_PKIUSER PRIMARY KEY (PKIUSER_ID)

);

<프로시져 보기>

select * from user_procedures -- 프로시져 목록보기

select * from user_source -- 프로시져 소스보기