-
SQL Server 2008教程之监控当前数据库谁在运行什么
一些有用的SQL，都是Oracle manage常用的。
列在这里做参考，因为太难记了。
时时更新。
where a.sql_address =b.address order by address, piece;
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
select a.file_id "FileNo",a.tablespace_name "Tablespace_name",
round(a.bytes/1024/1024,4) "Total MB",
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",
round(sum(nvl(b.bytes,0))/a.bytes*100,4) "%Free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name
SELECT OSUSER,SERIAL#
FROM V$SESSION, V$SQL
WHERE
V$SESSION.SQL_ADDRESS=V$SQL.ADDRESS AND
V$SESSION.STATUS = 'ACTIVE';
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# = 38 and b.statistic# = 39
and c.statistic# = 40;
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
from v$librarycache;
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
from v$locked_object a, dba_objects c
where a.object_id=c.object_id
资料引用:http://www.knowsky.com/385333.html
hash_value,
disk_reads / executions disk_reads,
elapsed_time / 1000000 / executions as "ELAPSD_TIME(s)",
buffer_gets / executions bgets_per,
executions,
first_load_time as first_time,
sql_text
from v$sql
where executions > 0
and (disk_reads > executions * 500 or buffer_gets > executions * 20000)
and command_type = 3
order by 3, 4;
select *
from dba_segments
where TABLESPACE_NAME = 'SYSTEM';
查询发现该表字段OWNER中，出现了不属于SYSTEM的用户。然后使用下面语句将不属于SYSTEM的表（字段SEGMENT_NAME）,转移到指定的表空间内。
ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME;
ALTER INDEX -- 改变一个索引的定义
Synopsis
ALTER INDEX name
action [, ... ]
ALTER INDEX name
RENAME TO new_name
这里的 action 是下列之一：
OWNER TO new_owner
SET TABLESPACE indexspace_name
ORA-01502错误成因和解决方法
方法2：通过常见所以彻底解决这个问题
select index_name, index_type, tablespace_name, table_type, status
from user_indexes
where tablespace_name = 'USERS' and status='UNUSABLE'
alter session set skip_unusable_indexes=false;
alter index SMS_AUDIT rebuild;
首先，先设置 “skip_unusable_indexes=false”，也就是不跳过失效索引
SQL> alter session set skip_unusable_indexes=false;
Session altered.
SQL>
然后重建这个失效的索引
SQL> alter index idxt rebuild;
Index altered.
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDXT NORMAL DATA_DYNAMIC TABLE VALID
SQL>
我们看到重建索引后，索引的状态就正常了。
现在插入数据，看看是正常：
SQL> insert into t values(12);
1 row created.
SQL> commit;
Commit complete.
SQL>
看来，重建索引才是解决这类问题的彻底的方法。
SQL> create table t(a number);
Table created.
现在，我们建立一个唯一索引来看看：
SQL> create unique index idx_t on t(a);
Index created.
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T';
no rows selected
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T NORMAL DATA_DYNAMIC TABLE VALID
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
将索引手工修改为unusable状态（模拟发生索引失效的情况）：
SQL> alter index idx_t unusable;
Index altered.
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T NORMAL DATA_DYNAMIC TABLE UNUSABLE
ORA-28000: the account is locked.
要对账户解锁，可在数据库管理员账户(sys/system)中使用“alter user”命令的account unlock子句。
如下所示：
alter user username account unlock;
账户解锁后，username 账户再一次被允许连接。
cn1 number;
cn2 number;
begin
insert into t_primary_1
select * from t_primary;
cn1 := sql%rowcount;--获取上面语句执行的条数（必须在commit之前赋值）
commit;
insert into t_log (name, cnt) values ('1', cn1);
commit;
insert into t_primary_2
select * from t_primary_2;
cn2 := sql%rowcount;
commit;
insert into t_log (name, cnt) values ('2', cn2);
commit;
end;
使用alter table .. move compress使一个已存在但未压缩的表转换为压缩表.
SQL> alter table tmp_test move compress;
同样,也可以使用alter table.. move nocompress来解压一个已经压缩的表:
SQL> alter table tmp_test move nocompress;
确定表是否被压缩:
确定一个表是否使用了压缩,查询user_tables,compression字段表明表是否被压缩.
SQL> select table_name,compression from user_tables where table_name not like 'BIN%';
TABLE_NAME COMPRESS
------------------------------ --------
CLASSES ENABLED
ROOMS ENABLED
STUDENTS DISABLED
MAJOR_STATS DISABLED
INTO SALES_HISTORY_COMP
SELECT * FROM SALES_HISTORY;
select *
from v$session t1, v$locked_object t2
where t1.sid = t2.SESSION_ID;
查看当前执行的进程：
select * from v$access
查看想要杀死的进程的SID和serial#字段：
select * from v$session where sid in (刚才你查出来的sid）;
杀session：
alter system kill session 'sid,serial#'; 需要dba权限
查看session状态 现在应该是killed
select status from v$session
where sid in (刚才你查出来的sid);
系统级别上的杀session：
查找到你想要杀的session对应的sid
select paddr from v$session where sid = ????
select spid from v$process where address = 上面查询的paddr
查找到对应的spid，然后在数据库所在的操作系统上的dos环境下执行
orakill 数据库实例名 对应spid
sqlplus /nolog
SQL> conn username/password@SID（登录DBA权限工号）
SQL> alter user username identified by password;（密码重置）
SQL> alter user username account unlock;（解锁）
create or replace procedure pr_sms_interface (stat_date in varchar2,serv_number in varchar2,message_in in varchar2)
/** head
* @name pr_sms_interface
* @caption 应用层短信接口
* @type 接口
* @parameter stat_date in varchar2 统计日期格式：yyyymmdd
* @parameter oi_return out number 执行状态码，整数，0 正常，-1 出错
* @description
* @target db_app#pr_sms_interface
* @source
* @middle
* @version 1.0
* @author
* @create-date 2010-09-28
* @todo 无
* @version
* @mender
* @modify_date
* @modify_desc
* @copyright
*/
-- ********************************************************************************
-- 程序名称: db_app.pr_sms_interface
-- 功能描述: 应用层短信接口
-- 输入参数: stat_date - 统计日期
-- 输出参数: oi_return - 执行状态码，整数，0 正常，-1 出错
-- 输入资源:
-- 输出资源:
-- 中间资源:
-- 创建人员: 姜春涛
-- 创建日期: 2010-09-28
-- 版本说明: v1.0
-- 修改人员:
-- 修改日期:
-- 修改原因:
-- 版本说明:
-- 公司名称: 南京联创
-- ********************************************************************************
is
vs_task_name varchar2(30); -- 任务名称
vs_table_name varchar2(30); -- 表名称
vs_message varchar2(200); -- 日志信息
vi_task_id integer; -- 日志id
vi_row_count number; -- 临时结果
begin
vs_task_name := 'pr_sms_interface';
vs_table_name := 'pr_sms_interface';
-- 程序开始日志
db_app.ps_log(vs_task_name, vs_table_name, stat_date, 1, null, vi_task_id);
select kt.sms_id.nextval@dblink_hljcrm into vi_row_count from dual;
commit;
insert into ods.sms_info@odsdb
(
id,
msisdn,
flag,
msg,
sts,
get_date,
send_date,
pri
)
select
vi_row_count,
serv_number,
'ODS',
message_in,
'A',
sysdate,
sysdate,
0
from dual;
commit;
-- 备份发送接口信息
insert into db_app.tr_sms_info
(
id,
msisdn,
flag,
msg,
sts,
get_date,
send_date,
pri
)
select
vi_row_count,
serv_number,
'ODS',
message_in,
'A',
sysdate,
sysdate,
0
from dual
;
commit;
-- 程序结束日志
db_app.ps_log(null, null, null, 2, null, vi_task_id);
------------------------------------------------------------
-- 程序结束日志
db_app.ps_log(null, null, null, 2, null, vi_task_id);
-- 成功返回
return;
exception
when others then
-- 得到出错信息
vs_message := substr(sqlerrm, 1, 200);
-- 回滚事务
rollback;
-- 程序出错日志
db_app.ps_log(null, null, null, 3, vs_message, vi_task_id);
-- 出错返回
return;
end;
列在这里做参考，因为太难记了。
时时更新。
1、监控当前数据库谁在运行什么SQL 语句
SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;
2、查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extentsFROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
3。表空间使用状态
select a.file_id "FileNo",a.tablespace_name "Tablespace_name",
round(a.bytes/1024/1024,4) "Total MB",
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",
round(sum(nvl(b.bytes,0))/a.bytes*100,4) "%Free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name
4。查看USER
SELECT OSUSER,SERIAL#
FROM V$SESSION, V$SQL
WHERE
V$SESSION.SQL_ADDRESS=V$SQL.ADDRESS AND
V$SESSION.STATUS = 'ACTIVE';
5。监控 SGA 的命中率
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# = 38 and b.statistic# = 39
and c.statistic# = 40;
6。监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
7。监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"from v$librarycache;
8。监控内存和硬盘的排序比率
监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
9。哪笔数据正在被人update，而且是被谁正在update
select a.os_user_name, a.oracle_username,a.object_id,c.object_name,c.object_typefrom v$locked_object a, dba_objects c
where a.object_id=c.object_id
资料引用:http://www.knowsky.com/385333.html
10、查看sql语句占用的空间
select address,hash_value,
disk_reads / executions disk_reads,
elapsed_time / 1000000 / executions as "ELAPSD_TIME(s)",
buffer_gets / executions bgets_per,
executions,
first_load_time as first_time,
sql_text
from v$sql
where executions > 0
and (disk_reads > executions * 500 or buffer_gets > executions * 20000)
and command_type = 3
order by 3, 4;
11、表的表空间更改
近日，发现pl/sql无法登陆oracle，查询之后发现，system表空间竟然满了。原来是我在建立oracle表空间建立时，出现了默认表空间归属错误，使用sys用户登陆该SID的DBA后，使用语句select *
from dba_segments
where TABLESPACE_NAME = 'SYSTEM';
查询发现该表字段OWNER中，出现了不属于SYSTEM的用户。然后使用下面语句将不属于SYSTEM的表（字段SEGMENT_NAME）,转移到指定的表空间内。
ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME;
12、ALTER INDEX
NameALTER INDEX -- 改变一个索引的定义
Synopsis
ALTER INDEX name
action [, ... ]
ALTER INDEX name
RENAME TO new_name
这里的 action 是下列之一：
OWNER TO new_owner
SET TABLESPACE indexspace_name
ORA-01502错误成因和解决方法
方法2：通过常见所以彻底解决这个问题
select index_name, index_type, tablespace_name, table_type, status
from user_indexes
where tablespace_name = 'USERS' and status='UNUSABLE'
alter session set skip_unusable_indexes=false;
alter index SMS_AUDIT rebuild;
首先，先设置 “skip_unusable_indexes=false”，也就是不跳过失效索引
SQL> alter session set skip_unusable_indexes=false;
Session altered.
SQL>
然后重建这个失效的索引
SQL> alter index idxt rebuild;
Index altered.
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDXT';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDXT NORMAL DATA_DYNAMIC TABLE VALID
SQL>
我们看到重建索引后，索引的状态就正常了。
现在插入数据，看看是正常：
SQL> insert into t values(12);
1 row created.
SQL> commit;
Commit complete.
SQL>
看来，重建索引才是解决这类问题的彻底的方法。
SQL> create table t(a number);
Table created.
现在，我们建立一个唯一索引来看看：
SQL> create unique index idx_t on t(a);
Index created.
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='T';
no rows selected
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T NORMAL DATA_DYNAMIC TABLE VALID
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
将索引手工修改为unusable状态（模拟发生索引失效的情况）：
SQL> alter index idx_t unusable;
Index altered.
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='IDX_T';
INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ --------------------------- ------------------------------ ----------- --------
IDX_T NORMAL DATA_DYNAMIC TABLE UNUSABLE
ORA-28000: the account is locked.
要对账户解锁，可在数据库管理员账户(sys/system)中使用“alter user”命令的account unlock子句。
如下所示：
alter user username account unlock;
账户解锁后，username 账户再一次被允许连接。
13、计算各表使用空间语句
（相关sql教程 及sql server 2008 教程）
select segment_name,sum(bytes)/1024/1024/1024 from user_segments group by segment_name14、存储过程中增加每步执行结果日志信息和异常捕捉
create or replace procedure makedata iscn1 number;
cn2 number;
begin
insert into t_primary_1
select * from t_primary;
cn1 := sql%rowcount;--获取上面语句执行的条数（必须在commit之前赋值）
commit;
insert into t_log (name, cnt) values ('1', cn1);
commit;
insert into t_primary_2
select * from t_primary_2;
cn2 := sql%rowcount;
commit;
insert into t_log (name, cnt) values ('2', cn2);
commit;
end;
15、压缩表所占空间的语句（压缩表可以提高查询速度，但插入和删除速度会大大受影响）
压缩一个已经存在但并未压缩的表使用alter table .. move compress使一个已存在但未压缩的表转换为压缩表.
SQL> alter table tmp_test move compress;
同样,也可以使用alter table.. move nocompress来解压一个已经压缩的表:
SQL> alter table tmp_test move nocompress;
确定表是否被压缩:
确定一个表是否使用了压缩,查询user_tables,compression字段表明表是否被压缩.
SQL> select table_name,compression from user_tables where table_name not like 'BIN%';
TABLE_NAME COMPRESS
------------------------------ --------
CLASSES ENABLED
ROOMS ENABLED
STUDENTS DISABLED
MAJOR_STATS DISABLED
16、插入效率高的语句
INSERT /*+ APPEND */INTO SALES_HISTORY_COMP
SELECT * FROM SALES_HISTORY;
17、杀死锁/或杀指定进程
通过下面语句查询出死锁的sessionselect *
from v$session t1, v$locked_object t2
where t1.sid = t2.SESSION_ID;
查看当前执行的进程：
select * from v$access
查看想要杀死的进程的SID和serial#字段：
select * from v$session where sid in (刚才你查出来的sid）;
杀session：
alter system kill session 'sid,serial#'; 需要dba权限
查看session状态 现在应该是killed
select status from v$session
where sid in (刚才你查出来的sid);
系统级别上的杀session：
查找到你想要杀的session对应的sid
select paddr from v$session where sid = ????
select spid from v$process where address = 上面查询的paddr
查找到对应的spid，然后在数据库所在的操作系统上的dos环境下执行
orakill 数据库实例名 对应spid
18、DBA密码遗忘及用户被锁解决方法
DOS环境下：sqlplus /nolog
SQL> conn username/password@SID（登录DBA权限工号）
SQL> alter user username identified by password;（密码重置）
SQL> alter user username account unlock;（解锁）
19、存储过程样本范例
存储过程编写 可参考此范本：create or replace procedure pr_sms_interface (stat_date in varchar2,serv_number in varchar2,message_in in varchar2)
/** head
* @name pr_sms_interface
* @caption 应用层短信接口
* @type 接口
* @parameter stat_date in varchar2 统计日期格式：yyyymmdd
* @parameter oi_return out number 执行状态码，整数，0 正常，-1 出错
* @description
* @target db_app#pr_sms_interface
* @source
* @middle
* @version 1.0
* @author
* @create-date 2010-09-28
* @todo 无
* @version
* @mender
* @modify_date
* @modify_desc
* @copyright
*/
-- ********************************************************************************
-- 程序名称: db_app.pr_sms_interface
-- 功能描述: 应用层短信接口
-- 输入参数: stat_date - 统计日期
-- 输出参数: oi_return - 执行状态码，整数，0 正常，-1 出错
-- 输入资源:
-- 输出资源:
-- 中间资源:
-- 创建人员: 姜春涛
-- 创建日期: 2010-09-28
-- 版本说明: v1.0
-- 修改人员:
-- 修改日期:
-- 修改原因:
-- 版本说明:
-- 公司名称: 南京联创
-- ********************************************************************************
is
vs_task_name varchar2(30); -- 任务名称
vs_table_name varchar2(30); -- 表名称
vs_message varchar2(200); -- 日志信息
vi_task_id integer; -- 日志id
vi_row_count number; -- 临时结果
begin
vs_task_name := 'pr_sms_interface';
vs_table_name := 'pr_sms_interface';
-- 程序开始日志
db_app.ps_log(vs_task_name, vs_table_name, stat_date, 1, null, vi_task_id);
select kt.sms_id.nextval@dblink_hljcrm into vi_row_count from dual;
commit;
insert into ods.sms_info@odsdb
(
id,
msisdn,
flag,
msg,
sts,
get_date,
send_date,
pri
)
select
vi_row_count,
serv_number,
'ODS',
message_in,
'A',
sysdate,
sysdate,
0
from dual;
commit;
-- 备份发送接口信息
insert into db_app.tr_sms_info
(
id,
msisdn,
flag,
msg,
sts,
get_date,
send_date,
pri
)
select
vi_row_count,
serv_number,
'ODS',
message_in,
'A',
sysdate,
sysdate,
0
from dual
;
commit;
-- 程序结束日志
db_app.ps_log(null, null, null, 2, null, vi_task_id);
------------------------------------------------------------
-- 程序结束日志
db_app.ps_log(null, null, null, 2, null, vi_task_id);
-- 成功返回
return;
exception
when others then
-- 得到出错信息
vs_message := substr(sqlerrm, 1, 200);
-- 回滚事务
rollback;
-- 程序出错日志
db_app.ps_log(null, null, null, 3, vs_message, vi_task_id);
-- 出错返回
return;
end;
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
SQL SERVER中递归
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
这是目前我见过最好的跨域解决方案！
减少回流与重绘
减少回流与重绘
如何使用KrpanoToolJS在浏览器切图
performance.now() 与 Date.now() 对比
一款纯 JS 实现的轻量化图片编辑器
关于开发 VS Code 插件遇到的 workbench.scm.
前端设计模式——观察者模式
前端设计模式——中介者模式
创建型-原型模式