Friday, December 18, 2009

awk in multiline records

I had a database creation script that looks like spanning thousands of lines. However I was only interested to see only few of them which were only containing name pattern TEST1 and TEST2.

I could have used grep to find the valid lines of the file but my records were spread across the lines means each valid record consisted of multiple lines. So I googled for the problem and found that awk can also do a multi line search given proper inputs.

Suppose I have a file input.sql that contains many multiline database object creation queries. I now have to find all queries which only contains TEST1 and TEST2. All of these queries/records are separated by separator ";".

Write a awk input script as follows.

#scrip.awk
BEGIN {
RS=";"
}
{
if (($0 ~ /TEST1/ )||($0 ~ /TEST2/)) {
print $0 ";"
}
}

Here I am specifying that my record separator RS is equal to ";". Also In the awk execution line I specified that if each of my record field which is represented by $0 contains either TEST1 or TEST2 then dump that field to console using print command of awk. Also I have appended a additional string ";" so that my output file output.sql contains actual queries when run on sqlplus prompt

Now I can execute the command as follows on command prompt.
awk -f script.awk input.sql > output.sql

This should give you the all matching record containing TEST1 and TEST2 in the file output.sql

A very nice awk Related tutorial can be found at
http://www.ibm.com/developerworks/library/l-awk1.html
http://www.ibm.com/developerworks/library/l-awk2.html

Sphere: Related Content

EXPDP/IMPDP example in oracle for database copy

Normally we need to have a database copy to export/import on our other database and separate schema. Here are the steps to do the schema copy from one database to another. You need to be admin user for performing some tasks.

$ mkdir datapump

$ chmod 777 datapump/

$ sqlplus adminuser1/adminpassword1@dbsid1

SQL> create directory datapump as '/path/to/folder/datapump';

SQL> grant read, write on directory datapump to public;

$ expdp user1/password1@dbsid1 dumpfile=datapump:myschema.dmp logfile=datapump:myschema_exp.log schemas=myschema

$ sqlplus adminuser2/adminpassword2@dbsid2

SQL> create directory datapump as '/same/path/to/folder/datapump';

SQL> grant read, write on directory datapump to public;

$ impdp user2/password2@dbsid2 dumpfile=datapump:myschema.dmp logfile=datapump:myschema_imp.log remap_schema=myschema:yourschema remap_tablespace=mytablespace:yourtablespace

You can remove remap_schema and remap_tablespace clauses in case your schema name and tablespace name are same on both the databases.

You also need to have same datapump location accessible from both the databases.

Sphere: Related Content

Wednesday, November 25, 2009

Browsing svn revisions in a web browser

Browsing svn revisions in a web browser

http://{repository url}/!svn/bc/{revision}/{mymodule}/{trunk or branch}

for example
I want to see the revision 80121 of module testmodule's trunk
and my repository is http://svn/repository

Then the URL will be like

http://svn/repository/!svn/bc/80121/testmodule/trunk

Sphere: Related Content

Monday, October 26, 2009

Configuring resin for remote debugging

I am using resin application server in my development environment. At times I need to debug my applications using resin container. I did some search and found a way to integrate resin to my IDE as follows.

For Resin-3.1.8.
I added few lines to my resin.conf file as follows
<jvm-arg>-Xdebug</jvm-arg>
<jvm-arg>-Xnoagent</jvm-arg>
<jvm-arg>-Djava.compiler=NONE</jvm-arg>
<jvm-arg>-Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005</jvm-arg>


For versions prior to Resin-3.1.8.
I added few lines to my resin startup script as follows
httpd.exe -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005 -Xnoagent -Djava.compiler=NONE -conf D:\resin\conf\resin.conf

Sphere: Related Content

Tuesday, October 6, 2009

Using sed to search and replace a token without opening a file

#!/bin/bash

i=0; while [ $i -le 47 ]

do

echo " Creating temp file for slice : $i"

sed s/slicenum/$i/g adspace_buy.ctl > adspace_buy.ctl.tmp

echo " Loading Started ..."

sqlldr userid=tfr_rep/welcome@datagen1-aswain:1521/tfrdb control=adspace_buy.ctl.tmp data=adspace_buy_data.$i.csv log=logssqlldr_log_adspaceBuy.log bad=logssqlldr_bad_adspaceBuy.log direct = true

echo "Loading Done for slice : $i"

i=`expr $i + 1`

done

echo "Script completed"

