2038 lines
81 KiB
SQL
Executable file
2038 lines
81 KiB
SQL
Executable file
# 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
|
|
|
|
|
|
|
|
|