How to use database server mode using dbms_utility package
is_parallel_server functions returns Boolean value to indicate whether database is running in parallel server mode or not. Returns true if it is running in parallel server mode else returns false. This function is not available in later version of Oracle database.
Function signature:
function is_parallel_server return boolean;
Example:
This anonymous pl/sql script will display message to tell you whether Oracle database server is running in PARALLEL SERVER mode or not.
set serverout on
declare
begin
if dbms_utility.is_parallel_server then
dbms_output.put_line(‘Your database is running in Parallel server mode’);
else
dbms_output.put_line(‘Your database is NOT running in Parallel server mode’);
end if;
end;
Learn more on dbms_utility in action:
How to use dbms_utility.analyze_database to analyze Oracle database
analyze_database: is used to analyze an Oracle database. This is a procedure in Oracle built-in DBMS_UTILITY package.
Procedure signature:
procedure analyze_database(method varchar2,
estimate_rows number default null,
estimate_percent number default null,
method_opt varchar2 default null)
Parameters:
method – Possible values COMPUTE, ESTIMATE and DELETE
estimate_rows – number of rows to estimate. Needed when method=ESTIMATE
estimate_percent – percentage of rows to estimate. Needed when method=ESTIMATE and estimate_rows is not specified.
method_opt – possible values, FOR TABLE, FOR ALL INDEXS and FOR ALL [INDEXED] COLUMNS] [SIZE n]
Example:
To analyze the everything on an Oracle database
Login as sys or system user or any user who has ANALYZE ANY privilege.
Then run any of the commands below as required.
exec dbms_utility.analyze_database(‘ESTIMATE’, 100, NULL, ‘FOR TABLE’);
exec dbms_utility.analyze_database(‘ESTIMATE’, NULL, 100, ‘FOR TABLE’);
exec dbms_utility.analyze_database(‘ESTIMATE’, NULL, 100, ‘FOR ALL INDEXS’);
exec dbms_utility.analyze_database(‘ESTIMATE’, 100, NULL, ‘FOR ALL INDEXS’);
Learn more on dbms_utility in action:
How to use dbms_utility.analyze_schema to Analyze all objects in a schema
analyze_schema is used to analyze all the tables, clusters and indexes in a given schema. This is a procedure in Oracle built-in DBMS_UTILITY package.
Precedure signature:
procedure analyze_schema(schema varchar2,
method varchar2,
estimate_rows number default null,
estimate_percent number default null,
method_opt varchar2 default null)
Parameters:
schema – The name of the schema which you want to analize
method – Possible values COMPUTE, ESTIMATE and DELETE
estimate_rows – number of rows to estimate. Needed when method=ESTIMATE
estimate_percent – percentage of rows to estimate. Needed when method=ESTIMATE and estimate_rows is not specified.
method_opt – possible values, FOR TABLE, FOR ALL INDEXS and FOR ALL [INDEXED] COLUMNS] [SIZE n]
Here are some examples:
Login as sys or system user or any user who has ANALYZE ANY privilege.
Then run any of the commands below as required.
exec dbms_utility.analyze_schema(‘SCOTT’,'ESTIMATE’, 70, NULL, ‘FOR TABLE’);
exec dbms_utility.analyze_schema(‘SCOTT’,'ESTIMATE’, 70, NULL, ‘FOR TABLE’);
exec dbms_utility.analyze_schema(‘SCOTT’,'ESTIMATE’, NULL, 50, ‘FOR TABLE’);
exec dbms_utility.analyze_schema(‘SCOTT’,'ESTIMATE’, 50, NULL, ‘FOR ALL INDEXS’);
exec dbms_utility.analyze_schema(‘SCOTT’,'ESTIMATE’, NULL, 50, ‘FOR ALL INDEXS’);
Learn more on dbms_utility in action:
How to compile all Oracle schema objects using dbms_utility.complie_schema
compile_schema is used to compile a whole schema which means you can use the procedure to re-compile procedures, functions, packages and triggers. This procedure in Oracle built-in called DBMS_UTILITY package.
The package should be run with caution as this will take a long time to execute – depending on the size and number of objects in your schema.
Precedure signature (Oracle 8x):
procedure compile_schema(schema varchar2);
Paramemters:
schema – The name of schema that you want to compile.
Oracle 11g Syntax:
procedure compile_schema(schema varchar2,
compile_all boolean default TRUE,
reuse_settings boolean default FALSE);
Paramemters:
schema – The name of schema that you want to compile.
compile_all – a boolean flag to indicate if all schema objects should be compiled even if they are INVALID
reuse_settings – a boolean flag to indicate if the sessions settings should be reused
Example:
To compile objects in SCOTT schema
dbms_utility.compile_schema(‘SCOTT’);
dbms_utility.compile_schema(‘SCOTT’,TRUE, TRUE);
Learn more on dbms_utility in action:
How to execute ddl statement in Oracle 8x using dbms_utility.exec_ddl_statement
exec_ddl_statement is to execute DDL statements.
This is a procedure in Oracle built-in called dbms_utility. EXECUTE_IMMEDIATE is another command that you can use to execute DDL statements in an Oracle database from PL/SQL.
Precedure signature:
procedure exec_ddl_statement(parse_string in varchar2);
The command is passed as a variable which is then executed by the package.
The example below shows – using dbms_utility package how to create a table and an index on that table from an anonymous pl/sql block:
Example:
declare
l_cnt number;
begin
– create a table called emp
dbms_utility.exec_ddl_statement(‘create table emp ( empno number, ename varchar2(10), age number)’);
– create index if the table is there
dbms_utility.exec_ddl_statement(‘create index emp_ind1 on emp (empno)’);
end;
Notice that there is no semi colon after the the command – i.e. create table emp ( empno number, ename varchar2(10), age number) .It is because dbms_utility package will automatically put a semicolon for you while it runs this command. If you put a semicolon then it will not work.
Learn more on dbms_utility in action:
Recent Comments