행위

DATABASE 조회 scripts

DB CAFE

Dbcafe (토론 | 기여)님의 2024년 7월 12일 (금) 15:53 판 (→‎DATABASE 조회 scripts)
thumb_up 추천메뉴 바로가기

목차

1 DB 정보 조회 스크립트[편집]

1.1 리두로그 멤버 조회[편집]

col member for a56
set pagesize 299
set lines 299
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) "Size (MB)"
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/

1.2 전체 테이블스페이스 DDL 추출[편집]

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_tablespace.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
spool off

1.3 유저에게 부여된 모든 권한 - DDL 추출[편집]

set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt "Enter username :"

--This line add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

-- This will generate the DDL for the user and add his objects,system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;

1.4 DB (데이터/템프/로그/남은공간) SIZE 조회[편집]

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

1.5 시간별 생성되는 아키이브 로그 조회[편집]

set lines 299
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

1.6 현재 트랜잭션 상태 조회[편집]

col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'

select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/

1.7 계정 잠금 사유 조회[편집]

-- Return code 1017 ( INVALID LOGIN ATTEMPT)
-- Return code 28000 ( ACCOUNT LOCKED)

set pagesize 1299
set lines 299
col username for a15
col userhost for a13
col timestamp for a39
col terminal for a23
SELECT username,userhost,terminal,timestamp,returncode
FROM dba_audit_session
WHERE username='&USER_NAME' and returncode in (1017,28000);

1.8 테이블에서 중복된 ROW 찾기[편집]

--- Reference metalink id - 332494.1
-- Save as duplicate.sql and run as @duplicate.sql

REM This is an example SQL*Plus Script to detect duplicate rows from
REM a table.
REM
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If more than
prompt one column is specified, you MUST separate with commas.
prompt
accept c prompt 'Column(s): '
prompt
select &&c from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

1.9 데이터파일 RESIZE 스크립트 생성기[편집]

generate resize datafile script without ORA-03297 error
 

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

1.10 DB 한달 증가량 조회[편집]

select to_char(creation_time, 'MM-RRRR') "Month"
     , sum(bytes)/1024/1024/1024 "Growth in GB
  from sys.v_$datafile
 where to_char(creation_time,'RRRR')='&YEAR_IN_YYYY_FORMAT'
 group by to_char(creation_time, 'MM-RRRR')
 order by to_char(creation_time, 'MM-RRRR');

1.11 DB UPTIME 조회[편집]

select to_char(startup_time, 'DD-MM-YYYY HH24:MI:SS'),floor(sysdate-startup_time) DAYS from v$Instance;

1.12 현재 scn 정보 조회[편집]

-- Get current scn value:

select current_scn from v$database;

-- Get scn value at particular time:

select timestamp_to_scn('19-JAN-08:22:00:10') from dual;

-- Get timestamp from scn:

select scn_to_timestamp(224292)from dual;


1.13 스키마(유저)의 모든 테이블 row건수[편집]

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype('select /*+ PARALLEL(8) */ count(*) cnt from "&&SCHEMA_NAME".'||table_name),'/ROWSET/ROW/CNT'))
rows_in_table from dba_TABLES
where owner='&&SCHEMA_NAME';


1.14 인덱스 사용량 모니터링[편집]

---Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.

-- First enable monitoring usage for the indexes. (운영중인 db에서는 사용금지. 라이브러리 캐시 락 발생됨.)

alter index siebel.S_ASSET_TEST monitoring usage;

--Below query to find the index usage:

select * from v$object_usage;

1.15 SQL 쿼리 HTML로 출력하기[편집]

We can spool output of an sql query to html format:

set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>EMPLOYEE REPORT</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"
spool report.html
Select * from scott.emp;
spool off
exit

1.16 오라클 패치 정보 조회[편집]

--- From 12c onward

set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;

--- For 11g and below:

set lines 2000
select * from dba_registry_history;

1.17 Data Pump jobs 정리[편집]

-- Find the orphaned Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%' and state='NOT RUNNING'
ORDER BY 1,2;

-- Drop the tables

SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and job_name NOT LIKE 'BIN$%'

1.18 설치된 RDBMS 컴포넌트[편집]

