• Why JDBC connection to an Oracle database fails when using service name in jdbc connection string that differs from the database name?
    Post By: admin

Lot of users complain that they can not connect to an Oracle database using JDBC connection string when using database service name in it whereas they can connect to the database using service name when connecting to the database using SQLPLUS. There is nothing wrong with the underlying technology but the syntax for using the JDBC connection string is different when using the service name in JDBC connection string. Below are two java code examples having JDBC connection string with the database name and service name.

JDBC Connection String with database name Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbhost:1521:sid_name", "dbusername", "dbpassword"); 
JDBC Connection String with service name Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbhost:1521/service_name", "dbusername", "dbpassword"); 

  • How do I identify if a Critical Patch Update (CPU) is installed on the database prior to release 9.2?
    Post By: admin

Prior to Oracle database release 9.2, there is no direct way to identify this information BUT Oracle create directories something like cpu\CPUOct2005\* under the ORACLE_HOME, the patch was applied. So by looking at the directories, one can have idea about the CPU applied on to the Oracle database.

  • How do I identify if a Critical Patch Update (CPU) is installed on the database?
    Post By: admin

Well from CPUJan2006 onward, if you are running Oracle database release 9.2 or higher then you can execute the following SQL statement against the target database to identify the CPU patches applied to the database.

col action_time for a30 
col action for a6 
col namespace for a9 
col version for a10 
col id for 999999999 
col comments for a10
select * from registry$history;

 

  • There are various articles on the internet suggesting to set the underscore parameters '_allow_resetlogs_corruption' and '_corroupted_rollback_segments' incase there is severe redo log and rollback segment corruption. in the Oracle database What are the c
    Post By: admin

First of all, never set these underscore parameters until suggested by Oracle Support. You MUST rebuild the database if you set these parameters to startup the database because of the following reasons.

  1. By setting these parameters, you are telling Oracle kernel that the rollback segments are corrupted and any uncommitted transactions in it will not be rolled back as part of the crash recovery at the next startup.
  2. The data dictionary will be logically corrupt after bypassing the consistency checks (Result of setting the above said parameter) of the Oracle kernel. Hence, it is VERY VERY important that you MUST rebuild the database to avoid any unforeseen problem later on this database.

 

  • How to insert '&' in a character type column in an Oracle table?
    Post By: admin

Execute the following statement on the SQL*Plus prompt before executing the INSERT statement.

SET DEFINE OFF