Oracle Administration

Audit RMAN Backup Job History with V$RMAN_BACKUP_JOB_DETAILS Purpose V$RMAN_BACKUP_JOB_DETAILS is the one dynamic view built specifically at the job level rather than the piece or file level. Where other RMAN views report on individual datafile backups or individual backup set pieces, this view collapses an entire …

Redefine a Table Online with DBMS_REDEFINITION, No DML Blocking Purpose A DBA moving a table to a new tablespace, adding compression, or rebuilding a badly fragmented segment has one hard requirement: users can't lose access to that table while the change is happening. For decades the available tools — CREATE TABLE AS …

Manage the Recycle Bin with DBA_RECYCLEBIN Purpose Where does a table actually go the instant DROP TABLE runs? Not straight to disk deallocation. Since Oracle Database 10g, a dropped table is renamed and moved into the recycle bin instead of being physically removed, and it stays there, fully intact, until something …

Move a Datafile Online with ALTER DATABASE MOVE DATAFILE Purpose Oracle 12.1 closed a gap that had existed since the earliest releases: moving or renaming a datafile always meant taking something offline first. A DBA had three choices — switch the tablespace offline, switch the individual datafile offline, or shut the …

Flush the Shared Pool and Buffer Cache with ALTER SYSTEM Purpose Where DBMS_SHARED_POOL.PURGE removes one cursor or one package from the library cache, ALTER SYSTEM FLUSH SHARED_POOL clears every parsed statement, stored procedure, function, package, and trigger cached in the shared pool at once — a blunt instrument …

Review Job Run History with DBA_SCHEDULER_JOB_RUN_DETAILS Purpose A stuck job that fails silently overnight rarely announces itself. The next morning's dependent process just doesn't have the data it expected, and by then the only trace of what actually happened lives in the Scheduler's own run log. …

Locate the Alert Log and Trace Files with V$DIAG_INFO Purpose Before Oracle 11g, finding the alert log meant knowing the value of BACKGROUND_DUMP_DEST and constructing the filename by hand. 11g replaced that parameter, along with USER_DUMP_DEST and CORE_DUMP_DEST, with a single DIAGNOSTIC_DEST parameter and moved every …

Kill and Disconnect Oracle Sessions with ALTER SYSTEM Purpose V$SESSION exposes the SID and SERIAL# pair that both ALTER SYSTEM KILL SESSION and ALTER SYSTEM DISCONNECT SESSION require as their targeting key — a DBA cannot terminate a stuck session without pulling that pair first, and getting it wrong means terminating …

Check Redo Log Switch Frequency with V$LOG_HISTORY Purpose How many times does the online redo log switch in a single hour, and does that number spike during the nightly batch window or stay flat around the clock? A redo log sizing decision made from a single "switches per day" total hides the answer to that question. …

Run a Consistent Export with expdp FLASHBACK_TIME Purpose A Data Pump export that reads one table at 02:00 and the next at 02:15 is not a consistent snapshot — it is a photograph taken in pieces over time. For schemas where rows in one table reference rows in another, that time spread means the export can capture …