sys@ARTDOM>col name format a8
sys@ARTDOM>col check_chng format 9,999,999,999,999
sys@ARTDOM>col arch_chng format 9,999,999,999,999
sys@ARTDOM>select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE# check_chng,
2 ARCHIVELOG_CHANGE# arch_chng from v$database ;
NAME CREATED LOG_MODE CHECK_CHNG ARCH_CHNG
-------- --------- ------------ ------------------ ------------------
ARTDOM 19-JUN-08 NOARCHIVELOG 9,096,120,977,434 0
sys@ARTDOM>col value format 99,999,999,999,999 heading 'Size|(BYTES)'
sys@ARTDOM>col KBVAL format 99,999,999,999,999 heading 'SIZE|(BK)'
sys@ARTDOM>break on report
sys@ARTDOM>compute sum of value kbval on report
sys@ARTDOM>select name, value, round(value/1024,1) kbval from v$sga ;
Size SIZE
SGA SEGMENT (BYTES) (BK)
-------------------- ------------------- -------------------
Fixed Size 1,329,584 1,298
Variable Size 316,913,232 309,486
Database Buffers 855,638,016 835,584
Redo Buffers 524,288 512
------------------- -------------------
sum 1,174,405,120 1,146,880
sys@ARTDOM>select * from v$sgastat where name ='free memory';
POOL SGA SEGMENT BYTES
------------ -------------------- ----------
shared pool free memory 201733152
large pool free memory 16157440
java pool free memory 9576064
sys@ARTDOM>show parameter ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
__db_cache_size big integer 816M
__java_pool_size big integer 16M
__large_pool_size big integer 16M
__shared_pool_size big integer 256M
active_instance_count integer
aq_tm_processes integer 0
archive_lag_target integer 0
asm_diskgroups string
asm_diskstring string
asm_power_limit integer 1
...
user_dump_dest string /app/oracle/admin/ARTDOM/udump
utl_file_dir string
workarea_size_policy string AUTO
sys@ARTDOM> select name, value from v$sysstat order by name, value
SELECT SID, SERIAL#, STATUS, TADDR, OSUSER, USERNAME, PROGRAM,
TO_CHAR(LOGON_TIME,'YYYY/MM/DD HH:MI') LOGON_TIME
FROM V$SESSION
WHERE TYPE != 'BACKGROUND'
AND STATUS ='ACTIVE';
SELECT S.SID, S.SERIAL#, S.STATUS, S.USERNAME, S.OSUSER, S.PROCESS, SS.STATISTIC#, SS.VALUE
FROM V$SESSION S, V$SESSTAT SS
WHERE S.SID = SS.SID AND SS.STATISTIC# = 12 ;
more check_obj.sql
set verify off linesize 120
undef obj_nm
col today format a20 new_value curr_time
col today noprint
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') today from dual ;
prompt =========================================================
prompt == Session/program status for db object ==
prompt ==> DATA : [ &CURR_TIME ]
prompt =========================================================
col object format a23
col program format a25
col cnt format 9,999
break on object
SELECT B.OBJECT,SUBSTR(A.PROGRAM,1,15) AS PROGRAM, COUNT(*) AS CNT
FROM V$SESSION A, V$ACCESS B
WHERE A.SID = B.SID
AND B.OWNER NOT IN ('SYS')
AND A.TYPE != 'BACKGROUND'
AND B.OBJECT LIKE UPPER('&obj_nm')||'%'
GROUP BY B.OBJECT, SUBSTR(A.PROGRAM,1,15) ;
SELECT S.SID, S.SERIAL#, S.STATUS "SESSION", S.OSUSER, S.TERMINAL,
S.USERNAME "DBUSER", R.SEGMENT_NAME "ROLLBACK", T.STATUS "TRANSACTION",
S.TADDR, T.USED_UBLK, T.USED_UREC, T.START_TIME
FROM V$SESSION S, V$TRANSACTION T, DBA_ROLLBACK_SEGS R
WHERE S.TADDR = T.ADDR
AND T.XIDUSN = R.SEGMENT_ID ;
SELECT A.SID, A.OSUSER, A.TERMINAL, COUNT(B.SID) "CURSOR",
A.USERNAME, A.PROGRAM, A.STATUS
FROM V$SESSION A, V$OPEN_CURSOR B
WHERE A.SID = B.SID(+)
GROUP BY A.SID, A.OSUSER, A.TERMINAL, A.USERNAME, A.PROGRAM, A.STATUS;
more check_run_sql.sql
set verify off linesize 120
accept pgm_procs char default 'O' prompt 'Enter program prcess id:'
col today format a20 new_value curr_time
col today noprint
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') today from dual ;
prompt ============================================================
prompt == process/sql status ==
prompt => DATE : [ &CURR_TIME ]
prompt ============================================================
col pgm_ps format a9
col sid format 999
col serial@ format 99999
col machine format a10
col db_object format a22
col sql_text format a40
col row_x format 99,999
break on pgm_ps on program on sid
SELECT B.PROCESS AS PGM_PS, B.SID, SUBSTR(MACHINE,1,10) AS MACHINE, ROWS_PROCESSED AS ROW_X, C.SQL_TEXT AS SQL_TEXT
FROM V$SESSION B, V$SQL C
WHERE ( B.SQL_ADDRESS = C.ADDRESS OR B.PREV_SQL_ADDR = C.ADDRESS )
AND B.USERNAME IS NOT NULL
AND C.SQL_TEXT NOT LIKE 'ALTER SESSION SET %'
AND C.SQL_TEXT NOT LIKE 'SELECT VLAUE$ FROM PROPS$%'
AND B.PROCESS = DECODE(&PGM_PROCS,'O',B.PROCESS,&PGM_PROCS)
ORDER BY MACHINE ;
set verify off linesize 120 pagesize 100
col today format a20 new_value curr_time
col today noprint
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') today from dual ;
prompt ============================================================
prompt == control and log file and data file ==
prompt => DATE : [ &CURR_TIME ]
prompt ============================================================
col db format a5
col log_db format a10
col phy_db format a50
col sz_kb format 9,999,999
SELECT 'DATA' AS DB, TABLESPACE_NAME AS LOG_DB, FILE_NAME AS PHY_DB, BYTES/1024 AS SZ_KB
FROM DBA_DATA_FILES
UNION ALL
SELECT 'LOG 'AS DB, TO_CHAR(V1.GROUP#) AS LOG_DB, V1.MEMBER AS PHY_DB, V2.BYTES/1024 AS SZ_BK
FROM V$LOGFILE V1, V$LOG V2
WHERE V1.GROUP# = V2.GROUP#
UNION ALL
SELECT 'CTL ' AS DB,'CONTROL' AS LOG_DB, NAME AS PHY_DB, 2 AS SZ_BK
FROM V$CONTROLFILE ;
more "check_datafiles.sql"
set verify off linesize 120 pagesize 100
col today format a20 new_value curr_time
col today noprint
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') today from dual ;
prompt ============================================================
prompt == free space usage pct% for each tablespace and datafile ==
prompt => DATE : [ &CURR_TIME ]
prompt ============================================================
col tablespace_name format a10
col file_name format a30
col free_kb format 999,999,999
col tot_kb format 999,999,999
col usage_p format 999.9
SELECT A.TABLESPACE_NAME, A.FILE_NAME, SUM(A.BYTES)/1024 AS TOT_KB, NVL(SUM(SZ_KB),0) AS FREE_KB,
NVL((1. - SUM(SZ_KB)/(SUM(A.BYTES)/1024.))*100,100) AS USAGE_P
FROM DBA_DATA_FILES A,
(SELECT FILE_ID, SUM(BYTES)/1024. AS SZ_KB
FROM DBA_FREE_SPACE
GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID(+)
GROUP BY A.TABLESPACE_NAME, A.FILE_NAME ;
more "check_io.sql"
prompt ***************************************************
prompt * contents : 1. datafile physical I/O status *
prompt ***************************************************
prompt
column t0 format a10 heading 'TABLESPACE'
column t1 format a17 heading 'FILENAME'
column t2 format 9,999,999 heading 'PHYS_READ'
column t3 format 99.9 heading '% READ'
column t4 format 999,999 heading 'PHYS_WRITES'
column t5 format 99.9 heading '% WRITE'
column t6 format 99,999 heading 'SIZE(MB)'
SELECT F.TABLESPACE_NAME T0, F.FILE_NAME T1, X.PHYRDS T2,
TO_CHAR(TRUNC((PHYRDS/TOT_RDS)*100,2), 99.99)||'%' T3,
X.PHYWRTS T4,
TO_CHAR(TRUNC((PHYWRTS/TOT_WRTS)*100,2),99.99)||'%' T5,
ROUND((I.BYTES/1024)/1024) T6
FROM V$FILESTAT X, DBA_DATA_FILES F, V$DATAFILE I,
( SELECT SUM(PHYRDS) TOT_RDS, SUM(PHYWRTS) TOT_WRTS FROM V$FILESTAT ) TOT_IO
WHERE X.FILE# = F.FILE_ID
AND F.FILE_ID = I.FILE#
ORDER BY F.TABLESPACE_NAME ;
alert log 을 통해서 corruption 이 발생한
file_id 값과 block_id 값을 확인 할수 잇고,
아래 SQL 수행을 통해서 정확한 SEGMENT 체크 가능
more "check_corrupt.sql"
col segment_name format a20
select segment_name, segment_type from dba_extents
where file_id = &file_no
and &block_no between block_id and block_id + blocks - 1 ;
more "check_tbs.sql"
col tablespace_name for a10
col segment_name for a20
col segment_type for a10
col file_name for a30
break on tablespace_name on TOT(MB) on FREE(MB) on used(%)
set feadback off
set head off
set pagesize 555
spool ts_tables.lst
select sg.tablespace_name, round(df.tbs_byte/1048576,0) "TOT(MB)",
round(fs.free_byte/1048576,0) "FREE(MB)",
round(((df.tbs_byte -fs.free_byte)/df.tbs_byte)*100,0) "Used(%)",
sg.segment_name, sg.segment_type
from user_segments sg,
( select tablespace_name, sum(bytes) tbs_byte
from dba_data_files group by tablespace_name ) df,
( select tablespace_name, sum(bytes) free_byte
from dba_free_space group by tablespace_name ) fs
where sg.segment_type in ('TABLE','INDEX')
and sg.tablespace_name(+) = df.tablespace_name
and fs.tablespace_name(+) = df.tablespace_name
order by segment_type, tablespace_name, segment_name ;
spool off
set feedback on
set head on
more "check_files.sql"
prompt =============================================================
prompt tablespace
prompt =============================================================
col tbs_nm format a10
col phy_db format a50
col sz_kb format 9,999,999,999
select tablespace_name as tbs_nm, file_name phy_db, bytes/1024 as sz_kb
from dba_data_files;
more "check_free_files.sql"
prompt ***************************************************************
prompt dba_free_space
prompt ***************************************************************
col tablespace_name format a25
select tablespace_name, round(max(bytes)/1024/1024) "Max_Free(MB)",
round(sum(bytes)/1024/1024) "DB_Free(MB)"
from dba_free_space
group by tablespace_name ;
more lock.sql
set verify off
set pages 500
column username format a10
column SID format 999999
column lock_type format a15
column HELD format a11
column REQUESTED format a10
column LOCK_ID1 format a8
column LOCK_ID2 format a8
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) HELD,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.request)) REQUESTED,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0)
order by held, SID ;
more "check_lock_object.sql"
col oracle_username for a15
col os_user_name for a15
col object_name for a15
select a.session_id, b.serial#, a.os_user_name, a.oracle_username,
c.object_name, a.locked_mode, a.xidusn
from v$locked_object a, v$session b, dba_objects c
where a.object_id = c.object_id
and a.session_id = b.sid ;
more "check_lock_process.sql"
col "ORACLE USER" Format a11
col serial# format 999999
col "OS USER" format a8
select substr(s.username,1,11) "ORACLE USER",
p.pid "PROCESS ID",
s.sid "SESSION ID",
s.serial#, osuser "OS USER",
p.spid "PROC SPID",
s.process "SESS SPID",
s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid = s.sid
and p.addr = s.paddr
and s.username != 'SYS' ;
more "check_lock_sql.sql"
col username for a10
col lockwait for a10
col sql_text for a80
col object_owner for a14
col object for a15
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.piece, a.sql_text sql
from v$sqltext a, v$session b, v$access c
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value
and b.sid = c.sid
and c.owner != 'SYS' ;
more "check_roll.sql"
col rbs_seg for a10
col tbs_nm for a10
col status for a10
col ini_kb for 9,999,999
col nxt_kb for 9,999,999
col tot_kb for 9,999,999
col xacts for 99999
col extents for 9999
col usn for 99999
SELECT A.SEGMENT_NAME RBS_SEG, C.USN, B.STATUS, C.XACTS,
ROUND(A.INITIAL_EXTENT/1024,0) INI_KB,
ROUND(A.NEXT_EXTENT/1024,0) NXT_KB,
A.EXTENTS, ROUND(A.BYTES/1024,0) TOT_KB, A.MAX_EXTENTS,
A.TABLESPACE_NAME TBS_NM
FROM DBA_SEGMENTS A, DBA_ROLLBACK_SEGS B, V$ROLLSTAT C
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
AND B.SEGMENT_ID = C.USN(+)
AND A.SEGMENT_TYPE = 'ROLLBACK';
more "check_con_roll.sql"
col t0- for a10 heading 'ROLLBACK'
col t1 for 9,999,999 heading 'TRANS|TBL_GETS'
col t2 for 9,999,999 heading 'TRANS|TBL_WAITS'
col t3 for 999,999 heading 'MISS RATIO'
col t4 for a10 heading 'SIZE(KB)'
col t5 for 999,999 heading 'SHRINKS'
select name t0,
gets t1,
waits t2,
to_char(trunc(waits/gets*100,2),099.99)||'%' T3,
to_char(round(rssize/1024)) t4,
shrinks t5,
extends t6
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn ;
more "check_tables.sql"
col segment_name for a15
col tablspace_name for a10
select segment_name, round(initial_extent/1024,0) ini_kb,
round(next_extent/1024,0) nxt_kb,
round(bytes/1024,0) tot_kb,
pct_increase pct, extents, tablespace_name
from dba_segments
where segment_type ='TABLE' and extents >= nvl(&extent_num,1) ;
(2) 테이블에 걸려있는 인덱스 조회
more "checkindex.sql"
set head off
set linesize 200
spool index.lst
select 'set head off' from dual ;
select 'set feedback off' from dual ;
select a.table_name, b.index_name, c.partition_name,
c.tablespace_name,
round(c.bytes/1024/1024,0), c.extents,
round(c.initial_extent/1024/1024,0),
round(c.next_extent/1024/1024,0)
from user_tables a, user_indexes b, user_segments c
where a.table_name = b.table_name(+)
and b.index_name = c.segment_name(+)
order by table_name, index_name, partition_name ;
select 'spool off' from dual ;
select 'set head on' from dual ;
select 'set feedback on' from dual ;
spool off
set head on
set feedback on
more "check_index_status.sql"
col segment_name for a15
col tablespace_name for a10
select segment_name, round(initial_extent/1024,0) ini_kb,
round(next_extent/1024,0) nxt_kb,
round(bytes/1024,0) tot_kb,
pct_increase pct, extents, tablespace_name
from dba_segments
where segment_type ='INDEX' and extents >= nvl(&extent_num,1);
more "check_index_columns.sql"
break on owner on table_name on index_name
col owner for a15
col index_name for a20
col column_name for a20
col p for 999
select table_name, index_name, column_position p, column_name
from user_ind_columns
order by table_name, index_name, column_position;
more "check_fk.sql"
prompt **************************************************************
prompt fk column
prompt **************************************************************
break on table_name on constraint_name on r_constraint_name on status
col table_name for a15
col constraint_name for a20
col r_contraint_name for a15
col column_name for a25
col status for a10
select a.table_name, a.constraint_name, a.r_constraint_name, a.status,
b.position, b.column_name
from dba_constraints a, dba_cons_columns b
where a.constraint_type ='R'
and a.constraint_name = b.constraint_name
and a.table_name = upper('&table_name')
order by constraint_name, position ;
more "check_constraint.sql"
break on owner
col owner for a10
col table_name for a15
col constraint_name format a20
col type format a5
col ref_table format a15
select a.owner, a.table_name, a.constraint_name,
decode(a.constraint_type,'R','FK',
'P','PK',
'C','CHECK',
'NONE') type,
rtrim(a.r_owner)||'.'||rtrim(b.table_name) ref_table, a.status
from dba_constraints a, dba_constraints b
where a.constraint_type ='R' and
a.table_name like upper('&FK_TABLE_NAME')||'%' and
b.table_name like upper('&PK_TABLE_NAME')||'%' and
a.r_constraint_name = b.constraint_name ;
more "check_fk_relation.sql"
set head off
st pagesize 50000
break on table_name on index_name
col table_name for a25
col constraint_name for a25
col column_name for a35
spool cons_col.lst
select table_name, constraint_name, column_name, position
from user_cons_columns
where constraint_name like '%FK%'
order by table_name, constraint_name, position ;
spool off
more "check_extent.sql"
spool extent.lst
col segment_name for a17
col tablespace_name for a12
col type for a6
col ext for 99999
select segment_name, segment_type "TYPE",
tablespace_name "TABLESPACE",
round(bytes/1024) "SZ(KB)",
extents "EXT", round(initial_extent/1024) "INI_EXT(KB)",
round(next_extent/1024) "NXT_EXT(KB)"
from user_segments
where segment_type in ('TABLE','INDEX')
order by segment_type, tablespace_name, segment_name ;
spool off
more "check_trigger.sql"
prompt ********************************************************
prompt trigger status
prompt ********************************************************
break on table_owner on table_name
col table_owner for a15
col table_name for a20
col trigger_name for a20
select table_owner, table_name, trigger_name, status
from dba_triggers
order by table_name, trigger_name ;
more "check_part.sql"
col table_name for a12
col partition_name for a14
col high_value for a15
col pp for 99
col tablespace_name for a10
col p_count for 99999
col p_key_count for 99999
col ini_ext for a10
col next_ext for a10
select table_name, partition_name, high_value, partition_position pp,
tablespace_name, initial_extent, next_extent
from user_tab_partitions
order by table_name, parrition_name ;
select * from user_part_key_columns;
select table_name, partitioning_type p_type, partition_count p_count,
partitioning_key_count p_key_count, def_tablespace_name tablespace_name,
def_initial_extent ini_ext, def_next_extent next_ext
from user_part_tables ;
more "check_obj2.sql"
set pagesize 60
break on owner on tablespace
col owner for a10
col objects for a20
select owner,tablespace_name, count(*)||'TABLES' objects
from dba_tables
group by owner, tablespace_name
union
select owner,tablespace_name, count(*)||'INDEXES' objects
from dba_indexes
group by owner, tablespace_name
spool user_locs.lst
/
spool off
more "check_col.sql"
col table_name for a12
col column_name for a20
col data_type for a10
select table_name, column_name, data_type,data_length
from user_tab_columns
where data_type not in ('NUMBER','CHAR','VARCHAR2','DATE');
more "check_view.sql"
spool view.lst
break on owner
col owner for a15
col object_name for a30
select owner, object_name, object_type, status from dba_objects
where object_type ='VIEW'
/
spool off
!more "check_synonym.sql"
prompt =============================================================
prompt == SYNONYM STATUS ==
prompt =============================================================
col owner for a15
col synonym_name for a20
col table_owner for a15
col table_name for a15
select a.owner, a.synonym_name, a.table_owner, a.table_name,
max(decode(b.privilege,'SELECT','S '))||
max(decode(b.privilege,'INSERT','I '))||
max(decode(b.privilege,'UPDATE','U '))||
max(decode(b.privilege,'DELETE','D '))||
max(decode(b.privilege,'REFERENCES','R ')) as privs
from dba_synonyms a, dba_tab_privs b
where a.owner in ('PUBLIC')
and a.owner =b.grantee
and a.table_name = b.table_name
group by a.owner, a.synonym_name, a.table_owner, a.table_name ;
more "check_proc.sql"
break on owner
col owner for a15
col object_name for a20
col type for a10
col creat_dt for a20
select owner, object_name, object_type type, status,
to_char(created, 'YYYY/MM/DD HH:MI:SS') creat_dt
from dba_objects
where object_type in ('PROCEDURE','FUNCTION');