This script will search for a token slicenum in
adspace_buy.ctl file and replace it with $i value of the loop counter. Also it redirects the output to a new file adspace_buy.ctl.tmp.
After that this script uses sql loader utility to load the data to oracle database using this newly generated file. This whole process continues for 48 times.

Sphere: Related Content

To get the top 1000 lines of all the files in a folder in another folder keeping the original file name same

To copy files stripped from one folder to another
cd dir;
for file in *
do
head -1000 $file > ../dir_temp/$file
done

Sphere: Related Content

Friday, August 7, 2009

Export sqlplus results to a csv file

Create the file buy_export.sql:
set echo off

set feedback off

set linesize 100

set pagesize 0

set sqlprompt ''

set trimspool on

spool buyInfo.csv


select buyid||','|| name
from dbbuy;

spool off

Run it using

sqlplus -S [username]/[password]@[SID] @buy_export.sql

Sphere: Related Content

Tuesday, August 4, 2009

Change the permission of table space

To change the permission of table space to read write use following sql command

alter tablespace TFR_REP read write;

Sphere: Related Content

Monday, August 3, 2009

To change the oracle user tablespace

In case if you have assigned wrong tablespace while creating a user, you can change tablespace of that user by following command.

first connect with the system on the database where you have created this user.

alter user AHSAN_TFR default tablespace TFR_REP quota unlimited on TFR_REP;

must assign quota otherwise you cannot run any SQL statement.

Sphere: Related Content

Tuesday, July 14, 2009

To know tablespaces of oracle user

To know the tablespace assigned to a user run following query on sqlplus

select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME


Sphere: Related Content

Sunday, July 12, 2009

Cut nth field of a csv file and sort

Using cut command you can print out the nth field of a csv input file in.csv.

cut -d"," -f3 in.csv | sort |uniq > ~/out.csv

Above command cuts the 3rd field and then sorts and then take a unique of sorted output and then finally output in out.csv

Sphere: Related Content

Killing a defunct process

A defunct (or "zombie") process is a process that isn't running, isn't eligible to run, and takes up no system resources. It's actually a process that has exited, but its parent has not called wait() in order to find out its exit status.

defunct processes can't be killed since they are already dead. To make them disappear you have to kill their parent process.

Find the parent process id of the defunct process and then kill that parent process:

ps -fe | grep defunctprocess | awk '{print $3}'

kill -9 parentprocessid

Sphere: Related Content

Saturday, July 4, 2009

Class Loading in Java

Class Loading in Java

Class loaders are hierarchical. Classes are introduced into the JVM as they are referenced by name in a class that is already running in the JVM. So how is the very first class loaded? The very first class is specially loaded with the help of static main() method declared in your class. All the subsequently loaded classes are loaded by the classes, which are already loaded and running. A class loader creates a namespace. All JVMs include at least one class loader that is embedded within the JVM called the primordial (or bootstrap) class loader. Now let’s look at non-primordial class loaders. The JVM has hooks in it to allow user defined class loaders to be used in place of

primordial class loader. Let us look at the class loaders created by the JVM.

  1. Bootstrap (primordial) - Not reloadable – Loads JDK internal classes, java.* packages. (as defined in the sun.boot.class.path system property, typically loads rt.jar and i18n.jar)
  2. Extensions – Not reloadable – Loads jar files from JDK extensions directory (as defined in the java.ext.dirs system property – usually lib/ext directory of the JRE)
  3. System – Not reloadable – Loads classes from system classpath (as defined by the java.class.path property, which is set by the CLASSPATH environment variable or –classpath or –cp command line options)

Classes loaded by Bootstrap class loader have no visibility into classes loaded by its descendants (ie Extensions and Systems class loaders). The classes loaded by system class loader have visibility into classes loaded by its parents (ie Extensions and Bootstrap class loaders).
If there were any sibling class loaders they cannot see classes loaded by each other. They can only see the classes loaded by their parent class loader.

Class loaders are hierarchical and use a delegation model when loading a class. Class loaders request their parent to load the class first before attempting to load it themselves. When a class loader loads a class, the child class loaders in the hierarchy will never reload the class again. Hence uniqueness is maintained. Classes loaded by a child class loader have visibility into classes loaded by its parents up the hierarchy but the reverse is not true.

Two objects loaded by different class loaders are never equal even if they carry the same values, which mean a class is uniquely identified in the context of the associated class loader. This applies to singletons too, where each class loader will have its own singleton.

