import oracle utility_Oracle DBMS_UTILITY 用法例子

越来越觉得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