2018.11.06 / 10:33
12.2 À¯¿ëÇÑ µ¥ÀÌÅͺ£À̽º ¸ð´ÏÅ͸µ ½ºÅ©¸³Æ®
hanulbit
Ãßõ ¼ö 181
À¯¿ëÇÑ µ¥ÀÌÅͺ£À̽º ¸ð´ÏÅ͸µ ½ºÅ©¸³Æ®
µ¥ÀÌÅͺ£À̽º ±âº» ³»¿ë Á¶È¸
(1) µ¥ÀÌÅͺ£À̽º À̸§,»ý¼ºÀÏÀÚ,¹é¾÷ ¸ðµå Á¶È¸
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
(2) µ¥ÀÌÅͺ£À̽º SGA Å©±â Á¶È¸
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
(3) µ¥ÀÌÅͺ£À̽º SGA FREE ¸Þ¸ð¸® Á¶È¸
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
(4) µ¥ÀÌÅͺ£À̽º Àüü ÆĶó¹ÌÅÍ Á¶È¸
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
(5) µ¥ÀÌÅͺ£À̽º ½Ã½ºÅÛ Á¶È¸
sys@ARTDOM> select name, value from v$sysstat order by name, value
¼¼¼Ç ¹× Æ®·£Àè¼Ç Á¶È¸
(1) µ¥ÀÌÅͺ£À̽º¿¡ Á¢¼ÓµÇ¾î ÀÖ´Â ¿î¿µÃ¼Á¦ »ç¿ëÀÚ ¹× ÇÁ·Î±×·¥ Á¶È¸
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';
(2) µ¥ÀÌÅͺ£À̽º¿¡ Á¢¼ÓµÇ¾î ÀÖ´Â ¿î¿µÃ¼Á¦ »ç¿ëÀÚ ¹× ÇÁ·Î¼¼½º Á¶È¸
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 ;
(3) ¿ÀºêÁ§Æ®¿¡ Á¢¼ÓµÇ¾î ÀÖ´Â ÇÁ·Î±×·¥ Á¶È¸
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) ;
(4) ÀÛ¾÷ÁßÀÎ µ¥ÀÌÅͺ£À̽º Æ®·£Àè¼Ç Á¶È¸
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 ;
(5) ¿·ÁÀÖ´Â Ä¿¼ Á¶È¸
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;
(6) ½ÇÇàµÈ SQL ¹®Àå Á¶È¸
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 ;
Å×ÀÌºí ½ºÆäÀ̽º ¹× µ¥ÀÌÅÍÆÄÀÏ Á¶È¸
(1) µ¥ÀÌÅÍÆÄÀÏ°ú ÄÁÆ®·Ñ ÆÄÀÏ, ·Î±× ÆÄÀÏ Á¶È¸
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 ;
(2) µ¥ÀÌÅÍÆÄÀÏÀÌ Â÷ÁöÇÏ°í ÀÖ´Â µð½ºÅ©ÀÌ Àüü Å©±â¿Í ÇöÀç »ç¿ëµÇ´Â Å©±â¸¦ Á¶È¸
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 ;
(3) µ¥ÀÌÅÍÆÄÀϺ° ¹°¸®Àû I/O ÇöȲ Á¶È¸
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 ;
(4) ±úÁø(Corrupted) ºí·Ï Á¶È¸
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 ;
(5) Å×ÀÌºí ½ºÆäÀ̽º¿¡ ÀÖ´Â ¿ÀºêÁ§Æ® Á¶È¸
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
(6)Å×ÀÌºí ½ºÆäÀ̽º¿¡ ´ëÇÑ µ¥ÀÌÅÍÆÄÀÏ°ú Å©±â Á¶È¸
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;
(7) ºñ¾îÀÖ´Â Å×ÀÌºí ½ºÆäÀ̽º Á¶È¸
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 ;
Àá±Ý Á¤º¸ Á¶È¸
(1) Àá±Ý ¹ß»ý À¯Çü Á¶È¸
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 ;
(2) Àá±Ý »óÅ ¿ÀºêÁ§Æ® Á¶È¸
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 ;
(3) Àá±Ý ÇÁ·Î¼¼½º Á¶È¸
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' ;
(4) Àá±Ý SQL ±¸¹® Á¶È¸
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' ;
·Ñ¹é ¼¼±×¸ÕÆ® Á¶È¸
(1)·Ñ¹é ¼¼±×¸ÕÅ© Á¶È¸
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';
(2) ·Ñ¹é ¼¼±×¸ÕÆ® ÀÌ¿ë ½Ã°£ Á¶È¸
(3) ·Ñ¹é ¼¼±×¸ÕÆ® °æÇÕ Á¶È¸
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 ;
µ¥ÀÌÅͺ£À̽º ¿ÀºêÁ§Æ® Á¶È¸
(1) Å×ÀÌºí¿¡ µû¸¥ ½ºÅ丮Áö ¹× Å×ÀÌºí ½ºÆäÀ̽º Á¶È¸
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
(3) À妽º ÇöȲ Á¶È¸
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);
(4) À妽º¿¡ ´ëÇÑ Ä÷³ Á¶È¸
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;
(5) Å×ÀÌºí¿¡ °É·Á ÀÖ´Â FOREIGN KEY Á¶È¸
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 ;
(6) Á¦¾à »çÇ× Á¶È¸
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 ;
(7)FOREIGN KEY °ü°è Á¶È¸
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
(8) ¿ÀºêÁ§Æ® EXTENT Á¶È¸
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
(9) Æ®¸®°Å Á¶È¸
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 ;
(10) ÆÄƼ¼Ç ¿ÀºêÁ§Æ® Á¶È¸
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 ;
(11) OWNER¿Í Å×ÀÌºí ½ºÆäÀ̽º¿¡ µû¸¥ ¿ÀºêÁ§Æ® ÇöȲ Á¶È¸
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
(12) »ç¿ëÀÚ°¡ ¸¸µç Ä÷³ Á¶È¸
[VLDB: ÇöȲ¿¡ ¸ÂÁö ¾Ê´Â´Ù.]
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');
(13) ºä Á¶È¸
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
(14)½Ã³ë´Ô(SYNONYM) ÇöȲ Á¶È¸
!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 ;
(15) ÇÁ·Î½ÃÀú ¹× ÇÔ¼ö Á¶È¸
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');