Infa: 2011

Nov 30, 2011

Agile Methodology

Agile is software development methodology. It is very
effective where Client frequently changes his requirement.
Since it has more iteration so you can assure a solution
that meets clients requirement.
More than one build deployement for a project.
It involves more client interection and testing effort.

Agile methodology is more of people oriented. Agile
methodology helps us to increase productivity and reduce
risks. There are 2 popular agile methods- Extreme
programming (XP) and Scrum.

Agile methodology will help to increase productivity by
scrum meetings. A scrum duration is 1-2 months. Every day
scrum meeting will be done in presence of lead.

There are two methods by which this methodology can be
implemented:-

1- Scrum
2- Extreme Progamming

Scrum: Each iteration would called a scrum which can be a 1-
2 Months.In Scrum Client prioritise his requirements what
he want first. If developer did not meets all the
requirement which was being fixed for a perticular scrum
than rest of the development part would be transferred to
the next scrum (would be delievered in the next build),
means developer cann't increase time decided for a scrum.
Its fixed.

Extreme Programming (XP): here iteration period would be
less then in scrum , which is being 2-4 weeks.
Here developer prioritise what to do first on the basis of
client requirement.
This duration which was being fixed for a iteration, can be
increase if the some development part is still pending.
The build would deployed with having all the client needs.
Thus iteration period is not fixed here it can be increase.
but iteration should meets all the client's requirement in
this build.
More attension is required for testing in XP

Aug 16, 2011

Oracle DBA to find locks on a table and space used

select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

select username,sid,serial#,program,status from v$session --where sid=101;

to check the tableSpace:
1:
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4

2:
select
a.TABLESPACE_NAME,a.MB Total_MB,b.MB Free_MB,round(((a.MB-b.MB)/a.MB)*100,2) percent_used
from
(select TABLESPACE_NAME,sum(BYTES)/1048576 MB from dba_data_files group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,sum(BYTES)/1048576 MB from dba_free_space group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.MB-b.MB)/a.MB) desc;

More ways to find locks on DB 10g

SQL> select session_id, oracle_username, os_user_name,locked_mode,object_name,object_type from v$locked_object a,dba_objects b
where a.object_id=b.object_id;

SQL> select s1.username || '@' || s1.machine
2 || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
3 4 from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
5 6 7 and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

Oracle DBA to find locks on a table

Oracle DBA to find locks on a table and space used

Jul 19, 2011

Informatica 8.6 installation

Steps to install Informatica Power Center Server 8.6

Pre-requisites:

1)Window xp sp2 ( You can also install the same on any high end OS ).
2)At least one database(here we assume you to have Oracle XE pre installed ). 3)2 GB Ram Recommended

Informatica 8.6 Server Installation:
1) Select the sever folder from the informatica installation CD.
2)There will find set up, click on the set up file.

3)click on Next button.

4)Select the license key by using browse button.It will be available the fallowing location:…\Informatica 8.6\RAWANA

5)Click on Next button

. 6)Click on Next butt

on.

7)Select complete radio button and click on Next button.
8)Leave the defaults and select next button.

9)Click on Install button

10) Choose create new domain radio button, and click on Next button.

Select database type
Database URL: ComputerName:portno

Eg:windows-4dc4631:1521

To find the computer name.. Click on Start->Right click on My computer->Properties->Computer Name.
Provide the database credentials along with the Service name.
Find database service name:
Control Panel--Administrative tools--Services

Here XE is the service name.
For Oracle 10g users you will find the service name like :OracleServiceORCL then service name would be ORCL.

Note:SQL Server users they can give the name of the server name in the above field instead of service name.

Click on Test connection:If this succeed then it will take you to the further step.

11. Leave the defaults, enter password and confirm password and click on next button.

12.Uncheck the check box and click on next button.

13. Click on Done.
14.After finishing installation need to run the services
Run the Informatica services:
windows+r->services.msc-> Locate the Informatica services –> Start them.

Configure the repository service and Integration service:

To create these two above, please follow the below steps.

Give your Credentials

Select administrative Console. Make sure Domain is running.

Make sure node is also running.From create drop down select repository service.

