Oracle Database – Technical Questions Answers

  • What is Log Switch? – The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.


  • What is On-line Redo Log? – The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.


  • Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace? – All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.


  • What are the steps involved in Database Startup? – Start an instance, Mount the Database andOpen the Database.


  • What are the steps involved in Instance Recovery? – Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.


  • Can Full Backup be performed when the database is open? – No.


  • What are the different modes of mounting a Database with the Parallel Server? – Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
  • What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? – Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.


  • What are the steps involved in Database Shutdown? – Close the Database, Dismount the Database and Shutdown the Instance.


  • What is Archived Redo Log? – Archived Redo Log consists of Redo Log files that have archived before being reused.


  • What is Restricted Mode of Instance Startup? – An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.


  • What is Partial Backup? – A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.


  • What is Mirrored on-line Redo Log? – A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.


  • What is Full Backup? – A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.


  • Can a View based on another View? – Yes.


  • Can a Tablespace hold objects from different Schemes? – Yes.


  • Can objects of the same Schema reside in different tablespaces? – Yes.


  • What is the use of Control File? – When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.


  • Do View contain Data? – Views do not contain or store data.


  • What are the Referential actions supported by FOREIGN KEY integrity constraint? – UPDATE and DELETE Restrict – A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade – When a referenced row is deleted all associated dependent rows are deleted.


  • What are the type of Synonyms? – There are two types of Synonyms Private and Public


  • What is a Redo Log? – The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.


  • What is an Index Segment? – Each Index has an Index segment that stores all of its data.


  • Explain the relationship among Database, Tablespace and Data file.? – Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace


  • What are the different type of Segments? – Data Segment, Index Segment, Rollback Segment and Temporary Segment.


  • What are Clusters? – Clusters are groups of one or more tables physically stores together to share common columns and are often used together.


  • What is an Integrity Constrains? – An integrity constraint is a declarative way to define a business rule for a column of a table.


  • What is an Index? – An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.


  • What is an Extent? – An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.


  • What is a View? – A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)


  • What is Table? – A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.


  • What is a Synonym? – A synonym is an alias for a table, view, sequence or program unit.


  • What is a Sequence? – A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.


  • What is a Segment? – A segment is a set of extents allocated for a certain logical structure.


  • What is schema? – A schema is collection of database objects of a User.


  • Describe Referential Integrity? – A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.


  • What is Hash Cluster? – A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.


  • What is a Private Synonyms? – A Private Synonyms can be accessed only by the owner.


  • What is Database Link? – A database link is a named object that describes a “path” from one database to another.


  • What is a Tablespace? – A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together


  • What is Rollback Segment? – A Database contains one or more Rollback Segments to temporarily store “undo” information.


  • What are the Characteristics of Data Files? – A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.


  • How to define Data Block size? – A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.


  • What does a Control file Contain? – A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.


  • What is difference between UNIQUE constraint and PRIMARY KEY constraint? – A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? – A Cluster with an index on the Cluster Key 48.When does a Transaction end? – When it is committed or Rollbacked.


  • What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? – What are the factors that affect OPTIMIZER in choosing an Optimization approach? – Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.


  • What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? – The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.


  • What is the function of Optimizer? – The goal of the optimizer is to choose the most efficient way to execute a SQL statement.


  • What is Execution Plan? – The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.


  • What are the different approaches used by Optimizer in choosing an execution plan? – Rule-based and Cost-based.

Shell Script Tutorial for Oracle Applications

  • Steps to Register Shell Script as a concurrent program
  • Sample Shell Script to copy the file from source to destination
  • Basic Shell Script Commands

Steps to Register Shell Script as a concurrent program

step 1:
Place the <name>.prog script under the bin directory for your
applications top directory.

For example, call the script KO_DEMO.prog and place it under

step 2:
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called KO_DEMO.prog use this:

 ln -s $FND_TOP/bin/fndcpesr KO_DEMO

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

