ORA errors

Thumbnail image for Grant SELECT on V$ Views

Grant SELECT on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error: The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as logging in as sys and granting select on the v$ view […]

Read the Full Article →
Thumbnail image for Access Control List (ACL) in Oracle 11G

Access Control List (ACL) in Oracle 11G

I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade. The code failed with the error: Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above […]

Read the Full Article →
Thumbnail image for ORA-12560: TNS:protocol adapter error

ORA-12560: TNS:protocol adapter error

Sometimes you try to login to your Oracle XE on your home computer, and get this curt blocking response: Here’s the top reason why it might happen and how to fix it.

Read the Full Article →
Thumbnail image for ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

ORA-01450: Maximum Key Length Exceeded – Possible Causes and Fix

ORA-01450 might crop up when a table index is being created in the database: ORA-01450: maximum key length (6398) exceeded Oracle documentation has this to say about the error: Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating […]

Read the Full Article →
Thumbnail image for UTL_FILE: Simple Write To File Example and Debugging Common Errors

UTL_FILE: Simple Write To File Example and Debugging Common Errors

A simple example of using UTL_FILE in PLSQL to create a file and write into it. Plus, the common ORA errors you encounter with the use of UTL_FILE, and how to resolve them.

Read the Full Article →
Thumbnail image for The Curious Case of The Missing ORA-00904

The Curious Case of The Missing ORA-00904

Here’s a puzzle for you to solve. You’re given these two tables – empl and dept – in which column emp_dept_id of table empl references dept_id of table dept. Note that the foreign key column names in the two tables is not identical. It’s called dept_id in table dept, emp_dept_id in table empl.

Read the Full Article →
Thumbnail image for Referencing User Defined Types over DBLink: Problem and Alternatives

Referencing User Defined Types over DBLink: Problem and Alternatives

Consider a PL/SQL call to a function which returns a user-defined type (UDT) – say, a SQL Type object. This works well as long as the called function resides in the same database as the calling PL/SQL, but if the called function is in a remote database, the PL/SQL call via DBLink end ups with […]

Read the Full Article →
Thumbnail image for ORA-55610: Invalid DDL statement on history-tracked table

ORA-55610: Invalid DDL statement on history-tracked table

If you have got Oracle Total Recall enabled on a table, and now want to truncate or drop the table, you are in for a problem. Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled. How do you get around this problem?

Read the Full Article →
Thumbnail image for How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

How to Drop and Recreate All Foreign Keys that Reference a Table in Oracle

In the last post you saw a workaround for the error "ORA-02449: unique/primary keys in table referenced by foreign keys", which blocks our attempt to drop an Oracle table if there are foreign keys from other tables referring to it. The caveat: if the dropped table has to be recreated, the dropped foreign keys must […]

Read the Full Article →
Thumbnail image for ORA-02449: Solving the DROP TABLE Dilemma

ORA-02449: Solving the DROP TABLE Dilemma

Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449: Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this […]

Read the Full Article →