DBA日常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 '%meer%';

--求系统中较大的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 wner='&' 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', ptions=>sys.dbms_logmnr.new);

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

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

exec sys.dbms_logmnr.add_logfile(logfilename=>'/home/oracle/oradata/esal/archive/1_22914.dbf', ptions=>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 用oupput的ora过程.sql 用户命令查询.sql 用户进程查询.sql 监控数据库性能的SQL.sql 看user_job.sql 索引表清除sql生成.sql 索引表空间使用情况查询.sql 索引表空间整理.sql 统计.sql 获取数据库版本信息.sql 表空间使用情况.txt 表空间使用查询.sql 表空间剩余空间查看.sql 表空间回收.sql 过滤表清除sql生成.sql 进程使用回滚段查询.sql 锁表查询.sql
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值