Category Archives: Oracle

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.

DBMS_SESSION.CLOSE_DATABASE_LINK ('DB LINK NAME')
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
begin
rollback;
for links in (select db_link from v$dblink) loop

DBMS_SESSION.CLOSE_DATABASE_LINK (links.db_link);
end loop;
end;

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.

 begin

 for i in (select * from user_tables)       

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


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
Connected.
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).

What to do when listener.log is growing.

over time, the Listener.log file can grow to huge size and take a lot of disk space.
If you will try to delete it in windows you may get the following error:

—————————
Error Deleting File or Folder
—————————
Cannot delete listener: It is being used by another person or program.

Close any programs that might be using the file and try again.
—————————
OK
—————————


Linux will allow you to delete the file but it will not create a new one.


The solution to this issue is simple. following are some options:


The easiest solution would be to stop the listener process, delete the file and restart the listener service. However, stopping the listener will prevent new connections to the database and disrupt service.


A better option would be to stop logging to the listener.log file:

In the command prompt type >lsnrctl
This will open the Listener control utility. Type LSNRCTL> set log_status off
now you can navigate to the file location and delete the file using the operating system.
This will prevent the file from growing in the futre.
If you like, you can restart logging LSNRCTL> set log_status on
This will allow you to delete listener.log but nothing will be logged when the log_status is set to off.


If you want to the logging to continue. you can switch to a different logging file:
type LSNRCTL> set log_file listener2.log

The listener will now log to the new file. you can delete or move the old file.