step 3:
Register the concurrent program, using an execution method of
‘Host’. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:

step 4:
Your script will be passed at least 4 parameters, from $1 to $4.

  $1 = orauser/pwd
  $2 = userid(apps)
  $3 = username,
  $4 = request_id

Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.

Sample Shell Script to copy the file from source to destination

#Note: If you see # in front of any line it means that its a comment line not the actual code
#** ********************************************************************
# Created By : Shivmohan P

# Creation Date : 19-Sep-2008
# Script Name : KO_demo.prog
# Description : This Script accepts three parameters
# 1)Data File Name 2)Source Directory Path 3)Target Directory Path
# Then copy the file from source location to target location.
# If copy fails send the error status/message to concurrent program so that user can see status.
# ========
# History
# ========
# Version 1 shivmohan 19-sep-2008 Created for users
#** ********************************************************************
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $4 : Concurrent Request ID
echo “————————————————–”
echo “Parameters received from concurrent program ..”
echo ” Time : “`date`
echo “————————————————–”
echo “Arguments : ”
echo ” Data File Name : “${DataFileName}
echo ” SourceDirectory : “${SourceDirectory}
echo ” TargetDirectory : “${TargetDirectory}
echo “————————————————–”
echo ” Copying the file from source directory to target directory…”
cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}
if [ $? -ne 0 ]
# the $? will contain the result of previously executed statement.
#It will be 0 if success and 1 if fail in many cases
# -ne represents not “equal to”

echo “Entered Exception”
exit 1
# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success
echo “File Successfully copied from source to destination”
exit 0
echo “****************************************************************”

Basic Shell Script Commands

# Create Directory
mkdir <dirname>
# Remove Directory
rmdir <dirname>
#remove folder with files
rm -r -f <dirname>
# Change Directory
cd <newpath>
# Create new file
vi <newfile.ext>
#insert data into file
vi <openfilename.ext>
esc i <make changes>
#Save file
esc :wq enter
# exit with out saving changes
esc :q! enter
# open existing file
vi <existingfilename.ext>
#remove file
rm <filename.ext>
# copy file with same name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>
# copy file with new name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>/<newfilename.ext>
# Move file with same name
mv <sourcedir>/<sourcefilename.ext> <destinationdir>
# move file with data appended to filename in the front
mv <sourcedir>/<sourcefilename.ext> <destinationdir>/`date+%H%M%d%m%y`<filename.ext>
#print line
echo “your text here to print”
#print date
echo `date`

Interview Questions for Oracle 11i Apps (Manufacturing)

Hello Friends, here some of Inventory Questions , mostly in context on Item Setup. It has been long time i work on inventory module, it used to be one of my favorites and strongest modules sometime. still inventory bring lot of good challenging work. hope you will benefit from these Q & A.


Q. Once an item is assigned to an organization, is it possible to remove this association at a later time?
A. If you have assigned an item to an organization, it can only be deleted from that organization if the item has NOT been transacted. If it has been transacted against you cannot delete the item from the organization. You can, however, make the status of the item “inactive” on the main region of the Item Define form INVIDITM in ‘Item Status’ field. This will prevent users from transacting against the item although the item will continue to be included in LOV’s. 

Q. How do you setup Unit of Measure for an item?
A. When transacting an item, it will always default to the defined Primary Unit of Measure, so if you need to use another UOM, you must select it from the List of Values when you transact it. This implies that you want the Primary Unit of Measure to be that which is used most often and which is likely to be the smallest unit of that type of item. Once you have set the primary UOM for an item, it CANNOT be changed.


Q. How do I change an item’s locator control method once I have transacted against it, for example from No Control to Prespecified?
A. You cannot change this attribute if there is onhand quantity for the item. You must first perform a miscellaneous issue of all quantity. Then the attribute can be changed and once the locator(s) are set up, you can perform a miscellaneous receipt to receive the quantity back into a specified subinventory/locator.


