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 …

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 …

Check RESUMABLE_TIMEOUT and RESUMABLE Status in V$SESSION Purpose V$SESSION carries two columns that tell you whether a session can survive running out of space: RESUMABLE and the resumable timeout that governs it. When resumable space allocation is enabled, a statement that hits a space error — a full tablespace, a …