Category Archives: Oracle

OCI-21700: object does not exist or is marked for delete (a quick reminder)

While many reasons can cause the OCI-21700: object does not exist or is marked for delete message, many times the OCI-21700 is caused by difficulty to access resources that are checked only at runtime.

The most common reason for this error is granting execute privileges on a function without granting privileges on referenced object like types for example.

Giving the same grants to the referenced objects will solve the problem.

Update: it appears that this error message can be raised when using Weblogic Data Source to run a function as well.
Sometimes, mostly after a network failure/database restart/DB schema changes, the “OCI-21700: object does not exist or is marked for delete” will be raised. The weird thing is that the same function will fail when running it with Weblogic but it can run without a problem when you run it from the sql console using the same credentials.
It appears that some Data Base actions like importing or droping objects or even unexpected network failure can “corrupt the DataSource”. If this is the case, all you have to do to solve the problem is restarting the Data Source.

Update 2: Apparently, there is a bug in Oracle 10 that raises the OCI-21700 error regardless of providing the required permission. A unexplained workaround, if all else fails, is creating the function with the same name in the schema you are connected to and retry. Even weirder, if this solves the problem, it will still work even if you drop the newly created function. Sound like Oracle voodoo but hey, if it solve the problem …

ORA-02020 – Too many database links in use (also a workaround for ORA-28267 )

If many of your queries are done over db links, you will probably encounter the ORA-02020
(too many database links in use)
error. The cause for this error according to the Oracle documentation is ” The current session has exceeded the INIT.ORA open_links maximum” . The default value for this parameter is 4 but this can easily be verified by running the following query

select * from v$parameter where name ='open_links'

In plain English, this means that the current session is using more than 4 db links.

The problem is that Oracle keeps the database link open until the session is closed. This is especially problematic when working with Weblogic data sources pools for example. Since the Weblogic server keeps its session open constantly, all queries are using the same session (or sessions). This cause the number of open links to accumulates with every query from a new remote server. The final result is that when the fourth database link is used the “ora-02020-too many database links in use” is raised. The fact that many times this message is raised when running a query that uses only one database link makes it hard to debug.

According to Oracle the solution is to Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases. However, changing the open_links parameter can not be done online and require downtime. In addition, not every DBA will be happy to increase this parameter.

Using the following query to identify the number of open database links will show that rolling back or committing does not always free up the open links.

select db_link from v$dblink;

The solution is to actively close the db link after every query using the following command.

This is equivalent to
execute immediate 'alter session close database link DB LINK NAME'

However, it is not always feasible to change existing code. A possible solution might be to create a procedure that will close all open db links and run it after or before queries that uses database links.

create or replace procedure rollback_and_close_db_links
authid current_user is
for links in (select db_link from v$dblink) loop

end loop;

UPDATE: Apperantly, this procedure can act as a workaround for the ORA-28267 Invalid NameSpace error that could happen when your SOA suite connects to a database using JDBC and try to invoke an object that connect to a remote DB. For example, weblogic connects to a function on one DB and this function uses a db link. if you add a call to “rollback_and_close_db_links ” before returning the result, you can workaround this error.

How to grant privileges on all tables to a user

Sometimes you need to grant privileges to one user’s objects to another user. While granting should be done very carefully, when you have a lot of tables it is nice to have an automated script to do it.

It is important to note that the best way to do that is creating a role, granting the privileges to this role and then grant the role to the user. This way, you can fine tune the privileges and the process will be a lot faster next time.

Another important thing to note is that you should avoid the temptation to use the  “SELECT ANY TABLE” privilege. This will indeed allow the user to select any table but (and this is a big but) since it is a system privilege, it will allow him to select any table from any schema. Depends on the setting, it might even allow to user to select from the dictionary tables themselves. There is no reason to grant this privilege to someone who is not the DBA.

The easiest way to grant select on all of  one account’s object to another user is to use a PL/SQL loop. This script will grant select on tables but it should be easy to adapt it to any other privilege.


 for i in (select * from user_tables)       

     execute immediate ‘grant select on ‘||i.table_name||’ to a_role_created_for_this’;
   end loop;

In case you need a pure SQL solution, the best way to go would be to spool the results of the following query and run the script created in grant-all.txt.

SQL> set lin 150

SQL> spool c:\grant-all.txt

SQL>select ‘grant select  to ‘||table_name||’ to “the new user”;’ from all_tables where owner=’the old user;

SQL> spool off

“A service specific error occurred: 2” when trying to start dbconsole

When logging in to database control after some time you may get a message that some accounts’ passwords need to be changed (sysman, system etc..).  A page will be displayed allowing you to change those password but if something (like timeout) happens during the page processing you will not be able to log in again to the database control and change the passwords.

usually, to solve you should restart the dbconsole by opening the command prompt and typing emctl stop dbconsole. However, you may find that the service is no longer running. Trying to start the dbconsole by typing emctl start dbconsole in the command prompt will result in the following message:

The OracleDBConsole[ServiceName] service could not be started.

A service specific error occurred: 2.

More help is available by typing NET HELPMSG 3547.

as expected, typing NET HELPMSG will not provide usefull information.

To solve this:

1) Delete and recreate the Enterprise Manager agent.

In the command prompt type> emca -deconfig dbcontrol db

even if this fails, type> emca -config dbcontrol db

You will be asked to provide the database SID, the listener port, password for SYS, DBSNMP and SYSMAN accounts, and some optional settings.

This process will run for several minutes.

2) restart the dbconsole

> emctl stop dbconsole

> emctl start dbconsole

This should solve the issue, however, if you are still unable to log in or you get the following error:

ORA-28001: the password has expired (DBD ERROR: OCISessionBegin), make sure that the SYSMAN account is unlocked

SQL> conn / as sysdba
SQL> alter user sysman identified by [new password]
2  /

User altered.
SQL> alter user sysman account unlock;

User altered.
SQL> exit

> emctl stop dbconsole

> emctl start dbconsole

if you still have problems logging in to database control, refer to the following post

What to do when Enterprise Manager is not able to connect to the database instance.

Net configuration assistant changes are not saved on Windows 7

If you are trying to use the oracle net configuration assistant in order to create tnsnames.ora entry on windows 7, you may notice that sometimes the net service name is simply not created. Even after you run through the wizard successfully you may get the famous “ORA-12154 – TNS could not resolve service name” when you actually try to use it. Another symptom would be that when you run the net configuration assistant again, the entry you just added will not be there.


This issue is caused by the change in Windows 7 default permissions. Windows 7 has the “Administrator” account disabled by default right out of the box. During the Windows 7 setup, the user is prompted for a username that has administrator rights and is in the administrator group. Running the net configuration assistant as the “administrator” user does not guarantee that this user has the administrator’s rights. This probably prevents the Network Configuration Assistant from editing the tnsnames.ora therefore the changes are not saved.



A simple solution to this issue is to run the network configuration assistant as an administrator.


  1.  Press and hold Ctrl+Shift while opening the program. Alternatively, you can navigate to the program shortcut (or the actual exe file), right click and then click on Run as administrator.
  2. If prompted by the UAC you should click on Yes to apply permission to allow the program to run with full permission as an Administrator (You may need to provide the administrator password if you are logged in as a standard user).