Static class loading:
Classes are statically loaded with Java’s “new” operator.
class MyClass {
public static void main(String args[])
{
Car c = new Car();
}
}
A NoClassDefFoundException is thrown if a class is referenced with Java’s “new” operator (i.e. static loading) but the runtime system cannot find the referenced class.

Dynamic class loading:
Dynamic loading is a technique for programmatically invoking the functions of a class loader at run time.
Class.forName (String className); //static method which returns a Class
The above static method returns the class object associated with the class name. The string className can be supplied dynamically at run time. Unlike the static loading, the dynamic loading will decide whether to load the class Car or the class Jeep at runtime based on a properties file and/or other runtime conditions. Once the class is dynamically loaded the following method returns an instance of the loaded class. It’s just like creating a class object with no arguments.
class.newInstance (); //A non-static method, which creates an instance of a class (ie creates an object).
Jeep myJeep = null ;
//myClassName should be read from a properties file or Constants interface.
//stay away from hard coding values in your program.
String myClassName = “au.com.Jeep” ;
Class vehicleClass = Class.forName(myClassName) ;
myJeep = (Jeep) vehicleClass.newInstance();
myJeep.setFuelCapacity(50);
A ClassNotFoundException is thrown when an application tries to load in a class through its string name using the following methods but no definition for the class with the specified name could be found:

  • The forName(..) method in class – Class.
  • The findSystemClass(..) method in class – ClassLoader.
  • The loadClass(..) method in class – ClassLoader.

Sphere: Related Content

Find a class has been loaded by which Jar

How to know from which jar file a class has been loaded?

