ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_ACTION$ by 128 in tablespace SYSAUX

I got below error while trying to deploy an already successfully compiled Oracle package from my QA environment to dev environment:

ORA-00604: error occurred at recursive SQL level 1

ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_ACTION$ by 128 in tablespace SYSAUX

00604. 00000 -  "error occurred at recursive SQL level %s"

*Cause:    An error occurred while processing a recursive SQL statement

           (a statement applying to internal dictionary tables).

*Action:   If the situation described in the next error on the stack

           can be corrected, do so; otherwise contact Oracle Support.

I had taken backup of the original package from my dev environment before I tried to overwrite it with the one from QA – so I tried compiling this old package again and surprisingly it worked just fine.

I got confused – googled around and found that the above error could be solved by setting value of PLSCOPE_SETTINGS to IDENTIFIERS:NONE from IDENTIFIERS:ALL:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

So there are couple of oracle data dictionary views related to this:

USER_PLSQL_OBJECT_SETTINGS

USER_IDENTIFIERS

I took a look at these views and found that for the package that I was trying to compile - the PLSCOPE_SETTINGS value was IDENTIFIERS:ALL.

But I was little skeptical of altering the setting since I did not know the impact of that.

I googled more and came across one post mentioning that this issue is just with Oracle SQL Developer – it did not sound believable but then I thought that these settings might be related to PL/SQL environment in which I am trying to compile the package.

So, I tried compiling the package using SQLPLUS command prompt and it worked  - YEAH!!! J

Useful points about Oracle APPEND Hint

Some points to keep in mind while using APPEND hint in ORACLE:

I am listing down some points that I came across and had to consider while using an APPEND hint.  This might not be a complete list - just throwing out which I think are useful.   

It does a direct path insert

Adds rows above high water mark

Parallel hint is always a direct path, if you go parallel, you will be appending.

Insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger.

Only one session can do a direct path load on a table, no other session can do direct path on the same table.  Also no other session could also issue insert, update or delete statements on this table.

Feel free to add more points in the comments section...

Hello blog

Hey Readers,

I am quite new to the blogging world.  I had been thinking since sometime for now to start writing about the technology that I work on - Oracle.  I would love to say that I never got enough time to start writing - but sadly that's not true.  I was (and am) just lazy to do so.  

But here I am shrugging off my laziness and writing the first post...

I will be penning down my experiences with Oracle technology - database in specific.  I am not looking to write neat and sophisticated blogs - but I hope they will be detailed enough to be understood by everyone...

Thanks for visiting..keep reading..