1)Enter any service name.
2)Select License.
3)Select database type as oracle (if your sys contains oracle db)
4)Select node for which you want to create repository.
5)Leave the code page as default.
6)Connection string if ORCL for oracle users(Make sure connection string should be same as the service name what is given while installing server)
7)Enter Database Credentials
8)Leave other defaults as well.
9)Click on create button.
10)Enable the repository service.

Now Create content for the repository by selecting drop down shown below.

Note:Make sure Processes are enabled and repository service is enabled.
Creation of Integration service:

1)Give any name for Integration sevice.
2)Assign node to the IS.
3)Assign Repository to the IS.
4)Enter the rep.username and password.
Note:make this username and password should same as username and password of the Repository service.
5.Select data movement mode as ASCII.

In the process tab of the IS, enable the process.
Before enable, edit this section and select “Code page” as shown above.
Now Install client.
Open Informatica Repository Manager.

Select the RS from the RHS pane and click on OK.

Connect to the repository service.

Enter the repository username and password.

Jul 18, 2011

Need count of a character that how many times it repeat

Oracle:
select regexp_count('SRILANKA','A',1,'c') cnt from dual.
Informatica:
SRC-->SQ-->EXP-->TGT

In expression create two variable ports
in_NAME
v_REPLACE_A_CNT=LENGTH(REPLACECHR(0,NAME,'A',''))
v_NAME_CNT=LENGTH(NAME)
out_CNT=v_NAME_CNT-v_REPLACE_A

Target:
connect out_CNT to target port

If you pass 'SRILANKA' as NAME then out put is 2.

Types of Partitions in Informatica.

There are four types of partitions that can be done at session level.
1. Round Robin
2. Key Range
3. Hask Key
4. Pass Through
Depending on your requirement you can add partitioning scheme. When you want to group data based on some key, then you can go with Hash Key Partitioning. When you parititon data based on upper and lower limit then go for key range partition. Round robin is most useful when you can apply it on passive transformations before loading to target, it basically splits the flow into multiple chunks and creates multiple temporary files and later merges all into one at the end of session run. Pass through partitioning will run in single pipeline...which is by default for all your sessions. It is always important while choosing the partitions schemes while running sessions and also depends how your data is, if the data is very sparse and if you dont define the required partition correctly, then some pipelines will have less records whereas as others will be more...so ideally you should understand range or volume or groups before you set.

Jul 7, 2011

Check the DB connection through unix

Shell Script

#!/usr/bin/ksh

## Assign the User, Password and database name below
. `dirname $0`/$1
echo ". `dirname $0`/$1"
uname=$user_id
passwd=$pass
sid=$sid_nm

$ORACLE_HOME/bin/sqlplus $uname/$passwd@$sid << END
select user from dual;
exit
END

estatus=$?

if [ $estatus != 0 ]
then
echo "Hi\n This is Just a test mail.\n Thanks,\nSupport Team" | mailx -s "Unable to connect to database $sid" ajit@XXX.com
fi

Config file:
export user_id=XXXX
export pass=XXXX
export sid_nm=DB_NAME(SID)

while run in unix u have to run both the shell and config file like below:
Script.sh file.cfg
Note: This is for Oracle DB

Jun 23, 2011

Cursors

Cursors: You can define Cursor Statements in Declaration section. Cursor statements let you bring data from tables and views into your PL/SQL Programs.

Why use cursors? Well when you retrieve subset of data from table (or whole table), then that data remains stored in SGA (Shared memory) until cursor is closed, so in this way you cache data and caching on database is good idea.

Choosing explicit or implicit cursor in your PL/SQL program?

Implicit cursors are used when you have a simple SELECT ... INTO single row of data into local program variables. It's the easiest path to your data, but it can often lead to coding the same or similar SELECTs in multiple places in your code.

Explicit cursors are defined in declaration section (package or block) and in this way, you can open and fetch from cursor in one or more places.

Implicit cursor will run more efficient than equivalent explicit cursor (from Oracle 8 Database onwards). So is there reasons to use explicit cursors at all? Off course. Explicit cursor can still be more efficient and they off course offer much programmatic control.

Implicit cursor

