Monday, September 26, 2011

What are invalid objects in the database?


Some types of schema objects reference other objects. For example, a view contains a query that references tables or other views, and a PL/SQL subprogram might invoke other subprograms and might use static SQL to reference tables or views. An object that references another object is called a dependent object, and an object being referenced is a referenced object. These references are established at compile time, and if the compiler cannot resolve them, the dependent object being compiled is marked invalid object.

An invalid dependent object must be recompiled against the new definition of a referenced object before the dependent object can be used. Recompilation occurs automatically when the invalid dependent object is referenced.

Does invalid objects affects performance?

Object invalidation affects applications in two ways. First, an invalid object must be revalidated before it can be used by an application. Re-validation adds latency to application execution. If the number of invalid objects is large, the added latency on the first execution can be significant. Second, invalidation of a procedure, function or package can cause exceptions in other sessions concurrently executing the procedure, function or package. If a patch is applied when the application is in use in a different session, the session executing the application notices that an object in use has been invalidated and raises one of the following 4 exceptions: ORA-4061, ORA-4064, ORA-4065 or ORA-4068.

DEMO:

Create one table, one invalid view and one valid view:

SQL> create table first_table ( col1 number, col2 number);
Table created.

SQL> create force view first_view as select col3 from first_table;
Warning: View created with compilation errors.

SQL> create view second_view as select col2 from first_table;
View created.

Check Status:

SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID

FIRST_VIEW VIEW INVALID

SECOND_VIEW VIEW VALID

SQL> alter table FIRST_TABLE add (col3 number);
Table altered.

RECOMPILING NOW,

SQL> alter view FIRST_VIEW compile;


SQL> select object_name, object_type,status from user_objects;

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID

FIRST_VIEW VIEW VALID


How to recompile large number of invalid objects?

There will be occasions when you are faced with the need to recompile hundreds or thousands of invalid objects. Typically, this occurs after an upgrade to an application, or perhaps after applying patches. Rather than recompiling them individually, use the supplied utility script. On Unix,

SQL> @?/rdbms/admin/utlrp.sql

How to recompile a pakage?

ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;

HTH:-)

9 comments:

sandeep said...

many thanks for steps. searched many websites but did not get any steps on how to create invalid object. your blog helped. thank you.

Nithya M said...

Hi, Thanks thanks thank you sooo muchhh... for this blog. It very much useful.

Anonymous said...

Thank you!!! Very clear and helpful.

Zheng junxai5 said...

zhengjx20160428
coach outlet online
gucci belts
michael kors purses
louis vuitton outlet
pandora charms
louis vuitton
ray ban sunglasses uk
true religion outlet
ray ban wayfarer
michael kors outlet clearance
louis vuitton handbags
jordan concords
oakley sunglasses
adidas running shoes
air force 1 trainers
timberlands
gucci handbgs
discount jordans
cheap basketball shoes
michael kors handbags
air jordan shoes
michael kors bags
christian louboutin sale
coach outlet
hollister clothing
ugg outlet
adidas factory outlet
hollister clothing
kate spade outlet
abercrombie outlet
tods sale
michael kors handbags
louis vuitton outlet
michael kors handbags
true religion jeans
coach outlet store
canada goose outlet
coach factory outlet online
jordan 3 retro

oakleyses said...

cheap oakley sunglasses, nike air max, jordan shoes, polo outlet, longchamp outlet, louis vuitton, ray ban sunglasses, tory burch outlet, longchamp pas cher, oakley sunglasses, replica watches, longchamp outlet, nike free run, louis vuitton outlet, nike air max, polo ralph lauren, christian louboutin outlet, nike outlet, oakley sunglasses, burberry pas cher, oakley sunglasses, tiffany jewelry, louis vuitton, prada outlet, replica watches, ray ban sunglasses, jordan pas cher, sac longchamp pas cher, chanel handbags, tiffany and co, ugg boots, oakley sunglasses wholesale, longchamp outlet, uggs on sale, polo ralph lauren outlet online, christian louboutin uk, michael kors pas cher, gucci handbags, christian louboutin, christian louboutin shoes, prada handbags, nike roshe, nike free, air max, kate spade outlet, ray ban sunglasses, ugg boots, louboutin pas cher

oakleyses said...

michael kors outlet, hollister pas cher, ralph lauren uk, michael kors, north face uk, coach outlet store online, true religion outlet, mulberry uk, abercrombie and fitch uk, guess pas cher, true religion outlet, lululemon canada, ray ban pas cher, michael kors, uggs outlet, vans pas cher, nike free uk, michael kors outlet, michael kors outlet online, replica handbags, nike tn, michael kors outlet, oakley pas cher, burberry outlet, polo lacoste, nike air max uk, nike blazer pas cher, hollister uk, michael kors outlet online, sac hermes, uggs outlet, michael kors outlet online, burberry handbags, new balance, coach purses, true religion jeans, north face, kate spade, ray ban uk, michael kors outlet online, true religion outlet, nike air max, converse pas cher, nike air force, nike air max uk, nike roshe run uk, sac vanessa bruno

oakleyses said...

ghd hair, hollister clothing, ray ban, iphone cases, celine handbags, oakley, insanity workout, converse, abercrombie and fitch, instyler, gucci, lululemon, ferragamo shoes, baseball bats, nike huaraches, beats by dre, babyliss, louboutin, new balance shoes, mac cosmetics, hollister, herve leger, hermes belt, giuseppe zanotti outlet, timberland boots, vans, valentino shoes, nike trainers uk, asics running shoes, p90x workout, nfl jerseys, nike air max, vans outlet, reebok outlet, soccer shoes, north face outlet, soccer jerseys, converse outlet, longchamp uk, nike roshe run, wedding dresses, ralph lauren, hollister, bottega veneta, north face outlet, mont blanc pens, nike air max, jimmy choo outlet, mcm handbags, chi flat iron

oakleyses said...

canada goose outlet, canada goose, toms shoes, links of london, karen millen uk, doudoune moncler, pandora jewelry, louis vuitton, marc jacobs, montre pas cher, canada goose outlet, moncler uk, moncler, pandora charms, juicy couture outlet, louis vuitton, thomas sabo, replica watches, canada goose jackets, ugg uk, ugg, canada goose, ugg,uggs,uggs canada, louis vuitton, wedding dresses, juicy couture outlet, canada goose, swarovski, moncler, canada goose outlet, supra shoes, moncler outlet, lancel, coach outlet, canada goose uk, louis vuitton, moncler outlet, moncler, louis vuitton, swarovski crystal, ugg pas cher, barbour, pandora uk, barbour uk, ugg,ugg australia,ugg italia, moncler, hollister, pandora jewelry

raybanoutlet001 said...

ray ban sunglasses outlet
yeezy shoes
49ers jersey
rolex watches
michael kors handbags
eagles jerseys
ugg boots
nike air force 1
true religion outlet store
fitflops sale