a.table_name,
b.column_name,
nvl(c.COLUMN_NAME, '***no index***')
from user_constraints a
join user_cons_columns b
on a.constraint_name = b.constraint_name
left outer join user_ind_columns c
on b.constraint_name = c.COLUMN_NAME
and b.table_name = c.TABLE_NAME
where constraint_type = 'R'
order by 2, 1;
展示主键与外键的关系
select a.constraint_type,
a.table_name,
a.constraint_name,
b.table_name,
b.constraint_name,
b.constraint_type
from dba_constraints a, dba_constraints b
where a.owner = ''
and a.constraint_type = 'R'
and a.r_owner = b.owner
and a.r_constraint_name = b.constraint_name
查看对象的依赖关系
select '+' || lpad(' ', level + 2) || type || ' ' || owner || '.' || name dep_tree
from dba_dependencies
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('$object_owner')
and referenced_name = upper('$object_name')
and owner is not null;
查看最耗费游标的会话
select *
from (select from v$sesstat a,
v$statname b,
v$session c wehre a.STATISTIC# = b.STATISTIC# and c.SID = a.SID and b.NAME = 'opened cursors current' and a.value != 0 and c.USERNAME is not null
order by 1 desc, 2)
where rownum < 21;
查看v$log_history可以看到redo的切换
评估undo大小是否合适
select to_char(begin_time,'MM-DD-YYYY HH24:MI') begin_time,ssoldrrcnt ora_01555_cnt,nospaceerrcnt no_space_cnt,txncount max_num_txns,maxquerylen max_query_len,expiredblks blck_in_expired from v$undostat where begin_time>sysdate -1 order by begin_time;
select sum(bytes)/1024/1024 cur_mb_size,dbms_undo_adv.required_undo_size(900) req_mb_size from dba_data_files where tablespace_name like 'UNDO%';
查看临时表信息
select tbalespace_name,sum(bytes_used)/1024/1024 mb_used from v$temp_extent_pool group by tablespace_name;
select name,bytes/1024/1024 mb_alloc from v$tempfile;