Implicit cursors are used when you need to retrieve single row from database. If you want to retrieve more than one row, then you must use either an explicit cursor or bulk collect.

Here one example of implicit cursor usage:

view plain print ?

SET serveroutput on;
DECLARE
PROCEDURE find_employee (employee_id_v employees.employee_id%TYPE)
IS
--Record in which we will fetch entire row.
emp_rec employees%ROWTYPE;
BEGIN
--Begining of implicit cursor statement.
SELECT *
INTO emp_rec --Fetch into record.
FROM employees
WHERE employee_id = employee_id_v;
--Write result.
DBMS_OUTPUT.put_line (emp_rec.employee_id || ' ' || emp_rec.first_name);
--Catch exception when there is no such employee.
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Unknown employee with id: ' || employee_id_v);
END find_employee;
BEGIN
find_employee (101);
find_employee (102);
--This one will produce exeption (OK, only if you do not have employee with id 1021).
find_employee (1021);
END;

SET serveroutput on;

DECLARE

   PROCEDURE find_employee (employee_id_v employees.employee_id%TYPE)
   IS
      --Record in which we will fetch entire row.  
      emp_rec   employees%ROWTYPE;
   BEGIN
      --Begining of implicit cursor statement.
      SELECT *
        INTO emp_rec --Fetch into record.
        FROM employees
       WHERE employee_id = employee_id_v;
       --Write result.
      DBMS_OUTPUT.put_line (emp_rec.employee_id || ' ' || emp_rec.first_name);
   --Catch exception when there is no such employee.
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN         
         DBMS_OUTPUT.put_line ('Unknown employee with id: ' || employee_id_v);
   END find_employee;
BEGIN
   find_employee (101);
   find_employee (102);
   --This one will produce exeption (OK, only if you do not have employee  with id 1021).
   find_employee (1021);
END;

We encapsulate query with function (this is _aways_ a good idea). This function print employee information from database to output. Also we introduce some exception handling (when
no employee is found).

Because PL/SQL is so tightly integrated with the Oracle database, you can easily retrieve complex datatypes (entire row for example - as we did in our example).

You can see that using implicit cursor is quite simple (with basic understanding of SQL) we just create simple select statement and insert rowset into record (that we declared as local variable).

Explicit cursor

Explicit cursor is explicitly defined in the declaration section. With explicit cursor, you have complete control over the different PL/SQL steps involved in retrieving information from the database. You decide when to open, when fetch and how many records and when to close cursor. Information about the current state of cursor is available through examination of cursor attributes.

Example:

view plain print ?

SET SERVEROUTPUT on;
DECLARE
PROCEDURE get_all_employees
IS
--Employee record variable.
employee_rec employees%ROWTYPE;
--Cursor variable for explicit use.
CURSOR employee_cur
IS
SELECT *
FROM employees;
BEGIN
--Open cursor so you can use it.
OPEN employee_cur;
--Go through all employees.
LOOP
--Load current row from cursor into employee record.
FETCH employee_cur
INTO employee_rec;
--Loop until cursor attribute signals that no rows are found.
EXIT WHEN employee_cur%NOTFOUND;
DBMS_OUTPUT.put_line ( employee_rec.employee_id
|| ', '
|| employee_rec.first_name
);
END LOOP;
CLOSE employee_cur;
EXCEPTION
--Remember to close cursor even if there was some error.
WHEN OTHERS
THEN
IF employee_cur%ISOPEN
THEN
CLOSE employee_cur;
END IF;
END get_all_employees;
BEGIN
get_all_employees ();
END;

SET SERVEROUTPUT on;

DECLARE
   PROCEDURE get_all_employees
   IS
      --Employee record variable.
      employee_rec   employees%ROWTYPE;
      --Cursor variable for explicit use.
      CURSOR employee_cur
      IS
         SELECT *
           FROM employees;
   BEGIN
      --Open cursor so you can use it.      
      OPEN employee_cur;
      --Go through all employees.
      LOOP
         --Load current row from cursor into employee record. 
         FETCH employee_cur
          INTO employee_rec;
         --Loop until cursor attribute signals that no rows are found.
         EXIT WHEN employee_cur%NOTFOUND;
         DBMS_OUTPUT.put_line (   employee_rec.employee_id
                               || ', '
                               || employee_rec.first_name
                              );
      END LOOP;

      CLOSE employee_cur;
   EXCEPTION
      --Remember to close cursor even if there was some error.
      WHEN OTHERS
      THEN
         IF employee_cur%ISOPEN
         THEN
            CLOSE employee_cur;
         END IF;
   END get_all_employees;
