Flashback Recovery is a new enhancement to the 10g database for the DBA's toolkit. Effectively, it's an "Oh shit!" protection mechanism for DBAs as it reduces recovery time from hours to minutes. Ask any DBA about the main cause of application outage - other than hardware failure - and the answer will be "human error". Such errors can result in logical data corruption and can bring down the complete system. Part of being human is making mistakes. Without advance planning, these errors are extremely difficult to avoid, and can be very difficult to recover from. Typical user-errors may include the accidental deletion of valuable data, deleting the wrong data, or dropping the wrong table.
Some DBAs complain that Oracle's pre-10g job queue interface is lacking. Unlike cron or Windows Scheduler, pre-10g Oracle doesn't provide a mechanism to schedule jobs to run twice a week, on the first of the month, etc.
Oracle has been available on Windows Server since Windows NT shipped in 1994. In that time there have been many changes to Oracle as features have been added and improved. One of the most significant improvement to Oracle on Windows has just been introduced (this was a year ago they supported 64 bit AMD/EM64T) without much fanfare or publicity. This is the introduction of Oracle 10g on Microsoft Windows Server x64.
In 10gR1, Oracle introduced the Scheduler, a new way to schedule jobs to run from within Oracle. The old DBMS_JOB mechanism is still there, but the Scheduler has several advantages over its predecessor. This article will take a quick look at those advantages and discuss transitioning from DBMS_JOB/ DBA_JOBS to the Scheduler. My next article will take a more in-depth look at some of the Scheduler's features.
This article discusses the various types of primary keys and list the advantages and disadvantages they provide.
If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.
External Tables let you query data in a flat file as though the file were an Oracle table. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table, although you can't write to an existing table.
10gR1 revamped Oracle clustered database management and features. 10gR2 builds on this success with a long list of improvements and enhancements. Oracle has streamlined the installation process and provided more filesystem options, made some performance and monitoring improvements, and improved manageability with a half-dozen administration enhancements. This article will take a look at the major changes.
This article shows how materialized views can be analyzed and optimized to ensure they can be FAST REFRESHed. As tools, the DBMS_MVIEW.explain_mview procedure and the MV_CAPABILITIES_TABLE are used. In this particular case, refresh time was reduced from more than 14 hours to less than 2!
If there is a task in Oracle for which the wheel has been reinvented many times, it is that of generating database object DDL. There are numerous scripts floating in different forums doing the same thing. Some of them work great, while others work only until a specific version. Sometimes the DBAs prefer to create the scripts themselves. Apart from the testing overhead, these scripts require substantial insight into the data dictionary. As new versions of the database are released, the scripts need to be modified to fit the new requirements.
Starting from Oracle 9i Release 1, the DBMS_METADATA package has put an official end to all such scripting effort. This article provides a tour of the reverse engineering features of the above package, with a focus on generating the creation DDL of existing database objects. The article also has a section covering the issue of finding object dependencies.