Oracle Scripts

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

Enable a 10046 Trace for an Oracle Session Purpose A 10046 trace returns wait events only if the level includes them. Enabling the trace at level 1 and then wondering why the file shows no waits is the most common 10046 mistake in production diagnosis — the output has elapsed times and call counts, but none of the db …

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 …

Monitor Data Pump Progress with V$SESSION_LONGOPS Purpose Where the expdp and impdp log files record a status line per completed object — writing output at each table boundary, not while a large table is mid-transfer — V$SESSION_LONGOPS records progress while the operation is still running. That gap matters on a …

Insert Control Characters in vi with Ctrl-V for Oracle Prompts Purpose A custom SQL*Plus prompt that prints the connected instance name in colour, or a login script that clears the screen before the banner, both need one thing the keyboard cannot type directly: a literal control character embedded in the file. Press …