컨텐츠 바로가기

알티베이스 모니터링 쿼리 모음

http://hanghee.egloos.com/5200405

원문 : http://atc.altibase.com/support/doc/monitoring_query.htm

 

 

모니터링 쿼리

 모니터링 요소에 대응하는 쿼리이다.

 

 

Session

세션의 상태를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다특정 세션에 관한 정보만 획득하려면 WHERE절에 v$session “id” 컬럼을 기술하면 된다.

 

[SS01전체 세션 개수

iSQL> select count(*) from v$session

;

 

[SS02세션 정보

iSQL>

select a.id,

       a.db_username user_name,

       a.task_state,

       a.SESSION_STATE,

       decode(a.login_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.login_time / (1*24*60*60), 'mm/dd hh:mi:ss')) login_time,

       decode(a.idle_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.idle_start_time / (1*24*60*60), 'mm/dd hh:mi:ss')) idle_start_time,

       a.client_app_info,

       replace2(replace2(a.comm_name, 'socket-', null), '-server', null) comm_name,

       a.client_pid,

       decode(a.autocommit_flag, 1, 'Y', 'N') commitmode,

       a.opened_stmt_count stmt_count,

       NVL(ltrim(b.query), 'NONE') current_query

  from v$session a left outer join v$statement b

    on a.CURRENT_STMT_ID = b.id

;

 

[SS03] SYSDBA 권한으로 접속중인 세션 정보

iSQL>

select a.id,

       a.db_username user_name,

       a.task_state,

       a.SESSION_STATE,

       decode(a.login_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.login_time / (1*24*60*60), 'mm/dd hh:mi:ss')) login_time,

       decode(a.idle_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + a.idle_start_time / (1*24*60*60), 'mm/dd hh:mi:ss')) idle_start_time,

       a.client_app_info,

       replace2(replace2(a.comm_name, 'socket-', null), '-server', null) comm_name,

       a.client_pid,

       decode(a.autocommit_flag, 1, 'Y', 'N') commitmode,

       a.opened_stmt_count stmt_count,

       NVL(ltrim(b.query), 'NONE') current_query

  from v$session a left outer join v$statement b

    on a.CURRENT_STMT_ID = b.id

 where a.SYSDBA_FLAG = 1

;

 

 

Statement

쿼리의 상태를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다쿼리수행과 관련된 상세한 시간정보를 확인하기 위해서는 반드시 TIMED_STATISTICS enable(1)되어 있어야 한다.

 

