Scripts

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 schema export reads many tables over a span of time, and for schemas where rows in one table reference rows in another, you usually want every table in the dump to reflect the same moment. Community Data Pump documentation puts it directly: expdp exports are …

Monitor Data Pump Progress with V$SESSION_LONGOPS Purpose V$SESSION_LONGOPS shows how far a running expdp or impdp job has got. That matters on a full-database export scheduled overnight, or an import that has been running for six hours. The dynamic performance view gives a number to plan around instead of a guess. …

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 …

Monitor PGA Aggregate Target with V$PGASTAT in Oracle Purpose Is PGA_AGGREGATE_TARGET set high enough for the work the instance actually does? V$PGASTAT answers that question. It is the instance-level statistics view for the Program Global Area (PGA), the private memory that server processes use for work areas. Oracle …