Author: DBA Corner
-
Fast Guide: PL/SQL
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…
-
IMP-00017: Statement Failed with ORA-02304 During Schema Import
An Oracle schema import can fail when object types are imported back into the same database under a different schema. The log commonly shows IMP-00017 together with ORA-02304: invalid object identifier literal, or the Data Pump wrapper error ORA-39083. Why it happens Oracle object types can have database-wide object identifiers (OIDs). By default, an export…
-
ORA-19511 / NetBackup Status 25: Cannot Connect on Socket
ORA-19511 is Oracle RMAN reporting an error returned by the media-management layer. When the accompanying NetBackup message is status 25: cannot connect on socket, troubleshoot the NetBackup connection rather than the Oracle database first. What status 25 means A NetBackup process timed out while connecting to another NetBackup process. Common causes include name-resolution errors, a…
-
IBM TSM (ADSM) Backup: ANS1017E TCP/IP Connection Failure
ANS1017E (RC-50) Session rejected: TCP/IP connection failure is a client-side message used by IBM Tivoli Storage Manager, now IBM Storage Protect. It tells you the session could not be established or continued, but it does not by itself identify the cause. The server activity log at the same timestamp is usually the best place to…
-
ORA-01861: Using Date Formats in RMAN SET UNTIL TIME
During point-in-time recovery, RMAN can report ORA-01861: literal does not match format string when the value supplied to SET UNTIL TIME does not match the session’s date format. The reliable fix Use an explicit TO_DATE expression with a matching format mask. This makes the recovery script independent of the current NLS_DATE_FORMAT setting. In RMAN, the…
-
EXP-00003: No Storage Definition Found for Segment
EXP-00003: no storage definition found for segment is raised by Oracle’s legacy Export utility when it cannot obtain the storage definition for a table, index, or cluster. On older releases, a common trigger was running an exp executable whose patch level did not match the source database. First checks Record the full export log, including…
-
Oracle Bind Variable Peeking and Plan Instability
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…
-
Historical Oracle ANSI Join Limit: 1,050 Columns
This note refers to an old Oracle 10g issue in which very wide ANSI join queries could fail after internal query transformation. The reported threshold was roughly 1,050 projected columns, and some failures surfaced as ORA-01445. It should not be treated as a universal limit in current Oracle releases. Historical context The issue was associated…
-
Oracle: CPU Used by Each Database Session
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…
-
Oracle IPC Resources: Map Shared Memory to an Instance
On UNIX and Linux hosts with several Oracle instances, ipcs can show many shared-memory and semaphore resources without identifying the owning database clearly. Oracle’s sysresv utility maps System V IPC resources to an Oracle SID. Report resources for an instance To check one or more explicitly named instances: Run the command as the Oracle software…