查看未创建索引的外键

select a.constraint_name,
       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;



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值