Oracle DBMS_DBMS_UTILITY
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Container for a group of unrelated utility procedures and functions
AUTHID DEFINER
Constants
Name Data Type Value
inv_error_on_restrictions PLS_INTEGER 1
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 BY BINARY_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);
Dependencies (254 Objects) SELECT name FROM dba_dependencies WHERe referenced_name = 'DBMS_UTILITY'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_UTILITY';
Documented Yes
Exceptions
Error Code Reason
ORA-00900 Input is not valid
ORA-00942 Current user does not have select privs on all the views and tables recursively referenced in the input SQL
ORA-20000 Insufficient privileges for some object in this schema
ORA-20001 Cannot recompile SYS objects
ORA-24237 inv_not_exist_or_no_priv
ORA-24238 inv_malformed_settings
ORA-24239 inv_restricted_object
ORA-24251 Raised if the input_sql text is not a select statement
ORA-29261 hash size is 0
ORA-29477 Input LOB size exceeds the maximum size of 4GB -1
First Available 7.3.4
Security Model Owned by SYS EXECUTE is granted to PUBLIC, WMSYS, ORDSYS, and APEX_040200
Source {ORACLE_HOME}/rdbms/admin/dbmsutil.sql
Subprograms
 
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'
exec 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
DELETE
ESTIMATE
exec dbms_utility.analyze_schema('UWCLASS','ESTIMATE', NULL, 10);
 
CANONICALIZE
Canonicalizes a given string dbms_utility.canonicalize(
name       IN  VARCHAR2,
canon_name OUT VARCHAR2,
canon_len  IN  BINARY_INTEGER);
set serveroutput on

DECLARE
 cname user_tables.table_name%TYPE;
BEGIN
  dbms_utility.canonicalize('uwclass.test', cname, 16);
  dbms_output.put_line(cname);
END;
/
 
COMMA_TO_TABLE
Parses a comma delimited string

Overload 1
dbms_utility.comma_to_table(
list   IN  VARCHAR2,
tablen OUT BINARY_INTEGER,
tab    OUT UNCL_ARRAY);
conn uwclass/uwclass@pdbdev

CREATE TABLE c2t_test (
readline VARCHAR2(200));

INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"');
INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"');
INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"');
INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"');
INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"');
COMMIT;

SELECT * FROM c2t_test;

CREATE TABLE test_import