Finding out the tables referencing the given table in Oracle Database:

Below query gives you details about the tables referencing the given table in Oracle database:


select table_name, constraint_name, status, owner
from all_constraints
where r_owner =  :owner
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = :tablename
   and owner = :owner
 )
order by table_name, constraint_name

Replace the parameters owner, tablename to your schema owner and the table name.

Choosing the right framwork for webapps

Comparing JVM Web Frameworks - TSSJS 2011

View more presentations from Matt Raible

JVM shutdown hook (Surviving abrupt shotdown)

Project I am working currently requires the ability to detect shutdown on JVM. Its basically required to be detected so that active connections to the JMS server to be destroyed. I wrote code to destroy connection on ServletContextListener which worked fine for some scenario, but failed if I kill web server processes explicitly.


The solution is a little known feature of the language that lets you register JVM shut down hooks. A shut down hook is simply a Thread that is registered with the JVM and run just before the JVM shuts down.

Here is code snippet to register shutdown hook with JVM:

Runtime.getRuntime().addShutdownHook(new Thread()
  {
   public void run()
   {
    // your code here which needs be executed before shutdown
   }
  });

Runtime.getRuntime().addShutdownHook(new Thread()
  {
   public void run()
   {
    try
    {
     Thread.sleep(10000);
    }
    catch (InterruptedException ex)
    {
    }
    // halt will bail out without calling further shutdown hooks or
    // finalizers

    Runtime.getRuntime().halt(1);
   }
  });

Database encryption using Jasypt (Java Simplified Encryption)

Here are required jars for using Jasypt

commons-lang

commons-codec

jasypt

Here is example of string encryptor with Spring and Hibernate.

Define encryptor in spring configuration file:

    
    
        PBEWithSHA1AndDESede
    
  
    
        jasypt
    
    
        4
    
 
  
  
    
        strongHibernateStringEncryptor
    
    
        
    
 

In hibernate mapping file define type as follows:

@TypeDef(
        name="encryptedString", 
        typeClass=EncryptedStringType.class, 
        parameters={@Parameter(name="encryptorRegisteredName",
                               value="strongHibernateStringEncryptor")}
    )

And then specify type as encryptedString for the columns you want to encrypt.

e.g.

@Column(name = "QO_OPTION", length = 4000, nullable = false)
 @Type(type="encryptedString")
 private String optionText;

Here hibernate will take care of encrypting while saving string to database and decrypting while loading object from database.

Execute an SQL script file in SQLPlus

To execute a script file in SQLPlus, type @ and then the file name.

SQL > @{file}

e.g if your file name is script.sql

then

SQL > @script.sql

If script is not in current directory you can specify the path

SQL > @{path}{file}

e.g

SQL >@/opt/oracle/script.sql

Oracle database backup and restore

Here is command to take backup from the oracle database:

exp userid=system/{system_passwd}@{schema} owner={schema_owner} file={backup_file}.dmp buffer=102400 statistics=none grants=n log={backup_log_file}.log

Where

system_passwd is password for system user

schema is database schema name

schema_owner is user name for the database

backup_file is backup file name

backup_log_file is log file name for backup logs

To restore database from the backup (dmp file) use following command

imp system/{system_passwd}@{schema} file={backup_file}.dmp log={import_log_file}.log fromuser={existing_user} touser={new_user}


Where
system_passwd is password for system user

schema is database schema name

backup_file is backup file name

import_log_file is log file name where all the logs will be written

existing_user is database user name from the dmp file

new_user is database user name which is being restored.

Creating DBLink in Oracle

Login to database execute below command:

create public database link {link_name} connect to {user_name} identified by using '{connect_descriptor}'

Where

1. link_name is the name of the database link

2. user_name is the username of the database to get connected

3. connect_descriptor is the tns entry ($OARCLE_HOME/network/admin/tnsname.ora)