[ST01전체 쿼리 개수

iSQL> select count(*) as stmt_count from v$statement

;

 

[ST02쿼리 정보

iSQL>

select session_id,

       id stmt_id,

       tx_id,

       decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time,

       (parse_time+validate_time+optimize_time)/1000000 prepare_time,

       execute_flag,

       execute_time/1000000 execute_time,

       total_time/1000000 total_time,

       nvl(ltrim(query), 'none') query

from v$statement

order by total_time desc

;

 

[ST03현재 수행중인 쿼리 개수

iSQL> select count(*) as active_count from v$statement where execute_flag = '1'

;

 

[ST04현재 수행중인 쿼리 정보

iSQL>

select session_id,

       id stmt_id,

       tx_id,

       decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time,

       (parse_time+validate_time+optimize_time)/1000000 prepare_time,

 fetch_start_time/1000000 fetch_start_time,

execute_time/1000000 execute_time,

       total_time/1000000 total_time,

       nvl(ltrim(query), 'none') query

  from v$statement

 where execute_flag = '1'

order by execute_time desc

;

 

[ST05장시간 수행되는 쿼리 (3600 이상)

iSQL>

select b.session_id,

       a.comm_name,

       a.client_pid,

       b.id as stmt_id,

       round(b.total_time/1000000) as total_time,

decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time,

nvl(ltrim(b.query), 'none') query

  from v$session a,

       v$statement b

 where a.id = b.session_id

   and (b.execute_time/1000000) > 3600

   and execute_flag = 1

order by b.execute_time desc

;

 

[ST06장시간 수행되는 DML트랜잭션의 마지막 쿼리 정보 (3600 이상)

iSQL>

select st.session_id,

       ss.comm_name,

       ss.CLIENT_PID,

       st.id as stmt_id,

       base_time - tr.FIRST_UPDATE_TIME as utrans_time,

decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time,

nvl(ltrim(st.query), 'none') query

  from v$transaction tr,

       v$statement st,

       v$sessionmgr,

       v$session ss

 where tr.id = st.tx_id

   and st.session_id = ss.id

   and tr.FIRST_UPDATE_TIME != 0

   and (base_time - tr.FIRST_UPDATE_TIME) > 3600

 order by utrans_time desc

;

 

[ST07풀스캔 쿼리 정보

iSQL>

select session_id,

       id stmt_id,

       tx_id,

       decode(last_query_start_time, 0, '-', to_char(to_date('1970010109', 'yyyymmddhh') + last_query_start_time / (24*60*60), 'mm/dd hh:mi:ss')) last_start_time,

       (parse_time+validate_time+optimize_time)/1000000 prepare_time,

 fetch_start_time/1000000 fetch_start_time,

execute_time/1000000 execute_time,

       total_time/1000000 total_time,

       nvl(ltrim(query), 'none') query

from v$statement

where (mem_cursor_full_scan > 0 or disk_cursor_full_scan > 0)

 and upper(query) not like '%INSERT%'

   and execute_flag = '1'

 order by execute_time desc

;

 

[ST] 풀스캔 쿼리 통계

iSQL>

select count(execute_success) execute_cnt,

       ltrim(query)

  from v$statement

 where (mem_cursor_full_scan > 0 or disk_cursor_full_scan > 0)

   and upper(query) not like '%INSERT%'

   and execute_flag = 1

 group by query

order by execute_cnt desc

;

 

 

Lock

Lock 정보를 확인하기 위한 모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[LK01] lock 정보

iSQL>

select

s.id session_id,

s.comm_name,

s.client_pid,

ltrim(CLIENT_APP_INFO) APP_INFO,

a.trans_id tx_id,

decode(b.status,

              0, 'BEGIN(HOLDING)',

         1, 'PRECOMMIT',

              2, 'COMMIT_IN_MEMORY',

              3, 'COMMIT',

              4, 'ABORT',

              5, 'BLOCKED',

                      6, 'END') status,

d.wait_for_trans_id blocked_by,

a.lock_desc,

decode(b.update_status,0,'READ-ONLY',1,'UPDATING', '?') update_status,

b.UPDATE_SIZE 'UPDATE_SIZE(b)',

decode(b.first_update_time, 0, '0', to_char(to_date('1970010109','YYYYMMDDHH') + b.first_update_time / (60*60*24), 'MM/DD HH:MI:SS')) update_time,       

decode(b.DDL_FLAG,0, 'non-DDL', 'DDL') DDL,

st.total_time/1000000 total_time,

decode(b.first_undo_next_lsn_fileno, -1, '-', b.first_undo_next_lsn_fileno) 'logfile#',

        a.table_oid,

e.user_name || '.' || f.table_name as table_name,

decode(b.log_type, 1, '-- REPLICATION_TX --', ltrim(c.query)) query

    from v$lock a

        left outer join v$lock_statement c on c.tx_id = a.trans_id

        left outer join v$lock_wait d on d.trans_id = a.trans_id,

        v$transaction b,

v$session s,

v$statement st,

system_.sys_users_ e,

system_.sys_tables_ f

 where a.trans_id = b.id

and e.user_id = f.user_id

and a.table_oid = f.table_oid

and c.session_id = s.id

and st.tx_id = c.tx_id

and a.lock_desc <> 'IS_LOCK'

;

 

 

 

Transaction

트랜잭션 테이블 정보를 확인하기 위한 모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[TR01트랜잭션 테이블 정보

iSQL>

select a.id tx_id,

decode (a.status,

0, 'BEGIN(HOLDING)',

1, 'PRECOMMIT',

2, 'COMMIT_IN_MEMORY',

3, 'COMMIT',

4, 'ABORT',

5, 'BLOCKED',

6, 'END',

'?') status,

decode(a.update_status,0,'READ-ONLY',1,'UPDATING', '?') update_status,

a.UPDATE_SIZE 'UPDATE_SIZE(b)',

decode(a.first_update_time, 0, '0', to_char(to_date('1970010109','YYYYMMDDHH') + a.first_update_time / (60*60*24), 'MM/DD HH:MI:SS')) update_time,       

decode(a.first_undo_next_lsn_fileno, -1, '-', a.first_undo_next_lsn_fileno) 'logfile#',

b.session_id,

c.comm_name,

c.client_pid,

decode(c.autocommit_flag, 1, 'ON', 'OFF') autocommit,

decode(a.DDL_FLAG,0, 'non-DDL', 'DDL') DDL,

decode(a.log_type, 1, '-- REPLICATION_TX --', substr(ltrim(b.query),1,30)) query

    from v$transaction a,

        v$statement b,

          v$session c,

          v$sessionmgr d

    where a.status != 6

and a.id = b.tx_id

      and b.session_id = c.id

order by a.first_update_time desc

;

 

 

 

 

Logfile

Logfile 정보를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[LO01] redo logfile 정보

iSQL>

select oldest_active_logfile "Oldest logfile",

        current_logfile "Current logfile",

        current_logfile-oldest_active_logfile "logfile Gap"

  from v$archive

;

 

 

GC

GC 정보를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[GC01메모리 DB GC 정보

iSQL> select * from v$memgc

;

 

[GC02메모리 DB GC gap

iSQL> select gc_name, ADD_OID_CNT-GC_OID_CNT gc_gap from v$memgc

;

 

 

OS

OS에서 ALTIBASE CPU, 메모리 사용률 관련 정보를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[OS01] ALTIBASE CPU 사용률 합계

 

 

[OS02] ALTIBASE의 메모리 사용량 합계

iSQL>

select round(sum(max_total_size)/1024/1024) as max_total_mb,

        round(sum(alloc_size)/1024/1024) as current_mb

from v$memstat

;

 

[MS01] ALTIBASE의 메모리 사용내역 상세(모듈단위)

iSQL> select * from v$memstat order by 4 desc

;

 

 

 

TBS(tablespace)

TBS 정보를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[TS01전체 테이블스페이스 사용량

iSQL>

select SPACE_NAME tablespace_name,

       round(ALLOC_PAGE_COUNT*32*1024/1024/1024) || '(Memory)' 'ALLOC(M)'

  from v$mem_tablespaces

 where SPACE_ID <> 0

 union all

select name tablespace_name,

       alloc || '(Disk)' 'ALLOC(M)'

  from (select name,

               id,

               round(TOTAL_PAGE_COUNT*8*1024/1024/1024) tot,

               round(ALLOCATED_PAGE_COUNT*8*1024/1024/1024) alloc

          from v$tablespaces) a,

       (select round(sum(case2(MAXSIZE=0, currsize, MAXSIZE))*8*1024/1024/1024) maxsz,

               SPACEID

          from v$datafiles

         group by SPACEID) b

 where a.id = b.SPACEID

;

 

[TS02메모리 DB 테이블스페이스 사용량

iSQL>

select 'MEM TOTAL SIZE' tablespace_name,

       round(MEM_MAX_DB_SIZE/1024/1024) as 'max size(M)' ,

       round((MEM_MAX_DB_SIZE - MEM_ALLOC_PAGE_COUNT*32*1024)/1024/1024) as 'free size(MB)',

       round(MEM_ALLOC_PAGE_COUNT*32*1024/1024/1024) as 'alloc size(MB)',

       round((MEM_MAX_DB_SIZE - MEM_ALLOC_PAGE_COUNT*32*1024)/MEM_MAX_DB_SIZE*100, 2) as 'free %'

  from v$database

 union all

select SPACE_NAME tablespace_name,

       case2(space_id =1, 0, round(case2(MAXSIZE=0, CURRENT_SIZE, MAXSIZE)/1024/1024)) as 'total size(MB)',

       case2(space_id =1, 0, trunc((case2(MAXSIZE=0, CURRENT_SIZE, MAXSIZE) ? CURRENT_SIZE)/1024/1024)) as 'free size(MB)',

       round(ALLOC_PAGE_COUNT*32*1024/1024/1024) as 'alloc size(MB)',

       case2(space_id =1, 0, trunc((case2(MAXSIZE=0, CURRENT_SIZE, MAXSIZE) ? CURRENT_SIZE)/case2(MAXSIZE=0, CURRENT_SIZE, MAXSIZE)*100, 2)) 'free %'

  from v$mem_tablespaces

 where SPACE_ID <> 0

;

 

[TS03디스크 DB 테이블스페이스 사용량

iSQL>

select name tablespace_name,

       maxsz as 'total size(MB)',

       maxsz - alloc as 'free size(MB)',

       round((maxsz-alloc)/maxsz*100, 2) as 'free %'

  from (select name,

               id,

               round(TOTAL_PAGE_COUNT*8*1024/1024/1024) tot,

               round(ALLOCATED_PAGE_COUNT*8*1024/1024/1024) alloc

          from v$tablespaces) a,

       (select round(sum(case2(MAXSIZE=0, currsize, MAXSIZE))*8*1024/1024/1024) maxsz,

               SPACEID

          from v$datafiles

         group by SPACEID) b

 where a.id = b.SPACEID

;

 

[TS04언두 테이블스페이스 사용량

iSQL>

select a.name,

       b.maxsz as 'MAX(M)',

       a.tot as 'SEG_TOTAL(M)',

       c.free as 'SEG_FREE(M)'

  from (select name,

               type,

               id,

               round(TOTAL_PAGE_COUNT*8*1024/1024/1024) tot,

               round(ALLOCATED_PAGE_COUNT*8*1024/1024/1024) alloc

          from v$tablespaces) a,

       (select round(sum(case2(MAXSIZE=0, currsize, MAXSIZE))*8*1024/1024/1024) maxsz,

               SPACEID

          from v$datafiles

         group by SPACEID) b,

       (select 'SYS_TBS_DISK_UNDO' name,

               sum(round((TOTAL_PAGE_COUNT)*8/1024, 0)) total,

               sum(round((FREE_PAGE_COUNT)*8/1024, 0)) free

          from v$undo_seg) c

 where a.id = b.SPACEID

   and a.name = c.name

   and a.type in (7)

;

 

[TS05템프 테이블스페이스 사용량

iSQL>

select name,

       maxsz as 'MAX(M)',

       tot as 'TOTAL(M)',

       alloc 'ALLOC(M)',

       round(alloc/maxsz * 100, 2) as usage_percent

  from (select name,

               type,

               id,

               round(TOTAL_PAGE_COUNT*8*1024/1024/1024) tot,

               round(ALLOCATED_PAGE_COUNT*8*1024/1024/1024) alloc

          from v$tablespaces) a,

       (select round(sum(case2(MAXSIZE=0, currsize, MAXSIZE))*8*1024/1024/1024) maxsz,

               SPACEID

          from v$datafiles

         group by SPACEID) b

 where a.id = b.SPACEID

   and a.type in (5)

;

 

[TS06메모리 DB 테이블스페이스 데이터파일

SHELL>

select d.* from x$stable_mem_datafiles d inner join v$tablespaces t on d.space_id = t.id

;

 

[TS07디스크 DB 테이블스페이스 데이터파일

iSQL>

select b.name tablespace_name,

       a.name datafile_name,

       round(case2(a.MAXSIZE=0, currsize, a.MAXSIZE)*8/1024) 'MAX_SIZE(MB)'

  from v$datafiles a,

       v$tablespaces b

 where b.id = a.spaceid

 order by b.name,

       a.name

;

 

[TS08] 테이블스페이스 상태

iSQL>

select name 'TABLESPACE NAME',

decode(STATE,

1, 'offline',

2, 'online',

5, 'offline backup',

6, 'online backup',

128, 'dropped', 'discard') state

  from v$tablespaces

;

 

 

 

Object

object 정보를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[OB01메모리 DB 사용량 합계

iSQL>

select a.user_name uname,

       b.table_name tname,

       d.name tbs_name,

       --c.table_oid,

       c.mem_slot_size fixed_row_size,

       round((c.fixed_alloc_mem + c.var_alloc_mem)/(1024*1024),3)

alloc_size,

       round((c.fixed_used_mem + c.var_used_mem)/(1024*1024),3)

used_size,

        round( (c.fixed_used_mem + c.var_used_mem)/

(c.fixed_alloc_mem + c.var_alloc_mem)*100,2) efficiency

    from system_.sys_users_ a,

                system_.sys_tables_ b,

       v$memtbl_info c,

       v$tablespaces d

    where a.user_name <> 'SYSTEM_'

  and b.table_type = 'T'

  and a.user_id = b.user_id

  and b.table_oid = c.table_oid

  and b.tbs_id = d.id

;

 

 

 

select mem_max_db_size/1024/1024 as mem_max_db_size,

       trunc(mem_alloc_page_count*32/1024, 2) as alloc_mem_mb,

       round(trunc((mem_alloc_page_count*32*1024)/mem_max_db_size, 2)*100) allocpercent

from v$database

;

 

 

 

 

 

[OB02크기가 1G 이상이면서 efficiency 50% 이하인 메모리테이블

iSQL>

select b.table_name ,

       round((fixed_alloc_mem+var_alloc_mem)/1024/1024, 2) as alloc_mem,

       round((((FIXED_ALLOC_MEM+VAR_ALLOC_MEM)-(FIXED_USED_MEM+VAR_USED_MEM))/1024/1024), 2) free,

round((FIXED_USED_MEM+VAR_USED_MEM)/(FIXED_ALLOC_MEM+VAR_ALLOC_MEM)*100, 2) effciency

  from v$memtbl_info a ,

       system_.SYS_TABLES_ b

 where a.TABLE_OID = b.TABLE_OID

and round((FIXED_ALLOC_MEM+VAR_ALLOC_MEM)/1024/1024, 2) => 1024

and round((FIXED_USED_MEM+VAR_USED_MEM)/(FIXED_ALLOC_MEM+VAR_ALLOC_MEM)*100, 2) =< 50

and b.USER_ID <> 1

 order by free desc

;

 

[OB03큐 테이블 사용량(전체/일부)

iSQL>

SELECT USER_NAME table_owner, TABLE_NAME table_name, ROUND((FIXED_ALLOC_MEM + VAR_ALLOC_MEM)/1024/1024 ) 'allocate(M)', 'MEMORY' as TYPE

FROM SYSTEM_.SYS_TABLES_ A, SYSTEM_.SYS_USERS_ B,

     V$MEMTBL_INFO C

WHERE A.USER_ID = B.USER_ID

  AND A.TABLE_OID = C.TABLE_OID

  AND B.USER_ID <> 1

  AND TABLE_TYPE = 'Q'

UNION ALL

SELECT USER_NAME table_owner,

       A.TABLE_NAME table_name,

       ROUND((B.DISK_PAGE_CNT*8*1024)/1024/1024) 'allocate(M)',

       'DISK' as TYPE

  FROM SYSTEM_.SYS_TABLES_ A,

       V$DISKTBL_INFO B,

       SYSTEM_.SYS_USERS_ C

 WHERE A.TABLE_OID = B.TABLE_OID

   AND A.USER_ID = C.USER_ID

   AND C.USER_ID <> 1

   AND TABLE_TYPE = 'Q'

;

 

[OB04메모리 DB 인덱스 사용량(전체/일부)

iSQL>

SELECT C.USER_NAME index_owner,

       E.INDEX_NAME index_name,

       case2(E.INDEX_TYPE=1, 'B-TREE', E.INDEX_TYPE=2, 'R-TREE') INDEX_TYPE ,

       '(Memory)'||'16 bytes * ROWCOUNT ' 'allocate(M)' ,

       table_name

  FROM X$INDEX B,

       SYSTEM_.SYS_USERS_ C,

       V$MEM_TABLESPACES D,

       SYSTEM_.SYS_INDICES_ E,

       SYSTEM_.SYS_TABLES_ F

 WHERE B.INDEX_ID = E.INDEX_ID

   AND E.USER_ID = C.USER_ID

   AND F.USER_ID = E.USER_ID

   AND F.TBS_ID = D.SPACE_ID

   AND F.TABLE_OID = B.TABLE_OID

   and c.user_id <> 1

;

 

[OB05디스크 DB 인덱스 사용량(전체/일부)

iSQL>

SELECT  USER_NAME index_owner,

        E.INDEX_NAME index_name,

        case2(E.INDEX_TYPE=1,'B-TREE', E.INDEX_TYPE=2,'R-TREE') INDEX_TYPE,

         '(Disk)'||ROUND(D.EXTENT_PAGE_COUNT*D.PAGE_SIZE*A.EXTENT_TOTAL_COUNT/1024/1024) 'allocate(M)'

        , table_name

FROM V$SEGMENT A,

     X$INDEX B,

     SYSTEM_.SYS_USERS_ C,

     V$TABLESPACES D,

     SYSTEM_.SYS_INDICES_ E,

     SYSTEM_.SYS_TABLES_ F

WHERE A.SEGMENT_PID = B.INDEX_SEG_PID

  AND B.INDEX_ID = E.INDEX_ID

  AND E.USER_ID = C.USER_ID

  AND A.SPACE_ID = D.ID

  and F.TBS_ID = D.ID

  AND A.SEGMENT_TYPE='INDEX'

  AND F.USER_ID = E.USER_ID

AND F.TABLE_OID = B.TABLE_OID

;

 

[OB06메모리 DB 테이블 사용량(전체/일부)

iSQL>

SELECT USER_NAME table_owner, TABLE_NAME table_name, ROUND((FIXED_ALLOC_MEM + VAR_ALLOC_MEM)/1024/1024 ) 'allocate(M)', 'MEMORY' as TYPE

FROM SYSTEM_.SYS_TABLES_ A, SYSTEM_.SYS_USERS_ B,

     V$MEMTBL_INFO C

WHERE A.USER_ID = B.USER_ID

  AND A.TABLE_OID = C.TABLE_OID

  AND B.USER_ID <> 1

AND TABLE_TYPE = 'T'

;

 

[OB07디스크 DB 테이블 사용량(전체/일부)

iSQL>

SELECT USER_NAME table_owner,

       A.TABLE_NAME table_name,

       ROUND((B.DISK_PAGE_CNT*8*1024)/1024/1024) 'allocate(M)',

       'DISK' as TYPE

  FROM SYSTEM_.SYS_TABLES_ A,

       V$DISKTBL_INFO B,

       SYSTEM_.SYS_USERS_ C

 WHERE A.TABLE_OID = B.TABLE_OID

   AND A.USER_ID = C.USER_ID

   AND C.USER_ID <> 1

   AND TABLE_TYPE = 'T'

;

 

[OB08시퀀스

iSQL>

select user_name SEQUENCE_OWNER,

       table_name SEQUENCE_NAME,

       min_seq min,

       MAX_SEQ max,

       INCREMENT_SEQ Increment,

       CACHE_SIZE cache,

       current_seq,

       CURRENT_SEQ LAST_NUMBER,

       IS_CYCLE

  from v$seq a,

       system_.sys_users_ b,

       system_.sys_tables_ c

 where a.SEQ_OID = c.table_oid

   and b.user_id = c.user_id

   and b.user_id <> 1

;

 

[OB09시노님

iSQL>

select SYNONYM_NAME,

       object_owner_name table_owner,

       OBJECT_NAME table_name

  from system_.SYS_SYNONYMS_ a

 where nvl(SYNONYM_OWNER_ID, 0) = 0

   and OBJECT_OWNER_NAME <> 'SYSTEM_'

;

 

[OB10] invalid PSM(프로시저)

iSQL>

select OWNER,

       object_type,

       object_name,

       LAST_DDL_TIME,

       invalid

  from (select a.user_name OWNER,

               b.table_name object_name,

               STATUS invalid,

               b.LAST_DDL_TIME,

               decode(b.table_type, 'T', 'TABLE', 'S', 'SEQUENCE', 'V', 'VIEW', 'W', 'QUEUE_SEQ', 'Q', 'QUEUE') object_type

          from system_.sys_users_ a,

               system_.sys_tables_ b,

               system_.sys_views_ c

         where a.user_id = b.user_id

           and b.table_id=c.VIEW_ID

           and b.table_type='V'

         union all

select a.user_name OWNER,

               PROC_NAME object_name,

               STATUS invalid,

               b.LAST_DDL_TIME,

               decode(OBJECT_TYPE, 0, 'PROCEDURE', 1, 'FUNCTION', 3, 'TYPESET') object_type

          from system_.sys_users_ a,

               system_.sys_procedures_ b

         where a.user_id=b.user_id )

 where invalid != 0

;

 

 

 

Privileges

Privileges 정보를 확인하기 위한  모니터링 요소에 대응하는 쿼리는 아래와 같다.

 

[PV01사용자 시스템 권한

select a.user_name grantee,

        c.user_name grantor,

        replace(d.priv_name, '_', ' ') priv_name

    from system_.sys_users_ a,

        system_.sys_grant_system_ b,

        system_.sys_users_ c,

                system_.sys_privileges_ d

    where c.user_name <> 'SYSTEM_'

   and b.grantee_id = a.user_id

   and b.grantor_id = c.user_id

   and b.priv_id = d.priv_id

;

 

 

[PV02사용자 오브젝트 권한

select a.user_name grantee,

f.user_name owner,

e.table_name object_name,

e.table_type object_type,

c.user_name grantor,

        replace(d.priv_name, '_', ' ') priv_name,

decode(b.with_grant_option, 0, 'N', 'Y') grantable

  from system_.sys_users_ a,

system_.sys_grant_object_ b,

system_.sys_users_ c,

system_.sys_privileges_ d,

system_.sys_tables_ e,

system_.sys_users_ f

 where c.user_name <> 'SYSTEM_'

   and b.grantee_id = a.user_id

   and b.grantor_id = c.user_id

   and b.priv_id = d.priv_id

   and b.obj_id = e.table_id

   and e.user_id = f.user_id

 order by grantor, owner, object_type, object_name, priv_name

;

 

 


덧글|신고