col comp_id for a10
col comp_name for a56
col version for a12
col status for a10
set pagesize 200
set lines 200
set long 999
select comp_id,comp_name,version,status from dba_registry;


1.19 AWR retention 기간 조회/변경[편집]

-- View current AWR retention period

select retention from dba_hist_wr_control;

-- Modify retention period to 7 days and interval to 30 min

select dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 10080);

NOTE - 7 DAYS = 7*24*3600= 10080 minutes

1.20 언두 리텐션 최적 사이즈 찾기[편집]

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value, 1, 25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) /
(1024 * 1024) "NEEDED UNDO SIZE [MByte]"
FROM (SELECT SUM(a.bytes) undo_size
FROM gv$datafile a, gv$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#) d,
gv$parameter e,
gv$parameter f,
(SELECT MAX(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
FROM v$undostat) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';


1.21 오래된 AWR 스냅샷 Purge[편집]

-- Find the AWR snapshot details.

select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id

-- Purge snapshot between snapid 612 to 700

execute dbms_workload_repository.drop_snapshot_range(low_snap_id =>612 , high_snap_id =>700);

-- Verify again

select snap_id,begin_interval_time,end_interval_time from sys.wrm$_snapshot order by snap_id

1.22 AWR window_size 조사/변경[편집]

-- Check the current moving window baseline size:

select BASELINE_TYPE,MOVING_WINDOW_SIZE from dba_hist_baseline;

-- Modify window_size to (7 days):

execute dbms_workload_repository.modify_baseline_window_size(window_size=> 7);

1.23 열린 DBLINK 정보 조회[편집]

set pagesize 200
set lines 200
col db_link for a19
set long 999

SELECT db_link,
owner_id,
logged_on,
heterogeneous,
open_cursors,
in_transaction,
update_sent
FROM gv$dblink
ORDER BY db_link;

1.24 현재 사용중인 리루로그량  %[편집]

SELECT le.leseq "Current log sequence No"
     , 100*cp.cpodr_bno/le.lesiz "Percent Full"
     , cp.cpodr_bno "Current Block No"
     , le.lesiz "Size of Log in Blocks"
  FROM x$kcccp cp, x$kccle le
 WHERE le.leseq =CP.cpodr_seq
   AND bitand(le.leflg,24) = 8

1.25 FK 컬럼에 인덱스가 없는 테이블 조회[편집]

select * from (
select c.table_name, co.column_name, co.position column_position
from user_constraints c, user_cons_columns co
where c.constraint_name = co.constraint_name
and c.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;

select
a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,nvl(c.column_name,'***No Index***') ind_column
from user_constraints a
join
user_cons_columns b on a.constraint_name = b.constraint_name
left outer join
user_ind_columns c on b.column_name = c.column_name
and b.table_name = c.table_name
where constraint_type = 'R'
order by 2,1;

1.26 DB서버 cpu 메모리 현황[편집]

set pagesize 200
set lines 200
col name for a21
col stat_name for a25
col value for a13
col comments for a56

select STAT_NAME
     , to_char(VALUE) as VALUE 
     , COMMENTS 
  from v$osstat 
 where stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
 union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB' 
     , COMMENTS 
  from v$osstat 
 where stat_name IN ('PHYSICAL_MEMORY_BYTES');

1.27 DB incarnation 정보 조회[편집]

assignment * incarnation
  1. 데이터베이스를 resetlogs 옵션으로 open 할때 incarnation이 생성됨(reset)
  2. 일반 recover 완료 후에는 resetlogs 없이도 정상 open 할 수도 있지만 Point In Time Recovery 나 using backup controlfile로 복구한 후에는 resetlogs 옵션으로 데이터베이스를 open 해야 하므로 데이터베이스의 새로운 incarnation이 생성됨
  3. 데이터베이스는 서로 다른 두 redo stream의 SCN이 동일하지만 서로 다른 시간에 발생한 경우 혼란을 피하기 위해 새로운 incarnation을 필요로함
  4. 데이터베이스에 잘못된 redo를 적용하면 데이터베이스가 손상을 초래함.
  5. 단일 데이터베이스에 여러 개의 incarnation이 존재함에 따라 rman이 현재 incarnation 경로에 없는 백업을 처리하는 방식이 결정됨
  6. 일반적으로 current incarnation의 데이터베이스를 사용하는 것이 좋음
  7. 일부 경우에 데이터베이스를 이전 incarnation로 재설정하는 것이 가장 좋은 방법일수도 있음


set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on

select INCARNATION# INC#
     , RESETLOGS_CHANGE# RS_CHANGE#
     , RESETLOGS_TIME
     , PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#
     , STATUS
     , FLASHBACK_DATABASE_ALLOWED FB_OK 
  from v$database_incarnation;


1.28 DB 타임존 조회[편집]

SELECT version FROM v$timezone_file;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

1.29 전체 DB 사이즈(사용량/남은량/총량) 조회[편집]

set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'


WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;

1.30 테이블스페이스 사용량 모니터링[편집]

set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;

1.31 언두 테이블스페이스 사용량 모니터링[편집]

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
  from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
          from dba_data_files a, dba_tablespaces b
         where a.tablespace_name = b.tablespace_name
           and b.contents = 'UNDO'
         group by b.tablespace_name) a
       , (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
            from DBA_UNDO_EXTENTS c
           where status <> 'EXPIRED'
           group by c.tablespace_name) b
 where a.tablespace_name = b.tablespace_name;

1.32 템프 테이블스페이스 사용량 모니터링[편집]

select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;

1.33 블럭킹 세션 조회[편집]

SELECT s.inst_id
     , s.blocking_session
     , s.sid
     , s.serial#
     , s.seconds_in_wait
  FROM gv$session s
 WHERE blocking_session IS NOT NULL;

1.34 오래 수행중인 세션 조회[편집]

select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining
  from gv$session_longops
 where totalwork <> sofar
/

1.35 현재 락이 발생된 세션 조회[편집]

col session_id head 'Sid' form 9999
col object_name head "Table|Locked" form a30
col oracle_username head "Oracle|Username" form a10 truncate
col os_user_name head "OS|Username" form a10 truncate
col process head "Client|Process|ID" form 99999999
col mode_held form a15

select lo.session_id,lo.oracle_username,lo.os_user_name
     , lo.process,do.object_name
     , decode(lo.locked_mode,0, 'None'			
                            ,1, 'Null'
                            ,2, 'Row Share (SS)'
                            ,3, 'Row Excl (SX)'
                            ,4, 'Share'
                            ,5, 'Share Row Excl (SSX)'
                            ,6, 'Exclusive'
                            ,to_char(lo.locked_mode)) mode_held
  from v$locked_object lo, dba_objects do
 where lo.object_id = do.object_id
 order by 1,5
/


1.36 프로시져로 실행된 SQL 조회[편집]

-- Below script will provide the dependent queries getting triggered from a procedure. 

SELECT s.sql_id, s.sql_text
  FROM gv$sqlarea s 
  JOIN dba_objects o 
    ON s.program_id = o.object_id
   and o.object_name = '&procedure_name';

1.37 os 프로세스 id 로 sql 찾기[편집]

- Get sid from os pid ( server process)

col sid format 999999
col username format a20
col osuser format a15

select b.spid,a.sid, a.serial#,a.username, a.osuser
  from v$session a, v$process b
 where a.paddr= b.addr
   and b.spid='&spid'
 order by b.spid;


1.38 SID로 프로세스 아이디 조회[편집]

set lines 123
col USERNAME for a15
col OSUSER for a8
col MACHINE for a15
col PROGRAM for a20

SELECT b.spid
     , a.username
     , a.program
     , a.osuser
     , a.machine
     , a.sid
     , a.serial#
     , a.status
  FROM gv$session a, gv$process b
 WHERE addr = paddr(+)
   AND sid = &sid;

1.39 모든 세션 유저 KILL[편집]

BEGIN
FOR r IN (select sid,serial# from v$session where username = 'TEST_ANB')
LOOP
EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid
|| ',' || r.serial# || '''';
END LOOP;
END;
/


1.40 sql_id가 같은 모든 세션 KILL (주로 병렬쿼리 kill 시킬때 )[편집]

select 'alter system kill session ' ||''''||SID||','||SERIAL#||' immediate ;' from v$session
where sql_id='&sql_id';

# RAC용 

select 'alter system kill session ' ||''''||SID||','||SERIAL#||',@'||inst_id||''''||' immediate ;'
from gv$session where sql_id='&sql_id'

1.41 병렬 쿼리 상세 조회[편집]

col username for a9
col sid for a8
set lines 299

select s.inst_id
     , decode(px.qcinst_id,NULL,s.username
     , ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username"
     , decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" 
     , to_char( px.server_set) "Slave Set"
     , to_char(s.sid) "SID"
     , decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID"
     , px.req_degree "Requested DOP"
     , px.degree "Actual DOP", p.spid
  from gv$px_session px
     , gv$session s
     , gv$process p
 where px.sid=s.sid (+) 
   and px.serial#=s.serial# 
   and px.inst_id = s.inst_id
   and p.inst_id = s.inst_id
   and p.addr=s.paddr
 order by 5 , 1 desc
/

1.42 SNIPED(짤린) 세션 KILL[편집]

-- It will generate kill session statements for all snipped sessions:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' 
  from v$session where status='SNIPED' ;

1.43 높은 실행 시간 TOP 쿼리[편집]

-- Queries in last 1 hour ( Run from Toad, for proper view)
 
Select module,parsing_schema_name
     , inst_id,sql_id,CHILD_NUMBER
     , sql_plan_baseline,sql_profile
     , plan_hash_value,sql_fulltext
     , to_char(last_active_time,'DD/MM/YY HH24:MI:SS' )
     , executions
     , elapsed_time/executions/1000/1000
     , rows_processed,sql_plan_baseline 
  from gv$sql 
 where last_active_time>sysdate-1/24 
   and executions <> 0 
  order by elapsed_time/executions desc


1.44 병렬 쿼리 모니터링[편집]

select
      s.inst_id,
      decode(px.qcinst_id,NULL,s.username,
            ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
      decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
      to_char( px.server_set) "Slave Set",
      to_char(s.sid) "SID",
      decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
      px.req_degree "Requested DOP",
     px.degree "Actual DOP", p.spid
   from
     gv$px_session px,
     gv$session s, gv$process p
   where
     px.sid=s.sid (+) and
     px.serial#=s.serial# and
     px.inst_id = s.inst_id
     and p.inst_id = s.inst_id
     and p.addr=s.paddr
  order by 5 , 1 desc

1.45 열려있는 커서 조회[편집]

-- Current open cursor

select a.value, s.username, s.sid, s.serial#
  from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';

 -- Max allowed open cursor and total open cursor

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

1.46 [ash] 세션 로그인 이력[편집]

SELECT c.username
       , a.SAMPLE_TIME
       , a.SQL_OPNAME
       , a.SQL_EXEC_START
       , a.program
       , a.module
       , a.machine
       , b.SQL_TEXT
    FROM DBA_HIST_ACTIVE_SESS_HISTORY a
       , dba_hist_sqltext b
       , dba_users c
   WHERE a.SQL_ID = b.SQL_ID(+)
     AND a.user_id = c.user_id
     AND c.username = '&username'
ORDER BY a.SQL_EXEC_START ASC;

1.47 버퍼캐시 히트율[편집]

SELECT ROUND( (1 - (phy.VALUE / (cur.VALUE + con.VALUE))) * 100, 2 )    "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'
/

1.48 디스크 READ가 많은 유저 TOP SQL[편집]

SELECT username                             users
       , ROUND( DISK_READS / Executions )     DReadsExec
       , Executions                           Exec
       , DISK_READS                           DReads
       , sql_text
    FROM gv$sqlarea a, dba_users b
   WHERE a.parsing_user_id = b.user_id
     AND Executions > 0
     AND DISK_READS > 100000
   ORDER BY 2 DESC;


1.49 프로시져 오브젝트로 수행중인 세션 / SQL ID[편집]

SELECT sid
     , sql_id
     , serial#
     , status
     , username
     , program
  FROM v$session
 WHERE PLSQL_ENTRY_OBJECT_ID IN ( SELECT object_id
                                    FROM dba_objects
                                   WHERE object_name IN ('&PROCEDURE_NAME') );


1.50 버퍼 캐시 사용량 조회[편집]

col object_name format a30
col to_total format 999.99


  SELECT owner
       , object_name
       , object_type
       , COUNT
       , (COUNT / VALUE) * 100     to_total
    FROM (  SELECT a.owner
                 , a.object_name
                 , a.object_type
                 , COUNT( * )     COUNT
              FROM dba_objects a, x$bh b
             WHERE a.object_id = b.obj
               AND a.owner NOT IN ('SYS', 'SYSTEM')
          GROUP BY a.owner, a.object_name, a.object_type
          ORDER BY 4)
       , v$parameter
   WHERE name = 'db_cache_size'
     AND (COUNT / VALUE) * 100 > .005
ORDER BY to_total DESC
/

1.51 롤백 트랜잭션 조회[편집]

SELECT state
     , UNDOBLOCKSDONE
     , UNDOBLOCKSTOTAL
     , UNDOBLOCKSDONE / UNDOBLOCKSTOTAL * 100
  FROM gv$fast_start_transactions;

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

SELECT usn
     , state
     , undoblockstotal                     "Total"
     , undoblocksdone                      "Done"
     , undoblockstotal - undoblocksdone    "ToDo"
     , DECODE(
               cputime
             , 0, 'unknown'
             ,   SYSDATE
               + (  (  (undoblockstotal - undoblocksdone)
                     / (undoblocksdone / cputime))
                  / 86400) )               "Estimated time to complete"
  FROM v$fast_start_transactions;

  SELECT a.sid
       , a.username
       , b.xidusn
       , b.used_urec
       , b.used_ublk
    FROM v$session a, v$transaction b
   WHERE a.saddr = b.ses_addr
ORDER BY 5 DESC;

1.52 테이블/컬럼 사용 통계[편집]

set lines 150
set pages 500
col table_name for a20
col column_name for a20

  SELECT a.object_name     table_name
       , c.column_name
       , equality_preds
       , equijoin_preds
       , range_preds
       , like_preds
    FROM dba_objects a, col_usage$ b, dba_tab_columns c
   WHERE a.object_id = b.OBJ#
     AND c.COLUMN_ID = b.INTCOL#
     AND a.object_name = c.table_name
     AND b.obj# = a.object_id
     AND a.object_name = '&table_name'
     AND a.object_type = 'TABLE'
     AND a.owner = '&owner'
ORDER BY 3 DESC, 4 DESC, 5 DESC;


1.53 백그라운드 프로세스 상세 조회[편집]

col ksbddidn for a15
col ksmfsnam for a20
col ksbdddsc for a60
set lines 150 pages 5000

  SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
    FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
   WHERE ksbdd.indx = ksbdp.indx
     AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;

1.54 오라클 DB는 몇비트 인가?[편집]

select
length(addr)*4 || '-bits' word_length
from
v$process
where
ROWNUM =1;

1.55 오라클 라이센스 사용 정보[편집]

/* Formatted on 2024/07/11 오후 11:55:53 (QP5 v5.391) */
SELECT samp.dbid
     , fu.name
     , samp.version
     , detected_usages
     , total_samples
     , DECODE( TO_CHAR( last_usage_date, 'MM/DD/YYYY, HH:MI:SS' )
             , NULL, 'FALSE'
             , TO_CHAR( last_sample_date, 'MM/DD/YYYY, HH:MI:SS' ), 'TRUE'
             , 'FALSE' )    currently_used
     , first_usage_date
     , last_usage_date
     , aux_count
     , feature_info
     , last_sample_date
     , last_sample_period
     , sample_interval
     , mt.description
  FROM wri$_dbu_usage_sample      samp
     , wri$_dbu_feature_usage     fu
     , wri$_dbu_feature_metadata  mt
 WHERE samp.dbid = fu.dbid
   AND samp.version = fu.version
   AND fu.name = mt.name
   AND fu.name NOT LIKE '_DBFUS_TEST%'
   AND                                          /* filter out test features */
       BITAND( mt.usg_det_method, 4 ) != 4  /* filter out disabled features */;


1.56 오라클 OPTIMIZER PROCESSING RATE[편집]

SELECT OPERATION_NAME, DEFAULT_VALUE
  FROM V$OPTIMIZER_PROCESSING_RATE
 WHERE OPERATION_NAME IN ('IO_BYTES_PER_SEC'
                         ,'CPU_BYTES_PER_SEC'
                         , 'CPU_ROWS_PER_SEC');
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DATABASE_조회_scripts&oldid=12265"