import oracle 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

Built-in Data Types

-- array of anydata

TYPE anydata_array IS TABLE OF ANYDATA

INDEX BY BINARY_INTEGER;

-- Lists of database links

TYPE dblink_array IS TABLE OF VARCHAR2(128)

INDEX BY BINARY_INTEGER;

-- Order in which objects should be generated.

TYPE index_table_type IS TABLE OF BINARY_INTEGER

INDEX BY BINARY_INTEGER;

-- List of active instance numbers and instance names

-- Starting index of instance_table is 1;

TYPE instance_record IS RECORD (

inst_number NUMBER,

inst_name   VARCHAR2(60));

-- Instance_table is dense.

TYPE instance_table IS TABLE OF instance_record

INDEX BY BINARY_INTEGER;

-- Lists of Long NAME: includes

-- fully qualified attribute names.

TYPE lname_array IS TABLE OF VARCHAR2(4000)

INDEX BY BINARY_INTEGER;

-- Lists of large VARCHAR2s should be stored here

TYPE maxname_array IS TABLE OF VARCHAR2(32767)

INDEX BY BINARY_INTEGER;

-- Lists of NAME

TYPE name_array IS TABLE OF VARCHAR2(30) INDEX BYBINARY_INTEGER;

-- The order in which objects should be

-- generated is returned here for users

TYPE number_array IS TABLE OF NUMBER

INDEX BY BINARY_INTEGER;

-- Lists of "USER"."NAME"."COLUMN"@LINK

TYPE uncl_array IS TABLE OF VARCHAR2(227)

INDEX BY BINARY_INTEGER;

SUBTYPE maxraw IS RAW(32767);

Constants

Name

Data Type

Value

inv_error_on_restrictions

1

Dependencies

SELECT name

FROM dba_dependencies

WHERE referenced_name = 'DBMS_UTILITY'

UNION

SELECT referenced_name

FROM dba_dependencies

WHERE name = 'DBMS_UTILITY';

Exceptions

Error Code

Description

Insufficient privileges for some object in this schema

Cannot recompile SYS objects

inv_not_exist_or_no_priv

inv_malformed_settings

inv_restricted_object

hash size is 0

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;

/

ANALYZE_DATABASE

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

Deprecated in 10g

dbms_utility.analyze_database (

method           IN VARCHAR2,

estimate_rows    IN NUMBER DEFAULT NULL, -- # of rows to est.

estimate_percent IN NUMBER DEFAULT NULL, -- % of rows for est.

method_opt       IN VARCHAR2 DEFAULT NULL);

-- method options:

ESTIMATE, COMPUTE and DELETE

-- method_opt options:

FOR TABLE

FOR ALL [INDEXED] COLUMNS] [SIZE n]

FOR ALL INDEXES

-- requires grant of ANALYZE ANY

exec dbms_utility.analyze_database('ESTIMATE', 100, NULL, 'FOR TABLE');

ANALYZE_PART_OBJECT

Equivalent to analyze table or index for partitioned objects

dbms_utility.analyze_part_object (

schema        IN VARCHAR2 DEFAULT NULL,

object_name   IN VARCHAR2 DEFAULT NULL,

object_type   IN CHAR DEFAULT 'T',

command_type  IN CHAR DEFAULT 'E',

command_opt   IN VARCHAR2 DEFAULT NULL,

sample_clause IN VARCHAR2 DEFAULT 'sample 5 percent');

-- command type options

C (compute statistics)

D (delete statistics)

E (estimate statistics)

V (validate structure)

-- assumes a partitioned table named 'part_tab'

dbms_utility.analyze_part_object('UWCLASS', 'PART_TAB', 'T', 'E', 'V');

ANALYZE_SCHEMA

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

Deprecated in 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

D