BEGIN
   get_all_employees ();
END;

This PL/SQL block performs following:
Declare the cursor.
Declare а record based on that cursor.
Open the cursor.
Fetch rows until there are no rows left.
Close cursor.
Handle exception and close cursor if it is not closed.

Copied from link

Apr 20, 2011

Surrogate key Vs primary key

Surrogate Key is similar to Primary key interms of its characteristics like the value will be user generated or auto generated sequence having not null values. Surrogate Key is primary key for the Key columns defined in the Dimension tables in DWH. In OLTP system this will be achieved by introducing Composite Primary Keys. Suppose, if you want query a table with composite key, you have to define no of columns specified in the composite key in the where condition this will be overhead to database. Instead, we define a Surrogate key which is unique for the composite key in Dimension Table. Having a Surrogate Key will help the BI Team to fetch the dimension records faster than using Primary or composite primary key.

Apr 5, 2011

Informatica Repository Backup and Restore

pmrep connect -r Repo name -n User_name -x Pass -h host -o Port
pmrep backup -o filename.rep

Shell Script:
#!/usr/bin/ksh

# Usage : bkp_rep.sh --repository name

REP=$1
REPUSER=repository user name
REPPWD=repository pwd
cd /opt/informatica/server/bin/ > /opt/informatica/server/bin/backup/$REP.rep
HOST=`hostname`
pmrep connect -r $REP -n $REPUSER -x $REPPWD -h $HOST -o 5001
# Check Status
STATUS=$?
if [ $STATUS -ne 0 ]
then
echo '[ERROR] : Repository Connection Failed'
return $STATUS
fi
# Backup Repository
FILENAME='Backup/'$REP
pmrep Backup -o $FILENAME -d backup -f -b -j -q
# Cleanup
pmrep cleanup
# Compress File
compress -f /opt/informatica/server/bin/Backup/$REP.rep
copied from link

For Restore(from Informatica Help)
restore -u administrator -p password -i repository1_backup.rep -y.

Mar 20, 2011

source based commit and Target based commit

If you set the source based commit, say 1000, it executes commit after immediate of 1000 records read, irrespective of how many rows passed to target (may be 600 since 400 dropped).

Similarly, if you set target based commit, say 1000, it executes commit after immediate of 1000 records inserted, irrespective of records read from source (may be 1500, rest

of 500 dropped)

Informatica Tips

1. When you join unsorted data using Joiner transformation, You can optimize performance by designating the source with fewer rows as master source.
2. When you join Sorted Data using a joiner transformation, you can optimize performance by designating the source with few duplicate key values as the master source.
3. You can edit a port in a mapping and then propagate changes to the other transformations,expressions, and conditions in the mapping.
4. Create an entry in ODBC.ini file in server to connect to a sybase IQ, SQL Server using ODBC.
5. If a lookup transformation has multiple conditions, you can improve performance by placing
conditions that use the equality operator first on the condition tab.
6. You can Optimize session performance by specifying the DB partitioning partition type for multi-node IBM DB2 targets.
7. You can improve the lookup performance by building the cache files concurrently.
8. you can send text message to all users connected to the repository by
selecting the repository in the administration console and selecting actions>Notify Users.

The concept of Additional Concurrent pipelines and perform prebuilt lookup cache are different

Additional Concurrent pipelines:

Ans: In General a mapping starts build lookups when its start reading source data and at certain level the record

propagation happens and at the first record touches the lookup then the lookup building happens the lookup

building happens in sequence one after the other

When the Concurrent pipelines is selected in the session level (Default is Auto), then all the lookup builds start

parallel this will reduce the time load time

Note: This is not preferred as the space consuming will be more and changes of session failure is more

Prebuilt lookup caches:

Ans: By selecting this the session looks for the same lookups and for that I will only one cache and will be shared by the

