By Sriram Sanka

  • $riram $anka


    The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.

  • Likes So far

    • 89,410 hits
  • Enter your email address to follow this blog and receive notifications of new posts by email.

Handy Package DBMS_UTILITY

Posted by Sriram Sanka on December 10, 2010


Really Its  great handy package for Oracle People!

We can use this in so many different scenarios.

SQL> DECLARE
2 parnam VARCHAR2(256);
3 intval BINARY_INTEGER;
4 strval VARCHAR2(256);
5 partyp BINARY_INTEGER;
6 BEGIN
7 partyp := dbms_utility.get_parameter_value('max_dump_file_size',
8 intval, strval);
9 dbms_output.put('parameter value is: ');
10 IF partyp = 1 THEN
11 dbms_output.put_line(strval);
12 ELSE
13 dbms_output.put_line(intval);
14 END IF;
15 IF partyp = 1 THEN
16 dbms_output.put('parameter value length is: ');
17 dbms_output.put_line(intval);
18 END IF;
19 dbms_output.put('parameter type is: ');
20 IF partyp = 1 THEN
21 dbms_output.put_line('string');
22 ELSE
23 dbms_output.put_line('integer');
24 END IF;
25 END;
26 /
parameter value is: UNLIMITED
parameter value length is: 9
parameter type is: string

PL/SQL procedure successfully completed.

SQL> declare
2 ver varchar2(100);
3 com varchar2(100);
4 begin
5 dbms_utility.db_version(ver,com);
6 dbms_output.put_line(ver||'-'||com);
7 end;
8 /
10.2.0.1.0-10.2.0.1.0

PL/SQL procedure successfully completed.
SQL> declare
2 id number;
3 str varchar2(40);
4 begin
5 id:=dbms_utility.get_parameter_value('compatible',id,str);
6 dbms_output.put_line(str);
7 end;
8 /
10.2.0.1.0

PL/SQL procedure successfully completed.

SQL> create or replace procedure sriram
2 as
3 begin
4 NULL;
5 end;
6 /

Procedure created.

SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';

OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 VALID SRIRAM

SQL> exec dbms_utility.invalidate(52565);

PL/SQL procedure successfully completed.

SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';

OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 INVALID SRIRAM

SQL> exec dbms_utility.validate(52565);

PL/SQL procedure successfully completed.

SQL> select object_id,status,object_name from user_objects
2 where lower(object_name)='sriram';

OBJECT_ID STATUS OBJECT_NAME
---------- ------- ----------------------------------------------
52565 VALID SRIRAM

SQL>

We can use This package in different situations like to compile schema objects analtze database etc…..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
Thinking Out Loud

Michael T. Dinh, Oracle DBA

Notes On Oracle

by Mehmet Eser

Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Know-Oracle

Oracle Cloud ERP Blog by Team CLT

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Anand's Data Stories

Learn. Share. Repeat.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

DBAtricksWorld.com

Sharing Knowledge is ultimate key to Gaining knowledge...

%d bloggers like this: