偶日常用的sql列表,希望有用

--监控索引是否使用
alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name = &index_name;

--求数据文件的I/O分布
select df.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim
?from v$filestat fs,v$dbfile df
where fs.file#=df.file# order by df.name;

--求某个隐藏参数的值
?col ksppinm format a54
?col ksppstvl format a54
?select ksppinm, ksppstvl
? from x$ksppi pi, x$ksppcv cv
?where cv.indx=pi.indx and pi.ksppinm like '/_%' escape '/' and pi.ksppinm like '%&parameer%';

--求系统中较大的latch
select name,sum(gets),sum(misses),sum(sleeps),sum(wait_time)
?from v$latch_children
group by name having sum(gets) > 50 order by 2;

--求归档日志的切换频率(生产系统可能时间会很长)
select start_recid,start_time,end_recid,end_time,minutes from (select test.*, rownum as rn
?from (select b.recid start_recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') start_time,
? a.recid end_recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.first_time-b.first_time)*24)*60,2) minutes
?from v$log_history a,v$log_history b where a.recid=b.recid+1 and b.first_time > sysdate - 1
? order by a.first_time desc) test) y where y.rn < 30
?
--求回滚段正在处理的事务
select a.name,b.xacts,c.sid,c.serial#,d.sql_text
?from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
?and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;

--求出无效的对象
select 'alter procedure '||object_name||' compile;'
?from dba_objects
where status='INVALID' and owner='&' and object_type in ('PACKAGE','PACKAGE BODY');
/
select owner,object_name,object_type,status from dba_objects where status='INVALID';

--求process/session的状态
select p.pid,p.spid,s.program,s.sid,s.serial#
?from v$process p,v$session s where s.paddr=p.addr;

--求当前session的状态
select sn.name,ms.value
?from v$mystat ms,v$statname sn
where ms.statistic#=sn.statistic# and ms.value > 0;

--求表的索引信息
select ui.table_name,ui.index_name
?from user_indexes ui,user_ind_columns uic
where ui.table_name=uic.table_name and ui.index_name=uic.index_name
?and ui.table_name like '&table_name%' and uic.column_name='&column_name';

--显示表的外键信息
col search_condition format a54
select table_name,constraint_name
? from user_constraints
?where constraint_type ='R' and constraint_name in (select constraint_name from user_cons_columns where column_name='&1');

select rpad(child.table_name,25,' ') child_tablename,
?rpad(cp.column_name,17,' ') referring_column,rpad(parent.table_name,25,' ') parent_tablename,
?rpad(pc.column_name,15,' ') referred_column,rpad(child.constraint_name,25,' ') constraint_name
?from user_constraints child,user_constraints parent,
????? user_cons_columns cp,user_cons_columns pc
where child.constraint_type = 'R' and child.r_constraint_name = parent.constraint_name and
? child.constraint_name = cp.constraint_name and parent.constraint_name = pc.constraint_name and
? cp.position = pc.position and child.table_name ='&table_name'
?order by child.owner,child.table_name,child.constraint_name,cp.position;

--显示表的分区及子分区(user_tab_subpartitions)
col table_name format a16
col partition_name format a16
col high_value format a81
select table_name,partition_name,HIGH_VALUE from user_tab_partitions where table_name='&table_name'

--使用dbms_xplan生成一个执行计划
explain plan set statement_id = '&sql_id' for &sql;
select * from table(dbms_xplan.display);

--求某个事务的重做信息(bytes)
select s.name,m.value
? from v$mystat m,v$statname s
?where m.statistic#=s.statistic# and s.name like '%redo size%';

--求cache中缓存超过其5%的对象
select o.owner,o.object_type,o.object_name,count(b.objd)
?from v$bh b,dba_objects o
where b.objd = o.object_id
?group by o.owner,o.object_type,o.object_name
?having count(b.objd) > (select to_number(value)*0.05 from v$parameter where name = 'db_block_buffers');

--求谁阻塞了某个session(10g)
select sid, username, event, blocking_session,
? seconds_in_wait, wait_time
?from v$session where state in ('WAITING') and wait_class != 'Idle';