public static String whichJAR(Class clazz){
String name = clazz.getName();
name = name.substring(name.lastIndexOf(’.') + 1);
String jar = clazz.getResource(name + “.class”).toString(); //NOI18N
return jar.substring(0, jar.indexOf(’!'));
}

Sphere: Related Content

Monday, June 29, 2009

Copying only schema definition to another schema

Here is the command to copy only the schema definition from one schema to another schema.

First export the schema definition from user tfr_rep to empty_schema.dmp file

exp tfr_rep/welcome@datagen1-ajaved:1521/tfrdb file=empty_schema.dmp log=export.log rows=n constraints=n owner=tfr_rep

Then copy the dump file to ahsan_tfr user from tfr_rep user as follows.

imp userid=ahsan_tfr/welcome@tfindiadbzone:1521/tfindia file=empty_schema.dmp log=import.log rows=n fromuser=tfr_rep touser=ahsan_tfr

NOTE: Names of tablespaces on both the schema should be same, otherwise import will fail indicating oracle error

Sphere: Related Content

Sunday, February 22, 2009

Materialized View Refresh Status

To know the materialized view refresh status along with their refresh time, you can issue following query to the database.

SELECT mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date FROM user_mviews;

Also to know the materialized view logs present in schema of a user, use this query

SELECT log_owner, master, log_table FROM user_mview_logs;

Sphere: Related Content

Tuesday, February 17, 2009

Script to create multiple startup screens in UNIX

Here is the scriipt that creates few startup screens on window.

Save this file in your home directory as MYSCREEN

execute it using screen -c {path to home}/MYSCREEN

startup_message off
vbell off
caption always "%{= bb}%{+b w}%n %h %=%t %c"
hardstatus alwayslastline "%-Lw%{= BW}%50>%n%f* %t%{-}%+Lw%<"
activity "Activity in %t(%n)"
shell -/bin/bash
screen -t SCREEN1
screen -t SCREEN2
screen -t SCREEN3
screen -t SCREEN4

This will create 4 screens named as SCREEN1,SCREEN2,SCREEN3 and SCREEN4 on your window

Sphere: Related Content

Script to export/import oracle schema

Here is the bash script that does the job of export and import of an oracle schema to another schema.

Save this file as EXPORT_IMPORT.

For exporting the schema type
EXPORT_IMPORT EXP

This file generates the schema dump in exp_database.dmp file and it also makes log of export process in exp_database.log file.

For importing the same exported exp_database.dmp dump file to another schema type
EXPORT_IMPORT IMP

if [ $# -lt 1 ]; then
echo "First argument to this file should be either EXP or IMP."
exit
fi
if [ "$1" = "EXP" ]; then
if [ $# != 2 ]; then
echo "No of arguments to this file should be two."
echo "USAGE: EXPORT_IMPORT EXP "
exit
fi
exp "$2" FILE=exp_database.dmp COMPRESS=N CONSISTENT=N CONSTRAINTS=Y GRANTS=Y INDEXES=Y RECORD=Y ROWS=Y Feedback=1000 LOG=exp_database.log
fi
if [ "$1" = "IMP" ]; then
if [ $# != 3 ]; then
echo "No of arguments to this file should be three."
echo "USAGE: EXPORT_IMPORT IMP "
exit
fi
from_user=`echo "$2" | cut -f1 -d'/'`
to_user=`echo "$3" | cut -f1 -d'/'`
imp userid="$3" fromuser="$from_user" touser="$to_user" file=exp_database.dmp buffer=64000 commit=y
fi

Sphere: Related Content

Thursday, February 12, 2009

Know all users on an Oracle database

To know all users on Oracle database, issue following command on sqlplus window

select * from all_users


Sphere: Related Content

Friday, January 30, 2009

Create a new schema in Oracle

Here we will first create a tablespace and then we will create a user for that tablespace.

create tablespace ahsan
add datafile 'e:\ahsan.dbf' size 100 m;

create user ahsan identified by welcome
default tablespace ahsan temporary tablespace temp;

grant create session, connect, resource to ahsan;

Hence now you are ready with the new tablespace and a new user that belongs to the ahsan tablespace;

Sphere: Related Content

Installing oracle 10g database on parallels desktop

Recently, I switched to Mac from Windows machine. I had a challenge to make Mac development environment as similar to Windows development environment. Being a java developer I usually use Oracle database. So I tried to install it first on my mac, then I found somehow that Oracle does not support my mac version. Then I had to install Parallels Desktop to make it work.
I proceeded this way

1. Install Parallels Desktop, You can find how to do it on parallels support center.

2. Install Oracle 10g database on Parallels Desktop, It is smooth, so no problem.

3. Steps to install InstantClient and Sqlplus package on mac to access Parallel's oracle DB.

4. Download InstantClient and SqlPlus packages from Oracle download site.

5. Extract these two packages to a single directory like I chose /Applications/OracleClient

6. Copy tnsnames.ora file from parallels oracle installation to the Mac's /Applications/OracleClient directory you chose for installing InstantClient and SqlPlus.

7. Modify your .bash_profile to indicate these entries

PATH=/Applications/OracleClient:${PATH}
export PATH
ORACLE_HOME=/Applications/OracleClient
export ORACLE_HOME
DYLD_LIBRARY_PATH=/Applications/OracleClient
export DYLD_LIBRARY_PATH
TNS_ADMIN=/Applications/OracleClient
export TNS_ADMIN

8. Check for the IP of the Parallel's Desktop machine and note it.

9. Check for the IP of the Mac machine and note it.

10. open vi editor and edit /etc/hosts file for Mac machine to reflect the IP obtained from parallels desktop machine(Step 7)

192.168.128.82 ahsan-javed.mydomain.com
(Note:ahsan-javed.mydomain.com is the machine name that was given in my office network.)

11. open notepad and edit C:/Windows/System32/drivers/etc/hosts file for Parallels Desktop to reflect the IP obtained from Mac machine (step 8)

192.168.128.132 ahsanjaved
(Note: ahsanjaved is the machine name I assigned to parallels desktop while installing Windows XP on it.)

12. Go to Windows Control Center and disable firewall/enable 1521,1158 and 5506 ports for communication between Parallels Oracle and Mac machine.

13. Now go to shell and type sqlplus. it should ask you for username and password. Here you go connected.

Sphere: Related Content

Thursday, January 29, 2009

Oracle cursors parameters

To view open cursors on oracle

No of Open cursors on Oracle(Admin permission is required)
select count(*) from v$open_cursor;

To see open_cursors parameter on oracle db.
show parameter open_cursors;

To set open_cursors parameter on oracle db.

alter system set open_cursors=700 scope=memory;

To see the sql query associated with cursor

select sid, sql_text from v$Open_cursor order by sid, sql_text;

To select number of open cursors, machine name, operating system and user name executing the query, run the following

SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid ;

Sphere: Related Content

Rebuild unusable index on a column of a table in Oracle schema

To rebuild unusable indexes on Oracle schema we can use this simple script.
This script first checks for all unusable indexes in the entire schema and then rebuild them to VALID state.

set serverout on
set head on
set echo on
declare
str varchar2(500);
begin for i in (select index_name,status from user_indexes where Status='UNUSABLE')
loop
str:='alter index ' || i.index_name || ' rebuild unrecoverable' ;
execute immediate str;
dbms_output.put_line('rebuilt index ' || i.index_name);
end loop;
end;
/

Sphere: Related Content