Oracle Scripts

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 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 …

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 …

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. …