Q. How do I change an item’s Unit of Measure?
A. You CANNOT change the UOM for an item once the UOM is assigned to the item. The suggested solution is as follows: If the item has not been transacted you can delete the item and then re-add it with the correct UOM. If the item has been transacted and has no outstanding orders, purchase orders or WIP jobs you can do the following if approved by your management.
1. Rename the existing item ‘123’ to ‘123-Bad UOM’ and save the item. Any prior history for item ‘123’ will now be associated with item ‘123-Bad UOM’.
2. Add a new item ‘123’ with the correct UOM.


Q. How do I delete an Item Template I no longer use?
A. Suggested steps to follow to delete unwanted Item Templates:
1. Navigate Setup: Items: Templates – Find Item Templates form appears.
2. Click on the LOV, and select the Item Template that is to be removed.
3. Click the [Find] button on the Find Item Templates form. The Item Templates Summary form appears, with the Item Template selected.
4. Click the [Open] button on the Item Templates Summary form, and the Item Templates form appears.
5. From the menu, select Edit | Delete Record, then select Action | Save and Proceed. 6. Close the Item Templates Summary form. Be careful not to delete Item Templates you may need again as they are not retrievable once deleted.


Q. Is there a way to create custom item attributes?
A. No but you can create and define the descriptive flexfield for this form. This will allows you to associate additional data with an item such as an attribute. See the Oracle System Administrators
Users Guide and the Oracle Flexfields Manual for information on creating Descriptive Flexfields.


Q. Why does the application prompt you for Organization when you enter the Master Item form for the very first time even though you have only one Master Organization.
A. Once you setup a Multi-Org environment and you enter the form for the first time, there is the possibility there can be more than one Master Organization. The application
therefore prompts you for an organization which is then used to determine the Master Organization associated with it. In other words, you are choosing the Master Organization by choosing one of the Orgs that belongs to it.


Q. How do you change the control level for item attributes from master level to organization level for example. When I try to change this in the Attribute Control Form, I get the message: frm-40200: field protected against update.
A. First change value of the field called “status” to “not used”. You should then be able to change the control level of the attribute from master level to org level. Make sure and change to status back to what it was before the change and save the record.


Q. How do I inactivate an item?
A. On the Master Item Form (Inventory->Items->Master Items), change the item status field to “Inactive” or any another defined status which disables all attributes that make an item usable (stockable, shippable, orderable, etc.). The “Inactivate” status comes pre-seeded with the Application and is for this purpose.

Q. What characters can I use in an item name (including special characters)?
A. The only hard fast rule is not to use the character designated in the segment delimiter for the System Item Flexfield. Also, you will not be able to enter any characters not allowed by the valueset associate with the flexfield. It is recommended that you avoid the use of any char that can be used as a delimiter to avoid future problems. It is best to not use special characters like #@%& if at all possible as these may have a negative impact down the road. Some characters, like percent (%), underscore (_), and pound(#), have special meaning when performing queries by form as do the query operator characters (=!><).


Q. Can you change the costing enabled and inventory asset flags on an item that has already been transacted against even if there is no on-hand quantities for the item?
A. Yes, you can change the flags. If you are changing the flags from checked to unchecked you will receive a warning that the associated cost records will be deleted alerting you that the Cost of Goods will not be calculated any longer for transactions of this item. In effect what you are doing is changing the item from an asset item to an expense item. If you are changing the flags from being unchecked to checked then a database
trigger will fire and cause cost records to be added for this item. There is no warning that is given.


Q. When I create a new item, and choose to assign to All orgs, the application allows me to even though some of those organizations are restricted to my responsibility. Is this the way it is supposed to work? If I assign in an Organization by Organization fashion, I am not allowed to assign to the restricted organizations.
A. The situation described is standard functionality. Even if a responsibility has limited access to a certain number of organizations, the “Assign to All Organizations” check box will assign the item to all orgs, not just the ones limited to the user’s responsibility.


