set pagesize 100
set linesize 200
col name format a45
col member format a45
1. 데이터파일 확인
select a.file#, b.name ,a.name, a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;
2. online 백업 SCN 확인
select a.name, a.checkpoint_change#, b.status, b.change#, to_char(b.time,'yyyy-mm-dd hh24:mi:ss') time
from v$datafile a, v$backup b
where a.file# = b.file#;
3. redo log 그룹 확인
select a.group#,a.thread#, a.sequence#, b.member, a.archived, a.status,a.bytes/1024/1024 as MB, a.first_change#, a.next_change#
from v$log a, v$logfile b
where a.group# = b.group#;
4. v$archived_log 뷰 확인
select sequence#,name, first_change#, next_change# from v$archived_log;
5. 생성 테이블 데이터파일 주소
select f.tablespace_name, f.file_name,count(*)
from dba_extents e, dba_data_files f
where f.file_id = e.file_id
and e.segment_name = 'CBC_LATCH'
and e.owner = 'HR'
group by f.tablespace_name, f.file_name;
6. 현재 데이터파일의 전체사이즈, 사용사이즈, 남은 사이즈 확인
SELECT a.tablespace_name,
b.file_name,
b.bytes/1024/1024 as "Total Size MB",
(b.bytes - c.free_byte)/1024/1024 as "Used Size MB",
c.free_byte/1024/1024 as "Free Size MB",
b.autoextensible
FROM dba_tablespaces a, dba_data_Files b,
(SELECT tablespace_name, file_id, sum(bytes) AS free_byte
FROM dba_free_space
GROUP BY tablespace_name, file_id) c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
AND b.file_id = c.file_id;
7. UNDO SEGMENT 확인
select segment_id, segment_name,owner, tablespace_name, status
from dba_rollback_segs;
8. 리두 과거정보 확인
select * from v$log_history;
9. undo 사용시 transaction 정보
select s.username, s.sid, s.serial#, r.name, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t, v$rollname r
where s.taddr = t.addr
and t.xidusn = r.usn;
10. online 상태와 pending offline 상태의 undo를 확인할때
select a.name, b.status
from v$rollname a, v$rollstat b
where a.usn = b.usn;
11. log miner
supplemental 파라미터 확인
select supplemental_log_data_min from v$database;
supplemental 변경
alter database add supplemental log data;
분석해야할 redo파일 설정
begin
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ORA19C/redo03.log', options=> dbms_logmnr.new); -- 처음 분석 대상 파일은 new
dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/ORA19C/redo02.log', options=>dbms_logmnr.addfile); -- 이후부터는 addfile로 진행
end;
/
설정한 redo 파일 확인
select db_name, filename from v$logmnr_logs;
분석 시작
begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);--분석기
end;
/
분석한 내용 확인
select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') as timestamp, operation,sql_redo, sql_undo from v$logmnr_contents where seg_owner = 'HR';
분석 종료
execute dbms_logmnr.end_logmnr;
12. 제약조건 확인
col constraint_name format a15
col search_condition format a25
col r_constraint_name format a15
col index_name format a15
select constraint_name, constraint_type, search_condition, r_constraint_name, status, validated, index_name
from dba_constraints
where owner='HR' and table_name = 'EMPLOYEES';
13. 히든 파라미터 확인
select a.ksppinm as parameter,b.ksppstvl as session_value, c.ksppstvl as instance_value
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and a.ksppinm = '_log_simultanous_copies';
14. 인덱스 확인
select c.column_name ,u.constraint_name, u.constraint_type, u.search_condition,u.r_constraint_name, u.index_name
from dba_constraints u, dba_cons_columns c
where u.constraint_name = c.constraint_name
and u.table_name in ('EMPLOYEES','DEPARTMENTS')
and u.owner = 'HR'
order by 1;
15. 테이블 통계정보 확인
select num_rows, blocks, avg_row_len, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_tables
where owner='HR'
and table_name='EMP';
16. 인덱스 통계정보 확인
select
index_name,
blevel,
leaf_blocks,
distinct_keys,
clustering_factor,
num_rows,
to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_indexes
where owner='HR'
and table_name='EMP';
17. 컬럼 통계정보 확인
select column_name, num_distinct, num_buckets, histogram
from dba_tab_columns
where owner='HR'
and table_name='TAB';
18. 파티션 정보 확인
select partition_position,partition_name,high_value, tablespace_name, num_rows,blocks,avg_row_len, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_tab_partitions
where table_owner='HR'
and table_name='EMP_LOCAL';
19. 세그먼트 정보 확인
select partition_name, tablespace_name, blocks, bytes from dba_segments where segment_name= 'EMP_LOCAL';
20. 파티션 정보와 조인한 인덱스 정보
select i.index_name, i.uniqueness, p.locality, p.alignment, i.partitioned, p.partition_count
from dba_indexes i, dba_part_indexes p
where i.table_name = 'EMP_LOCAL'
and i.owner = 'HR'
and p.table_name = i.table_name
and p.index_name = i.index_name;
