How to avoid invalid objects in Oracle and modify during off hours

The question is, how can we implement application code changes during normal working hours and avoid the outage caused by objects becoming invalidated?

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database design and architecture Why am I receiving Oracle memory allocation errors? How to join two tables with unique keys in Oracle How does the Oracle LGWR write to online redo log files? Can I check an Oracle instance without logging into the Oracle server? How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table Can I install an Oracle client on Windows 7? How to use the Oracle Database Upgrade Assistant (DBUA) Can I specify Oracle column order in my database table? Can I have a single Oracle 11g RAC instance across multiple databases? How to use the Oracle export utility to duplicate database structure Oracle database design and architecture Why am I receiving Oracle memory allocation errors? How to join two tables with unique keys in Oracle How does the Oracle LGWR write to online redo log files? Can I check an Oracle instance without logging into the Oracle server? How to determine your SQL database through needs analysis Diving deeper into the SQL database features The MySQL open source database in the enterprise Breaking down the contenders in the SQL database market What managers should consider when starting a database scaling project How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary E. F. Codd  (SearchOracle.com) extent  (SearchOracle.com) flexfield  (SearchOracle.com) foreign key  (SearchOracle.com) multidimensional database  (SearchOracle.com) object-oriented database management system  (SearchOracle.com) quad tree  (SearchOracle.com) relational online analytical processing  (SearchOracle.com) row  (SearchOracle.com) splay tree  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

In most cases, you cannot perform these tasks during normal working hours without invaliding objects. Think about it this way, you modify stored procedure PROC1, which is called by database trigger TRIG1. How is TRIG1 to know that the changes you made to PROC1 are syntactically correct? TRIG1 does not know this so the object becomes INVALID. The next time a user calls TRIG1, Oracle will recompile it to ensure PROC1 has not broken TRIG1. If everything is still good, Oracle executes TRIG1 for the user. If the changes to PROC1 broke TRIG1, then the user will receive an error. But Oracle will not know for sure until it recompiles the INVALID object.

Depending on what changes you are attempting, the DBMS_REDEFINITION package can help you perform these changes with minimal downtime. But you may still run into situations where there is an impact on end users or online visitors.