"DATABASE 조회 scripts"의 두 판 사이의 차이

1 DATABASE 조회 scripts[편집]

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;

"DATABASE 조회 scripts"의 두 판 사이의 차이

DB CAFE

2024년 7월 11일 (목) 21:57 판

목차

1 DATABASE 조회 scripts[편집]

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

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

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

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

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

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

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

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

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

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

1.11 DB UPTIME 조회[편집]

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

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

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

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

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

1.17 Data Pump jobs 정리[편집]

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

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

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

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

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

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

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

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