How to use dbms_utility package to analyze Oracle partitions
analyze_part_object: procedure is used to analyze partitions in a table or index. If a table or index has more than one partition then the procedure will start a separate job queue for each partition which will run in parallel. This is a procedure in Oracle built-in DBMS_UTILITY package.
Procedure signatures:
procedure 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’);
Parameters:
schema – The name of the schema which owns the object (table/index).
object_name – name of the object (the name of table or index)
object_type – Type of the object. Possible values T (for table) and I (for index). Defaukt is T.
command_type – Type of analyze to happen. C(ompute statistics), E(stimate statistics), D(elete statistics) and V(alidate structure). Default is E.
command_opt – Possible values FOR TABLE, FOR ALL LOCAL INDEXES, FOR ALL COLUMNS
sample_clause – specifies the amout of data used to estimate. Only applicable when command_type ‘E’ is selected.
Example:
This call will analyze the partitions of EMP table in SCOTT schema taking 10% as sample data.
exec dbms_utility.analyze_part_object(‘SCOTT’, ‘EMP’, ‘T’,'E’,'FOR TABLE’, ‘sample 10 percent’);
Learn more on dbms_utility in action:
How to get database version info using Oracle dbms_utility package
db_version: returns as OUT parameter the current database version and the version of the database with which this current version is compatible with. This is a procedure in Oracle built-in DBMS_UTILITY package.
Procedure signature:
procedure db_version(version out varchar2,
compatibility out varchar2);
Example:
The anonymous pl/sql script below will return the database version and compatibility version.
declare
l_ver varchar2(100);
l_comp varchar2(100);
begin
dbms_utility.db_version(l_ver, l_comp);
dbms_output.put_line(‘database version=’||l_ver);
dbms_output.put_line(‘compatibility=’||l_comp);
end;
You can also query v$version table to get the version of your database server, pl/sql release, TNS version and NLSRTL version as
select * from v$version;
Learn more on dbms_utility in action:
How to get operating system version and port of the operating system using Oracle dbms_utility package
port_string: returns the operating system version and port of the operating system (i.e ALPHA/VMS-8.0.0-64). This is a function in Oracle built-in DBMS_UTILITY package.
Function signature:
function port_string return varchar2;
Example:
The example below returns the operating system version and port of the operatign system.
DECLARE
l_portstr varchar2(100);
BEGIN
l_portstr := dbms_utility.port_string;
dbms_output.put_line(‘l_parm_type=’||l_portstr);
END;
Learn more on dbms_utility in action:
How to get parameter value and parameter type of a given parameter using dbms_utility
get_parameter_value: functions returns the init.ora parameter type and parameter value of a given parameter. This is a function in Oracle built-in DBMS_UTILITY package.
Function signature:
function get_parameter_value(parnam in varchar2,
intval in out binary_integer,
strval in out varchar2)
return binary_integer;
Parameters:
Parnam – Name of the parameter
Intval – returns integer value
Strval - returns string value
Later version syntax (Oracle 11g):
function get_parameter_value(parnam in varchar2,
intval in out binary_integer,
strval in out varchar2,
listno in binary_integer default 1)
return binary_integer;
New parameter
listno – lists the item number. This is useful when a parameter name can be used multiple times in init.ora file.
For example utl_file_dir can be used multiple times to define different directories to be used by UTL_FILE
Example:
This function anonymous pl/sql scripts returns the value and type of the value of init.ora parameter “db_block_size”
DECLARE
l_int BINARY_INTEGER;
l_str VARCHAR2(256);
l_parm_type BINARY_INTEGER;
BEGIN
l_parm_type := dbms_utility.get_parameter_value(‘db_block_size’,l_int, l_str);
IF l_parm_type = 1 THEN
dbms_output.put(‘parameter value = ‘||l_str);
ELSE
dbms_output.put(‘parameter value = ‘||l_int);
END IF;
IF l_parm_type = 1 THEN
dbms_output.put(‘ and parameter type is character string ‘);
ELSE
dbms_output.put_line(‘ and parameter type is numeric’);
END IF;
END;
This function anonymous pl/sql scripts returns the value and type of the value of init.ora parameter “utl_file_dir”
DECLARE
l_int BINARY_INTEGER;
l_str VARCHAR2(256);
l_parm_type BINARY_INTEGER;
BEGIN
l_parm_type := dbms_utility.get_parameter_value(‘background_dump_dest’,l_int, l_str);
IF l_parm_type = 1 THEN
dbms_output.put(‘parameter value = ‘||l_str);
ELSE
dbms_output.put(‘parameter value = ‘||l_int);
END IF;
IF l_parm_type = 1 THEN
dbms_output.put_line(‘ and parameter type is string’);
ELSE
dbms_output.put_line(‘ and parameter type is integer’);
END IF;
END;
Learn more on dbms_utility in action:
How to get current time using dbms_utility package
get_time: function returns the current time in 100th’ of a second. This is a function in Oracle Built-in DBMS_UTILITY package.
Function signature:
function get_time return number;
Example:
declare
l_num number;
begin
l_num := dbms_utility.get_time;
dbms_output.put_line(l_num);
end;
Learn more on dbms_utility in action:
Recent Comments