other same lookups in the same mapping i.e if a mapping have test_table as lookup for 10 times with same condition then

mapping will be build cache for one lookup only

Note: Good practice to use this

Feb 28, 2011

Simple PL/SQL Procedure

create table emp (Acolumn number(8,2),Bcolumn varchar2(50))
create table emp_bkp as select * from emp
insert into emp (acolumn) values (1);
select * from emp_bkp
truncate table emp_bkp

There are two types of cursors
1. Implicit Cursor
2. Explicit Cursor

Implicit Cursor is used by Oracle Server to pass and execute SQL Statements.
SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.

Explicit Cursors are explicitly declared by programmer.

Boolean data type means the o/p will be TRUE or FALSE.

IF and Case statements
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSEIF-END IF

IF Syntax:
IF Condition THEN
Statements;
ELSEIF Condition THEN
Statements;
ELSE
Statements;
END IF;

Case Syntax:
CASE Column name or CASE
When ‘A’ then ‘AJ’
WHEN ‘B’ then ‘BD’
ELSE ‘NO SUCH VALUE’
END;

:variable_name is called as bind variable
/* Procedure 1 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE := 10;
begin
select Acolumn into v_evnt_ky from emp where Acolumn=1;
insert into emp_bkp (Acolumn) values (v_evnt_ky);
commit;
end;

/* Procedure 2 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
--select Acolumn into v_evnt_ky from emp where Acolumn=1;
insert into emp_bkp (Acolumn) values (v_bonus);
commit;
END IF;
end;

/* Procedure 3 */
declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
DBMS_OUTPUT.PUT_LINE('This is my first PL/SQL Procedure');
commit;
END IF;
end;

/*Procedure for Informatica*/
create or replace procedure dummy_emp (
v_evnt_ky IN number,
v_bonus IN emp.acolumn%TYPE )
IS
begin
IF v_bonus = 10 THEN
insert into emp_bkp (Acolumn) values (v_evnt_ky);
commit;
END IF;
end dummy_emp;

/*Display mailing address*/
declare
v_name varchar2(50) :='Aj';
v_address varchar2(30) :='Veedhi';
v_state varchar2(50) :='SJC';
v_m_address varchar2(50);
begin
v_m_address:=v_name||chr(10)||v_state||chr(10)||v_address;
DBMS_OUTPUT.PUT_LINE(v_m_address);
END;
o/p:Aj
SJC
Veedhi

/*Qualify an Identifier: By using Block label Prefix*/

< < OUTER > >
declare
birthdate date;
begin
    declare
    birthdate date;
    Begin
    OUTER.birthdate:=to_date('03/01/2011','mm/dd/yyyy');
    END;
    DBMS_OUTPUT.PUT_LINE(birthdate);
    END;
   -- O/P=01-MAR-11

/* Raise the error Using raise application error message*/

declare
v_evnt_ky number(8,2);
v_bonus emp.acolumn%TYPE ;
begin
select acolumn into v_bonus from emp where Acolumn=1;
IF v_bonus = 1 THEN
select Acolumn into v_evnt_ky from emp where Acolumn=2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--DBMS_OUTPUT.PUT_LINE('HISTORY');
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR-'||SQLERRM);
end;

O/P:

ORA-20001: An error was encountered - 100 -ERROR- ORA-01403: no data found

ORA-06512: at line 14

/* Using Define in PL/SQL */
DEFINE P_NUM1=9;
DEFINE P_NUM2=3;
DECLARE
    V_NUM1 number :=&P_NUM1;
    V_NUM2 number :=&P_NUM2;
     V_RESULT number;
BEGIN
    V_RESULT := (V_NUM1/V_NUM2)+V_NUM2;
    /*Printing PL/SQL Variable*/
    DBMS_OUTPUT.PUT_LINE(V_RESULT);
    END;

/*Using Merge function*/

DECLARE
v_bonus emp.acolumn%TYPE := 1;
BEGIN
MERGE INTO EMP_BKP BKP
USING EMP E
ON
(BKP.ACOLUMN=v_bonus)
WHEN MATCHED THEN
UPDATE
SET
--BKP.ACOLUMN = E.ACOLUMN,
BKP.BCOLUMN =E.BCOLUMN
WHEN NOT MATCHED THEN
INSERT VALUES(E.ACOLUMN,E.BCOLUMN);
COMMIT;
END;

