:

The Latest Oracle Posts

The Oracle Explain Plan

The Oracle explain plan is one of the most useful SQL tuning utilities that you are going to use when tuning SQL queries within your Oracle database. From novice to expert users, you should always know how to generate an explain plan for a given piece of SQL. As with most things in Oracle, there […]

Continue Reading

ORA-22905 cannot access rows from a non-nested table item

As discussed in another one of my articles on explain plans,  there are many ways to generate them and they are a vital part of your SQL  Tuning tool kit. So, what happens when you get an error such as the one below? SELECT * FROM  TABLE(dbms_xplan.display(‘PLAN_TABLE’,’test’,’ALL’)) * ERROR at line 1: ORA-22905: cannot access […]

Continue Reading

Oracle DBA Interview Questions and Tips

It’s a topic that every Oracle DBA has asked themselves at one point or another “What questions will I be asked at my interview”. And wouldn’t we all like to know the answer to that one! Interviews can be tough and when it comes to Oracle DBA technical interviews the range of subject areas and […]

Continue Reading

Listener, TNSNames and Connection Configuration

Listener, TNSNames and Connection Configuration

The Oracle listener is a cause of many issues when attempting to configure it for use. Because the listener is usually configured and then forgotten about it’s sometimes overlooked and only learnt about when there are errors. So, I thought it would be a good idea to cover some of the basics along with some […]

Continue Reading

V$STREAMS_CAPTURE – Waiting for Dictionary Redo

If you are configuring streams, or in my case Change Data Capture (CDC), you will be familiar with the wait events show in the v$streams_capture Oracle view. Once of these wait events is the “Waiting for Dictionary Redo” event. This is what is shown while the streams capture process is waiting to receive the dictionary […]

Continue Reading

What’s in The Oracle Buffer Cache?

First of all, you can try running this query: SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS   FROM V$DB_CACHE_ADVICE      AND BLOCK_SIZE    = (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’)      AND ADVICE_STATUS = ‘ON’; This many not return any rows depending upon what other initilisation parameters you have or have not set. You may find […]

Continue Reading

ORA-27486: insufficient privileges

The Problem When attempting to submit a job via dbms_scheduler, you receive the following error: ORA-27486: insufficient privileges ORA-06512: at “SYS.DBMS_ISCHED”, line 5 ORA-06512: at “SYS.DBMS_SCHEDULER”, line 30 The Cause You don’t have appropriate permissions to create jobs in this way. The Solution You will need to grant the appropriate privileges to the user in […]

Continue Reading

ORA-01031 insufficient privileges – Scheduler

The Problem You are attempting to create a chain using dbms_scheduler, but you are receiving the following error: ORA-01031: insufficient privileges ORA-06512: at “SYS.DBMS_ISCHED”, line 3761 ORA-06512: at “SYS.DBMS_ISCHED”, line 772 ORA-06512: at “SYS.DBMS_SCHEDULER”, line 1236 ORA-06512: at line 2 The Cause You don’t have the appropriate privileges to complete this action. The Solution Grant […]

Continue Reading

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

The Problem When running a data pump export job, I received the following error message: ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes Job “SYS”.”SYS_EXPORT_TABLE_01″ stopped due to fatal error at 12:09:55 The Cause Because I wanted to make my imports faster I had set the parallel parameter on the import […]

Continue Reading