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 […]
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 […]
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 […]
opiino: Attach failed! error=-1 ifvp=0000000000000000
By default, Oracle services are created and run under the local system account. I’m not entirely sure the reason for that, but one reason I can think of is so that processes from within the database can only write to the local system and not anywhere else. For that reason, you may have a process […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
