Category: Guide

PL/SQL is Oracle’s procedural extension to SQL. It lets you combine SQL statements with variables, conditions, loops, reusable subprograms, and exception handling. This quick guide covers the pieces you will use most often. 1. The basic PL/SQL block A block has an optional declaration section, a required executable section, and an optional exception section. The…

Bind variables improve cursor sharing, but a plan chosen for one bind value can perform poorly for another. Oracle calls the first-value optimization behavior bind variable peeking. How peeking affects a plan During a hard parse, the optimizer can inspect the current bind values and estimate selectivity as though literals were present. This helps when…

Oracle exposes cumulative database CPU time for each session through V$SESSTAT. Join it to V$STATNAME by name—never hard-code a statistic number, because statistic numbers can change between releases. Current CPU by session The statistic is cumulative for the life of the session and is traditionally recorded in hundredths of a second. It is Oracle database…

A rapidly growing archive destination can fill during bulk loads or data migrations. A usage-triggered RMAN job is a useful emergency backstop, but it should complement—not replace—scheduled archivelog backups, monitoring, and capacity planning. If you use the fast recovery area Measure the Oracle-managed recovery area from the database rather than parsing a filesystem command: V$RECOVERY_AREA_USAGE…

The old sp_attach_db procedure is deprecated. Modern SQL Server uses CREATE DATABASE … FOR ATTACH, and every data and log file must be accounted for. Prefer backup and restore For planned migrations, a tested full backup and restore is usually safer and easier to audit. Attach is appropriate when you have a cleanly detached, trusted…

Oracle’s DBNEWID utility, invoked as nid, can change a database identifier (DBID), a database name (DBNAME), or both. This is useful after cloning a database that must be distinguished from its source in an RMAN repository. Understand the consequences Changing the DBID makes backups and archived logs created under the old DBID unusable for the…

A DBID is Oracle’s internal identifier for a database. RMAN uses it to distinguish databases, so a physical clone that retains the source DBID can conflict with the source in the same recovery catalog. Oracle 9i Release 2 and later Oracle 9i Release 2 introduced the supported DBNEWID utility. Use nid rather than manually editing…

SQL-DMO and ActiveX Script job steps belong to an older SQL Server era. For current systems, use SQL Server Management Objects (SMO) from PowerShell and the maintained SqlServer module. Script every SQL Server Agent job Import-Module SqlServer $serverName = “SERVER\INSTANCE” $outputDir = “C:\DBA\SqlAgentJobs” New-Item -ItemType Directory -Force -Path $outputDir | Out-Null $server = New-Object Microsoft.SqlServer.Management.Smo.Server…

On Windows, Oracle database activity is easiest to diagnose by correlating Oracle sessions with operating-system process and thread identifiers. The old Quick Slice utility is no longer needed; use supported Windows tools such as Task Manager, Performance Monitor, or Process Explorer. Map sessions to Windows activity SELECT s.sid, s.serial#, s.username, s.status, s.program, p.spid, p.stid, p.pname,…

Compression choices should be based on your own data, recovery-time objectives, and available CPU. A benchmark copied from another system can be misleading because text, database exports, binaries, and already-compressed files behave very differently. Benchmark without changing the source file The following commands write new compressed files and leave sample.dat untouched: /usr/bin/time -f ‘gzip elapsed=%e…