Feb 17, 2011

How to find a locked object in oracle

select a.sid,a.serial#,c.object_name
from V$session a,
V$lock b,
user_objects c
where a.sid=b.sid
and b.ID1=c.object_id;

or

select a.sid,a.serial#,c.object_name 
 from V$session a, 
 V$locked_object b, 
 user_objects c 
 where a.sid=b.session_id 
 and b.object_id=c.object_id;

to kill it use the below query

ALTER SYSTEM KILL SESSION 'sid,serial#';

Feb 14, 2011

Export Data From an Oracle's Table to a Flat File on the Unix Box

How to Export Data From an Oracle's Table to a Flat File on the Unix Box
By Kristen Waters, eHow Contributor

Clck here to find more
Oracle is a relational database that can be installed on the Unix operating system. You can output the contents of Oracle SQL statements to a flat file that is stored outside of the Oracle database. This file is a text file that contains the information from the table at the moment that it was created. It will not be updated or changed by the changes made to the Oracle database.
Difficulty: Moderately Easy
Instructions
Things You'll Need:

    * Unix operating system
    * Oracle database

   1.Log into the Unix machine with your Oracle username.
   2.open a terminal window.
   3. Type the command "sqlplus" to log into the Oracle database. You will be presented with the "SQL>" command prompt where you will type the following commands.
   4.
      Type the command "SET SPACE 0" to set the space between columns in the text file.
   5.
       Type the command "SET LINESIZE 75" to set the line size for the text file.
   6.
         Type the command "SET PAGESIZE 0" to suppress all headings, page breaks, titles, the initial blank line and other formatting information in the text file.
   7.
         Type the command "SET HEADING OFF" to suppress the printing of page headings in the text file.
   8.
      Type the command "SET MARKUP HTML OFF SPOOL OFF" stop the printing of HTML markup information.
   9.
       Type the command "SET COLSEP " "" to use a space (" ") to separate the columns in the text file.
10.
        Type the command "SPOOL output.txt" to send the output of the following statements to a text file named "output.txt."
11.
       Type the statement "SELECT * from table;" to select all of the data from the table. Replace the the word "table" with the actual name of the table.
12.
        Type the command "SPOOL off" to stop the writing of output to the text file.
13.
        Type the command "QUIT" to exit the Oracle database. You will be presented with the normal Unix command prompt where you will type the next command.
14.
        Type the command "cat output.txt" to view the contents of the text file.

Read more: How to Export Data From an Oracle's Table to a Flat File on the Unix Box | eHow.com http://www.ehow.com/how_5682976_export-flat-file-unix-box.html#ixzz1Dy7bL466

Connect to Oracle from Unix

[infaadm]-> sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 14 15:13:19 2011

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect usr_id/pass@DB_name
Connected.
SQL> select * from dual
2 ;

Feb 11, 2011

get Month from date Column

select to_char(open_ts,'yyyymm'),count(*)
from table name
group by to_char(open_ts,'yyyymm')
order by to_char(open_ts,'yyyymm')
or select to_char(sysdate,'mm') from dual;

if the value is in mb or CLOB data type

SUBSTR(REPLACE(REPLACE(REPLACE(SUBSTR(COLUMN_NAME,INSTR(COLUMN_NAME,'to')+3),CHR(10),' '),CHR(13),' '),CHR(124),' '),1,40) AS DESCRIPTION
or

Use DBMS_LOB.SUBSTR(Column_name,250,1)

Feb 7, 2011

Delete Duplicate rows and perftunning while doing delete for huge data

Here's how you remove the duplicate rows before the primary key or unique indexes can be created:

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

Here column1, column2, column3 constitute the identifying key for each record.
Be sure to replace our_table with the table name from which you want to remove the duplicate rows. The GROUP BY is used on the columns that make the primary key for the table. This script deletes each row in the group after the first row.

OR

insert into EMP values ('1','20');
insert into EMP values ('2','30');
insert into EMP values ('2','30');
insert into EMP values ('3','40');
insert into EMP values ('4','50');
commit;