--求session的OS进程ID
col program format a54
select p.spid "OS Thread", b.name "Name-User", s.program
?from v$process p, v$session s, v$bgprocess b
?where p.addr = s.paddr and p.addr = b.paddr
UNION ALL
select p.spid "OS Thread", s.username "Name-User", s.program
?from v$process p, v$session s where p.addr = s.paddr and s.username is not null;

--查会话的阻塞
col user_name format a32
select /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name, o.owner,o.object_name,s.sid,s.serial#
?from v$locked_object l,dba_objects o,v$session s
where l.object_id=o.object_id and l.session_id=s.sid order by o.object_id,xidusn desc ;

col username format a15
col lock_level format a8
col owner format a18
col object_name format a32
select /*+ rule */ s.username, decode(l.type,'tm','table lock', 'tx','row lock', null) lock_level, o.owner,o.object_name,s.sid,s.serial#
?from v$session s,v$lock l,dba_objects o
where l.sid = s.sid and l.id1 = o.object_id(+) and s.username is not null ;

--求等待的事件及会话信息/求会话的等待及会话信息
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
? from v$session s,v$session_event se
where s.username is not null and se.sid=s.sid and s.status='ACTIVE' and se.event not like '%SQL*Net%' order by s.username;

select s.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait
? from v$session s,v$session_wait sw
where s.username is not null and sw.sid=s.sid and sw.event not like '%SQL*Net%' order by s.username;

--求会话等待的file_id/block_id
col event format a24
col p1text format a12
col p2text format a12
col p3text format a12
select sid,event,p1text, p1, p2text, p2, p3text, p3
? from v$session_wait
where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%' order by event;

