常用数据库语句
1.日期函数相加 current date+n year/month/day --当前日期加上n年/月/日
1. 查看数据库的版本
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print 'Server Name...............：' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................：' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname'
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
注意：更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
查看某数据库下,对象级用户权限
sp_helprotect
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8.查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext '@procedure_name'
查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock
进程号1--50是
SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3
检查死锁用sp_who_lock过程
sp_who_lock
11.查看和收缩数据库日志文件的方法
查看所有数据库日志文件大小
dbcc sqlperf(logspace)
如果某些日志文件较大，收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法：
set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable('@error_table_name',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数：
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。
修复完成后，请备份数据库。
repai*_**st 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。
这些修复可以很快完成，并且不会有丢失数据的危险。
repair_rebuild 执行由 repai*_**st 完成的所有修复，包括需要较长时间的修复（如重建索引）。
执行这些修复时不会有丢失数据的危险。
添加、删除、修改使用db.Execute(Sql)命令执行操作
╔--------------------╗
☆ 数据记录筛选 ☆
╚--------------------╝
注意：单双引号的用法可能有误(没有测式)
Sql = "Select D
istinct 字段名 From 数据表"
Distinct函数，查询数据库存表内不重复的记录
Sql = "Select Count(*) From 数据表 where 字段名1>#18:0:0# and 字段名1< #19:00# "
count函数,查询数库表内有多少条记录，“字段名1”是指同一字段
例：
set rs=conn.execute("select count(id) as idnum from news")
response.write rs("idnum")
sql="select * from 数据表 where 字段名 between 值1 and 值2"
Sql="select * from 数据表 where 字段名 between #2003-8-10# and #2003-8-12#"
在日期类数值为2003-8-10 19:55:08 的字段里查找2003-8-10至2003-8-12的所有记录，而不管是几点几分。
select * from tb_name where datetime between #2003-8-10# and #2003-8-12#
字段里面的数据格式为：2003-8-10 19:55:08，通过sql查出2003-8-10至2003-8-12的所有纪录，而不管是几点几分。
Sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]"
Sql="select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]"
模糊查询
Sql="select top 10 * from 数据表 where 字段名 order by 字段名 [desc]"
查找数据库中前10记录
Sql="select top n * form 数据表 order by newid()"
随机取出数据库中的若干条记录的方法
top n，n就是要取出的记录数
Sql="select * from 数据表 where 字段名 in ('值1','值2','值3')"
╔--------------------╗
☆ 添加数据记录 ☆
╚--------------------╝
sql="insert into 数据表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …)"
sql="insert into 数据表 valuess (值1,值2,值3 …)"
不指定具体字段名表示将按照数据表中字段的顺序，依次添加
sql="insert into 目标数据表 select * from 源数据表"
把源数据表的记录添加到目标数据表
╔--------------------╗
☆ 更新数据记录 ☆
╚--------------------╝
Sql="update 数据表 set 字段名=字段值 where 条件表达式"
Sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"
Sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n "
没有条件则更新整个数据表中的指定字段值
╔--------------------╗
☆ 删除数据记录 ☆
╚--------------------╝
Sql="delete from 数据表 where 条件表达式"
Sql="delete from 数据表"
没有条件将删除数据表中所有记录)
╔--------------------------╗
☆ 数据记录统计函数 ☆
╚--------------------------╝
AVG(字段名) 得出一个表格栏平均值
COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名) 取得一个表格栏最大的值
MIN(字段名) 取得一个表格栏最小的值
SUM(字段名) 把数据栏的值相加
引用以上函数的方法：
sql="select sum(字段名) as 别名 from 数据表 where 条件表达式"
set rs=conn.excute(sql)
用
rs("别名") 获取统的计值，其它函数运用同上。
╔-----------------------------╗
☆ 数据表的建立和删除 ☆
╚-----------------------------╝
CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… )
例：CREATE TABLE tab01(name varchar(50),datetime default now())
DROP TABLE 数据表名称 (永久性删除一个数据表)
╔--------------------------╗
☆ 记录集对象的方法 ☆
╚--------------------------╝
rs.movenext 将记录指针从当前的位置向下移一行
rs.moveprevious 将记录指针从当前的位置向上移一行
rs.movefirst 将记录指针移到数据表第一行
rs.movelast 将记录指针移到数据表最后一行
rs.absoluteposition=N 将记录指针移到数据表第N行
rs.absolutepage=N 将记录指针移到第N页的第一行
rs.pagesize=N 设置每页为N条记录
rs.pagecount 根据 pagesize 的设置返回总页数
rs.recordcount 返回记录总数
rs.bof 返回记录指针是否超出数据表首端，true表示是，false为否
rs.eof 返回记录指针是否超出数据表末端，true表示是，false为否
rs.delete 删除当前记录，但记录指针不会向下移动
rs.addnew 添加记录到数据表末端
rs.update 更新数据表记录
查看表空间的名称及大小:
SQL> SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024)), 0) TS_SIZE
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
查看表空间物理文件的名称及大小:
SQL> SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
查看回滚段名称及大小:
SQL> SELECT SEGMENT_NAME,
TABLESPACE_NAME,
R.STATUS,
(INITIAL_EXTENT / 1024) INITIALEXTENT,
(NEXT_EXTENT / 1024) NEXTEXTENT,
MAX_EXTENTS,
V.CUREXT CUREXTENT
FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V
WHERE R.SEGMENT_ID = https://www.wendangku.net/doc/fa18226544.html,N(+)
ORDER BY SEGMENT_NAME;
如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:
SQL> SELECT D.SQL_TEXT, https://www.wendangku.net/doc/fa18226544.html,
FROM V$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D
WHERE https://www.wendangku.net/doc/fa18226544.html,N = B.XIDUSN
AND B.ADDR = C.TADDR
AND C.SQL_ADDRESS = D.ADDRESS
AND C.SQL_HASH_VALUE = D.HASH_VALUE
AND https://www.wendangku.net/doc/fa18226544.html,N = 1;
(备注：你要看哪个，就把usn=?写成几就行了)
查看控制文件:
SQL> SELECT * FROM V$CONTROLFILE;
查看日志文件:
SQL> COL MEMBER FORMAT A50
SQL>SELECT * FROM V$LOGFILE;
如何查看当前SQL*PLUS用户的sid和serial#:
SQL>SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
如何查看当前数据库的字符集:
SQL>SELECT USERENV('LANGUAGE') FROM DUAL;
SQL>SELECT USERENV('LANG') FROM DUAL;
怎么判断当前正在使用何种SQL优化方式:
用EXPLAIN PLAN產生EXPLAIN PLAN?檢查PLAN_TABLE中ID=0
的POSITION列的值
SQL>SELECT DECODE(NVL(POSITION,-1),-1,'RBO',1,'CBO') FROM PLAN_TABLE WHERE ID=0;
如何查看系统当前最新的SCN号：
SQL>SELECT MAX(KTUXESCNW * POWER(2,32) + KTUXESCNB) FROM X$KTUXE;
在ORACLE中查找TRACE文件的脚本:
SQL>SELECT U_DUMP.VALUE || '/' || INSTANCE.VALUE || '_ORA_' ||
V$PROCESS.SPID || NVL2(V$PROCESS.TRACEID, '_' || V$PROCESS.TRACEID, NULL ) || '.TRC'"TRACE FILE" FROM V$PARAMETER U_DUMP CROSS JOIN V$PARAMETER INSTANCE CROSS JOIN V$PROCESS JOIN V$SESSION ON V$PROCESS.ADDR = V$SESSION.PADDR WHERE U_https://www.wendangku.net/doc/fa18226544.html, = 'USER_DUMP_DEST' AND
https://www.wendangku.net/doc/fa18226544.html, = 'INSTANCE_NAME' AND V$SESSION.AUDSID=SYS_CONTEXT('USERENV','SESSIONID');
SQL>SELECT D.VALUE || '/ORA_' || P.SPID || '.TRC' TRACE_FILE_NAME
FROM (SELECT P.SPID FROM SYS.V_$MYSTAT M,SYS.V_$SESSION S,
SYS.V_$PROCESS P WHERE M.STATISTIC# = 1 AND
S.SID = M.SID AND P.ADDR = S.PADDR) P,(SELECT VALUE FROM SYS.V_$PARAMETER WHERE NAME ='USER_DUMP_DEST') D;
如何查看客户端登陆的IP地址:
SQL>SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;
如何在生产数据库中创建一个追踪客户端IP地址的触发器：
SQL>CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
END;
查询当前日期:
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;
查看所有表空间对应的数据文件名：
SQL>SELECT DISTINCT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
查看表空间的使用情况:
SQL>SELECT SUM(BYTES)/(1024*1024) AS FREE_SPACE,TABLESPACE_NAME
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
COLUMN TABLESPACE_NAME FORMAT A18;
COLUMN SUM_M FORMAT A12;
COLUMN USED_M FORMAT A12;
COLUMN FREE_M FORMAT A12;
COLUMN PTO_M FORMAT 9.99;
SELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(https://www.wendangku.net/doc/fa18226544.html,EDSPACE/1024/1024))||'M' USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M, SUM(https://www.wendangku.net/doc/fa18226544.html,EDSPACE)/SUM(S.BYTES) PTUSED FROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE, SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE A.FILE_ID(+)=B.FILE_ID GROUP BY B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAM
E) S GROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) DESC;
查看数据文件的hwm（可以resize的最小空间）和文件头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE,
NUM1-NUM3 "USED_SPACE(HWM)",NVL(NUM2,0) DATA_SPACE,NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+) AND V1.FILE_ID=V3.FILE_ID(+);
数据文件大小及头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,
NUM1 TOTLE_SPACE,
NUM3 FREE_SPACE,
NUM1-NUM3 USED_SPACE,
NVL(NUM2,0) DATA_SPACE,
NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+)
AND V1.FILE_ID=V3.FILE_ID(+);
(运行以上查询，我们可以如下信息：
Totle_pace:该数据文件的总大小，字节为单位
Free_space:该数据文件的剩于大小，字节为单位
Used_space:该数据文件的已用空间，字节为单位
Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位
File_Head:该数据文件头部占用空间，字节为单位)
数据库各个表空间增长情况的检查：
SQL>SELECT A.TABLESPACE_NAME,(1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENT
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;
SQL>SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",F.TOTAL_BYTES"空闲空间(M)",
F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
查看各个表空间占用磁盘情况:
SQL>COL TABLESPACE_NAME FORMAT A20;
SQL>SELECT B.FILE_ID FILE_ID,
B.TABLESPACE_NAME TABLESPACE_NAME,
B.BYTES BYTES,
(B.BYTES-SUM(NVL(A.BYTES,0))) USED,
SUM(NVL(A.BYTES,0)) FREE,
SUM(NVL(A.BYTES,0))/(B.BYTES)*100 PERCENT
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_
ID,B.BYTES
ORDER BY B.FILE_ID;
数据库对象下一扩展与表空间的free扩展值的检查：
SQL>SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK
UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_INDEXES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK;
Disk Read最高的SQL语句的获取：
SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5;
查找前十条性能差的sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<10 ;
等待时间最多的5个系统等待事件的获取：
SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
查看当前等待事件的会话:
COL USERNAME FORMAT A10
SET LINE 120
COL EVENT FORMAT A30
SELECT SE.SID,https://www.wendangku.net/doc/fa18226544.html,ERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT
FROM V$SESSION S,V$SESSION_EVENT SE WHERE https://www.wendangku.net/doc/fa18226544.html,ERNAME IS NOT NULL AND SE.SID=S.SID
AND S.STATUS='ACTIVE' AND SE.EVENT NOT LIKE '%SQL*NET%';
SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAIT WHERE EVENT NOT LIKE '%MESSAGE%' AND EVENT NOT LIKE 'SQL*NET%' AND EVENT NOT LIKE '%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER';
找到与所连接的会话有关的当前等待事件：
SELECT SW.SID,https://www.wendangku.net/doc/fa18226544.html,ERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT
FROM V$SESSION S,V$SESSION_WAIT SW WHERE https://www.wendangku.net/doc/fa18226544.html,ERNAME IS NOT NULL AND SW.SID=S.SID
AND SW.EVENT NOT LIKE '%SQL*NET%' ORDER BY SW.WAIT_TIME DESC;
Oracle所有回滚段状态的检查：
SQL>SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUS FROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE#;
Oracle回滚段扩展信息的检查:
COL NAME FORMAT A10
SET LINESIZE 140
SELECT SUBSTR(NAME,1,40) NAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE
FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (https://www.wendangku.net/doc/fa18226544.html,N=https://www.wendangku.net/doc/fa18226544.html,N);
EXTENTS:回滚段中的盘区数量。
Rssize:以字节为单位的回滚段的尺寸。
optsize：为optimal参数设定的值。
Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。
Extends:系统为回滚段增加的盘区的次数。
Shrinks:系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。
Hwmsize:回滚段尺寸的上限，即回滚段曾经达到的最大尺寸。
(如果回滚段平均尺寸接近OPTIMAL的值，那么说明O
PTIMAL的值设置正确，如果回滚段动态增长次数或收缩次数很高，那么需要提高OPTIMAL的值)
查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
SELECT https://www.wendangku.net/doc/fa18226544.html,ERNAME, https://www.wendangku.net/doc/fa18226544.html, FROM V$TRANSACTION T,V$ROLLSTAT R,
V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND
T.XIDUSN=https://www.wendangku.net/doc/fa18226544.html,N AND https://www.wendangku.net/doc/fa18226544.html,N=https://www.wendangku.net/doc/fa18226544.html,N ORDER BY https://www.wendangku.net/doc/fa18226544.html,ERNAME;
如何查看一下某个shared_server正在忙什么:
SELECT https://www.wendangku.net/doc/fa18226544.html,ERNAME,A.MACHINE,A.PROGRAM,A.SID,
A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID=13161 AND B.ADDR=A.PADDR
AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
数据库共享池性能检查:
SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
检查数据重载比率:
SELECT SUM(RELOADS)/SUM(PINS)*100 "RELOAD RATIO" FROM
V$LIBRARYCACHE;
检查数据字典的命中率:
SELECT 1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT
RATIO" FROM V$ROWCACHE;
(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)
检查共享内存的剩余情况:
SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;
(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)
数据高速缓冲区性能检查:
SELECT 1-P.VALUE/(B.VALUE+C.VALUE) "DB BUFFER CACHE HIT RATIO" FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE https://www.wendangku.net/doc/fa18226544.html,='PHYSICAL READS' AND https://www.wendangku.net/doc/fa18226544.html,='DB BLOCK GETS' AND https://www.wendangku.net/doc/fa18226544.html,='CONSISTENT GETS';
检查buffer pool HIT_RATIO执行
SELECT NAME, (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS)> 0;
(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)
数据库回滚段性能检查:
检查Ratio执行
SELECT SUM(WAITS)* 100 /SUM(GETS) "RATIO", SUM(WAITS) "WAITS", SUM(GETS) "GETS" FROM V$ROLLSTAT;
检查count/value执行:
SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS LIKE '%UNDO%';
SELECT VALUE FROM V$SYSSTAT WHERE NAME='CONSISTENT GETS';
(两者的value值相除)
检查average_wait执行:
SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%UNDO%';
检查RBS header get ratio执行:
SELECT https://www.wendangku.net/doc/fa18226544.html,,https://www.wendangku.net/doc/fa18226544.html,N,S.WRAPS, DECODE(S.WAITS,0,1,1- S.WAITS/S.GETS)"RBS HEADER GET RATIO" FROM V$ROLLSTAT S,V$ROLLNAME N WHERE https://www.wendangku.net/doc/fa18226544.html,N=https://www.wendangku.net/doc/fa18226544.html,N;
(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)
杀会话的脚本:
SELECT A.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,https://www.wendangku.net/doc/fa18226544.html,ERNAME,A.OSUSER,A.LOGON_TIME,https://www.wendangku.net/doc/fa18226544.html,ST_CALL_ET/3600 LAST_HOUR,A.STATUS, 'ORAKILL '||SID||' '||SPID HOST_COMMAND,'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||'''' SQL_COMMAND FROM V$SE
SSION A,V$PROCESS B WHERE A.PADDR=B.ADDR AND SID>6;
查看排序段的性能:
SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('SORTS (MEMORY)', 'SORTS (DISK)');
7、查看数据库库对象:
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;
8、查看数据库的版本:
SELECT * FROM V$VERSION;
9、查看数据库的创建日期和归档方式:
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;
10、捕捉运行很久的SQL:
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
11、查看数据表的参数信息:
SELECT PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS,FREELIST_GROUPS, LOGGING, BUFFER_POOL, NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZED FROM DBA_TAB_PARTITIONS
--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER
ORDER BY PARTITION_POSITION;
12、查看还没提交的事务:
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM V$TRANSACTION;
13、查找object为哪些进程所用:
SELECT P.SPID,S.SID,S.SERIAL# SERIAL_NUM,https://www.wendangku.net/doc/fa18226544.html,ERNAME USER_NAME,
A.TYPE OBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECT OBJECT_NAME,DECODE(SIGN(48 - COMMAND),1,
TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,
P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUS FROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.TYPE = 'USER' AND A.SID = S.SID AND A.OBJECT='SUBSCRIBER_ATTR'ORDER BY https://www.wendangku.net/doc/fa18226544.html,ERNAME, S.OSUSER;
14、查看回滚段:
SQL>COL NAME FORMAT A10
SQL>SET LINESIZE 100
SQL>SELECT ROWNUM, SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME NAME, V$ROLLSTAT.EXTENTS EXTENTS, V$ROLLSTAT.RSSIZE SIZE_IN_BYTES, V$ROLLSTAT.XACTS XACTS, V$ROLLSTAT.GETS GETS, V$ROLLSTAT.WAITS WAITS, V$ROLLSTAT.WRITES WRITES, SYS.DBA_ROLLBACK_SEGS.STATUS STATUS FROM V$ROLLSTAT, SYS.DBA_ROLLBACK_SEGS, V$ROLLNAME WHERE V$https://www.wendangku.net/doc/fa18226544.html,(+) = SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME AND V$https://www.wendangku.net/doc/fa18226544.html,N (+) = V$https://www.wendangku.net/doc/fa18226544.html,N ORDER BY ROWNUM;
15、耗资源的进程(top session):
SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSER OS_USER_NAME,S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(https://www.wendangku.net/doc/fa18226544.html,ERNAME,'[ORACLE PROCESS]') USER_NAME,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,ST.VALUE CRITERIA_VALUE FROM V$SESSTAT ST,V$SESSION S,V$PROCESS P WHERE ST.SID = S.SID AND ST.STATISTIC# = TO_NUMBER('38') AND ('ALL'='ALL' OR S.STATUS ='ALL') AND P.ADDR=S.PADDR ORDER BY ST.VALUE DESC,P.SPID ASC,https://www.wendangku.net/doc/fa18226544.html,ERNAME ASC,S.OSUSER ASC;
根据PID查找相应
的语句:
SELECT https://www.wendangku.net/doc/fa18226544.html,ERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID=SPID AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;