"DATABASE 조회 scripts"의 두 판 사이의 차이
DB CAFE
(새 문서: == DATABASE 조회 scripts == === 리두로그 멤버 조회 === <source lang=sql> col member for a56 set pagesize 299 set lines 299 select l.group#, l.thread#, f.member, l.archived...) |
|||
73번째 줄: | 73번째 줄: | ||
group by free.p | group by free.p | ||
/ | / | ||
+ | </source> | ||
+ | |||
+ | === 시간별 생성되는 아키이브 로그 조회 === | ||
+ | <source lang=sql> | ||
+ | |||
+ | 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 | ||
+ | / | ||
+ | </source> | ||
+ | |||
+ | === 현재 트랜잭션 상태 조회 === | ||
+ | <source lang=sql> | ||
+ | |||
+ | 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 | ||
+ | / | ||
+ | </source> | ||
+ | |||
+ | === 계정 잠금 사유 조회 === | ||
+ | <source lang=sql> | ||
+ | |||
+ | -- 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); | ||
+ | </source> | ||
+ | |||
+ | === 테이블에서 중복된 ROW 찾기 === | ||
+ | <source lang=sql> | ||
+ | |||
+ | --- 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) | ||
+ | / | ||
+ | </source> | ||
+ | |||
+ | === 데이터파일 RESIZE 스크립트 생성기 === | ||
+ | <source lang=sql> | ||
+ | |||
+ | 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); | ||
</source> | </source> |
2024년 7월 11일 (목) 21:32 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
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);