select name,wait_time from v$latch l where exists (select 1 from (select sid,event,p1text, p1, p2text, p2, p3text, p3
? from v$session_wait
where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%'
) x where x.p1= l.latch#);

--求会话等待的对象
col owner format a18
col segment_name format a32
col segment_type format a32
select owner,segment_name,segment_type
? from dba_extents
where file_id = &file_id and &block_id between block_id and block_id + blocks - 1;

--求buffer cache中的块信息
select o.OBJECT_TYPE, substr(o.OBJECT_NAME,1,10) objname , b.objd , b.status, count(b.objd)
?from? v$bh b, dba_objects o
?where b.objd = o.data_object_id and o.owner = '&1' group by o.object_type, o.object_name,b.objd, b.status ;

--求日志文件的空间使用
select le.leseq current_log_sequence#, 100*cp.cpodr_bno/le.lesiz percentage_full
?from x$kcccp cp,x$kccle le
?where le.leseq =cp.cpodr_seq;

--求等待中的对象
select /*+rule */ s.sid, s.username, w.event, o.owner, o.segment_name, o.segment_type,
?????? o.partition_name, w.seconds_in_wait seconds, w.state
? from v$session_wait w, v$session s, dba_extents o
?where w.event in (select name from v$event_name? where parameter1 = 'file#'
?? and parameter2 = 'block#' and name not like 'control%')
?? and o.owner <> 'sys' and w.sid = s.sid and w.p1 = o.file_id and w.p2 >= o.block_id and w.p2 < o.block_id + o.blocks

--求当前事务的重做尺寸
select value
? from v$mystat, v$statname
?where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

--唤醒smon去清除临时段
column pid new_value Smon
set termout off
select p.pid from sys.v_$bgprocess b,sys.v_$process p where b.name = 'SMON' and p.addr = b.paddr
/
set termout on
oradebug wakeup &Smon
undefine Smon

--求回退率
select b.value/(a.value + b.value),a.value,b.value from v$sysstat a,v$sysstat b
where a.statistic#=4 and b.statistic#=5;

--求DISK READ较多的SQL
select st.sql_text from v$sql s,v$sqltext st
where s.address=st.address and s.hash_value=st.hash_value and s.disk_reads > 300;

--求DISK SORT严重的SQL
select sess.username, sql.sql_text, sort1.blocks
? from v$session sess, v$sqlarea sql, v$sort_usage sort1
?where sess.serial# = sort1.session_num
?? and sort1.sqladdr = sql.address
?? and sort1.sqlhash = sql.hash_value? and sort1.blocks > 200;

--求对象的创建代码
column column_name format a36
column sql_text format a99
select dbms_metadata.get_ddl('TABLE','&1') from dual;
select dbms_metadata.get_ddl('INDEX','&1') from dual;

--求表的索引
set linesize 131
select a.index_name,a.column_name,b.status, b.index_type
from user_ind_columns a,user_indexes b
where a.index_name=b.index_name and a.table_name='&1';

求索引中行数较多的
select index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where num_rows > 10000 and blevel > 0
select table_name,index_name,blevel,num_rows,CLUSTERING_FACTOR,status from user_indexes where status <> 'VALID'

--求当前会话的SID,SERIAL#
select sid, serial# from v$session where audsid = SYS_CONTEXT('USERENV','SESSIONID');

--求表空间的未用空间
col mbytes format 9999.9999
select tablespace_name,sum(bytes)/1024/1024 mbytes from dba_free_space group by tablespace_name;

--求表中定义的触发器
select table_name,index_type,index_name,uniqueness from user_indexes where table_name='&1';
select trigger_name from user_triggers where table_name='&1';

--求未定义索引的表
select table_name from user_tables where table_name not in (select table_name from user_ind_columns);

--执行常用的过程
exec print_sql('select count(*) from tab');
exec show_space2('table_name');

--求free memory
select * from v$sgastat where name='free memory';
select a.name,sum(b.value) from v$statname a,v$sesstat b where a.statistic# = b.statistic# group by a.name;

查看一下谁在使用那个可以得回滚段,或者查看一下某个可以得用户在使用回滚段,
找出领回滚段不断增长的事务,再看看如何处理它,是否可以将它commit,再不行
就看看能否kill它,等等, 查看当前正在使用的回滚段的用户信息和回滚段信息:
set linesize 121
SELECT r.name "ROLLBACK SEGMENT NAME ",l.sid "ORACLE PID",p.spid "SYSTEM PID ",s.username "ORACLE USERNAME"
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = p.pid(+) AND s.sid=l.sid AND TRUNC(l.id1(+)/65536) = r.usn AND l.type(+) = 'TX' AND l.lmode(+) = 6 ORDER BY r.name;

--查看用户的回滚段的信息
select s.username, rn.name from v$session s, v$transaction t, v$rollstat r, v$rollname rn
where s.saddr = t.ses_addr and t.xidusn = r.usn and r.usn = rn.usn

--生成执行计划
explain plan set statement_id='a1' for &1;
--查看执行计划
select lpad(' ',2*(level-1))||operation operation,options,OBJECT_NAME,position from plan_table
start with id=0 and statement_id='a1' connect by prior id=parent_id and statement_id='a1'

--查看内存中存的使用
select decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",sum(decode(bitand(flag,1),1,1,0)) "Dirty",
sum(dirty_queue) "On Dirty",count(*) "Total"
from x$bh group by decode(greatest(class,10),10,decode(class,1,'Data',2,'Sort',4,'Header',to_char(class)),'Rollback');

-- 查看表空间状态
?select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
?select table_name,freelists,freelist_groups from user_tables;

--查看系统请求情况
SELECT DECODE (name, 'summed dirty write queue length', value)/
DECODE (name, 'write requests', value) "Write Request Length"
FROM v$sysstat WHERE name IN ( 'summed dirty queue length', 'write requests') and value>0;

--计算data buffer 命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;

SELECT name, (1-(physical_reads/(db_block_gets+consistent_gets)))*100 H_RATIO FROM v$buffer_pool_statistics;

--查看内存使用情况
select least(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024)) shared_pool_used,
max(b.value)/(1024*1024) shared_pool_size,greatest(max(b.value)/(1024*1024),sum(a.bytes)/(1024*1024))-
(sum(a.bytes)/(1024*1024)) shared_pool_avail,((sum(a.bytes)/(1024*1024))/(max(b.value)/(1024*1024)))*100 avail_pool_pct
from v$sgastat a, v$parameter b where (a.pool='shared pool' and a.name not in ('free memory')) and b.name='shared_pool_size';

