Oracle Scripts

Track Current Redo Log Usage with X$KCCCP Purpose V$LOG reports a redo log group's allocated size and its status, but it does not report how much of the CURRENT group has actually filled. On a busy instance, that is the number a DBA wants: is the current log close to a switch, or has it barely started filling? V$LOG …

Query Segment Advisor Recommendations with DBA_ADVISOR_FINDINGS Purpose Which segments are actually worth shrinking right now, and which recommendation is safe to act on without second-guessing it? Oracle already ran that analysis — usually overnight, during the default automatic maintenance window — and wrote the …

Query Historical ASH Beyond Memory with DBA_HIST_ACTIVE_SESS_HISTORY Purpose Troubleshooting a performance spike after the fact is a different job than watching one happen live. By the time anyone opens a session the next morning to ask what was running during last night's batch window, the in-memory …

Audit RMAN Backup Job History with V$RMAN_BACKUP_JOB_DETAILS Purpose V$RMAN_BACKUP_JOB_DETAILS summarizes RMAN backup work at the job level. Oracle describes it as the view that "displays details about backup jobs." One row covers one job: when it started, when it ended, how much input it read, how much output it …

Watch Long-Running SQL in Real Time with V$SQL_MONITOR Purpose An AWR report describes what happened over the last hour. A SQL trace file describes what happened after the statement has already finished writing it. V$SQL_MONITOR answers a different question: what is running right now, and how far along is it. …

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 …

Get the Actual Execution Plan with DBMS_XPLAN.DISPLAY_CURSOR Purpose EXPLAIN PLAN never runs the statement it describes. It asks the optimizer what it would do, based on statistics and cardinality estimates, and prints that guess — which is exactly why a query that looks fine in EXPLAIN PLAN can still run slow in …

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 …