Oracle database architecture tutorial

Oracle Instance

An oracle instance is a set of different memory structures and the processes which manages all database activities, such as transaction processing, database recovery, form generation, and so on. There is different type of memory block which are accessed by different type of background processes

SGA :

It is also commonly known as  System Global area. It solve many purpose like storing parsed SQL qyeries/ Parsed Pl-sqL code and redo buffer cache.

It has below memory pools

• shared pool : 
• Data Dictionary Cache:
• Buffer Cache:
• Redo Log Buffer:
• Large Pool:
• Java Pool :

PGA :

Although the result of SQL statement parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

Oracle Background Process:

There some back ground process which keep on running to fulfill DB requests.

• DBWR
• LGWR
• CKPT
• SMON
• PMON
• ARCH
• DISPATCHER PROCESSES
• RECO
• SNPn
• LCKn
• Pnnn

Thanks for reading article !! I found a full explanation of these component here

unix interview question with answer

 Below are some list of some unix  interview question , which are commonly asked in unix interview.

1) How to print/display the last line of a file?
A) $> tail -1 file.txt

2) How to display n-th line of a file?
A) $> sed –n ' p' file.txt

3) How to remove the first line / header from a file?
A) $> sed '1 d' file.txt

4) How to remove the last line/ trailer from a file in Unix script?
A) $> sed –i '$ d' file.txt

5) How to remove certain lines from a file in Unix?
A) $> sed –i '5,7 d' file.txt

6) How to remove the last n-th line from a file?
A) $> sed –i '96,100 d' file.txt # alternative to command [head -95 file.txt]

7) How to check the length of any line in a file?
A) $> sed –n ' p' file.txt

8) How to get the nth word of a line in Unix?
A) cut –f -d' '

9) How to reverse a string in unix?
A) $> echo "unix" | rev
xinu

10) How to get the last word from a line in Unix file?
A) $>echo "C for Cat" | rev | cut -f1 -d' ' | rev
Cat

11)What are some common shells and what are their indicators?

sh – Bourne shell
csh – C SHell
bash – Bourne Again Shell
tcsh – enhanced C Shell
zsh – Z SHell
ksh – Korn SHell

12) What is a directory?

Every file is assigned to a directory. A directory is a specialized form of file that maintains a list of all files in it.

13) What is inode?

An inode is an entry created on a section of the disk set aside for a file system. The inode contains nearly all there is to know about a file, which includes the location on the disk where the file starts, the size of the file, when the file was last used, when the file was last changed, what the various read, write and execute permissions are, who owns the file, and other information.

14) You have a file called tonky in the directory honky. Later you add new material to tonky. What changes take place in the directory, inode, and file?

The directory entry is unchanged, since the name and inode number remain unchanged. In the inode file, the file size, time of last access, and time of last modification are updated. In the file itself, the new material is added.

For more good article on unix interview question with answer , pls check here.

Top unix interview question with answer :Part -1

Top unix interview question with answer :Part -2

Top unix interview question with answer :Part -3

Rank Transformation in Informatica with Example

Rank Transformation in Informatica , is a connected and active /Passive transformation which select top/bottom rows of   input. It is something similar to Rank analytical data function or oracle. Only difference is that, it also filter out the remaining rows  (which are not a part of top/bottom threshold).

Must check : Please also check how to create Aggregator Transformation

How Rank Transformation works in Informatica?

It first cache all the input data and then performs the rank calculation per group , filter out the unwanted records.

Steps to create an Rank transformation:

• In the Mapping Designer, open a Mapping.
• Click Transformation > Create. Select Rank transformation.
• Enter a name and click Done.
• You will see one port RANKINDEX port already there. This port store the ranking of each   record  and can be used to populate target as well
• Add all additional port from source input which  are going to be use in following transformation.
• Open the port tab and first check the Group by option for desired column ( for example deptno in our case)
• 
• Also  check the Rank (R) option for the port which you want to do ranking. For example salary in our case.

Note: We can define Group by indicator for multiple port, but  Ranking can be done   on single port only.

• Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as per need.
• 
• Click OK.
• Connect output ports of Rank Transformation to other transformation or target

Must Read : Learn more about informatica coding standard Example of Rank Transformation in Informatica: A good example of Rank transformation with different type of join can be found here. For more explanation on Rank transformation , you can read it here and also check here for informatica interview question on Rank transformation

Top tar command example in unix