--查看用户使用内存情况
select username, sum(sharable_mem), sum(persistent_mem), sum(runtime_mem)
from sys.v_$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id group by username;

--查看对象的缓存情况
select OWNER,NAMESPACE,TYPE,NAME,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS,KEPT
from v$db_object_cache where type not in ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE')
and executions>0 and loads>1 and kept='NO' order by owner,namespace,type,executions desc;

select type,count(*) from v$db_object_cache group by type;

--查看库缓存命中率
select namespace,gets, gethitratio*100 gethitratio,pins,pinhitratio*100 pinhitratio,RELOADS,INVALIDATIONS from v$librarycache

--查看某些用户的hash
select a.username, count(b.hash_value) total_hash,count(b.hash_value)-count(unique(b.hash_value)) same_hash,
(count(unique(b.hash_value))/count(b.hash_value))*100 u_hash_ratio
from dba_users a, v$sqlarea b where a.user_id=b.parsing_user_id group by a.username;

--查看字典命中率
select (sum(getmisses)/sum(gets)) ratio from v$rowcache;

--查看undo段的使用情况
SELECT d.segment_name,extents,optsize,shrinks,aveshrink,aveactive,d.status
FROM v$rollname n,v$rollstat s,dba_rollback_segs d
WHERE d.segment_id=n.usn(+) and d.segment_id=s.usn(+);

--无效的对象
select owner,object_type,object_name from dba_objects where status='INVALID';
select constraint_name,table_name from dba_constraints where status='INVALID';

--求出某个进程,并对它进行跟踪
select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid=&1;
exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,true);
exec dbms_system.SET_SQL_TRACE_IN_SESSION(&1,&2,false);

--求出锁定的对象
select do.object_name,session_id,process,locked_mode
from v$locked_object lo, dba_objects do where lo.object_id=do.object_id;

--求当前session的跟踪文件
SELECT p1.value || '/' || p2.value || '_ora_' || p.spid || '.ora' filename
? FROM v$process p, v$session s, v$parameter p1, v$parameter p2
?WHERE p1.name = 'user_dump_dest' AND p2.name = 'instance_name'
?? AND p.addr = s.paddr AND s.audsid = USERENV('SESSIONID') AND p.background is null AND instr(p.program,'CJQ') = 0;

--求对象所在的文件及块号
select segment_name,header_file,header_block
from dba_segments where segment_name like '&1';

--求对象发生事务时回退段及块号
select a.segment_name,a.header_file,a.header_block
from dba_segments a,dba_rollback_segs b
where a.segment_name=b.segment_name and b.segment_id='&1'

--9i的在线重定义表
/*如果在线重定义的表没有主键需要创建主键*/
exec dbms_redefinition.can_redef_table('cybercafe','announcement');
create table anno2 as select * from announcement
exec dbms_redefinition.start_redef_table('cybercafe','announcement','anno2');
exec dbms_redefinition.sync_interim_table('cybercafe','announcement','anno2');
exec dbms_redefinition.finish_redef_table('cybercafe','announcement','anno2');
drop table anno2
exec dbms_redefinition.abort_redef_table('cybercafe','announcement','anno2');

--常用的logmnr脚本(cybercafe)
exec sys.dbms_logmnr_d.build(dictionary_filename =>'esal',dictionary_location =>'/home/oracle/logmnr');
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_24050.dbf', options=>sys.dbms_logmnr.new);

exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22912.dbf', options=>sys.dbms_logmnr.addfile);
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22913.dbf', options=>sys.dbms_logmnr.addfile);
exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22914.dbf', options=>sys.dbms_logmnr.addfile);

exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/esal.ora');
create table logmnr2 as select * from v$logmnr_contents;

