Oracle DBMS_UTILITY 用法例子_小鹏ORACLE

http://blog.sina.com.cn/u/1835947255

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

Oracle DBMS_UTILITY 用法例子

(2012-03-09 09:37:19)

标签：

oracle

dbms

utility

杂谈

分类： oracle实验笔记

越来越觉得dbms_utility功能强了，在找它的使用方法时候看到这篇文章，于是先转过来，

再根据一步一步是实验，写出解释方便以后使用

General Information
Purpose	Container for a group of unrelated utility procedures and functions
First Availability	7.3.4
Source	{ORACLE_HOME}/rdbms/admin/dbmsutil.sql

几个常用的过程或方法：

ACTIVE_INSTANCES

Identify active instances in a cluster

识别在集群中活动的实例

dbms_utility.active_instances (
instance_table OUT INSTANCE_TABLE,
instance_count OUT NUMBER);

set serveroutput on

DECLARE
inst_tab dbms_utility.instance_table;
inst_cnt NUMBER;
BEGIN
IF dbms_utility.is_cluster_database THEN
dbms_utility.active_instances(inst_tab, inst_cnt);
dbms_output.put_line('-' || inst_tab.FIRST);
dbms_output.put_line(TO_CHAR(inst_cnt));
ELSE
dbms_output.put_line('Not A Clustered Database');
END IF;
END;
/

Not A Clustered Database

ANALYZE_SCHEMA

Analyzes all the tables, clusters, and indexes in a schema

Deprecated in 10g

10g弃用、不推荐

dbms_utility.analyze_schema (
schema IN VARCHAR2,
method IN VARCHAR2,
estimate_rows IN NUMBER DEFAULT NULL,
estimate_percent IN NUMBER DEFAULT NULL,
method_opt IN VARCHAR2 DEFAULT NULL);

-- method options
COMPUTE
DELETE
ESTIMATE

dbms_utility.analyze_schema('UWCLASS','ESTIMATE', NULL, 10)

COMPILE_SCHEMA

Compiles all procedures, functions, packages, and triggers in the specified schema

编译指定schema的所有的过程、函数、包、触发器

dbms_utility.compile_schema(
schema IN VARCHAR2,
compile_all IN BOOLEAN DEFAULT TRUE,
reuse_settings IN BOOLEAN DEFAULT FALSE);

exec dbms_utility.compile_schema('UWCLASS');

EXEC_DDL_STMNT

Execute A DDL Statement

执行ddl语句

dbms_utility.exec_ddl_statement(parse_string IN VARCHAR2);

-- Create a stored procedure owned by a schema with the alter any user system privilege.

CREATE OR REPLACE PROCEDURE sp_alter_user ( a_user_name VARCHAR2,
a_user_password VARCHAR2, a_admin VARCHAR2 := 'N') IS
l_user VARCHAR2(255);
l_user_grants VARCHAR2(255);
l_user_default_role VARCHAR2(255);
BEGIN
l_user := 'alter user ' || a_user_name ||
' identified by ' || a_user_password;

-- If they need roles granted
l_user_grants := 'GRANT connect,resource TO ' || a_user_name;

l_user_default_role := 'alter user ' || a_user_name ||
' default role dba';

dbms_utility.exec_ddl_statement(l_user);
dbms_utility.exec_ddl_statement(l_user_grants);
dbms_utility.exec_ddl_statement(l_user_default_role);
END sp_alter_user;
/

CREATE OR REPLACE PROCEDURE sp_create_user (a_user_name VARCHAR2,
a_user_password VARCHAR2, a_admin VARCHAR2 := 'N') IS
l_user VARCHAR2(255);
BEGIN
l_user := 'create user ' || a_user_name ||
' identified by ' || a_user_password ||
' temporary tablespace temp';

dbms_utility.exec_ddl_statement(l_user);

sp_alter_user(a_user_name, a_user_password, a_admin);
END sp_create_user;
/

GET_DEPENDENCY

Shows the dependencies on the object passed in

显示依赖这个对象的对象

dbms_utility.get_dependency(
type IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2);

CREATE TABLE testtab (
testcol VARCHAR2(20));

CREATE VIEW testview AS
SELECT * FROM testtab;

CREATE TRIGGER testtrig
BEFORE INSERT
ON testtab
BEGIN
NULL;
END testtrig;
/

CREATE OR REPLACE PROCEDURE testproc IS
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM testtab;

dbms_output.put_line(TO_CHAR(i));
END testproc;
/

set serveroutput on

exec dbms_utility.get_dependency('TABLE', 'PW', 'TESTTAB');

DEPENDENCIES ON PW.TESTTAB
---------------------------------------------------------------
*TABLE PW.TESTTAB()
*   VIEW PW.TESTVIEW()
*   TRIGGER PW.TESTTRIG()
*   PROCEDURE PW.TESTPROC()

PL/SQL procedure successfully completed

Executed in 0.063 seconds

PORT_STRING

Returns the operating system and the TWO TASK PROTOCOL version of the database

dbms_utility.port_string RETURN VARCHAR2;

SELECT dbms_utility.port_string
FROM dual;

PORT_STRING
------------------------------------------------------------
IBMPC/WIN_NT-8.1.0

Executed in 0.046 seconds

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：转载：亿级数据的高并发通用搜索引擎架构设计(转载)

后一篇：转载：谷歌搜索最重大调整：页面顶端显示问题答案

新浪BLOG意见反馈留言板　欢迎批评指正