oracle study（17）
select name from v$db_object_cache where locks & 0 and pins & 0 and type='PROCEDURE';
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：154858次
积分：2548
积分：2548
排名：第9960名
原创：90篇
转载：98篇
评论：16条
(1)(2)(2)(1)(1)(6)(2)(8)(1)(7)(1)(3)(3)(15)(3)(16)(7)(3)(1)(8)(11)(7)(1)(1)(2)(2)(6)(1)(5)(3)(2)(17)(5)(4)(1)(5)(14)(7)(4)&&&&存储过程的安全及性能优化
存储过程的安全及性能优化
存储过程的安全及性能优化
存储过程分类
? 系统存储过程
? 自定义存储过程
? SQL Server使用者编写的存储过程
? 扩展存储过程
? 动态链接库(DLL)函数的调用看，主要用于客户端和服务器端之间进行通信
? exec master..xp_cmdshell 'dir *.exe' -- 执行目录命令查询[sql2005\sql2008]
? exec master..xp_fixeddrives --列出硬盘分区各自可用空间
? xp_regwrite根键，子键，值名，值类型，值【sql2008拒绝访问】
? 写入注册表，例如：
? exec master..db.xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\run','TestValueName','reg_sz','hello'
? xp_regdeletevalue 根键，子键，值名【sql2008拒绝访问】
? 删除注册表某个值
? xp_regdeletekey键，值【sql2008拒绝访问】
? 删除该键下包括的所有值
xp_cmdshell语法
? xp_cmdshell {'command_string'} [,no_output]
? command_string是在操作系统命令行解释器上执行的命令字符串。command_string数据类型为varchar(255)或者nvarchar(4000),没有默认值
? no_output为可选参数，可以控制是否想客户端返回信息
? 该存储过程一般情况下被禁用的，需要手动开启使用，如下：
? exec sp_configure 'show advanced options',1
? reconfigure
? exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用
? reconfigure
删除xp_cmdshell
SQL SERVER200删除xp_cmdshell
use master
exec sp_dropextendedproc 'xp_cmdshell'
SQL SERVER2005以上禁用xp_cmdshell,但不能删除掉
exec sp_configure 'xp_cmdshell',0 —1表示启用，0表示禁用
reconfigure --让sp_configurre立即生效
? exec sp_configure 'show advanced options',0
reconfigure
--注意：SQL SERVER2008考虑安全性很多存储过程直接被拒绝访问
恢复/启用扩展存储过程
SQLServer2000
use master
exec sp_addextendedproc xp_cmdshell,'xplog70.dll'
SQL Server2005或SQL Server2008启用xp_cmdshell
? exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'xp_cmdshell',1—1表示启用，0表示禁用
reconfigure
扩展存储过程的定义
? 扩展存储过程是SQL Server中的另一类存储过程，它是以其它语言编写的外部程序，是以动态链接库（DLL）形式存储在服务器上，最终SQLServer就可以动态加载并执行它们
? 编写好后使用SQLServer的固定角色sysadmin注册该扩展存储过程,并将执行权限授予其它用户,这个扩展存储过程只能添加到master数据库。
? 在编写扩展存储过程中可能要用到某些系统存储过程，这些系统存储过程如下：
利用OLE自动化存储过程调用dll
1.创建类库程序集
namespace PB_ExtendProcedure
public class ExtendProcedure
public string SayHi()
return &hello world&;
2.生成动态链接库并注册到系统中
2.1.生成动态链接库使用VS2010命令行工具
使用sn命令生成一个强命名文件：
sn -k helpkey.snk
使用csc生成dll
csc /t:library /keyfile:helperkey.snk ExtendProcedure.cs
向系统注册这个dll
regasm /tlb:ExtendProcedure.tlb ExtendProcedure.dll /codebase
2.2.在SQL Server中编写扩展存储过程
--sp_OACreate
--sp_OAMethod
--sp_OADestroy
--sp_OAGetErrorInfo
DECLARE @object int --返回创建的对象
DECLARE @hr int
--过程返回值
DECLARE @return varchar(255) --dll方法的返回值
DECLARE @src varchar(255),@desc varchar(255) ---过程的错误原因、描述
--1.创建对象
EXEC @hr = sp_OACreate 'PB_ExtendProcedure.ExtendProcedure',@object out
EXEC sp_OAGetErrorInfo @object,@src,@desc out
select hr = convert(varchar(4),@hr),Source=@src,Description=@desc
--2.调用方法
EXEC @hr = sp_OAMethod @object,'SayHi',@return out
EXEC sp_OAGetErrorInfo @object,@src,@desc out
select hr = convert(varchar(4),@hr),Source=@src,Description=@desc
print @return
--3.销毁对象实例
EXEC @hr = sp_OADestroy @object
EXEC sp_OAGetErrorInfo @object,@src,@desc out
select hr = convert(varchar(4),@hr),Source=@src,Description=@desc
注意：默认情况sqlserver2008是禁止调用ole自动化存储过程的，解决方法如下：
sp_configure 'show advanced options',1
reconfigure
sp_configure 'ole automation procedures',1
reconfiugre
加密存储过程
创建加密存储过程语法
CREATE PROCEDURE &NAME& WITH ENCRYPTION AS
&SQL_STATEMENT&
存储过程执行机制
语法阶段：指创建存储过程时，系统检查其创建语句语法正确性的过程。若语法检查通过则系统将该存储过程存储在当前数据库的sys.sql_modules目录视图当中
解析阶段：指某个存储过程首次执行时，查询处理器从sys.sql_modules目录视图中读取该存储过程的文本并且检查该过程引用的对象是否存在的过程。
编译阶段：指分析存储过程和生成存储过程执行计划的过程。执行计划是来描述存储过程执行过程的。查询优化器是在分析完存储过程之后将生成的执行计划存储在存储过程高速缓冲存储器中，此后每次调用已经创建的存储过程时将直接执行不再需要编译，这样就可以提高程序的运行性能。
执行阶段：指执行驻留在存储过程高速缓冲存储区中的存储过程执行计划的过程。
了解SQL Server Profiler
SQL Server Profiler是图形化实时监视工具
能帮助系统管理员监视数据库和服务器行为，比如死锁数量、致命错误
跟踪T-SQL语句和存储过程
通常使用Profiler监视重要事件：
登录连接的失败
成功或断开连接
delete、insert、update命令
存储过程开始或结束
存储过程中的每一条语句
写入sql server错误日志的错误
向数据对象添加或释放锁
Profiler事件
SQL Server Profiler里Standard模板的事件类
数据库引擎优化顾问介绍
引擎优化顾问提供数据库系统的优化建议，经过管理员修改达到优化目的
引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现
引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销
数据库引擎优化顾问介绍
引擎优化顾问提供数据库系统的优化建议，经过管理员修改达到优化目的
引擎优化顾问分析数据库的工作负荷【Sql Server Profiler的跟踪文件或者表】和物理实现
引擎优化顾问提出合理的物理设计结构以降低工作负荷的开销
提高存储过程性能方法
存储过程里面包含很多语句，我们真正要提高性能的是存储过程里面这些语句
? 默认情况存储过程会返回每条语句执行后的影响行数，如果不需要这些信息的话，可以使用SET NOCOUNT ON来终止这种返回行为，这样可以避免客户端和服务器之间多次进行信息传递的过程。
? 减少可选参数：频繁使用可选参数是很容易降低性能的。
? 优化SQL语句
? 避免频繁访问同一张或多张表，尤其是数据量大的表
? 尽量避免大事务操作
? 尽量避免使用游标，游标效率很差，如果使用游标就不要在游标的循环中使用表连接操作
? 注意where语句的写法，应该根据索引的顺序、范围的顺序、范围的大小来确定条件子句的前后顺序，尽量让字段顺序与索引顺序保持一致
? 尽量使用exists代替select count()判断是否存在记录
? 注意表之间连接的数据类型
? 先写DDL,再写DML：
当DML先于DDL执行的时候，SQL Server会重新编译存储过程，因为DML引用了DDL中的一些内容,也就是所谓的一些表。这个时候SqlServer需要统计由DDL定义的一些对象的变化。以此来创建DML的执行计划，如果将DDL放在前面那么这种编译只需要以此就可以了。
? 合理使用索引：
根据实际查询需求来创建索引
尽量使用索引字段做查询条件
尽量避免对大数据量表进行全表扫描，可考虑新建索引
? 合理使用tempdb系统表
尽量避免使用distinct、order by、group by、having、join语句--这些语句加重tempdb的负担
? 避免频繁创建和删除临时表
? 临时表中插入数据过大，可使用select into 代替 create table
? 使用了临时表，要在存储过程最后显式删除
? 避免使用大临时表与其他大数据量表的连接查询和修改
嵌到我的页面
<input type="text" readonly="true" value="">
若举报审核通过，可奖励20下载分
被举报人：
jiangtongcn
举报的资源分：
请选择类型
资源无法下载
资源无法使用
标题与实际内容不符
含有危害国家安全内容
含有反动色情等内容
含广告内容
版权问题，侵犯个人或公司的版权
*详细原因：
VIP下载&&免积分60元/年（1200次）
您可能还需要
数据库下载排行17:28 提问
plsql查看存储过程目录报错ora-03114 未连接到数据库（oracle11G）
只要一点击左边的小箭头就报错，而且无法查看里面的内容，但是打开一个sql窗口却可以查询数据，并不是真的连接不上数据库，还请各位大神帮忙
按赞数排序
Ora-03114:未连接数据库
现象： 在使用pl/sql developer编译时总是报 Ora-03114:未连接数据库。
解决方法：
去掉cdc功能。
故障原因：PLSQLDev.exe程序和Oracle数据库的CDC功能产生冲突；使用TOAD，OEM等其他客户端工具则不会有以上情况。
DROP TRIGGER sys.cdc_alter_ctable_
DROP TRIGGER sys.cdc_create_ctable_
DROP TRIGGER sys.cdc_create_ctable_
DROP TRIGGER sys.cdc_drop_ctable_
做以上操作就可以编译了，如果还是不可以编译，就再执行如下的语句
CALL sys.dbms_java.dropjava('-s rdbms/jlib/CDC.jar');
Oracle CDC简介
CDC(Change Data Capture)是oracle在数据库级别实现的增量抽取解决方案。在一般的ETL过程中，对于增量抽取，无非是在数据上加时间截，全记录比对，关键字段比对，日志分析抽取等几种方法，要么需要修改原表结构，要么需要大量的算法，要么借助第三方的工具实现。Oracle从9i开始引入的CDC特性，使得有机会在数据库层面上直接实现增量抽取功能，在性能方面由于和数据库引擎的直接集成，比第三方工具应该具有一定的优势。
CDC有两个模式：同步和异步。两种模式的实现机制是截然不同的。同步CDC主要是采用触发器记录新增数据，基本能够做到实时增量抽取。而异步CDC则是通过分析已经commit的日志记录来得到增量数据信息，有一定的时间延迟，并且提供了到Oracle Streams的接口。同步CDC在企业版或者标准版中都可以使用，异步CDC则只包含在企业版中。注意CDC在9i和10g中有了比较大的改变，异步CDC主要采用了和Streams相同的技术。
CDC中将系统分为两个角色：发布者和订阅者。发布者主要负责捕获增量数据，订阅者则将增量数据传递给实际应用。这些任务都可以通过oracle提供的PL/SQL包实现。
官方文档说明：
ORA-03114 not connected to ORACLE
A call to Oracle was attempted when no connection was established. Usually this happens
because a user-written program has not logged on. It may happen if communication trouble causes
a disconnection. In addition, this message could occur when ALTER SYSTEM KILL SESSION or ALTER
SYSTEM DISCONNECT SESSION were issued with the IMMEDIATE qualifier because, in those cases,
　　ora-03114:未连接数据库the client's connection to the database is terminated without waiting for the client to issue a request.
　　ora-03114:未连接数据库Action: Try again. If the message recurs and the program is user written, check the program.
----end----
如果回答对您有帮助,请采纳
楼上的方法都试过了，不好用，谢谢。
楼上的方法都试过了，不好用，谢谢。
17关注|812收录
784关注|663收录
1907关注|145收录
其他相似问题
相关参考资料使用U8执行某个功能，如何查看执行了哪些SQL语句，或者存储过程（在线等）
[问题点数：40分，结帖人u]
使用U8执行某个功能，如何查看执行了哪些SQL语句，或者存储过程（在线等）
[问题点数：40分，结帖人u]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2011年 总版技术专家分年内排行榜第四2010年 总版技术专家分年内排行榜第九2009年 总版技术专家分年内排行榜第八
2011年10月 总版技术专家分月排行榜第一
匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。怎样判断存储过程中的变量的值类型，并判断是否为空
[问题点数：40分，结帖人leewoo999]
怎样判断存储过程中的变量的值类型，并判断是否为空
[问题点数：40分，结帖人leewoo999]
不显示删除回复
显示所有回复
显示星级回复
显示得分回复
只显示楼主
2008年 总版技术专家分年内排行榜第二
2010年 总版技术专家分年内排行榜第四2009年 总版技术专家分年内排行榜第五2007年 总版技术专家分年内排行榜第六
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年5月 总版技术专家分月排行榜第二2010年4月 总版技术专家分月排行榜第二
2011年7月 荣获微软MVP称号2010年7月 荣获微软MVP称号
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年5月 总版技术专家分月排行榜第二2010年4月 总版技术专家分月排行榜第二
2011年7月 荣获微软MVP称号2010年7月 荣获微软MVP称号
2010年5月 总版技术专家分月排行榜第二2010年4月 总版技术专家分月排行榜第二
2011年7月 荣获微软MVP称号2010年7月 荣获微软MVP称号
2010年7月 总版技术专家分月排行榜第二2010年6月 总版技术专家分月排行榜第二
2010年5月 总版技术专家分月排行榜第三
2010年5月 总版技术专家分月排行榜第二2010年4月 总版技术专家分月排行榜第二
2011年7月 荣获微软MVP称号2010年7月 荣获微软MVP称号
本帖子已过去太久远了，不再提供回复功能。