DBA常用SQL脚本

大家好，我是晓彬，今天和大家分享数据库运维的一些常用脚本。

1、数据库启动

A、startup nomount

　　非安装启动，这种方式下启动可执行：重建控制文件、重建数据库，读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件

B、startup mount (dbname)

　　安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机、重新定位数据文件、重做日志文件。

C、startup open (dbname)

　　先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件，这种方式下可访问数据库中的数据。

D、startup 等于按顺序执行以下三个命令

　 startup nomount

　 alter database mount

　 alter database open

E、startup restrict

　约束方式启动，这种方式能够启动数据库，但只允许具有一定特权的用户访问

F、startup force

　强制启动方式，当不能关闭数据库时，可以用startup force来完成数据库的关闭，先关闭数据库，再执行正常启动数据库命令

G、startup pfile=参数文件名

　　带初始化参数文件的启动方式，先读取参数文件，再按参数文件中的设置启动数据库

H、startup EXCLUSIVE

　　独占和共享启动

2、关闭数据的方式

A、shutdown normal
　　正常方式关闭数据库

B、shutdown immediate

立即方式关闭数据库，在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源），当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。

C、shutdown abort

　直接关闭数据库，正在访问数据库的会话会被突然终止，如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。

3、查询每个表空间大小

Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

4、查询剩余表空间大小

SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name

5、查询表分配空间大小

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

6、查询表实际占用空间大小

select num_rows * avg_row_len from user_tables

where table_name in (select table_name from user_tables)

7、检查系统中所有表空间总大小

select b.name,sum(a.bytes/1000000)总空间 from v$datafile a,v$tablespace b where a.ts#=b.ts# group by b.name

8、查询数据库中文件信息

select

　　b.file_name 物理文件名,

　　b.tablespace_name 表空间,

　　b.bytes/1024/1024 大小M,

　　(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,

　　substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率

　　from dba_free_space a,dba_data_files b

　　where a.file_id=b.file_id

　　group by b.tablespace_name,b.file_name,b.bytes

　　order by b.tablespace_name

9、查询所有用户表使用大小的前5

select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 5

10、查询碎片高的表

SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小 M",

(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"实际大小 M",

round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"

FROM USER_TABLES

where blocks>100

and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3

order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc

11、查询索引占碎片比例

select name,del_lf_rows,lf_rows,

round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct

from index_stats

where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30

12、集群因子高的表

集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,

round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"

from user_tables tab, user_indexes ind where tab.table_name=ind.table_name and tab.blocks>100

and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

13、根据 sid 查 spid 或根据 spid 查 sid

select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal

from v$session s,v$process p

where s.paddr=p.addr and s.sid=SS or p.spid=BB

14、根据 sid 查sql 语句

select username,sql_text,machine,osuser from v$session a,v$sqltext_with_newlinesb

where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value

and a.sid=&sid order by piece

15、查询sid

select instance_name from V$instance

16、查询ORACLE 的session的id

select sid from V$mystat where rownum=1

17、查询ORACLE 的SESSION_SERIAL

select * from v$session where username is not null

18、通过SID查询SPID在进一步查询到IP

select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal

from v$session s,v$process p

where s.paddr=p.addr and SID=7

netstat -anp |grep 2716

19、查询四锁SID

select sid, blocking_session, LOGON_TIME,sql_id,status,

event,seconds_in_wait,state, BLOCKING_SESSION_STATUS

from v$session where event like 'enq%'

and state='WAITING'

and BLOCKING_SESSION_STATUS='VALID'

20、查询DDL锁住的SQL

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",

s.LOGON_TIME FROM x$kglpn p, v$session s

WHERE p.kglpnuse=s.saddr AND kglpnhdl

IN(SELECT p1raw FROM sys.v_$session_wait WHERE event like 'library cache %')

21、查询锁住的DDL对象

select d.session_id,s.SERIAL#,d.name

from dba_ddl_locks d,v$session s

where d.owner='system' and d.SESSION_ID=s.sid

22、查询正在执行的SQL

select s.sid,s.serial#,s.username,spid,v$sql.sql_id,

machine,s.terminal,s.program,sql_text

FROM v$process,v$session s,v$sql

WHERE addr=paddr and s.sql_id=v$sql.sql_id

AND sql_hash_value=hash_value

23、查询正在执行的SCHEDULER_JOB

select owner,job_name,sid,b.SERIAL#,b.username,spid

from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process

where session_id=sid and paddr=addr

24、查询正在执行的 dbms_job

select job,b.sid,b.SERIAL#,b.username,spid

from DBA_JOBS_RUNNING a ,v$session b,v$process

where a.sid=b.sid and paddr=addr

25、查询会话和进程消耗的平均内存大小

select round(sum(pga_used_mem)/1024/1024,0) total_used_M,

round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,

round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M,

round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M

from v$process

26、执行次数排序

select *from (select executions,username,PARSING_USER_ID,sql_id,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID

order by executions desc)where rownum <=5

27、物理读查询

select *from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users

where user_id=PARSING_USER_ID

order by DISK_READS desc)where rownum <=3

28、逻辑读查询

select *from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users

where user_id=PARSING_USER_ID

order by BUFFER_GETS desc)where rownum <=3