Q. How do you tell that an item is an expense item or asset item?
A. Navigate to the Organization Items form: Inventory->Items->Organization Items. Query up the item in question. Change to the “Costing” alternative region. The value of the “Inventory Asset Value” flag determines this. If checked, the item is an asset item.


Q. Is there a way to delete or disable an item revision once entered?
A. There is no way to delete a revision nor is there a way to disable it. It must remain there for historical data. The suggested workaround is to create a new item, transfer any Quantity on-hand from the old item to new item and delete the old item. The only down side is you will lose some transaction history.

For more interview Question click here

 Thanks – Shivmohan Purohit


Overview – Oracle Projects

Last few days I have been involved with the Oracle Projects module. I’ll try and put forth some basic learning of this module.

Oracle Projects is meant primarily for organizations that are project-oriented. Using this module, it becomes easy to track costs, budget and track the project status.


Oracle Projects consists of the following products:

• Oracle Project Costing

• Oracle Project Billing

• Oracle Project Connect for Microsoft Project

• Oracle Activity Management Gateway

• Oracle Project Analysis Collection Pack


Prior to Oracle Projects Setup, one has to setup the Set of Books in GL, setup Organization and Organization hierarchy in Oracle HRMS, define employees and job in HRMS and create customer in Oracle Receivables. However if Oracle Projects is being installed as a standalone package then one needs to define all above in Oracle Projects itself. Some other mandatory setups include defining locations, defining implementation options, defining Project Accounting periods, defining expenditure types and categories, define revenue categories, etc. Also one has to create a burdening hierarchy in Oracle HRMS which may vary from the Project or Task Organization hierarchy.


Invariably a Project is broken into a hierarchy of tasks as per Work Breakdown Structure (WBS, a hierarchy of tasks that rollup into a project) to manage project and task related information. One can define as many levels of tasks in a hierarchy in Oracle Projects. However, proper naming conventions need to be followed while naming projects, tasks and sub-tasks. An organization has to be associated with a project and a Task, which may be same or different.


One can have three different Project Types for managing the cost of a project: Indirect, Capital and Contract. An Indirect Project Type is used to track the overhead costs and labor hours for overhead activities like Admin, Legal, etc. Capital Project Type is selected to track costs and labor hours related to asset development activities which ultimately results in an asset for the organization. A Contract Project Type is selected in case the costs are reimbursed by a client.


Oracle Costing is the used for processing of expenditures for finding out costs which can be attributed to projects and tasks which can then be posted to GL corresponding to different account lines. There are two cost amounts associated to each transaction: Raw and Burdened. The raw cost is the actual cost of the work performed, and the burden cost is the indirect cost or overhead of work performed, like administrative cost. The Burden cost is calculated by multiplying Raw Cost with the Burden Multiplier. The burdened cost is the total cost that is incurred, i.e., the sum of raw cost and burden cost.

Burden Cost = Raw Cost x Burden Multiplier

Burdened Cost = Raw Cost + Burden Cost

More on Oracle Projects, primarily I’ll try and provide inputs on Burdening Cost and Oracle Project Billing in subsequent days.

Understanding Oracle SQL*Loader

SQL*Loader is an Oracle utility that enables you to efficiently load large amounts of data into a database. If you have data in a flat file, such as a comma-delimited text file, and you need to get that data into an Oracle database, SQL*Loader is the tool to use. This chapter introduces you to the SQL*Loader utility, discusses its control file, provides the syntax for using the SQL*Loader command, and provides examples of using SQL*Loader to load data into databases.


Using SQL*Loader, you can do the following:

Ø       Load data from a delimited text file, such as a comma delimited file

Ø       Load data from a fixed-width text file

Ø       Load data from a binary file

Ø       Combine multiple input records into one logical record

Ø       Store data from one logical record into one table or into several tables

Ø       Write SQL expressions to validate and transform data as it is being read from a file

Ø       Combine data from multiple data files into one

Ø       Filter the data in the input file, loading only selected records