In Unix, the name of the tar command is short for tape archiving, the storing of entire file systems onto magnetic tape, which is one use for the command. However, a more common use for tar is to simply combine a few files into a single file, for easy storage and distribution. Must Read : Learn more about VI editor here To combine multiple files and/or directories into a single file, use the following command: tar -cvf file.tar inputfile1 inputfile2 Replace inputfile1 and inputfile2 with the files and/or directories you want to combine. You can use any name in place of file.tar, though you should keep the.tar extension. If you don't use the  f  option, tar assumes you really do want to create a tape archive instead of joining up a number of files. The  v  option tells tar to be verbose, which reports all files as they are added. To separate an archive created by tar into separate files, at the shell prompt, enter: tar -xvf file.tar Must Read : Learn more about find command here A good article of Tar/untar command can be found  here. [contact-form][contact-field label='Name' type='name' required='1'/][contact-field label='Email' type='email' required='1'/][contact-field label='Website' type='url'/][contact-field label='Comment' type='textarea' required='1'/][/contact-form]

Joiner Transformation in Informatica

Joiner Transformation in Informatica , is a connected and active transformation which let you join data from two heterogeneous source (same source system or different source system). Unlike normal SQL joins , we can join data from file system as well. [caption id="" align="alignnone" width="584"] Joiner Transformation in Informatica[/caption]

Must check : Please also check how to create Aggregator Transformation

Join Type

Normal Join A normal join will allow only those records which satisfy the joiner condition for both sources. So remaining records, who don’t match the condition get discarded Master Outer Join A master outer join will keeps all rows of data from the detail source and the matching rows from the master source. If any of master records don’t satisfy the condition , those rows get discarded. Detail Outer Join Just opposite to Master Outer join , it keep all rows of data from Master Source and the matching rows from Details Source. If  any of detail records don’t satisfy the condition , those rows get discarded. Full Outer Join A full outer join keeps all rows of data from both the master and detail sources.   Must Read : Learn more about informatica coding standard Example of Joiner Transformation in Informatica: A good example of Joiner transformation with different type of join can be found here. For explanation on joiner transformation , you can read it here Hope you enjoyed this tutorial on joiner transformation in informatica.

Java Transformation in Informatica with Example

Java Transformation in Informatica , is a connected and active /Passive transformation which provides a simple native programming interface to define transformation functionality with the Java programming language. You can use Java transformation in Informatica to quickly define simple or moderately complex transformation functionality without advanced knowledge of the Java programming language or an external Java development environment Must read : check more about Informatica Naming convention

Few of the sample scenarios are as follows:

• A Java transformation contains two input ports that represent a start date and an end date. You can generate an output row for each date between the start date and end date.
•  you can define transformation logic to loop through input rows and generate multiple output rows based on a specific condition.

Must check : Please also check how to create Aggregator Transformation Java Transformation: A very good example of Active Java transformation can be found here Extra read: Problem :  Create a Informatica mapping to convert subject wise marks (populated in different rows) for student  into  a single row for each student with marks in a separate column for each subject.

Source Data:

STUDENT_MARKS Table Data

STUDENT_NO	SUBJECT	MARKS
1	Math	87
1	Eng	66
1	Science	78
2	Math	45
2	Eng	64
2	Science	55
3	Math	46
3	Eng	89
4	Science	86

Target Data:

STUDENT_MARK_NEW :

STUDENT	MATH_MARK	SCIENCE_MARK	ENG_MARK
1	87	78	66
2	45	55	64
3	46	NULL	89
4	NULL	86	NULL

See solution here

Lookup Transformation in Informatica

Lookup Transformation in Informatica , is a connected/Unconnected and Passive transformation which let you look up data flat file/relation tables ,views or synonym. The Integration Service queries the lookup source based on the lookup ports in the transformation and a lookup condition. The Lookup transformation returns the result of the lookup to the target or another transformation . Example : you may lookup transformation on DEPT tables to get DEPT details based on DEPTNO as input port. Must check : Please also check how to create Aggregator Transformation Connected Lookup Transformation: A very well explained  example of Connected lookup transformation can be found here Unconnected Lookup Transformation: A very well explained  example of UnConnected lookup transformation can be found here Normally source of Lookup is either files or relational database, See here for explaination To improve the performance of lookup transformation , we will need to enable the cache , see here for  different type of cache lookup transformation. There are other guideline also given to improve the performance. A good article can be found here Also check here to see the  difference between Static cache and Dynamic Cache : http://www.tecktricks.com/difference-between-static-cache-and-dynamic-cache-in-informatica/