# This file contains all the SQL statements used by DBAComp # for the version mentionned in the square brackets below [9.2] lock_holders1: select s.username, s.osuser, s.sid, s.serial#, l.type "Lock type", decode ( l.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode held", decode ( l.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode requested", o.name "Object name", decode ( o.type#, null, null, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', /* 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', */ 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNDEFINED' ) "Object type", u.name "Object owner", s.row_wait_obj#, s.row_wait_block#, s.row_wait_row#, s.row_wait_file#, l.id1, l.id2 from v$lock l, v$session s, sys.obj$ o, sys.user$ u where l.sid = s.sid and l.request = 0 and s.row_wait_obj# = o.obj#(+) and o.owner# = u.user# (+) order by s.username, s.sid, l.type lock_holders: select ls.username, ls.osuser, ls.sid, ls.serial#, ls.type "Lock type", decode ( ls.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode held", decode ( ls.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode requested", o.name "Object name", o.type#, decode ( o.type#, null, null, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNDEFINED' ) "Object type", u.name "Object owner", ls.row_wait_obj#, ls.row_wait_block#, ls.row_wait_row#, ls.row_wait_file#, ls.id1, ls.id2 from ( select s.username, s.osuser, s.serial#, s.row_wait_obj#, s.row_wait_block#, s.row_wait_row#, s.row_wait_file#, l.*, decode ( s.row_wait_obj#, -1, decode ( l.id2, 0, l.id1, null ), s.row_wait_obj# ) objid from v$lock l, v$session s where l.sid = s.sid ) ls, sys.obj$ o, sys.user$ u where ls.request = 0 and ls.objid = o.obj#(+) and o.owner# = u.user# (+) order by ls.username, ls.sid, ls.type lock_waiters: select ls.username, ls.osuser, ls.sid, ls.serial#, ls.type "Lock type", decode ( ls.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode held", decode ( ls.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode requested", o.name "Object name", o.type#, decode ( o.type#, null, null, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNDEFINED' ) "Object type", u.name "Object owner", ls.row_wait_obj#, ls.row_wait_block#, ls.row_wait_row#, ls.row_wait_file#, ls.id1, ls.id2 from ( select s.username, s.osuser, s.serial#, s.row_wait_obj#, s.row_wait_block#, s.row_wait_row#, s.row_wait_file#, l.*, decode ( s.row_wait_obj#, -1, decode ( l.id2, 0, l.id1, null ), s.row_wait_obj# ) objid from v$lock l, v$session s where l.sid = s.sid ) ls, sys.obj$ o, sys.user$ u where ls.request != 0 and ls.objid = o.obj#(+) and o.owner# = u.user# (+) order by ls.username desc, ls.sid, ls.type lock_waiters1: select s.username, s.osuser, s.sid, s.serial#, l.type "Lock type", decode ( l.lmode, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode held", decode ( l.request, 0, 'NONE', 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?' ) "Mode requested", o.name "Object name", o.type#, decode ( o.type#, null, null, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', /* 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', */ 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 56, 'JAVA DATA', 'UNDEFINED' ) "Object type", u.name "Object owner", s.row_wait_obj#, s.row_wait_block#, s.row_wait_row#, s.row_wait_file#, l.id1, l.id2 from v$lock l, v$session s, sys.obj$ o, sys.user$ u where l.sid = s.sid and l.request != 0 and s.row_wait_obj# = o.obj#(+) and o.owner# = u.user# (+) order by s.username desc, s.sid, l.type register_application: begin dbms_application_info.set_client_info ( 'DBAComp V. '||:1 ); end; dba_rollback_segs_per_rbs: select rs.optsize, r.* from v$rollstat rs, dba_rollback_segs r where r.segment_id = rs.usn (+) and r.segment_name = :1 dba_tablespaces_per_tablespace: select * from dba_tablespaces where tablespace_name = :1 first_file: select file_name from dba_data_files where file_id = ( select min ( file_id ) from dba_data_files where tablespace_name = :1 ) v_parameter_per_parameter: select * from v$parameter where name = :1 sys_props_per_prop: select value$ from sys.props$ where name = :1 dba_constraints_per_view: select * from dba_constraints where owner = :1 and table_name = :2 and constraint_type = 'V' order by constraint_name dba_views_per_view: select * from dba_views where owner = :1 and view_name = :2 dba_triggers_per_trigger: select * from dba_triggers where owner = :1 and trigger_name = :2 dba_sequences_per_sequence: select * from dba_sequences where sequence_owner = :1 and sequence_name = :2 free_space_deficit: select nvl ( fs.max_fragment - s.next_extent, 0 ) "Space remaining", nvl ( fs.max_fragment, 0 ) "Largest free fragment", nvl ( fs.no_fragments, 0 ) "# of fragments", s.tablespace_name, s.next_extent "Largest next extent", t.segment_name, t.owner, t.segment_type, t.initial_extent, t.next_extent, t.pct_increase from ( select max ( x.next_extent ) next_extent, x.tablespace_name tablespace_name from dba_segments x group by tablespace_name ) s, dba_segments t, ( select max ( bytes ) max_fragment, tablespace_name fs_name, count ( tablespace_name ) no_fragments from dba_free_space group by tablespace_name ) fs where s.tablespace_name = fs.fs_name (+) and t.next_extent = s.next_extent and t.tablespace_name = s.tablespace_name order by 1 dba_db_links_per_db_link: select u.name owner, l.name db_link, l.userid username, l.password password, l.host host from sys.link$ l, sys.user$ u where l.owner# = u.user# and u.name = :1 and l.name = :2 dba_synonyms_per_synonym: select * from dba_synonyms where owner = :1 and synonym_name = :2 free_space: select dbf.tbsd TABLESPACE_NAME, dbf.filenum "#FILES", round ( dbf.Tbs_Size, 2 ) "SIZE", nvl ( round ( fsp.Free, 2 ), 0 ) FREE, nvl ( round ( fsp.Free / dbf.Tbs_Size * 100, 1 ), 0 ) "%FREE", nvl ( fsp.Fragments, 0 ) "Free Space Fragments" from ( ( select tablespace_name Tablespace, sum ( bytes ) / 1024 / 1024 Free, count ( bytes ) Fragments from dba_free_space group by tablespace_name union select h.tablespace_name Tablespace, h.bytes_free / 1024 / 1024 Free, ( select count(*) from v$temp_extent_map where tablespace_name = h.tablespace_name ) Fragments from v$temp_space_header h ) ) fsp, ( select tablespace_name tbsd, sum ( bytes ) / 1024 / 1024 Tbs_Size, count(*) filenum from ( select dba_data_files.* from dba_data_files union select dba_temp_files.* from dba_temp_files ) group by tablespace_name ) dbf where dbf.tbsd = fsp.Tablespace(+) order by "%FREE" #free_space: select dbf.tbsd TABLESPACE_NAME, \ # dbf.filenum "#FILES", \ # round(dbf.Tbs_Size,2) "SIZE", \ # nvl(round(fsp.Free,2),0) FREE, \ # nvl(round(fsp.Free/dbf.Tbs_Size*100,1),0) "%FREE", \ # nvl(fsp.Fragments,0) "Free Space Fragments" \ # from ( select tablespace_name Tablespace, \ # sum(bytes)/1024/1024 Free, \ # count(bytes) Fragments \ # from dba_free_space \ # group by tablespace_name) fsp, \ # ( select tablespace_name tbsd, \ # sum(bytes)/1024/1024 Tbs_Size, \ # count(*) filenum \ # from dba_data_files \ # group by tablespace_name) dbf \ # where dbf.tbsd=fsp.Tablespace(+) \ # order by "%FREE" dba_indexes_per_index: select * from dba_indexes where owner = :1 and index_name = :2 dba_constraints_per_constraint: select CONSTRAINT_TYPE, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, table_name from dba_constraints where owner = :1 and constraint_name = :2 dba_tables_params: select pct_free, pct_used, ini_trans, max_trans, degree, cluster_name, tablespace_name from dba_tables where owner = :1 and table_name = :2 dba_segments_storage: select INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS from dba_segments where owner = :1 and segment_name = :2 and segment_type = :3 v_archived_log: select * from v$archived_log order by recid extents_left: select * from ( select ( max_extents - extents ) "Extents left", round ( ( max_extents-extents ) / decode ( max_extents, 0, extents, max_extents ) * 100, 2 ) "Pct left", extents, max_extents, owner, segment_type, segment_name, tablespace_name, initial_extent, next_extent from dba_segments order by 1 ) where rownum < 11 v_process_per_session: select p.* from v$process p, v$session s where s.sid = :1 and s.paddr = p.addr v_session_event_per_session: select * from v$session_event where sid = :1 order by event v_access_per_session: select * from v$access where sid = :1 order by type, object prev_sql_per_session: select a.* from v$sqlarea a, v$session s where a.address = s.prev_sql_addr and s.sid = :1 cur_sql_per_session: select a.* from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = :1 v_transaction_per_session: select t.* from v$transaction t, v$session s where t.ses_addr = s.saddr and s.sid = :1 v_session_connect_info_per_session: select * from v$session_connect_info where sid = :1 v_session_wait_per_session: select * from v$session_wait where sid = :1 order by seq# v_sess_io_per_session: select * from v$sess_io where sid = :1 v_open_cursor_per_session: SELECT V$SQLAREA.SQL_TEXT, V$OPEN_CURSOR.HASH_VALUE, rawtohex ( V$OPEN_CURSOR.address ) ADDRESS FROM V$OPEN_CURSOR, v$sqlarea, v$session WHERE V$OPEN_CURSOR.SADDR = v$session.saddr and v$sqlarea.address = V$OPEN_CURSOR.address and v$session.sid = :1 v_sesstat_per_session: select n.name, s.value from v$statname n, v$sesstat s where n.statistic# = s.statistic# and s.sid=:1 order by n.name v_logfile_per_redolog_group: select * from v$logfile where group# = :1 v_filestat_per_data_file: select * from v$filestat where file# = :1 v_rollstat_per_rbs: select * from v$rollstat where usn = :1 dba_segments_per_rbs: select seg.* from dba_segments seg, dba_rollback_segs rbs where seg.segment_name = rbs.segment_name and rbs.segment_id = :1 and seg.segment_type = 'ROLLBACK' v_session_per_rbs: SELECT V$LOCK.SID, serial#, V$SESSION.USERNAME, V$SESSION.COMMAND, V$SESSION.LOCKWAIT, V$SESSION.STATUS, V$SESSION.SCHEMANAME, V$SESSION.SERVER, V$SESSION.OSUSER, V$SESSION.PROCESS, V$SESSION.MACHINE, V$SESSION.TERMINAL, V$SESSION.PROGRAM, V$SESSION.TYPE FROM V$LOCK, V$SESSION WHERE ( V$LOCK.SID = V$SESSION.SID ) and ( V$LOCK.TYPE = 'TX' ) AND ( trunc ( V$LOCK.ID1 / 65536 ) = :1 ) ORDER BY V$SESSION.USERNAME ASC dba_extents_per_rbs: select ext.* from dba_extents ext, dba_rollback_segs rbs where ext.segment_name = rbs.segment_name and rbs.segment_id=:1 and ext.segment_type='ROLLBACK' order by ext.extent_id dba_role_privs_per_role: select * from dba_role_privs where granted_role = :1 order by grantee dba_sys_privs_per_role: select * from dba_sys_privs where grantee = :1 order by privilege dba_tab_privs_per_role: select * from dba_tab_privs where grantee = :1 order by owner, table_name dba_col_privs_per_role: select * from dba_col_privs where grantee = :1 order by owner, table_name, column_name tbs_no: select count ( tablespace_name ) from dba_tablespaces rbs_no: select count ( segment_name ) from dba_rollback_segs file_no_dbsize: select count ( file_name ), sum ( bytes ) / ( 1024 * 1024 ) from dba_data_files redo_no: select count ( member ), count ( distinct group# ) from v$logfile t user_no: select count ( username ) from dba_users t tbs_size_per_tbs: select sum ( bytes ) / 1024 / 1024 tbs_size from dba_data_files where tablespace_name = :1 tbs_sizes: select dbf.tbsd TABLESPACE_NAME, dbf.filenum "#FILES", round ( dbf.Tbs_Size, 2 ) "SIZE", nvl ( round ( fsp.Free, 2 ), 0 ) FREE, nvl ( round ( fsp.Free / dbf.Tbs_Size * 100, 1 ), 0 ) "%FREE", nvl ( fsp.Fragments, 0 ) "Free Space Fragments" from ( ( select tablespace_name Tablespace, sum ( bytes ) / 1024 / 1024 Free, count ( bytes ) Fragments from dba_free_space group by tablespace_name union select h.tablespace_name Tablespace, h.bytes_free / 1024 / 1024 Free, ( select count(*) from v$temp_extent_map where tablespace_name = h.tablespace_name ) Fragments from v$temp_space_header h ) ) fsp, ( select tablespace_name tbsd, sum ( bytes ) / 1024 / 1024 Tbs_Size, count(*) filenum from ( select dba_data_files.* from dba_data_files union select dba_temp_files.* from dba_temp_files ) group by tablespace_name ) dbf where dbf.tbsd = fsp.Tablespace(+) #tbs_sizes: select dbf.tbsd TABLESPACE_NAME, # dbf.filenum "#FILES", \ # round(dbf.Tbs_Size,2) "SIZE", \ # nvl(round(fsp.Free,2),0) FREE, \ # nvl(round(fsp.Free/dbf.Tbs_Size*100,1),0) "%FREE", \ # nvl(fsp.Fragments,0) "Free Space Fragments" \ # from ( select tablespace_name Tablespace, \ # sum(bytes)/1024/1024 Free, \ # count(bytes) Fragments \ # from dba_free_space \ # group by tablespace_name) fsp, \ # ( select tablespace_name tbsd, \ # sum(bytes)/1024/1024 Tbs_Size, \ # count(*) filenum \ # from dba_data_files \ # group by tablespace_name) dbf \ # where dbf.tbsd=fsp.Tablespace(+) \ # order by TABLESPACE_NAME v_instance: select * from v$instance v_thread: select * from v$thread v_sga: select * from v$sga v_sgastat: select * from v$sgastat order by pool, name v_sqlarea: select * from v$sqlarea order by sql_text v_rowcache: select * from v$rowcache order by cache# v_librarycache: select * from v$librarycache v_db_object_cache: select * from v$db_object_cache order by owner, name v_session_v_process: select s.*, p.* from v$session s, v$process p where s.paddr=p.addr v_session_v_transaction: select s.*, t.* from v$session s, v$transaction t where s.saddr = t.ses_addr locks: select v$lock.sid, v$lock.type, serial#, v$session.username, v$lock.id1, dba_objects.object_name, dba_objects.object_type, dba_objects.owner, v$lock.id2, v$lock.lmode, v$lock.request, v$session.command, v$session.osuser, v$session.machine, v$session.terminal, v$session.program from v$lock, v$session, dba_objects where ( v$lock.id1 = dba_objects.object_id (+) ) and ( v$lock.sid = v$session.sid ) and ( ( v$lock.type in ('RW','TM','TX','UL') ) ) order by v$lock.sid dba_obj_audit_opts: select * from dba_obj_audit_opts order by owner, object_name all_def_audit_opts: select * from all_def_audit_opts dba_stmt_audit_opts: select * from dba_stmt_audit_opts dba_audit_trail: select * from dba_audit_trail order by timestamp dba_audit_session: select * from dba_audit_session order by timestamp dba_audit_object: select * from dba_audit_object order by timestamp v_mts: select * from v$mts v_dispatcher: select * from v$dispatcher v_shared_server: select * from v$shared_server v_queue: select * from v$queue v_recover_file: select * from v$recover_file v_recovery_log: select * from v$recovery_log datafile_recovery_status: select r.*, d.* from v$recover_file r, v$datafile d where r.file# = d.file# v_recovery_file_status: select * from v$recovery_file_status v_recovery_status: select * from v$recovery_status v_backup_v_datafile: select b.*, d.* from v$backup b, v$datafile d where b.file# = d.file# v_backup: select * from v$backup v_backup_corruption: select * from v$backup_corruption v_backup_datafile: select * from v$backup_datafile v_backup_device: select * from v$backup_device v_backup_piece: select * from v$backup_piece v_backup_redolog: select * from v$backup_redolog v_backup_set: select * from v$backup_set dba_clusters: select * from dba_clusters order by owner, cluster_name dba_db_links: select * from dba_db_links order by owner, db_link dba_functions: select distinct owner, name from dba_source where type = 'FUNCTION' order by owner, name dba_jobs: select * from dba_jobs order by priv_user, job dba_libraries: select * from dba_libraries order by owner, library_name dba_packages: select distinct owner, name from dba_source where type = 'PACKAGE' order by owner, name dba_procedures: select distinct owner, name from dba_source where type='PROCEDURE' order by owner, name dba_snapshot_logs: select * from dba_snapshot_logs order by log_owner, master dba_snapshots: select * from dba_snapshots order by owner, name dba_synonyms: select * from dba_synonyms order by owner, synonym_name dba_triggers: select * from dba_triggers order by owner, table_name, trigger_name dba_types: select * from dba_types order by owner, type_name dba_tables: select * from dba_tables order by owner, table_name dba_views: select OWNER, VIEW_NAME, TEXT_LENGTH, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE from dba_views order by owner, view_name dba_indexes: select OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_TYPE, UNIQUENESS, COMPRESSION, PREFIX_LENGTH, TABLESPACE_NAME, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_THRESHOLD, INCLUDE_COLUMN, FREELISTS, FREELIST_GROUPS, PCT_FREE, LOGGING, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, STATUS, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, DEGREE, INSTANCES, PARTITIONED, TEMPORARY, GENERATED, SECONDARY, BUFFER_POOL, USER_STATS, DURATION, PCT_DIRECT_ACCESS, ITYP_OWNER, ITYP_NAME, PARAMETERS, GLOBAL_STATS, DOMIDX_STATUS, DOMIDX_OPSTATUS, FUNCIDX_STATUS from dba_indexes order by owner, table_name, index_name invalid_objects: select * from dba_objects where status = 'INVALID' order by owner, object_type, object_name dba_objects: select * from dba_objects order by owner, object_type, object_name dba_segments: select * from dba_segments order by owner, segment_name dba_sequences: select * from dba_sequences order by sequence_owner, sequence_name dba_constraints: select owner, CONSTRAINT_NAME, CONSTRAINT_TYPE, table_name, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, to_char ( LAST_CHANGE ) LAST_CHANGE from dba_constraints order by owner, constraint_name alter_session: alter session set NLS_DATE_FORMAT = 'dd-mm-yy hh24:mi:ss' v_database: select * from v$database sort_stats: select name, value from v$sysstat where name like 'sorts%' hot_files: select v$filestat.file#, v$filestat.phyrds+v$filestat.phywrts TotalIO, v$dbfile.name, v$filestat.phyrds, v$filestat.phywrts, v$filestat.phyblkrd, v$filestat.phyblkwrt, v$filestat.readtim, v$filestat.writetim from v$dbfile, v$filestat where v$dbfile.file# = v$filestat.file# order by TotalIO DESC dba_users: select * from dba_users order by username dba_roles: select * from dba_roles order by role dba_profiles: select * from dba_profiles order by profile dba_tablespaces: select * from dba_tablespaces order by tablespace_name dba_data_files_per_tbs: select * from ( select dba_data_files.*, 'data file' FILE_TYPE from dba_data_files union select dba_temp_files.*, 'temp file' FILE_TYPE from dba_temp_files ) where tablespace_name = :1 order by file_name dba_segments_per_tbs: select * from dba_segments where tablespace_name = :1 order by segment_name dba_users_def_per_tbs: select * from dba_users where default_tablespace = :1 order by username dba_users_temp_per_tbs: select * from dba_users where temporary_tablespace = :1 order by username extents_alloc_per_tbs: select count (*), min ( bytes ), max ( bytes ), round ( sum ( bytes ) / ( 1024 * 1024 ), 3 ) from dba_extents where tablespace_name = :1 tbs_size: select round ( sum ( bytes ) / ( 1024 * 1024 ), 3 ) from dba_data_files where tablespace_name = :1 extents_free_per_tbs: select count(*), min ( bytes ), max ( bytes ), round ( sum ( bytes ) / ( 1024 * 1024 ), 3 ) from dba_free_space where tablespace_name = :1 v_access_per_tbs: select sg.*, s.* from v$access a, v$session s, dba_segments sg where ( a.sid = s.sid ) and ( a.owner = sg.owner ) and ( a.object = sg.segment_name ) and ( ( sg.tablespace_name = :1 ) ) order by s.username, s.osuser tbs_map_org: select segment_name, segment_type, to_char ( extent_id ), file_Id, block_Id, blocks, owner from dba_extents where tablespace_name = :1 UNION select '***FREE SPACE***', ' ', ' ', File_Id, block_id, blocks, ' ' from dba_free_space where tablespace_Name = :1 order by 4, 5 tbs_map: select * from ( select segment_name, segment_type, to_char ( extent_id ), file_Id, block_Id, blocks, owner, tablespace_name from dba_extents UNION select '***FREE SPACE***', ' ', ' ', File_Id, block_id, blocks, ' ', tablespace_name from dba_free_space ) where tablespace_Name = :1 order by 4, 5 dba_free_space_per_tbs: select f.*, df.* from dba_free_space f, dba_data_files df where ( f.file_id = df.file_id ) and ( ( f.tablespace_name = :1 ) ) order by f.file_id, f.block_id dba_rollback_segs: select rs.xacts, r.* from v$rollstat rs, dba_rollback_segs r where r.segment_id = rs.usn (+) order by segment_name dba_data_files: select * from ( select dba_data_files.*, 'data file' FILE_TYPE from dba_data_files union select dba_temp_files.*, 'temp file' FILE_TYPE from dba_temp_files ) order by tablespace_name dba_data_files_per_df: select * from dba_data_files where file_name = :1 v_log: select * from v$log order by group# v_controlfile: select * from v$controlfile order by name v_access: select * from v$access order by owner v_archive: select * from v$archive v_bgprocess: select * from v$bgprocess order by name v_compatibility_compatseg: select a.type_id, a.release, a.description, b.release, b.updated from v$compatibility a, v$compatseg b order by a.release v_datafile_header: select * from v$datafile_header v_dblink: select * from v$dblink v_db_pipes: select * from v$db_pipes v_event_name: select * from v$event_name v_license: select * from v$license v_lock: select * from v$lock v_locked_object: select * from v$locked_object v_log_history: select * from v$log_history v_nls_parameters: select * from v$nls_parameters v_nls_database_parameters: select * from v$nls_database_parameters v_nls_instance_parameters: select * from v$nls_instance_parameters v_nls_session_parameters: select * from v$nls_session_parameters v_nls_valid_values: select * from v$nls_valid_values v_option: select * from v$option v_parameter: select NAME, VALUE, DESCRIPTION, TYPE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISMODIFIED, ISADJUSTED, NUM from v$parameter order by name v_pq_slave: select * from v$pq_slave v_pq_sysstat: select * from v$sysstat v_pwfile_users: select * from v$pwfile_users v_resource_limit: select * from v$resource_limit v_rollstat: select * from v$rollstat v_session_longops: select * from v$session_longops v_sesstat: select s.sid, u.serial#, u.username, u.osuser, n.class, n.name, s.value from v$sesstat s, v$statname n, v$session u where n.statistic# = s.statistic# and s.sid = u.sid order by u.username, u.osuser, n.name v_session_wait: select * from v$session_wait v_sort_segment: select * from v$sort_segment v_sort_usage: select * from v$sort_usage v_sql_bind_data: select * from v$sql_bind_data v_sql_bind_metadata: select * from v$sql_bind_metadata v_sql_cursor: select * from v$sql_cursor v_sql_shared_memory: select * from v$sql_shared_memory v_subcache: select * from v$subcache v_sysstat: select * from v$sysstat v_system_event: select * from v$system_event v_transaction: select * from v$transaction v_waitstat: select * from v$waitstat v_parameter: select * from v$parameter order by name v_process: select * from v$process order by pid v_datafile: select * from v$datafile order by file# dba_sys_privs_per_user: select privilege, admin_option from dba_sys_privs where grantee = :1 dba_role_privs_per_user: select granted_role, admin_option, default_role from dba_role_privs where grantee = :1 dba_tab_privs_per_user: select p.owner, o.object_type, p.table_name, p.grantor, p.privilege, p.grantable from dba_tab_privs p, dba_objects o where grantee = :1 and p.owner = o.owner and p.table_name = o.object_name table_grants_per_user: select owner, table_name, grantee, privilege, grantable from dba_tab_privs where grantor = :1 dba_col_privs_per_user: select owner, table_name, column_name, grantor, privilege, grantable from dba_col_privs where grantee = :1 col_grants_per_user: select owner, table_name, column_name, grantee, privilege, grantable from dba_col_privs where grantor = :1 dba_profiles_per_user: select p.PROFILE, p.RESOURCE_NAME, p.LIMIT from dba_users u, dba_profiles p where u.username = :1 and u.profile = p.profile dba_ts_quotas_per_user: select tablespace_name, bytes, max_bytes, blocks, max_blocks from dba_ts_quotas where username=:1 dba_errors_per_user: select name, type, text, line, position, sequence from dba_errors where owner = :1 order by name, type, sequence dba_tables_per_user: select * from dba_tables where owner = :1 order by table_name dba_objects_per_tab_partition: select * from dba_objects where owner = :1 and object_name = :2 and subobject_name = :3 and object_type = 'TABLE PARTITION' dba_objects_per_table: select * from dba_objects where owner = :1 and object_name = :2 and object_type = 'TABLE' dba_segments_per_tab_partition: select * from dba_segments where owner = :1 and segment_name = :2 and partition_name = :3 and segment_type = 'TABLE PARTITION' dba_segments_per_table: select * from dba_segments where owner = :1 and segment_name = :2 and segment_type = 'TABLE' dba_constraints_per_table: select CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, to_char ( LAST_CHANGE ) LAST_CHANGE from dba_constraints where owner = :1 and table_name = :2 order by constraint_name dba_constraints_per_table_p_r_c: select * from dba_constraints where owner = :1 and table_name = :2 and constraint_type in ('P','R','C') order by constraint_name dba_indexes_per_table: select * from dba_indexes where table_owner = :1 and table_name = :2 order by owner, index_name dba_tab_partitions_per_table: select TABLE_OWNER, TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, HIGH_VALUE, HIGH_VALUE_LENGTH, PARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, to_char ( LAST_ANALYZED ) LAST_ANALYZED, BUFFER_POOL, GLOBAL_STATS, USER_STATS from dba_tab_partitions where table_owner = :1 and table_name = :2 order by partition_name dba_tab_subpartitions_per_table: select * from dba_tab_subpartitions where table_owner = :1 and table_name = :2 order by partition_name, subpartition_position dba_ind_partitions_per_index: select * from dba_ind_partitions where index_owner = :1 and index_name = :2 order by partition_name dba_ind_subpartitions_per_index: select * from dba_ind_subpartitions where index_owner = :1 and index_name=:2 order by partition_name, subpartition_position dba_tab_columns_per_table: select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID, DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, to_char ( LAST_ANALYZED ) LAST_ANALYZED, SAMPLE_SIZE, CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH, GLOBAL_STATS, USER_STATS, AVG_COL_LEN from dba_tab_columns where owner = :1 and table_name = :2 order by column_id dba_triggers_per_table: select * from dba_triggers where table_owner = :1 and table_name = :2 order by owner, trigger_name dba_tab_comments_per_table: select comments from dba_tab_comments where owner = :1 and table_name = :2 dba_tab_privs_per_table: select * from dba_tab_privs where owner = :1 and table_name = :2 order by privilege dba_views_per_user: select view_name, text_length, type_text_length, type_text, oid_text_length, oid_text, view_type_owner, view_type from dba_views where owner = :1 order by view_name view_text: select text from dba_views where owner = :1 and view_name = :2 dba_objects_per_view: select * from dba_objects where owner = :1 and object_name = :2 dba_indexes_per_user: select * from dba_indexes where owner = :1 order by index_name dba_objects_per_index: select * from dba_objects where owner = :1 and object_name = :2 dba_segments_per_index: select * from dba_segments where owner=:1 and segment_name=:2 dba_ind_columns_per_index: select * from dba_ind_columns where index_owner=:1 and index_name=:2 order by column_position dba_clusters_per_user: select * from dba_clusters where owner=:1 order by cluster_name dba_objects_per_cluster: select * from dba_objects where owner=:1 and object_name=:2 dba_segments_per_cluster: select * from dba_segments where owner=:1 and segment_name=:2 dba_sequences_per_user: select * from dba_sequences where sequence_owner=:1 order by sequence_name dba_objects_per_sequence: select * from dba_objects where owner=:1 and object_name=:2 dba_objects_per_user: select object_name, status, object_type, created, last_ddl_time, timestamp,object_id from dba_objects where owner=:1 order by object_name dba_segments_per_user: select * from dba_segments where owner=:1 order by segment_name dba_objects_per_segment: select * from dba_objects where owner=:1 and object_name=:2 dba_extents_per_segment: select * from dba_extents where owner=:1 and segment_name=:2 order by extent_id dba_procedures_per_user: select distinct name from dba_source where type='PROCEDURE' and owner=:1 order by name dba_source_per_procedure: select line,text from dba_source where type='PROCEDURE' and owner=:1 and name=:2 order by line dba_source_text_per_procedure: select text from dba_source where type='PROCEDURE' and owner=:1 and name=:2 order by line dba_objects_per_procedure: select * from dba_objects where owner=:1 and object_name=:2 dba_functions_per_user: select distinct name from dba_source where type='FUNCTION' and owner=:1 order by name dba_source_per_function: select line,text from dba_source where type='FUNCTION' and owner=:1 and name=:2 order by line dba_source_text_per_function: select text from dba_source where type='FUNCTION' and owner=:1 and name=:2 order by line dba_objects_per_function: select * from dba_objects where owner=:1 and object_name=:2 dba_packages_per_user: select distinct name from dba_source where type='PACKAGE' and owner=:1 order by name dba_source_per_package_header: select line,text from dba_source where type='PACKAGE' and owner=:1 and name=:2 order by line dba_source_per_package_body: select line,text from dba_source where type='PACKAGE BODY' and owner=:1 and name=:2 order by line dba_source_text_per_package_header: select text from dba_source where type='PACKAGE' and owner=:1 and name=:2 order by line dba_source_text_per_package_body: select text from dba_source where type='PACKAGE BODY' and owner=:1 and name=:2 order by line dba_objects_per_package_header: select * from dba_objects where owner=:1 and object_name=:2 and object_type='PACKAGE' dba_objects_per_package_body: select * from dba_objects where owner=:1 and object_name=:2 and object_type='PACKAGE BODY' dba_triggers_per_user: select TABLE_NAME, TRIGGER_NAME, TABLE_OWNER, TRIGGER_TYPE, TRIGGERING_EVENT, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION \ from dba_triggers where owner=:1 order by table_name, trigger_name dba_objects_per_trigger: select * from dba_objects where owner=:1 and object_name=:2 trigger_body: select trigger_body from dba_triggers where owner=:1 and trigger_name=:2 dba_synonyms_per_user: select * from dba_synonyms where owner=:1 order by synonym_name dba_objects_per_synonym: select * from dba_objects where owner=:1 and object_name=:2 dba_objects_per_function: select * from dba_objects where owner=:1 and object_name=:2 dba_constraints_per_user: select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, to_char(LAST_CHANGE) LAST_CHANGE from dba_constraints where owner=:1 order by constraint_name dba_cons_columns_per_constraint: select position, table_name, column_name from dba_cons_columns where owner=:1 and constraint_name=:2 order by position dba_objects_per_constraint: select * from dba_objects where owner=:1 and object_name=:2 dba_jobs_per_user: select * from dba_jobs where priv_user=:1 order by job dba_db_links_per_user: select * from dba_db_links where owner=:1 order by db_link dba_objects_per_db_link: select * from dba_objects where owner=:1 and object_name=:2 dba_snapshots_per_user: select * from dba_snapshots where owner=:1 order by name dba_snapshot_logs_per_user: select * from dba_snapshot_logs where log_owner=:1 order by master dba_libraries_per_user: select * from dba_libraries where owner=:1 order by library_name dba_types_per_user: select * From dba_types where owner=:1 order by type_name v_session: select username, osuser, SADDR, SID, SERIAL#, AUDSID, PADDR, USER#, COMMAND, TADDR, LOCKWAIT, STATUS, SERVER, SCHEMA#, SCHEMANAME, PROCESS, MACHINE, TERMINAL, PROGRAM, TYPE, SQL_ADDRESS, SQL_HASH_VALUE, PREV_SQL_ADDR, PREV_HASH_VALUE, MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO, FIXED_TABLE_SEQUENCE, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, to_char(LOGON_TIME) LOGON_TIME, LAST_CALL_ET from v$session order by username,osuser ddcache_ratio: select round(sum(getmisses)/sum(gets)*100,2) from v$rowcache libcache_ratio: select round(sum(reloads)/sum(pins)*100,2) from v$librarycache db_block_gets: select value from v$sysstat where name = 'db block gets' consistent_gets: select value from v$sysstat where name = 'consistent gets' phys_reads: select value from v$sysstat where name = 'physical reads' redo_log_space_req: select value from v$sysstat where name='redo log space requests' sys_rbs_header: select round(count/(:consistent_gets+:db_block_gets)*100,2) from v$waitstat where class = 'system undo header' sys_rbs_block: select round(count/(:consistent_gets+:db_block_gets)*100,2) from v$waitstat where class = 'system undo block' rbs_header: select round(count/(:consistent_gets+:db_block_gets)*100,2) from v$waitstat where class = 'undo header' rbs_block: select round(count/(:consistent_gets+:db_block_gets)*100,2) from v$waitstat where class = 'undo block' total_rbs_waits: select round(sum(waits)/sum(gets)*100,2) from v$rollstat disp_busy: select round(sum(busy)/(sum(busy)+sum(idle))*100,2) from v$dispatcher disp_resp_waits: select decode (sum(totalq),0, 'No responses', round(sum(wait)/sum(totalq),3)) from v$queue where type='DISPATCHER' server_waits: select decode (sum(totalq),0, 'No requests', round(sum(wait)/sum(totalq),3)) from v$queue where type='COMMON' calc_table_number: select count(*) from dba_tables calc_table_number_per_user: select count(*) from dba_tables where owner=:1 calc_view_number: select count(*) from dba_views calc_view_number_per_user: select count(*) from dba_views where owner=:1 calc_index_number: select count(*) from dba_indexes calc_index_number_per_user: select count(*) from dba_indexes where owner=:1 calc_object_number: select count(*) from dba_objects calc_object_number_per_user: select count(*) from dba_objects where owner=:1 calc_segment_number: select count(*) from dba_segments calc_segment_number_per_user: select count(*) from dba_segments where owner=:1 calc_sequence_number: select count(*) from dba_sequences calc_sequence_number_per_user: select count(*) from dba_sequences where sequence_owner=:1 calc_constraint_number: select count(*) from dba_constraints calc_constraint_number_per_user: select count(*) from dba_constraints where owner=:1 calc_cluster_number: select count(*) from dba_clusters calc_cluster_number_per_user: select count(*) from dba_clusters where owner=:1 calc_procedure_number: select count(distinct name) from dba_source where type='PROCEDURE' calc_procedure_number_per_user: select count(distinct name) from dba_source where owner = :1 and type='PROCEDURE' calc_function_number: select count(distinct name) from dba_source where type='FUNCTION' calc_function_number_per_user: select count(distinct name) from dba_source where owner = :1 and type='FUNCTION' calc_package_number: select count(distinct name) from dba_source where type='PACKAGE' calc_package_number_per_user: select count(distinct name) from dba_source where owner = :1 and type='PACKAGE' calc_trigger_number: select count(*) from dba_triggers calc_trigger_number_per_user: select count(*) from dba_triggers where owner=:1 calc_synonym_number: select count(*) from dba_synonyms calc_synonym_number_per_user: select count(*) from dba_synonyms where owner=:1 calc_job_number: select count(*) from dba_jobs calc_job_number_per_user: select count(*) from dba_jobs where priv_user=:1 calc_dblink_number: select count(*) from dba_db_links calc_dblink_number_per_user: select count(*) from dba_db_links where owner=:1 calc_snapshot_number: select count(*) from dba_snapshots calc_snapshot_number_per_user: select count(*) from dba_snapshots where owner=:1 calc_snapshot_log_number: select count(*) from dba_snapshot_logs calc_snapshot_log_number_per_user: select count(*) from dba_snapshot_logs \ where log_owner=:1 calc_type_number: select count(*) from dba_types calc_type_number_per_user: select count(*) from dba_types where owner=:1 calc_library_number: select count(*) from dba_libraries calc_library_number_per_user: select count(*) from dba_libraries where owner=:1 calc_column_number: select count(*) from dba_tab_columns calc_column_number_per_user: select count(*) from dba_tab_columns where owner=:1 calc_extent_number: select count(*) from dba_extents calc_extent_number_per_user: select count(*) from dba_extents where owner=:1 calc_source_line_number: select count(*) from dba_source calc_source_line_number_per_user: select count(*) from dba_source where owner=:1 calc_block_number: select sum(blocks) from dba_extents calc_block_number_per_user: select sum(blocks) from dba_extents where owner=:1 calc_segment_size: select nvl(round(sum(bytes)/(1024*1024),3),0) from dba_segments where segment_type like :1 calc_segment_size_per_user: select nvl(round(sum(bytes)/(1024*1024),3),0) from dba_segments where owner=:1 and segment_type like :2 all_tables: select object_name from dba_objects where object_type in ('TABLE', 'VIEW') order by object_name child_tables: select c.owner, c.table_name, c.constraint_name from dba_constraints m, dba_constraints c where m.owner= :1 and m.table_name=:2 and m.CONSTRAINT_TYPE='P' and c.r_owner=m.owner and c.r_constraint_name=m.constraint_name order by c.owner, c.table_name, c.constraint_name v_shared_pool_reserved: select * from v$shared_pool_reserved v_controlfile_record_section: select * from v$controlfile_record_section dba_users_per_user: select * from dba_users where username=:1 outline_sql_text: select sql_text from dba_outlines where owner=:1 and name=:2 dba_outlines: select NAME, OWNER, CATEGORY, USED, to_char(TIMESTAMP) TIMESTAMP, VERSION, SQL_TEXT from dba_outlines order by owner, name dba_outlines_per_user: select NAME, OWNER, CATEGORY, USED, to_char(TIMESTAMP) TIMESTAMP, VERSION, SQL_TEXT from dba_outlines where owner=:1 order by owner, name dba_tab_partitions: select * from dba_tab_partitions order by table_owner, table_name, partition_name dba_tab_partitions_per_user: select * from dba_tab_partitions where table_owner=:1 order by table_name, partition_name calc_tab_partition_number: select count(*) from dba_tab_partitions calc_tab_partition_number_per_user: select count(*) from dba_tab_partitions where table_owner=:1 calc_outline_number: select count(*) from dba_outlines calc_outline_number_per_user: select count(*) from dba_outlines where owner=:1 dba_outline_hints: select * from dba_outline_hints where owner=:1 and name=:2 order by node, stage, join_pos sys_props: select * from sys.props$ order by name v_instance_recovery: select * from v$instance_recovery v_latch: select * from v$latch order by name v_access_per_object: select a.*,s.username, s.osuser from v$access a, v$session s where a.owner=:1 and a.object=:2 and a.sid=s.sid v_sort_segment_per_tbs: select * from v$sort_segment where tablespace_name=:1 v_sort_usage_per_tbs: select * from v$sort_usage where tablespace=:1 v_buffer_pool: select * from v$buffer_pool v_bh: select * from v$bh v_tablespace: select * from v$tablespace dba_jobs_running: select s.username, j.* from dba_jobs_running j, v$session s where s.sid=j.sid order by s.username v_object_dependency: select * from v$object_dependency v_obsolete_parameter: select * from v$obsolete_parameter v_reserved_words: select * from v$reserved_words v_rowcache_parent: select * from v$rowcache_parent v_rowcache_subordinate: select * from v$rowcache_subordinate v_tempfile: select * from v$tempfile dba_errors_per_object: select sequence, line, position, text from dba_errors where owner=:1 and name=:2 and type = :3 order by sequence dba_errors_per_package_header: select sequence, line, position, text from dba_errors where owner=:1 and name=:2 and type = 'PACKAGE' order by sequence dba_errors_per_package_body: select sequence, line, position, text from dba_errors where owner=:1 and name=:2 and type = 'PACKAGE BODY' order by sequence dba_objects_per_lob: select * from dba_objects where owner=:1 and object_name=:2 and object_type='LOB' dba_segments_per_lob: select * from dba_segments where owner=:1 and segment_name=:2 and segment_type='LOBSEGMENT' dba_lobs: select * from dba_lobs dba_lobs_per_user: select * from dba_lobs where owner=:1 calc_lob_number: select count(*) from dba_lobs calc_lob_number_per_user: select count(*) from dba_lobs where owner=:1 dba_lobs_per_table: select * from dba_lobs where owner=:1 and table_name=:2 order by column_name top_sql: select round(buffer_gets/executions,2) "avg. buffer gets", round(disk_reads/executions,2) "avg. disk reads", round(parse_calls/executions,2) "avg. parse calls", EXECUTIONS, SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS, VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, ROWS_PROCESSED, COMMAND_TYPE, OPTIMIZER_MODE, PARSING_USER_ID, PARSING_SCHEMA_ID, KEPT_VERSIONS, ADDRESS, HASH_VALUE, MODULE, MODULE_HASH, ACTION, ACTION_HASH, SERIALIZABLE_ABORTS from v$sqlarea where executions > 0 order by "avg. buffer gets" DESC v_complete_list: select synonym_name "V$ VIEW" from dba_synonyms where table_owner='SYS' and synonym_name like 'V$%' and synonym_name not like 'V$\_%' escape '\' order by 1