delete from EMP t1
where t1.rowid >
( select min(t2.rowID) from EMP t2
where t1.col_A = t2.col_A
and t1.col_B = t2.col_b)

OR

delete cea
where rowid in (
select rid
from (
SELECT ROWID as rid,
row_number() over (partition by academic_period, load_week, sub_academic_period, person_uid, course_number, course_reference_number) rn
FROM cea
)
where rn > 1
)

OR

delete from emp
where rowid not in
(select max(rowid) from emp group by empno);

If you want to delete huge data then we need to use below syntax for better performance

ALTER SESSION ENABLE PARALLEL DML;

DELETE /*+ PARALLEL(12) */

FROM TABLE_NAME

WHERE COLUMN_NAME='CONDITION'

COMMIT;

ALTER SESSION DISABLE PARALLEL DML;

Jan 28, 2011

converting a .xls file to a | or ~ delimited

For Creating Pipe (|) demited file from Excel Follow the Steps.....
1)Go to Control Panel(Regional language) and Change the List separated comma (,) to Pipe (|).
2)Apply and Ok.
3)Open Excel File that to be create as Pipe (|) Delimited.
4)SaveAs Comma Separated File (.CSV) Format.
5)Open that CSV file in Notepad and replace Comma to Nothing and Save.
6)Change the List Separator from Pipe (|) to Comma (,) from Control Panel.

Jan 22, 2011

Errors in Prod@at Work

1.ORA-Value is too long
Trying to insert a 40,978 value in a column where data type is small int.
Fix: Change the data type

2. ora-26002 table has index defined upon it
Reason: At session the load is BULK mode and the target table has index on it.
Fix: Change the Load mode to Normal.

2/a). 'ORA-01555: snapshot too old: rollback segment number 2 with name "XXXXX" too small'
Ask the DBA to increase the undo_retention.

b).ORA-24806: LOB form mismatch
When sourc data changes while laoing to target this eror will come.
Run in different time interval where source won't change the data while the job is running.

3.ORA-03106:fatal two-task communication protocol error
rerun the job again. Solved.

4. Error: Cache file needed by named Lookup
Transformation in this mapping
appears to be created by an unnamed cache lookup transformation.
delete the cache files and run again. solve the problem.

5. CMN_1252 Error in looup creation

This error generally comes when Informatica Server is unable to provide sufficient space to the lookup for lookup cache creation.

The way We used is if there are multiple sessions are running then wait till there execution then run this failed session separately.
And if multiple sessions failed then also run each session one by one. So server can get time to accumulate the sufficient space which is required and is released from other sessions as on there completion.

6. error code ORA-03135 i.e the connection is lost or timeout
solution:
The keepalive parameter in the oracle configuration in the Informatica server is not set properly. we have set this value in such a way that the TCP/IP channel between Informatica Server and Oracle Database will never be in idle situation. This keepalive parameter contains a time (in ms) which is basically the duration between two PINGs between these two servers. This parameter can be found at the following locations.

/usr/sbin/ndd /dev/tcp tcp_keepalive_interval

Apart from that we have also included one entry "ENABLE = BROKEN" in the entry for that particular database in the tnsnames.ora file exists in the Informatica server.
Other possibilty is if the size of the table is very high.

After applying these two scenarios, the long run query is running fine.

7. CMN_1769 Inconsistent Recovery Cache
Solution: The task'sRecovery Strategy attribute (in the Properties tab of Edit Tasks)
needs to be "Fail task and continue workflow".

8. ORA-12514:TNS: listener does not currently know of service requested in connect descriptor
Sol: Restart the Job again.

9. ORA-12569: TNS:packet checksum failure
Sol: By removing the unwanted columns with CLOB and BLOB data types solved the issue.

10. sqlstate = 40003
Database driver error...
Function Name : Fetch
SQL Stmt : select
This error is mainly due to the size of the data when you fetch.
The reasons are if you have any CLOB or BLOB fileds present hten the size will be huge
the sol is use dbms.lob function for those kind of columns or else change the session properties like DTM Buffersize and Maximum Memory Allowed For Auto Memory Attributes