Ø       Collect bad records—that is, those records that won’t load—into a separate file where you can fix them


Control Files

Control files, contain a number of commands and clauses describing the data that SQL*Loader is reading. Control files also tell SQL*Loader where to store that data, and they can define validation expressions for the data. Understanding control file syntax is crucial to using SQL*Loader effectively. The control file is aptly named, because it controls almost every aspect of how SQL*Loader operates. The control file describes the format of the data in the input file and tells SQL*Loader which tables and columns to populate with that data.


Understanding the SQL*Loader Commands

The syntax for

the SQL*Loader command looks like this:   sqlldr [param=value[, param=value…]]


SQL*Loader Parameters

Parameter Description

userid :   Passes in your username, password, and Net8 service name. The syntax to use is the same as for any other command-line utility, and looks like this: userid=username[/password][@service]


control: Passes in the control file name. Here’s an example: control=[path]filename[.ext] The default extension for control files is .ctl.


log:  Passes in the log file name. Here’s an example: log=[path]filename[.ext] The default extension used for log files is .log. If you don’t supply a file name, the log file will be named to match the control file.


bad Passes in the bad file name. Here’s an example: bad=[path]filename[.ext] The default extension for bad files is .bad. If you don’t supply a file name, the bad file will be named to match the control file. Using this parameter overrides any file name that may be specified in the control file.


data Passes in the data file name. Here’s an example: data=[path]filename[.ext] The default extension used for data files is .dat. Specifying a data file name on the command line overrides the name specified in the  control file. If no data file name is specified anywhere, it defaults to the same name as the control file, but with the .dat extension.


discard Passes in the discard file name. Here’s an example: discard=[path]filename[.ext]

The default extension used for discard files is .dis. If you don’t supply a file name, the discard file will be named to match the control file. Using this parameter overrides any discard file name that may be specified in the control file.


discardmax Optionally places a limit on the number of discarded records that will

be allowed. The syntax looks like this: discardmax=number_of_records If the number of discarded records exceeds this limit, the load is aborted.


skip Allows you to skip a specified number of logical records. The syntax looks like this:

skip=number_of_records Use the skip parameter when you want to continue a load that has

been aborted and when you know how far into the file you want to go before you restart.

load Optionally places a limit on the number of logical records to load into the database. The syntax looks like this:


load=number_of_records Once the specified limit has been reached, SQL*Loader will stop.


Errors Specifies the number of errors to allow before SQL*Loader aborts the load. The syntax looks like this: errors=number_of_records SQL*Loader will stop the load if more than the specified number of errors has been received. The default limit is 50. There is no way to allow an unlimited number. The best you can do is to specify a very high value, such as 999999999.


Rows indirectly controls how often commits occur during the load process. The rows parameter specifies the size of the bind array used for conventional-path loads in terms of rows. SQL*Loader will round that value off to be some multiple of the I/O block size. The syntax for the rows parameter looks like this: rows=number_of_rows The default value is 64 for conventional-path loads. Direct-path loads, by default, are saved only when the entire load is done. However, when a direct-path load is done, this parameter can be used to control the commit frequency directly.

4. Run the applypreferences script. 


Ø       SQL*Loader is a versatile utility for loading large amounts of data into an Oracle database.

Ø       SQL*Loader control files are used to describe the data being loaded and to specify the table(s) into which that data is stored.

Ø       You can use the INFILE clause to identify the file, or files, that you want SQL*Loader to read.

Ø       You can use the INTO TABLE clause to identify the table, and the columns within that table that you wish to populate using the data read from the input file.

Ø       You can use the APPEND option after the INFILE clause to tell SQL*Loader to insert data into a table that already contains data to begin with.

Ø       You can use SQL*Loader to load delimited data, such as comma-delimited data, or you can use it to load data stored in fixed-width columns.

Ø       SQL*Loader fully supports all of Oracle8i’s datatypes, even to the point of allowing you to populate LOB columns.


Thanks Shivmohan Purohit