index_name, -- 字段名称 column_position, -- 字段顺序 column_name -- 字段名称 from user_ind_columns where table_name = 'POSTPAY_BILLED_REVENUE' order by index_name, column_position; 13、检查是否有失效的索引 Select * From User_Indexes t Where t.Status != 'VALID'; 14、对象总数量 -- 查看对象的数量 select count(*) from user_objects; 15、对象分类数量 -- 查看各种类型对象的数量 select object_type, -- 对象的类型 count(*) -- 对象的数量 from user_objects group by object_type order by object_type; 16、数据库链接列表 select * from user_db_links; 17、大对象列表 select * from user_lobs; 18、物化视图列表 select * from user_mviews; 19、视图列表 select * from user_views; 20、视图长度 -- 查看视图定义的长度 select owner, view_name, text_length from dba_views where view_name='&view_name'; 21、视图定义 -- 从数据字典视图中直接查询视图的定义 select text from dba_views where view_name='&view_name'; 22、视图定义 -- 通过应用程序编程接口获取视图的定义 select dbms_metadata.get_ddl('VIEW','&view_name','CAMPAIGN') from dual; 23、检查是否有无效的对象 col object_name format a25; select object_name,object_type,owner,status from dba_objects where status !='VALID' and owner not in ('SYS','SYSTEM') select owner,object_name,object_type from dba_objects where status='INVALID'; 24、检查sequence使用 set linesize 120 select sequence_owner,sequence_name,min_value,max_value,increment_by,last_number,cache_size,cycle_flag dba_sequences; 25、检查是否有运行失败的job col what format a20; select job,this_date,this_sec,next_date,next_sec,failures,what from dba_jobs where failures !=0 or failures is not null; from 26、检查不起作用的约束 select owner,constraint_name,table_name,constraint_type,status from dba_constraints where status='DISABLED' and constraint_type='p'; 27、检查无效的trigger select owner,trigger_name,table_name,status from dba_triggers where status='DISABLED'; 五、SQL
1、 查找死锁的语句 Select l.session_idsid, s.serial#, l.locked_mode, l.oracle_username, l.os_user_name, s.machine, s.terminal, o.object_name, s.logon_time From v$locked_object l, all_objects o, v$session s Where l.object_id = o.object_id And l.session_id = s.sid Order By sid, s.serial#; 2、 查看已经执行过的sql这些是存在共享池中的： Select * From v$sqlarea t Order By t.LAST_ACTIVE_TIMEDesc; 3、 获取SQL消耗资源 Select Distinct sl.target, s.sql_text, sp.options, sp.cost, sp.cpu_cost, sp.io_cost From v$session_longopssl, v$sql s, v$sql_plansp Where sl.SQL_ADDRESS = s.ADDRESS And sp.address = s.address And Cost Is Not Null And options Is Not Null 以上sql通过关联v$sql,v$session_longops(这个视图存放超过6秒的sql，尤其是存在大量的数据分散读等待事件时)得到相关信息。 target：对象名 sql_text：sql语句 options:执行情况 cost：总的成本 cpu_cost：cpu使用情况 io_cost：磁盘io读写情况 4、 查看值得怀疑的SQL Select Substr(to_char(s.pct, '99.00'), 2) || '%' Load, s.executions executes, p.sql_text From (Select address, disk_reads, executions, pct, Rank() Over(Order By disk_readsDesc) ranking From (Select address, disk_reads, executions, 100 * ratio_to_report(disk_reads) Over() pct From sys.v_$sql Where command_type != 47) Where disk_reads> 50 * executions) s, sys.v_$sqltext p Where s.ranking<= 5 And p.address = s.address Order By 1, s.address, p.piece; 5、 查看消耗内存多的SQL Select b.username, a. buffer_gets, a.executions, a.disk_reads / Decode(a.executions, 0, 1, a.executions), a.sql_text SQL From v$sqlarea a, dba_users b Where a.parsing_user_id = b.user_id And a.disk_reads> 10000 Order By disk_readsDesc; 6、 查看逻辑读多的SQL Select * From (Select buffer_gets, sql_text From v$sqlarea Where buffer_gets> 500000 Order By buffer_getsDesc) Where Rownum<= 30; 7、 查看执行次数多的SQL Select sql_text, executions From (Select sql_text, executions From v$sqlareaOrder By executions Desc) Where Rownum< 81; 8、 查看读硬盘多的SQL Select sql_text, disk_reads From (Select sql_text, disk_reads From v$sqlarea Order By disk_readsDesc) Where Rownum< 21; 9、 查看排序多的SQL Select sql_text, sorts From (Select sql_text, sorts From v$sqlarea Order By sorts Desc) Where Rownum< 21; 10、 分析的次数太多，执行的次数太少，要用绑变量的方法来写sql Set pagesize 600; Set linesize 120; Select Substr(sql_text, 1, 80) \ From v$sqlarea Where executions < 5 Group By Substr(sql_text, 1, 80) Having Count(*) > 30 Order By 2; 11、 游标的观察 Set pages 300; Select Sum(a.value), b.name From v$sesstat a, v$statname b Where a.statistic# = b.statistic# And b.name = 'opened cursors current' Group By b.name; Select Count(0) From v$open_cursor; Select user_name, sql_text, Count(0) From v$open_cursor Group By user_name, sql_text Having Count(0) > 30; 12、 查看当前用户&username执行的SQL Select sql_text From v$sqltext_with_newlines Where (hash_value, address) In (Select sql_hash_value, sql_address From v$session Where username = '&username') Order By address, piece;