--与权限相关的字典
ALL_COL_PRIVS????表示列上的授权,用户和PUBLIC是被授予者
ALL_COL_PRIVS_MADE? 表示列上的授权,用户是属主和被授予者
ALL_COL_RECD????表示列上的授权,用户和PUBLIC是被授予者
ALL_TAB_PRIVS????表示对象上的授权,用户是PUBLIC或被授予者或用户是属主
ALL_TAB_PRIVS_MADE? 表示对象上的权限,用户是属主或授予者
ALL_TAB_PRIVS_RECD? 表示对象上的权限, 用户是PUBLIC或被授予者
DBA_COL_PRIVS????数据库列上的所有授权
DBA_ROLE_PRIVS???显示已授予用户或其他角色的角色
DBA_SYS_PRIVS????已授予用户或角色的系统权限
DBA_TAB_PRIVS????数据库对象上的所有权限
ROLE_ROLE_PRIVS???显示已授予用户的角色
ROLE_SYS_PRIVS???显示通过角色授予用户的系统权限
ROLE_TAB_PRIVS???显示通过角色授予用户的对象权限
SESSION_PRIVS????显示用户现在可利用的所有系统权限
USER_COL_PRIVS???显示列上的权限,用户是属主、授予者或被授予者
USER_COL_PRIVS_MADE 显示列上已授予的权限,用户是属主或授予者
USER_COL_PRIVS_RECD 显示列上已授予的权限,用户是属主或被授予者
USER_ROLE_PRIVS???显示已授予给用户的所有角色
USER_SYS_PRIVS???显示已授予给用户的所有系统权限
USER_TAB_PRIVS???显示已授予给用户的所有对象权限
USER_TAB_PRIVS_MADE 显示已授予给其他用户的对象权限,用户是属主
USER_TAB_PRIVS_RECD 显示已授予给其他用户的对象权限,用户是被授予者

--如何用dbms_stats分析表及模式?
exec dbms_stats.gather_schema_stats(ownname=>'&USER_NAME',estimate_percent=>dbms_stats.auto_sample_size,
?method_opt => 'for all columns size auto',degree=> DBMS_STATS.DEFAULT_DEGREE);
exec dbms_stats.gather_schema_stats(ownname=>'&USER_NAME',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
/*
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...],
where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
integer--Number of histogram buckets. Must be in the range [1,254].
REPEAT--Collects histograms only on the columns that already have histograms.
AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns
*/

changefilter.sql oracle连接数查看和连接查看.sql Sun30新建oracle户.sql user_job.sql xaview.sql 修改分区索引.SQL 修改数据库核心字符集.txt 僵死进程查询.sql 创建ORACLE表空间.sql 回滚段等待.SQL 在数据库中建JOB.sql 外连接.SQL 外部例程.SQL 建6节点回滚段.SQL 建回滚段.SQL 建立一个与现存数据库相同但不含数据空库.sql 扩展超过100M查询.sql 整理表空间碎片.sql 新建表.sql 显示数据文件信息.sql 查插入表性能.sql 查看session正在执行什么SQL.sql 查看session正在等待何种系统资源.sql 查看哪些session正在使哪些回滚段.sql 查看某个进程正在执行什么SQL语句.sql 查看户表所占空间大小.sql 查看系统SGA区状态.sql 查看系统中使了哪些设备文件.sql 查看系统中每个表空间使情况.sql 查看系统中每个表空间大小.sql 查看系统联接数.sql 查看系统锁.sql 查看表空间使情况.sql 查看进程占回滚段情况.sql 查看那些数据库对象被修改过.sql oupputora过程.sql 户命令查询.sql 户进程查询.sql 监控数据库性能SQL.sqluser_job.sql 索引表清除sql生成.sql 索引表空间使情况查询.sql 索引表空间整理.sql 统计.sql 获取数据库版本信息.sql 表空间使情况.txt 表空间使查询.sql 表空间剩余空间查看.sql 表空间回收.sql 过滤表清除sql生成.sql 进程使回滚段查询.sql 锁表查询.sql

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 7

打赏作者

jaguarcts

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值