꿈꾸는 개발자, DBA 커뮤니티 구루비

데이터베이스 기본 내용 조회

(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';

(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) 사용자가 만든 컬럼 조회


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');