29、高CPU占用查询

select *from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users

where user_id=PARSING_USER_ID

order by CPU_TIME/1000000 desc)where rownum <=3

30、等待查询

select event,sum(decode(wait_time,0,0,1)) "之前等待次数",

sum(decode(wait_time,0,1,0)) "正在等待次数",

count(*) from v$session_wait

group by event order by 4 desc

31、查询需要使用绑定变量的SQL

select sql_id, FORCE_MATCHING_SIGNATURE, sql_text

from v$SQL

where FORCE_MATCHING_SIGNATURE

in(select /*+ unnest */FORCE_MATCHING_SIGNATURE from v$sql

where FORCE_MATCHING_SIGNATURE > 0

and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE

group by FORCE_MATCHING_SIGNATURE having count(1) > 10)

32、查询数据文件可用占比

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,

ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",

ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",

ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' "文件可用百分比"

from dba_free_space a,dba_data_files b where a.file_id=b.file_id

and b.file_id>4 group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES

order by b.tablespace_name;

33、查看表空间可用占比

select b.tablespace_name,a.total,b.free,

round((b.free/a.total)*100) "% Free"

from(select tablespace_name, sum(bytes/(1024*1024)) total

from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024))) free

from dba_free_space group by tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

order by "% Free"

34、查询undo表空间使用情况

select tablespace_name,status,sum(bytes)/1024/1024 M

from dba_undo_extents

group by tablespace_name,status

35、查看ASM磁盘组使用率

select name,round(total_mb/1024) "总容量",

round(free_mb/1024) "空闲空间",

round((free_mb/total_mb)*100) "可用空间比例"

from gv$asm_diskgroup

36、查询每个使用表空间情况

SELECT c.owner "用户",

a.tablespace_name "表空间名",

total/1024/1024 "表空间大小 M",

free/1024/1024 "表空间剩余大小 M",

( total - free )/1024/1024 "表空间使用大小 M",

Round(( total - free ) / total, 4) * 100 "表空间总计使用率%",

c.schemas_use/1024/1024 "用户使用表空间大小 M",

round((schemas_use)/total,4)*100 "用户使用表空间率%"

FROM (SELECT tablespace_name, Sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,

(SELECT tablespace_name,Sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b,

(Select owner ,Tablespace_Name,Sum(bytes) schemas_use From Dba_Segments Group By owner,Tablespace_Name) c

WHERE a.tablespace_name = b.tablespace_name

and a.tablespace_name =c.Tablespace_Name

order by c.owner,a.tablespace_name;

37、查看闪回区\快速恢复区空间使用率

select sum(percent_space_used)||'%' "已使用空间比例"

from V$RECOVERY_AREA_USAGE

38、查看行迁移或者行连接的表

select * From dba_tables where nvl(chain_cnt,0)<>0

39、查询缓冲区命中率

SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,

round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio

FROM v$sysstat a,v$sysstat b,v$sysstat c

WHERE a.NAME='db block gets'

AND b.NAME='consistent gets'

AND c.NAME='physical reads'

40、查询共享池命中率

select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache

41、查询归档日志切换频率

select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,

First_change#,switch_change#

from v$loghist

where first_time>sysdate-3 order by 1

42、查询没有建立索引的表

Select table_name

from user_tables

where table_name not in (select table_name from user_indexes)

43、查询LGWR一次需要的时长

select event,state,seq#,seconds_in_wait,program

from v$session

where program like '%LGWR%'

and state='WAITING'