Friday, March 25, 2011

Queries for debugging Oracle database issues

Accessing database as another user -
alter session set current_schema=schemaName;

Name of Users or Role having access to any database object
select * from USER_TAB_PRIVS_MADE where table_name='Object_name;
select * from DBA_TAB_PRIVS where table_name='Object_name';

Primary key columns of a table -
select * from all_cons_columns a join all_constraints c on a.constraint_name = c.constraint_name where c.table_name = 'table_name' and c.constraint_type = 'P'

Foreign key columns of a table -
select * from all_cons_columns a join all_constraings c on a.constraint_name = c.constraint_name where c.table_name = 'table_name' and c.constraint_type = 'R'

Query to compile the database objects
exec dbms_ddl.alter_compile ('Object_name','Schema_name','Object_name');
alter object_type object_name compile;

Query to compile schema
exec dbms_utility.compile_schema('SchemaName');

Query to create compile statements for invalid objects in a schema-

Select  'alter '||object_type||'  '||object_name||' compile;'
From user_objects
Where status <> 'VALID'
And object_type IN ('VIEW','SYNONYM',

SQL Block to empty the schema -

  cursor c_obj
    SELECT u1.object_type
    ,      u1.object_name
    FROM   user_objects u1
    and    ( u1.object_type <> 'TABLE' or u1.object_name not like 'BIN%')
    and not EXISTS (select 'x' from  user_objects u2 where u1.object_type='TABLE' AND U2.OBJECT_TYPE='MATERIALIZED VIEW' AND U1.OBJECT_NAME=U2.OBJECT_NAME);

  l_execute varchar2(2000);
  for r_obj in c_obj loop
     l_execute:= 'drop '||r_obj.object_type||' '||r_obj.object_name;
     if r_obj.object_type = 'TABLE'
       l_execute:= l_execute || ' CASCADE CONSTRAINTS';
     end if;
     EXECUTE IMMEDIATE l_execute;
  end loop;
purge recyclebin;
 SQL block to truncate all tables in schema

set serveroutput on size unlimited;

for c1 in (select y.table_name, y.constraint_name from user_constraints y, user_tables x where x.table_name = y.table_name and y.constraint_type='R') loop
        dbms_output.put_line('alter table '||c1.table_name||' disable constraint '||c1.constraint_name || ';');
        execute immediate  ('alter table '||c1.table_name||' disable constraint '||c1.constraint_name);
end loop;

for t1 in (select table_name from user_tables) loop
        dbms_output.put_line('truncate table '||t1.table_name || ';');
        execute immediate ('truncate table '||t1.table_name);
end loop;

for c2 in (select y.table_name, y.constraint_name from user_constraints y, user_tables x where x.table_name = y.table_name and y.constraint_type='R') loop
        dbms_output.put_line('alter table '||c2.table_name||' enable constraint '||c2.constraint_name || ';');
       execute immediate ('alter table '||c2.table_name||' enable constraint '||c2.constraint_name);
END loop;

 SQL block to rebuild all indexes in schema

set serveroutput on size unliminited
 v_index_name VARCHAR2(200);
  for v_index_name in (select OBJECT_NAME  index_name from user_objects where object_type='INDEX')
--      dbms_output.put_line('ALTER INDEX '|| v_index_name.index_name ||' REBUILD'||' ;' );
        execute immediate('ALTER INDEX '|| v_index_name.index_name ||' REBUILD' );
        dbms_output.put_line('ALTER INDEX '|| v_index_name.index_name ||' REBUILD'||' ; -------- failed');
        dbms_output.put_line('Error message = '||SQLERRM);
  end LOOP;
 SQL block to check if any table in schema has records

set serveroutput on size unlimited;
v_record_count NUMBER;
  for t1 in (select table_name from user_tables) loop
          EXECUTE IMMEDIATE ('select count(*) from '||t1.table_name) INTO v_record_count ;
          IF v_record_count>0
            dbms_output.put_line('select count(*) from '||t1.table_name || ';');
            dbms_output.put_line( t1.table_name || ' has ' || v_record_count || ' records'); 
          END IF;
  end loop;
 SQL Procedure, an alternate way to provide kill session privilege

create or replace procedure kill_session (pn_sid    number ,pn_serial number)
 lv_user varchar2(30);
 select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
 if lv_user is not null and lv_user not in ('SYS','SYSTEM') then
  execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''';
  raise_application_error(-20000,'Attempt to kill protected system session has been blocked.');
 end if;

Checking the oracle version -  
select * from v$version where banner like 'Oracle%' ;

Checking available directory objects -
select * from all_directories 

Query to check the last refresh time of materialized view
SELECT owner, mview_name, to_char(last_refresh_date, 'dd/mm/yy hh24:mi:ss') Last_Refresh From   All_Mviews

Checking priviliges in directory object -
select  grantor, grantee, table_schema, table_name, privilege from all_tab_privs where table_name = upper('&directoryObjectName')

Checking the space occupied by a table -
select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS), 2) SIZE_BYTE, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) SIZE_KB,ROUND((AVG_ROW_LEN * NUM_ROWS /(1024*1024)), 2) SIZE_MB from USER_TABLES where NUM_ROWS is not null order by SIZE_KB desc

Other useful info for debugging issues -
  1. Query to find blocking sessions
  2. Oracle system tables 
  3. Disable interactive prompts at SQL*Plus
  4. Setting materilized view to refresh on demand (manually)
  5. Refreshing materialized view manually
  6. Monitoring and managing temporary table spaces  
  7. Analyzing the table for faster insert and select
  8. Setting oracle server output on with unlimited size
  9. Managing oracle recycle bin and retrieve already dropped object
  10. Recycle bin data dictionary table (USER_RECYCLEBIN)
  11. When oracle index should be rebuild
  12. Debugging oracle units using SQL Developer 

Giving output number format of a query -

Link to refer to take DB dump -
Taking DB dump (using expdp) -
Taking DB dump (using exp) -

Thursday, March 24, 2011

Implementing connection pool in java

DBCP (Database Connection Pool API)
Many Apache projects support interaction with a relational database. Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. Opening a connection per user can be unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large. Accordingly, developers often wish to share a "pool" of open connections between all of the application's current users. The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required. The application itself logs into the DBMS, and handles any user account issues internally.

There are several Database Connection Pools already available, both within Apache products and elsewhere. This Commons package provides an opportunity to coordinate the efforts required to create and maintain an efficient, feature-rich package under the ASF license.

Spring bean definition of DBCP -
    <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${JDBC_DRIVER_CLASSNAME}" />
        <property name="url" value="${JDBC_URL}" />
        <property name="username" value="${JDBC_USERNAME}"/>
        <property name="password" value="${JDBC_PASSWORD}" />
        <property name="initialSize" value="20" />
        <property name="maxIdle" value="30" />
        <property name="maxActive" value="50" />
        <property name="maxWait" value="120000" />
     <property name="removeAbandonedTimeout" value="10" />
        <property name="removeAbandoned" value="true" />

Maven Dependency

Wednesday, March 23, 2011

Implementing continuous integration process

Cruise Control - CruiseControl is both a continuous integration tool and an extensible framework for creating a custom continuous build process. It includes dozens of plugins for a variety of source controls, build technologies, and notifications schemes including email and instant messaging. A web interface provides details of the current and previous builds. And the standard CruiseControl distribution is augmented through a rich selection of 3rd Party Tools.

Hudson -Hudson monitors executions of repeated jobs, such as building a software project or jobs run by cron. Among those things, current Hudson focuses on the following two jobs:
  1. Building/testing software projects continuously, just like CruiseControl or DamageControl. In a nutshell, Hudson provides an easy-to-use so-called continuous integration system, making it easier for developers to integrate changes to the project, and making it easier for users to obtain a fresh build. The automated, continuous build increases the productivity.
  2. Monitoring executions of externally-run jobs, such as cron jobs and procmail jobs, even those that are run on a remote machine. For example, with cron, all you receive is regular e-mails that capture the output, and it is up to you to look at them diligently and notice when it broke. Hudson keeps those outputs and makes it easy for you to notice when something is wrong

Apache Continuum (Continuous Integration and Build Server) - Apache Continuum is an enterprise-ready continuous integration server with features such as automated builds, release management, role-based security, and integration with popular build tools and source control management systems. Whether you have a centralized build team or want to put control of releases in the hands of developers, Continuum can help you improve quality and maintain a consistent build environment.

Luntbuild -Luntbuild is a powerful build automation and management tool. Continuous Integration or nightly builds can be easily set using a clean web interface. Executed builds are well managed using functions such as search, categorization, promotion, patching, deletion, etc. It also acts as a central build artifacts repository and download area for your whole team.

Which one to go for? - Each of these tools have their own pron and cons. You can refer to below link to find a summary of each tool, which would help you to choose the one that suites your need -

Adding encription capability in java applications

Jasypt is a java library which allows the developer to add basic encryption capabilities to his/her projects with minimum effort, and without the need of having deep knowledge on how cryptography works.

Sample code encrypt values - You will need commons-lang-2.3.jar and jasypt-1.6.jar to compile and execute the below program.

import org.jasypt.encryption.pbe.StandardPBEStringEncryptor;
import org.jasypt.encryption.pbe.config.EnvironmentStringPBEConfig;

public class MaskPassword
    public static void main(String[] args)
        EnvironmentStringPBEConfig conf = new EnvironmentStringPBEConfig();

        StandardPBEStringEncryptor enc = new StandardPBEStringEncryptor();

        System.out.println("VLUE_TO_BE_ENCRIPTED Encrypted Value : [" + enc.encrypt("VLUE_TO_BE_ENCRIPTED") + "]");
        System.out.println("nQL5VtGIKhG4mcYg/6U3KJE7Fi7y/SfxsYp7VQIeD54= Decrypted Value : [" + enc.decrypt("toIc7aXUxn6yEHPSWSbYOv7XTbSK8Mjf1OU0JjujV/M=") + "]");

Result -
VLUE_TO_BE_ENCRIPTED Encrypted Value : [0PyTACLyIpWoixZmpo7qgBdEkCX41Sw9GgG4gjq8rbQ=]
nQL5VtGIKhG4mcYg/6U3KJE7Fi7y/SfxsYp7VQIeD54= Decrypted Value : [VLUE_TO_BE_ENCRIPTED]

import org.jasypt.util.text.BasicTextEncryptor;

public class MaskPasswordWithBasicTextEncription
    public static void main(String[] args)
        String text = "We are using PBEWithMD5AndDES algorithm for encryption";
        System.out.println("Text      = " + text);

        BasicTextEncryptor bte = new BasicTextEncryptor();

        String encrypted = bte.encrypt(text);

        System.out.println("Encrypted = " + encrypted);
        String original = bte.decrypt(encrypted);
        System.out.println("Original  = " + original);

Result -
Text      = We are using PBEWithMD5AndDES algorithm for encryption
Encrypted = X5U1CiNtwaUw/fuXx5FIedALldrW5EUtL9YTQMuwwC4PqpD6bFOuQisc9+lGY3qjnvdKUUAV91owLv3csnSZEw==
Original  = We are using PBEWithMD5AndDES algorithm for encryption