Oracle Scripts

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 …

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 — the private memory Oracle hands to each server process for sorting, …

Oracle Scheduler Job Monitoring with DBA_SCHEDULER_RUNNING_JOBS Purpose Which Oracle Scheduler jobs are executing at this instant, and how long has each been running? DBA_SCHEDULER_RUNNING_JOBS answers that directly. It lists only the jobs that are active right now — not the full job catalog, not the run history, just …

Oracle DBMS_SCHEDULER: Create and Manage Jobs with PL/SQL Purpose Automating a recurring task — a nightly statistics gather, a log purge, an end-of-month rollup — is a standard part of running an Oracle database. DBMS_SCHEDULER.CREATE_JOB is the procedure that registers that task with the database so it runs on a …