Saturday, July 25, 2009

ORA-01000: Too many open cursors

You are running a long process, such as an export/import or SQL*Loader script, and get the "ORA-01000: Too many open cursors" error from your Oracle database.

This problem could be encountered because Oracle can keep closed cursors in cache which are included in the total open cursors count. One way to determine how many cursors are actually open in a database is to submit the following SQL:

"select value, name from v$sysstat where statistic# in (2,3);"

The standard recommended solution to the ORA-01000 is to increase the OPEN_CURSORS database initialization parameter. However, there is a workaround:

Break down your process into smaller pieces. An example is, instead of issuing a request for a full database export, do your export on a schema or table basis. You will have the inconvenience of having to execute more than one script or command, however, you will get around this error without having to change database initialization parameters and doing a shutdown/restart of your database.

Monday, July 13, 2009

ORA-06401 NETCMN: invalid driver designator

When recently issuing a command to connect to a remote database, the Oracle “ora-06401” error was encountered.

Oracle documentation states the following cause, along with what it suggests to be the sole solution:

ORA-06401 NETCMN: invalid driver designator

Cause: The login (connect) string contains an invalid driver designator.

Action: Correct the string and re-submit.

This usually means that the command syntax is incorrect. However, other causes exist:

1. improper authorization for the user issuing the command
2. invalid connection string for the target database version
3. ^M characters present in the file tnsnanes.ora file. These can be seen by viewing the ascii file after it has been converted to binary or by editing the file in a UNIX/Linux editor, such as vi. These characters are usually created by editing the file with a non-Linux/Unix editor and then transferring it by FTP to the server.
Use the following vi editor command to remove all of these characters from the file:


Thursday, July 9, 2009

A Recovery Manager Setup Obscure Point

When granting privileges to the rman user account, make sure that you make the RECOVERY_CATALOG_OWNER privilege the default privilege.