oracle query – 관리 명령.

오라클 관리에 필요한 쿼리들.

# 오라클이 spfile(동적파라메터)을 사용하는지 pfile(정적파라메터)을 사용하는지 확인

show parameter pfile;

# oracle Background process 확인

select program, pid, spid from v$process;

#v$sysstat 통계 보기
# class 1 : instance 전반
# class 2 : redo log buffer 통계
# class 4 : log 관련 통계
# class 8 : database buffer cache 통계
# class 16 : OS 관련 통계
# class 32 : 병렬처리 관련 통계
# class 64 : table access 통계
# class 128 : debug용

select name, class, value from v$sysstat;

#로그온중인 사용자 정보 보기.

select sid, username, type, server from v$session;

# 10000byte(10KB)이상 PGA를 사용하는 세션 보기.

select username, name, value 
from v$statname n, v$session s, v$sesstat t
where s.sid = t.sid and n.STATISTIC# = t.statistic#
and s.type = 'USER' and username is not null
and n.name = 'session pga memory' 
and t.value > 10000;

#oracle Process 사용 현황보기

select spid, program, pga_used_mem, pga_alloc_mem
from v$process;

select name, description from v$bgprocess;

#dedicate server -> shared server 전환

alter system set dispatchers = '(protocol = tcp)';

#shared server, dispatcher 보기

select name, status, circuit from v$shared_server;
select name, status from v$dispatcher;

#sga 보기(SGA = Shared_pool + buffer_cache + redo log buffer+ java pool + large pool+etc.)

show sga;

select name, value, issys_modifiable from v$parameter
where name in ('shared_pool_size', 'db_cache_size', 'log_buffer');

select name, value, issys_modifiable from v$parameter
where name in ('large_pool_size', 'java_pool_size');

show parameter sga;

# SGA 크기 변경(instance 재실행 필요)

alter system set sga_max_size = 300M scope=spfile;

#shared_pool_size 변경

alter system set shared_pool_size=100M;

#db_cache_size 변경

alter system set db_cache_size=150M;

#cache_size 확인

select name, value, issys_modifiable from v$parameter
where name like '%cache_size';

#PGA 확인

show parameter pga;
show parameter sort_area_size;

#sort 횟수와 대상행수 조회

select * from v$sysstat
where name like '%sort%';

#Sort 영역 사용량 조회(디스크sort 가 전체의 5%초과할때 sort memory 부족함)

select m.value memory, d.value disk
from v$sysstat m, v$sysstat d
where m.name = 'sorts (memory)' and d.name = 'sorts (disk)';

# SGA 상태 확인

select * from v$sgastat order by pool desc, name;

# 제어화일 확인

select * from v$controlfile;

select * from v$controlfile_record_section;

# data 화일 확인

select * from v$datafile;
select * from dba_data_files;
select file#, name, status, bytes/1024/1024 MB from v$datafile;
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, STATUS  from dba_data_files;

# temporary 화일 확인

select * from v$tempfile;
select * from dba_temp_files; 

select STATUS, BYTES/1024/1024 MB, NAME from v$tempfile;
select FILE_NAME, BYTES/1024/1024 MB, STATUS from dba_temp_files;

# Temporary 테이블 스페이스 현황 보기

select tablespace_name, sum(bytes)/1024/1024 MB
from dba_temp_files
group by tablespace_name;

# Temporary 테이블 스페이스 High Watermark 확인

select tablespace_name, sum(bytes_cached)/1024/1024 MB
from v$temp_extent_pool
group by tablespace_name;

# Temporary 테이블스페이스 사용량 조회

select ss.tablespace_name, sum(ss.used_blocks*ts.blocksize)/1024/1024 MB
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;

# REDO 로그 화일 확인

select * from v$logfile;

# 로그화일 상태 확인

select * from v$log;

# archive log 화일 확인

select * from v$archived_log;

show parameter archive;

# 화일 입출력 회수

select FILE#, PHYRDS, PHYWRTS, PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM
from v$filestat;

select a.FILE#, b.FILE_NAME, a.PHYRDS, a.PHYWRTS, a.PHYBLKRD, a.PHYBLKWRT, a.READTIM, a.WRITETIM
from  v$filestat a, dba_data_files b
where a.file#=b.file_id;

select a.FILE#, b.NAME, a.PHYRDS, a.PHYWRTS, a.PHYBLKRD, a.PHYBLKWRT, a.READTIM, a.WRITETIM
from v$filestat a, v$datafile b
where a.file# = b.file#

# TABLE 조작 횟수

select name, value from v$sysstat where name like '%table scan%';

# Tablespace Extent(DICTIONARY 인지 LOCAL인지)

select TABLESPACE_NAME, EXTENT_MANAGEMENT from dba_tablespaces;

# Tablespace EXTENT 방법 (LOCAL 결과 SYSTEM 이면 자동)

select tablespace_name, allocation_type, initial_extent
from dba_tablespaces
where extent_management='LOCAL';

select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE, MAX_EXTENTS
from dba_tablespaces
where EXTENT_MANAGEMENT = 'DICTIONARY';

# tablespace, segment 조회

select tablespace_name, segment_name, owner
from dba_segments
where tablespace_name in ('userts', 'sampts');

# system tablespace 사용량 조회

select username, tablespace_name, bytes, max_bytes
from dba_ts_quotas;

# user tablespace 조회

select username, default_tablespace, file_name
from dba_users, dba_data_files
where default_tablespace = tablespace_name;

# temporary 화일의 auto extend 확인

select file_name, tablespace_name, autoextensible, bytes
from dba_temp_files;

# data file의 최대 용량

select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES
from dba_data_files;

# 어떤테이블에 어떤 인덱스가 정의되어 있는지 확인.

select TABLE_NAME, INDEX_NAME, INDEX_TYPE, UNIQUENESS
from dba_indexes
where owner in ('SCOTT', 'HR');

# 인덱스가 오름차순인지 내림차순인지 확인.

select index_name, table_name, descend
from dba_ind_columns
where index_owner in ('SCOTT', 'HR');

# 어느 테이블에 synonym 오즈젝트가 작성되어 있는지 확인.

select table_name, synonym_name
from dba_synonyms
where owner in ('SCOTT', 'HR');

# 어느 테이블에 어떤 뷰가 작성되어 있는지 확인.

select VIEW_NAME, TEXT
from dba_views;

# PERFSTAT 유저의 테이블 제쟉확인.

select OWNER, CONSTRAINT_NAME, TABLE_NAME
from dba_constraints
where owner in ('PERFSTAT');

# 수동으로 REDO 로그 화일 전환
# 사용중인 로그화일 확인(CURRENT)

select GROUP#, SEQUENCE#, STATUS
from v$log;

alter system switch logfile;

# 테이블 스페이스의 확장
# 화일 크기 증가시키는 경우

alter database datafile '.dbf' resize 150M;

# 화일개수를 증가시키는 경우

alter tablespace tablespace-name add datafile ' .dbf' size 10M;

# 자신이 작성한 테이블 확인

select * from cat;

# primery key가 없는 테이블 확인

select table_name from user_tables where not exists(
select 1 from user_constraints
where user_constraints.table_name = user_tables.table_name
and user_constraints.constraint_type = 'P');

# 사용자 snowfox의 테이블 ‘TEST’ 분석과 결과 보기(table이름은 대문자로)

analyze table snowfox.TEST compute statistics;

select table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, last_analyzed
from dba_tables
where table_name='TEST';

# 테이블스페이스 사용량 보기(MB단위)

select a.tablespace_name,
               round(a.bytes_alloc / 1024 / 1024, 2) current_size,
               round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) free_size,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) used_size,
               round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) free_rate,
               100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) used_rate,
               round(maxbytes/1048576,2) max_size
        from   ( select f.tablespace_name,
                        sum(f.bytes) bytes_alloc,
                        sum(decode(f.autoextensible, 'yes',f.maxbytes,'no', f.bytes)) maxbytes
                 from dba_data_files f
                 group by tablespace_name) a,
               ( select f.tablespace_name,
                        sum(f.bytes)  bytes_free
                 from dba_free_space f
                 group by tablespace_name) b
        where a.tablespace_name = b.tablespace_name (+)
        union
        select tablespace_name,
               round(sum(bytes_used + bytes_free) / 1048576, 2),
               round(sum(bytes_free) / 1048576,2),
               round(sum(bytes_used) / 1048576,2),
               round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) free_rate,
               100 - round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) used_rate,
               round(max(bytes_used + bytes_free) / 1048576, 2)
        from   sys.v_$temp_space_header
        group by tablespace_name
        order by 1;