Koteswar Oracle Apps Techno Functional Blog: Technical Interview Questions in Oracle Apps

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions fromone environment to another without reKeying? Will you use FNDLOAD?
Answer: FNDLOAD can not be used in the scenario. You can use migrator available in "Oracle iSetup" Responsibility

Question: This is a very tough one, almost impossible to answer, but yet I will ask. Which Form in Oracle Applications has most number of Form Functions?
Answer: "Run Reports". And why not, the Form Function for this screen has a parameter to which we pass name of the "Request Group", hence securing the list of Concurrent Programs that are visible in "Run Request" Form. Just so that you know, there are over 600 form functions for "Run Reports"

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not implement interactive messages, i.e. a message will give multiple options for Response. The best you can get from Forms Personalization to do is popup up Message with OK option.

Question: You have just created two concurrent programs namely "XX PO Prog1" & "XX PO Prog2". Now you wish to create a menu for Concurrent Request submission such that only these two Concurrent Programs are visible from that Run Request menu. Please explain the steps to implement this?
Answer:
a) Define a request group, lets say with name "XX_PO_PROGS"
b) Add these two concurrent programs to the request group "XX_PO_PROGS"
c) Define a new Form Function that is attached to Form "Run Reports"
d) In the parameter field of Form Function screen, enter
REQUEST_GROUP_CODE="XX_PO_PROGS" REQUEST_GROUP_APPL_SHORT_NAME="XXPO" TITLE="XXPO:XX_PO_PROGS"
e) Attach this form function to the desired menu.

Question: Does Oracle 10g support rule based optimization?
Answer: The official stance is that RBO is no longer supported by 10g.

Question: Does oracle support partitioning of tables in Oracle Apps?
Answer: Yes, Oracle does support partitioning of tables in Oracle Applications. There are several implementations that partition on GL_BALANCES. However your client must buy licenses to if they desire to partition tables. To avoid the cost of licensing you may suggest the clients may decide to permanently close their older GL Periods, such that historical records can be archived.
Note: Before running the archival process the second time, you must clear down the archive table GL_ARCHIVE_BALANCES (don’t forget to export archive data to a tape).

Question: What will be your partitioning strategy on GL_BALANCES? Your views please?
Answer: This really depends upon how many periods are regularly reported upon, how many periods are left open etc. You can then decide to partition on period_name, or period ranges, or on the status of the GL Period.

Question: Does Oracle support running of gather stats on SYS schema in Oracle Apps?
Answer: If your Oracle Applications instance is on 10g, then you can decide to run stats for SYS schema. This can be done by exec dbms_stats.gather_schema_stats('SYS');
Alternately using command dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);
I will prefer the former with default values.
If you wish to delete the stats for SYS use exec dbms_stats.delete_schema_stats('SYS');
You can schedule a dbms_job for running stats for SYS schema.

Question: Can you use concurrent program "Gather Schema Statistics" to gather stats on sys schema in oracle apps?
Answer: No, "Gather Schema Statistics" has no parameters for SYS schema. Please use dbms_job.

Question: Which table is used to provide drill down from Oracle GL into sub-ledger?
Answer: GL_IMPORT_REFERENCES

Question: What is the significance of profile option “Node Trust Level” in Oracle Apps.
Answer: If this profile option is set to a value of external against a server, then it signifies that the specific mid-tier is External i.e. it will be exposed to the www. In other words this server is not within the firewall of your client. The idea behind this profile option is to flag such middle-tier so that special restrictions can be applied against its security, which means a very restricted set of responsibilities will be available from such Middle-Tier.

Question: What is the significance of profile option “Responsibility Trust Level”.
Answer: In order to make a responsibility accessible from an external web tier, you must set profile option “Responsibility Trust Level” at responsibility level to “External”. Only those responsibilities that have this profile option against them will be accessible from External Middle tiers.

Question: What else can you suggest to restrict the access to screens from external web tiers?
Answer: You may use URL filtering within Apache.

Question: What is the role of Document Manager in Oracle Purchasing?
Answer: POXCON is an immediate concurrent program. It receives pipe signal from the application when a request is made for approval/reservations/receipts.

Question: How to debug a document manager in Oracle Apps?
Answer: Document manger runs within the concurrent manager in Oracle Applications. When an application uses a Document Manager, it sends a pipe signal which is picked up by the document manager.
There are two mechanisms by which to trace the document manager
1. Set the debugging on by using profile option
    STEP 1. Set profile option "Concurrent:Debug Flags" to TCTM1
    This profile should only generate debugs when set at Site level(I think, as I have only tried site), because Document Manager runs     in a different session.
    STEP 2. Bounce the Document Managers
    STEP 3. Retry the Workflow to generate debugs.
    STEP 4. Reset profile option "Concurrent:Debug Flags" to blank
    STEP 5. have a look at debug information in table fnd_concurrent_debug_info

2. Enable tracing for the document managers
This can be done by setting profile option “Initialization SQL Statement – Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.

Question: You have written a Java Concurrent Program in Oracle Apps. You want to modify the CLASSPATH such that new class CLASSPATH is effective just for this program.
Answer: In the options field of the concurrent program you can enter something similar to below.
-cp <your custom lib pathused by Java Conc Prog> :/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java

Question: How will you open a bc4j package in jdeveloper?
Answer: Oracle ships a file named server.xml with each bc4j package. You will need to ftp that file alongside other bc4j objects(VO’s, EO’s, AM, Classes etc).
Opening the server.xml will load the complete package starting from AM(application module). This is a mandatory step when building Extensions to framework.

Question: In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Answer: Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Question: In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?
Answer: You will need to do the below steps
a….Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
b…Enter below text( change colours as needed)
<style name="DarkBackground">
    <property name="background-color">#000066</property>
</style>
<style name="TextForeground">
    <property name="color">#0000FF</property>
</style>
c… cd $OA_HTML/cabo/styles/cache
d…Take a backup of all the css files.
e…Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps Self Service, the Framework will rebuild the css file if found missing for your browser.

Question: Can you extend and substitue a root AM ( Application Module) in OA Framework using JDeveloper.
Answer: You can extend the AM in jDeveloper, but it doesn’t work( at least it didn’t work in 11.5.9). I am hopeful that Oracle will deliver a solution to this in the future.

Question: In a workflow notification, you have a free text response field where the user enters the Vendor Number for the new vendor. You want to validate the value entered in the notification response field upon the submission of a response. How will you do it?
Answer: You will need to attach a post notification function to the Workflow Notification.
The PL/SQL code will look similar to below:-
The below code will display an error in the notification when user attempts to create a Duplicate Vendor Number.
PROCEDURE validate_response_from_notif
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid    IN NUMBER
,funcmode IN VARCHAR2
,RESULT   IN OUT VARCHAR2
) IS
l_nid                      NUMBER;
l_activity_result_code     VARCHAR2(200);
v_newly_entered_vendor_num VARCHAR2(50);
CURSOR c_get_response_for_new_vendor IS
    SELECT wl.lookup_code
    FROM   wf_notification_attributes wna
          ,wf_notifications           wn
          ,wf_message_attributes_vl   wma
          ,wf_lookups                 wl
    WHERE wna.notification_id = l_nid
    AND    wna.notification_id = wn.notification_id
    AND    wn.message_name = wma.message_name
    AND    wn.message_type = wma.message_type
    AND    wna.NAME = wma.NAME
    AND    wma.SUBTYPE = 'RESPOND'
    AND    wma.format = wl.lookup_type
    AND    wna.text_value = wl.lookup_code
    AND    wma.TYPE = 'LOOKUP'
    AND    decode(wma.NAME, 'RESULT', 'RESULT', 'NORESULT') = 'RESULT';
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
    l_nid := wf_engine.context_nid;
    OPEN c_get_response_for_new_vendor;
    FETCH c_get_response_for_new_vendor
      INTO l_activity_result_code;
    CLOSE c_get_response_for_new_vendor;
    v_newly_entered_vendor_num := wf_notification.getattrtext(l_nid,'NEWLY_ENTERED_VENDOR_NUM_4_PO');
    IF l_activity_result_code = 'NEW_VENDOR'
       AND does_vendor_exist(p_vendor => v_newly_entered_vendor_num)
    THEN
      RESULT := 'ERROR: VendorNumber you entered already exists';
      RETURN;
    END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
    RESULT := SQLERRM;
END validate_response_from_notif;

Question: How to make concurrent program end with warning?
Answer: If the concurrent program is of type PL/SQL, you can assign a value of 1 to the “retcode” OUT Parameter.
For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");

Question: How do you link a Host type concurrent program to Concurrent Manager?
Answer: Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO

Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
      ,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM   apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;

Question: How do you send a particular Oracle Apps Workflow Activity/Function within a workflow process into background mode.
Answer: If cost of the workflow activity is greater than 50, then the workflow activity will be processed in background mode only, and it won’t be processed in online mode.

Question: What are the various ways to kick-off a workflow
Answer: You can eiter use wf_engine.start_process or you can attach a runnable process such ghat it subscribes to a workflow event.

Question: When starting (kicking off) an oracle workflow process, how do you ensure that it happens in a background mode?
--a)if initiating the process using start_process, do the below
    wf_engine.threshold := -1;
    wf_engine.createprocess(l_itemtype
                           ,l_itemkey
                           ,'<YOUR PROCESS NAME>');
    wf_engine.startprocess(l_itemtype, l_itemkey)
--B) When initiating the workflow process through an event subscription, set the Execution Condition Phase to be equal to or above 100 for it to be executed by background process.

Question: On 10g, how will you use awr?
Answer: By running below scripts. These are both the same scripts, but with differing parameters.
$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql

Question : How will you configure Apache to run in Debug mode, specifically usefull when debugging iProcurement ( prior to 11.5.10).
Answer: After 11.5.10, FND Logging can be used for debugging Oracle iProcurement.
Prior to 11.5.10
----STEPS IN A NUTSHELL-----
cd $ORACLE_HOME/../iAS/Apache
vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/ssp_init.txt
    DebugOutput=/home/<<SID>>/ora9/iAS/Apache/Apache/logs/debug.log
    DebugLevel=5
    DebugSwitch=ON

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.conf
    ApJServLogLevel debug

vi $ORACLE_HOME/../iAS/Apache/Jserv/etc/jserv.properties
    log=true

Question: How will you add a new column to a List Of Values ( LOV ) in Oracle Applications Framework? Can this be done without customization?
Answer: Yes, this can be done without customization, i.e. by using OA Framework Extension coupled with Personalization. Implement the following Steps :-
a) Extend the VO ( View Object ), to implement the new SQL required to support the LOV.
b) Substitute the base VO, by using jpximport [ similar to as explained in Link ]
c) Personalize the LOV Region, by clicking on Add New Item. While adding the new Item, you will cross reference the newly added column to VO.

Question: Can you do fnd_request.submit_request from SQL Plus in Oracle?
Answer: You will need to initialize the global variables first using fnd_global.initialize
DECLARE
    v_session_id INTEGER := userenv('sessionid') ;
BEGIN
fnd_global.initialize
(
SESSION_ID        =>    v_session_id
,USER_ID                =>    <your user id from fnd_user.user_id>
,RESP_ID                =>    <You may use Examine from the screen PROFILE/RESP_ID>
,RESP_APPL_ID           =>    <You may use Examine from the screen PROFILE/RESP_APPL_ID>
,SECURITY_GROUP_ID      =>    0
,SITE_ID                =>    NULL
,LOGIN_ID               =>    3115003--Any number here
,CONC_LOGIN_ID          =>    NULL
,PROG_APPL_ID           =>    NULL
,CONC_PROGRAM_ID        =>    NULL
,CONC_REQUEST_ID        =>    NULL
,CONC_PRIORITY_REQUEST =>    NULL
) ;
commit ;
END ;
/
Optionally you may use fnd_global.apps_initialize, which internally calls fnd_global.initialize
fnd_global.apps_initialize(user_id => :user_id,
                             resp_id => :resp_id,
                             resp_appl_id => :resp_appl_id,
                             security_group_id => :security_group_id,
                             server_id => :server_id);
By doing the above, your global variables upon which Concurrent Managers depend upon will be populated. This will be equivalent to logging into Oracle Apps and submitting the concurrent request from a responsibility.

Question: You are told that the certain steps in the Oracle Apps Form/Screen are running slow, and you are asked to tune it. How do you go about it.
Answer: First thing to do is to enable trace. Preferably, enable the trace with Bind Variables. This can be done by selecting menu Help/Diagnostics/Trace/”Trace With Binds and Wait”
Internally Oracle Forms issues a statement similar to below:-
alter session set events='10046 trace name context forever, level 12' ;

Question: What is the difference between running Gather Stats and “Program – Optimizer[RGOPTM]” in Oracle General Ledger?
Answer: “Gather Stats” will simply gather the stats against existing tables, indexes etc. However Gather Stats does not create any new indexes. But “Program – Optimizer[RGOPTM]” can create indexes on GL_CODE_COMBINATIONS, provided accounting segment has the indexed flag enabled,

Question: You have written a piece of code in POR_CUSTOM_PKG for Oracle iProcurement, but its not taking any effect? What may be the reason?
Answer: Depending upon which procedure in POR_CUSTOM_PKG has been programmed, one or more of the below profile options must be set to Yes
POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization

Question: What is the key benefit of punching out to suppliers catalogs rather than loading their catalogs locally in Oracle iProcurement?
Answer: Punchout has several advantages like, Catalogs don’t need to be loaded locally saves space on your system. You can get up-to-date list of catalogs by punching out and also you get the benefit of up-to-date pricing information on vendor items.

Question: Does oracle have a test environment on exchange?
Answer: http://testexchange.oracle.com

Question: Does Oracle Grants use its own schema or does it uses Oracle Project Accounting schema?
Answer: Although Oracle Grants has its own schema i.e. GMS, it reuses many of the tables with in Oracle Projects Schema like PA_PROJECTS_ALL, PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURE_TYPES etc.

Question: How to make an Oracle Report Type concurrent program produce an excel friendly output?
Answer: Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.
For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;

Question: What are the settings needed for printing bitmap reports?
Answer: Get your DBA to configure two files i.e. uiprint.txt & default.ppd
For details, refer to Metalink Note 189708.1

Question: For a PL/SQL based concurrent program do you have to issue a commit at the end?
Answer: The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.

Question: What is the best way to add debugging to the code in apps?
Answer: Use fnd_log.string , i.e. FND Logging. Behind the scenes Oracles FND Logging uses autonomous transaction to insert records in a table named fnd_log_messages.
For example
DECLARE
BEGIN
    fnd_log.STRING(log_level => fnd_log.level_statement
                  ,module    => 'xxxx ' || 'pkg/procedurename '
                  ,message   => 'your debug message here');
END ;
Three profile options effecting FND Logging are
FND: Debug Log Mode
FND: Debug Log Enabled
FND: Debug Log Module

Question: If you wish to trigger of an update or insert in bespoke table or take some action in response to a TCA record being created or modified, how would you do it? Will you write a database triggers on TCA Tables?
Answer: There are various pre-defined Events that are invoked from the Oracle TCA API’s.
TCA was Oracle’s first initiative towards a fully API based approach, which means the screen and the processes all use the same set of APIs for doing same task.
In order to take an action when these events occur, you can subscribe a custom PL/SQL procedure or a Custom Workflow to these events. Some of the important TCA events are listed below:-
oracle.apps.ar.hz.ContactPoint.update
oracle.apps.ar.hz.CustAccount.create
oracle.apps.ar.hz.CustAccount.update
oracle.apps.ar.hz.CustAcctSite.create
oracle.apps.ar.hz.CustAcctSite.update
oracle.apps.ar.hz.CustAcctSiteUse.create
oracle.apps.ar.hz.CustAcctSiteUse.update
oracle.apps.ar.hz.Location.create
oracle.apps.ar.hz.Location.update
oracle.apps.ar.hz.Organization.create
oracle.apps.ar.hz.Organization.update
oracle.apps.ar.hz.PartySite.create
oracle.apps.ar.hz.PartySite.update
oracle.apps.ar.hz.PartySiteUse.create
oracle.apps.ar.hz.PartySiteUse.update
oracle.apps.ar.hz.Person.create
oracle.apps.ar.hz.Person.update

Question: In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?
Answer: The MDS document details are loaded into database, in the following sets of tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XMLImporter, as detailed in XMLImporter Article

Question: In a Oracle Report data group, you have a “data link” between two queries. How do you ensure that the data link is made Outer Joined?
Answer: The data link is an Outer Join by default.

Question: How does substitution work in OA Framework?
What are the benefits of using Substitution in OA Framework?
Answer: Based on the user that has logged into OA Framework, MDS defines the context of the logged in user. Based upon this logged in context, all applicable personalization are applied by MDS. Given that substitutions are loaded as site level personalizations, MDS applies the substituted BC4J objects along with the personalizations. The above listed steps occur as soon as Root Application module has been loaded.
The benefit of using Substitution is to extend the OA Framework without customization of the underlying code. This is of great help during Upgrades. Entity Objects and Validation Objects can be substituted. I think Root AM’s can’t be substituted given that substitution kicks off after Root AM gets loaded.

Question: In OA Framework, once your application has been extended by substitutions, is it possible to revert back to remove those substitutions?
Answer: yes, by setting profile option “Disable Self-Service Personal%” to Yes, keeping in mind that all your personalizations will get disabled by this profile option. This profile is also very useful when debugging your OA Framework based application in the event of some error. By disabling the personalization via profile, you can isolate the error, i.e. is being caused by your extension/substitution code or by Oracle’s standard functionality.

Question: How can you import invoices into Oracle Receivables?
Answer: You can either use AutoInvoice by populating tables RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL & RA_INTERFACE_SALESCREDITS_ALL.
Alternately you may decide to use API ar_invoice_api_pub.create_single_invoice for Receivables Invoice Import.

Question: How do you setup a context sensitive flexfield
Answer: Note: I will publish a white paper to sho step by step approach.
But for the purpose of your interview, a brief explanation is…a)Create a reference field, b) Use that reference field in “Context Field” section of DFF Segment screen c) For each possible value of the context field, you will need to create one record in section “Context Field Value” ( beneath the global data elements).

Question: Does Oracle iProcurement use same tables as Oracle Purchasing?
Answer: Yes, iProcurement uses the same set of requisition tables as are used by Core Purchasing.

Question: What is the name of the schema for tables in tca
Answer: AR (at least till 11.5.10, not sure about 11.5.10).

Question: Are suppliers a part of TCA?
Answer: Unfortunately not yet. However, Release 12 will be merging Suppliers into TCA.

Question: What is the link between order management and purchasing
Answer: Internal Requisitions get translated into Internal Sales Orders.

Question: How would you know if the purchase order XML has been transmitted to vendor, looking at the tables.
Answer: The XML delivery status can be found from a table named ecx_oxta_logmsg. Use the query below
SELECT edoc.document_number
      ,decode(eol.result_code, 1000, 'Success', 'Failure') AS status
      ,eol.result_text
FROM   ecx_oxta_logmsg   eol
      ,ecx_doclogs       edoc
      ,ecx_outbound_logs eog
WHERE edoc.msgid = eol.sender_message_id
AND    eog.out_msgid = edoc.msgid
ORDER BY edoc.document_number

Question: You have done forms personalization, now how will you move it from one environment to another?
Answer: Use FNDLOAD. For examples visit FNDLOAD Article

Question: What are the key benefits of forms personalization over custom.pll?
Answer:
-->Multiple users can develop forms personalization at any given point in time.
-->It is fairly easy to enable and disable forms personalizations.
-->A programmer is not required to do simple things such as hide/disable fields or buttons.
-->Provides more visibility on customizations to the screen.

Question: Tell me some limitations of forms personalization when compared to CUSTOM.pll?
Answer:
-->Can't create record group queries, hence can’t implement LOV Query changes.
-->Can't make things interactive, i.e. can’t have a message box that gives multiple choices for example Proceed or Stop etc.

Question: Give me one example where apps uses partitioning?
Answer: WF_LOCAL_ROLES

Question: Give me one example of securing attributes in iProcurement.
Answer: You can define Realm to bundle suppliers into a Category. Such realm can then be assigned to the User using Define User Screen. Security Attribute ICX_POR_REALM_ID can be used. By doing so, the user will only be made visible those Punchout suppliers that belong to the realm against their securing attributes.

Question: Can you send blob attachments via workflow notifications?
Answer: Yes, you can send BLOB Attachments.

FORM

What is an SQL *FORMS?

SQL FORMS as known previously and SQL * FORMS in version 2, is more or less like an interface to interact with the oracle database. The form is in *.fmb format and compiled into an executable (*.fmx). These forms provide a means to add GUI elements like scroll bars, menus etc and reduce the efforts of writing complex queries.

Oracle - What is an SQL *FORMS? - August 28, 2008 at 17:10 pm by Amit Satpute

What is an SQL *FORMS?

Oracle Forms is part of Oracle's Internet Developer Suite. Its earlier versions were called SQL*Forms.

Oracle Forms is a 4GL Rapid Application Development (RAD) environment.

A Forms Builder is used to create applications to enter, access, change, or delete data from Oracle databases.

The Forms Runtime environment is needed to execute compiled Forms modules

How do you control the constraints in forms?

This can be done by selecting the Use Constrain Property to ON.
You can suppress or customize error messages in Forms. This can be done by setting the message level of the system variable SYSTEM.MESSAGE_LEVEL. The other way to do this is by using the ON-ERROR / ON-MESSAGE triggers.

When will ON-VALIDATE-FIELD trigger executed?

ON-VALIDATE-FIELD trigger is fired when an existing value is attempted to override the fields default behavior. For instance a user expects a city field to be mandatory and if on filling the form it is left blank, a validation error occurs.
Oracle - ON-VALIDATE-FIELD trigger - August 28, 2008 at 17:10 pm by Amit Satpute

When will ON-VALIDATE-FIELD trigger executed?

ON-VALIDATE-FIELD triggers are used for field validation. It fires when the field validation status is New or changed. However, if the field status is already valid then any further change to the value in the field will not fire this trigger.

What is the difference between system.current_field and system.cursor_field?

system.current_field gives the name of the field while system.cursor_field gives the name of the field where the cursor is lying along with the block name.
Oracle - difference between system.current_field and system.cursor_field - August 28, 2008 at 17:10 pm by Amit Satpute

What is the difference between system.current_field and system.cursor_field?

The only difference between these two is that System.current_field gives name of the field and System.cursor_field gives name of the field with block name.

What are dynamic reports? How will you create them?

Following steps should be followed to build a Dynamic Report:

Create a temporary table first.
Then insert data to the temporary table using the After Form Trigger.
Generate the report using this temporary table in the Data Model.
Delete all records from the temporary table in the After Report trigger.

Difference between Oracle Forms and Apps Forms.

Oracle Forms

Oracle Forms renders applications using metadata stored in an .fmx file.
It runs client-side PL/SQL.
It is accessed using a web browser and its user interface is rendered using a JVM.
It uses exact positioning.
It provides robust field-level validation and event processing.
It uses BI Beans as its integrated charting engine.
It supports a range of locking models with pessimistic as the default.
Each connected user in It maintains a synchronous connection to the Oracle database.
It uses synchronous connections to allow transactions to span multiple screen interactions.
With Oracle Forms, Application logic is processed in the Oracle database, a mid-tier Forms Server, or in the rich client.

Apps Forms

It renders applications using metadata stored in an Oracle database.
It uses server-side PL/SQL.
It is also invoked from a Web browser but its user interface is HTML and JavaScript.
It uses HTML-relative positioning.
It supports declarative page-level validation and event processing. Programmatic field-level validation and event processing requires Javascript and AJAX.
It uses Flash Charts as its integrated charting engine.
Due to its asynchronous architecture, It uses an optimistic locking model.
It does not transparently allow transactions to span page views. It programmatically supports transactions spanning page views using collections.
Its users are asynchronously connected to the Oracle database.
With Apps Forms, PL/SQL application logic is processed within the Oracle database. Client-side logic is implemented using JavaScript. HTTP communications are facilitated using Apache and Mod/PLSQL.

Both Oracle Forms and Apps Forms support the calling of Web Services, for example BPEL.

Sequence of firing triggers in forms.

The following sequence should be used to fire triggers when a form opens:
1. Pre-Form
2. Pre-Block
3. Pre-Record
4. Pre-Text-Item
5. When-New-Form-Instance
6. When-New-Block-Instance
7. When-New-Record-Instance
8. When-New-Item-Instance
The folowing sequence should be followed when you exit from the form:
1. post text item
2. post record
3. post block
4. post form

What is a record Group? What are different types of record group?

A record group is an oracle forms data structure that has a column/row framework similar to a database table.

There are three types of record groups: query record groups, non-query record groups, and static record groups.

Query record group
A query record group is a record group that has an associated SELECT statement. Query record groups can be created and modified at design time or at runtime.

Non-query record group
A non-query record group is a group that does not have an associated query. Non-query record groups can be created and modified only at runtime.

Static record group
A static record group is not associated with a query. Static record groups can be created and modified only at design time.

Explain the security features in Oracle.

Data abstraction has been achieved in Oracle by separating the client and the server side logic. Therefore the client applications cannot manipulate the data. The triggers perform content based auditing and selectively disable application updates.
Access control can be achieved in Oracle by allowing the users to manipulate the data by using only their definer’s permitted previledges

What are roles? How can we implement roles?

Roles in oracle restrict access to the different database objects. They are most useful when there are many database users. Once a role is created it can be granted to a user.
Example:
Create a role:-
Create role select_data;
Grant select on student. Employee, salary to select_data
Grant to a user:
Grant select_data TO John;
Revoke a role:-
Revoke select_date FROM John;

What is user Account in Oracle database?

A user account is given to a user for accessing the database. This user account is used to manage the database. Each account is also given privileges in order to restrict access.
Oracle - What is user Account in Oracle database? - August 28, 2008 at 17:10 pm by Amit Satpute

What is user Account in Oracle database?

A user account is a schema which is used to store database objects, applications, and components, and to determine a user's database privileges

What is a trace file and how is it created?

The files that are created when an oracle background process encounters an exception are Trace files.
Processes like dbwr, lgwr, pmon, smon create them.
Trace files are also created by ORA-00600 error or due to some diagnostic dump events

What are Tablespace Quota and default tablespace?

A tablespace is a logical storage unit within an Oracle database because it is not visible in the file system of the machine, the database resides on.
The Tablespace quotas are used to assign users with limited size on tablespaces.
Default tablespaces are used for users who are not configured with default tablespaces

What is Auditing? What are the different Levels of Auditing?

Auditing stores the tracking of events.

Statement Level:- Statement level auditing is tracking of sql statements.

Privilege level:- Privilege level auditing is tracking of sql statements of privileged users who have been granted the rights to execute the statement.

Object level:- Object level auditing is used to audit specific schema objects irrespective of user.

Oracle - What is Auditing? What are the different Levels of Auditing? - August 28, 2008 at 17:10 pm by Amit Satpute

What is Auditing? What are the different Levels of Auditing?

Auditing includes reading, writing and deleting the access on the objects at the table level. The privileges granted to a user can be audited.

There are three levels of auditing:

Statement level
Object level
Privilege level

Explain Statement Auditing, Privilege Auditing, And Object Auditing.

There are three levels of auditing:
Statement level: Statements found in stmt_audit_option_map are audited.
Object level: objects like tables, views, sequences, packages, stored procedures, stored functions are sudited.
Privilege level: All system privileges that are found in system_privilege_map are audited

What are the steps to creating a password authentication file?

A Password file in Oracle can be created using the ORAPWD command.
The command below creates a file pass.pwd for 30 users and temporary password as “tempo”
ORAPWD FILE=pass.pwd PASSWORD= tempo ENTRIES=30

Oracle - steps to creating a password authentication file - August 28, 2008 at 17:10 pm by Amit Satpute

What are the steps to creating a password authentication file?

The steps are :

First set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in init.ora file
Then dbs$orapwd file=orapw$ORACLE_SID password=sys force=y
Then startup force;
Then grant sysdba to user(any user in the database);
Then conn user/user
Then conn user/user as sysdba
Then show user

What is configure command and recovery catalog?

A recovery catalog is a schema stored in a database. It tracks backups and stores scripts for use in RMAN backup and recovery situations.
Configure command used in RMAN,is used to configure the parameters for the RMAN database.
It is used while making backups and recoveries.

What are different types of backup? (Hot, Cold, logical, Physical)?

Cold Backup
In this type of backup, after the database is shut down, DBA exits the SVRMGR utility and copies the log files, data files and control files onto a backup media.Once the backup has been completed the DBA can restart the database.
Physical Backup
The operating system saves the database files onto tape or some other media. This is useful to restire the system to an earlier point whenever needed.
Logical Backup
In logical backup technique, the IMPORT/EXPORT utilities are used to create the backup of the database. A logical backup backs-up the contents of the database. A logical backup can be used to restore the database to the last backup. However, unlike physical back, it should not be used to create an OS back up copy because restoring using this approach would make it possible to correct the damaged datafiles. Therefoe in these situations physical backups should be preferred.
Hot backup
A few systems that need to support continuous operation, it is difficult to bring down the database without interrupting the service provided to the users. In such cases, hot backup approach should be used.
There are two modes in which the hot backup works:
ARCHIEVELOG mode
NOARCHIVELOG mode
The database operations are suspended until archiving has been completed if the on-line redo log files are filled before they can be archived.
It is important that the directory containing the archived log files doesnt become full because if it does the ARCH thread wouldnt be able to archive the redo log files. The DBA has to continuously monitor the used-space percentage in the archive directory.
The database can be placed into ARCHIVELOG mode at the creation time or after the database has been created.
However, the database is shutdown before connecting as the user INTERNAL.
It was originally created in NOARCHIVELOG mode, this technique can be used to place the database in ARCHIVELOG mode

Whats the Concept of Flashback Query in Oracle (10g in particular) and how is it beneficial?

Due to human errors, valuable data gets deleted or misentered. Oracle Flashback Technology provides a set of new features to view and undo the data back and forth.

The benefits of flashback quering are:

- The capability of querying the historical data,
- perform change analysis, and
- perform self-service repair to recover from logical corruptions while the database is online

What is mean live lock and deadlock? How to prevent it?

A deadlock can occur when two or more processes that strive to acquire resources are not able to acquire them due to some of the following reasons:

Two processes may be interdependent upon each other,
If there are more than 2 processes, then if a graph is plotted, then there could be a cycle,
A server maintains a graph. The processes acquiring the resources would intimate the server about their requirement. If the server detects an occurance of a deadlock, the user process is be terminated.

In a livelock, a request for an exclusive lock is denied repeatedly due to a series of overlapping shared locks that keep interfering.

An SQL Server detects this situation after four denials and then refuses further shared locks.

When a write transaction is forced to wait indefinitely due to read transactions that monopolize a table or a page then a live lock occurs

What is Partial Backup?

A partial backup is similar to a full database backup, but a it does not contain all the filegroups.
It contains all the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files

What is Mirrored on-line Redo Log?

Mirrored on-line Redo logs are mirror or copies of actually on-line redo log files. These files are physically located on another disc and are updated if one member is updated. Mirroring the on-line Redo log files is essential to not loose data.
Oracle - What is Mirrored on-line Redo Log? - August 28, 2008 at 17:10 pm by Amit Satpute

What is Mirrored on-line Redo Log?

Due to Mirrored online redo logs and disk array mirroring, the occurrence of redo log corruptions has dropped to practically zero. The On-line Redo Log is a set of two or more on-line redo files that record all committed changes made to the database.

Explain the advantages of PL/SQL.

PL/SQL is a transaction processing language that offers the following advantages:

support for SQL - SQL is flexible, powerful and easy to learn.
support for object-oriented programming
better performance - with PL/SQL, an entire block of statements can be sent to Oracle at one time.
higher productivity - PL/SQL increases productivity by enabling use of better tools.
full portability - PL/SQL are portable to any operating system and platform on which Oracle runs.
tight integration with Oracle
security PL/SQL can achieve data abstraction, access control.

Explain block structure of PL/SQL.

DECLARE
--------declarations
BEGIN
--------statements
EXCEPTIONS
--------handlers
END;
The order of the parts is quite logical:
First comes the declarative part, in which items can be declared.
Items can be manipulated in the executable part.
Exceptions raised during can be handled in the exception-handling part

What is large object in oracle? Explain its purposes.

Large objects (LOB’s) are exclusively used to hold large amounts of data. It can hold data in tetra bytes. Different types of LOBs include internal, external, persistent and temporary. Binary LOB’s are typically used to store graphics, video, or audio data.

Purpose:

Enables you to access and manipulate the data efficiently in your application
Is optimized for large amounts of data
Provides a uniform way of accessing data stored within the database or outside the database

Explain types of large objects in oracle, i.e. BLOB, LLOB,NCLOB and BFILE.

Internal LOBs:
BLOBs, CLOBs, and NCLOBs are Internal LOBs stored inside database tablespaces to optimize space and efficient access. Changes to internal LOBs can be committed or rolled back.

They use copy semantics and participate in the transactional model of the server and can be recovered on media failure.

The ACIDFoot 1 properties are applicable to internal LOBs too.

BLOB
The BLOB data type stores binary large objects. BLOB can store up to 4 gigabytes of binary data.
CLOB
The CBLOB data type stores character large objects. CLOB can store up to 4 gigabytes of character data.
NCLOB
The NCBLOB data type stores character large objects in multibyte national character set. NCLOB can store up to 4 gigabytes of character data.

External LOBs:

BFILES
BFILES are External LOBs stored in operating system files outside database tablespaces. These files use reference semantics.

The BFILE datatype allows read-only byte stream access to large files on the file system of the database server. The maximum file size supported is 4 gigabytes.

Define read-only replication and its uses.

Read only replication creates local copy of table data originating from one or more remote master tables. An application can query the data in a read-only table snapshot, but cannot insert, update, or delete rows in the snapshot of the data.

Use

Can be treated as back ups of original.
For faster query performance, read only replication is used.

Read-only replication and snapshots.

Replication is the process of copying database into one or more databases to make a system distributed.

Changes made to one database are forwarded and applied to the databases at the rest of the locations.

Advantages of Replication:

Provides user with fast, local access to shared data,
Protects availability of applications
Even if one site becomes unavailable, users can continue to query or even update the remaining locations.

Read-only snapshots are used for query only. Changes made to the primary database are replicated to the snapshot by the Mobile Client.

Snapshot

A snapshot is a full set or a subset of rows of a table or view at a point in time.
A snapshot is created by executing a SQL query against a base table.
Snapshots are either read-only or updateable.
They vary in complexity.

Explain the steps executed during SQL statement processing.

The parser scans the statement and breaks it into logical units such as keywords, identifiers and operators.
A query or a sequence tree is built using the units above. This is done to transform the source data into the format required by the result set.
The Query optimizer analyzes the fastest way to access the source tables using minimum resources. The final optimized version of the updated query tree is called as execution plan.
Now, the relational engine starts to execute this plan. the relational engine requests that the storage engine pass up data from the row sets requested from the relational engine.
In turn, the relational engine processes this data into the format as desired by result set and returns the same.

Oracle - steps executed during SQL statement processing - Feb 14, 2009 at 15:00 PM by Amit Satpute

Explain the steps executed during SQL statement processing.

Steps in Processing SQL Statements

Prepare statement
Define an application request
Bind placeholders
For DML statements and queries with input variables, perform one or more bind calls to bind the address of each input variable array to each placeholder in the statement.
Execute statement
Describe select-list items
Define output variables
For queries, perform one or more define calls to define an output variable for each select-list item in the SQL statement
Fetch and process data

Explain the steps executed during a typical transaction.

A transaction contains one or more SQL statements.
It is an atomic unit.
It begins with the first executable SQL statement.
It ends when it is committed or rolled back.
The effects of all the SQL statements in a transaction can be either all committed or all rolled back.

Explain how oracle optimizer works.

Oracle optimizer determines the best possible way to execute a query. This optimizer uses internal set of rules or costing methods to ace hove the result. The output from the optimizer is a plan that describes an optimum method of execution the optimizer first evaluates expressions and conditions containing constants as fully as possible. Here after it chooses either a cost-based or rule-based approach and determines the goal of optimization. For each table accessed it chooses one access path from the available ones
Oracle optimizer - Feb 14, 2009 at 15:00 PM by Amit Satpute

Explain how oracle optimizer works.

The optimization is determining the most efficient way to execute a SQL statement after considering the factors related to the objects referenced and the conditions specified in the query.
This determination is an important step in the processing of any SQL statement and can greatly affect execution time.
The optimizer first evaluates expressions and conditions containing constants.Then Statement transformation follows which is followed by Choice of optimizer approaches. Then is the Choice of access paths followed by Choice of join orders and finally the Choice of join methods.

Describe optimization methods, i.e. cost-based approach and role-based optimization.

Oracle decides how to retrieve the necessary data whenever a valid SQL statement is processed.

This decision can be made using one of two methods:

Rule Based Optimizer

If the server has no internal statistics relating to the objects referenced by the statement then the RBO method is used. This method will be deprecated in the future releases of oracle.

Cost Based Optimizer

The CBO method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost based on the system resources.

Oracle optimizer - May 05, 2009 at 18:00 PM by Rajmeet Ghai

Explain the rule-based optimizer and cost-based optimizer.

In a Rule-based optimizer, based on the access paths and their ranks, an execution plan is chosen. Ranking of the access path is heuristic and operation with the lower rank is used if there is more than one way to execute a SQL statement.

Cost-based optimizer - The most advised approach and uses an SQL statement and tries to weigh different ways (plan) to execute it. It also assigns a cost to each plan and chooses the plan with the smallest cost. CBO uses some statistics to process the cost across different plans.

Describe in brief about oracle database tuning.

Oracle includes many performance tuning enhancements like:

Automatic Performance Diagnostic and Tuning Features
Automatic Shared Memory Management - Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA
Wait Model Improvements - A number of views have been updated and added to improve the wait model.
Automatic Optimizer Statistics Collection - gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB
Dynamic Sampling - enables the server to improve performance
CPU Costing - default cost model for the optimizer (CPU+I/O), with the cost unit as time
Optimizer Hints
Rule Based Optimizer Obsolescence - No more used
Tracing Enhancements - End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id
SAMPLE Clause Enhancements
Hash Partitioned Global Indexes

What is proactive tuning and reactive tuning?

Tuning aims to increase and optimize Oracle’s throughput. In Proactive tuning, different strategies are developed for different kinds of processing. Here, the database is tuned by finding the optimal hardware selection, capacity planning and settings to increase throughput. It is called proactive as the administrator proactively devises strategies for the architecture. Reactive tuning involves performance tuning and troubleshooting within boundaries of existing architecture.
Oracle - What is proactive tuning and reactive tuning? - March 03, 2009 at 22:00 PM by Amit Satpute

What is proactive tuning and reactive tuning?

In Proactive Tuning, the application designers can then determine which combination of system resources and available Oracle features best meet the needs during design and development.
In reactive tuning the bottom up approach is used to find and fix the bottlenecks. The goal is to make Oracle run faster.

Operating system level tuning.

System-level tuning involves the following steps:

Monitoring the operating system counters using a tool such as top, gtop, and GKrellM or the VTune analyzer’s counter monitor data collector for applications running on Windows.
Interpreting the counter data to locate system-level performance bottlenecks and opportunities for improving the way your application interacts with the system.

SQL-level tuning.

Tuning disk and network I/O subsystem to optimize the I/O time, network packet size and dispatching frequency is called the server kernel optimization.
Distribution of data can be studied by the optimizer by collecting and storing optimizer statistics. This enables intelligent execution plans.
Choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.
Tuning SQL Access workload with physical indexes and materialized views.

Database design level tuning

The steps involved in database design level tuning are:

Determination of the data needed by an application (what relations are important, their attributes and structuring the data to best meet the performance goals)
Analysis of data followed by normalization to eliminate data redundancy.
Avoiding data contention.
Localizing access to the data to the partition, process and instance levels.
Using synchronization points in Oracle Parallel Server.
Implementation of 8i enhancements that can help avoid contention are:
Consideration on partitioning the data
Consideration over using local or global indexes.

Explain rule-based optimizer and cost-based optimizer.

Oracle decides how to retrieve the necessary data whenever a valid SQL statement is processed.
This decision can be made using one of two methods:

Rule Based Optimizer

If the server has no internal statistics relating to the objects referenced by the statement then the RBO method is used.
This method will be deprecated in the future releases of oracle.

Cost Based Optimizer

The CBO method is used if internal statistics are present.
The CBO checks several possible execution plans and selects the one with the lowest cost based on the system resources.

Describe how to reference remote tables in procedures with an example.

Using SQL statements, remote tables can be accessed in procedures. Database links are specifically used for this purpose. They connect one oracle database to another.
CREATE DATABASE LINK employee.in.india.acme_auto.com ... ;
To access a remote table, the table name can be specified using a SQL statement.
SELECT * FROM salary.employee@employee.in.india.acme_auto.com ;
Oracle - significance of Return clause in stored procedure - Feb 18, 2010 at 11:00 PM by Rajmeet Ghai

Explain the significance of Return clause in stored procedure.

Return clause in a stored procedure returns the value to the calling programs subroutine.
Example:
The procedure below returns the salary for the employee id passed.
CREATE OR REPLACE FUNCTION GET_EMPLOYEE_SALARY
(
         p_employee_id NUMBER
)
RETURN FLOAT
IS p_salary FLOAT(25);
BEGIN
          SELECT salary INTO p_salary FROM EMPLOYEES
          WHERE EMPLOYEE_ID = p_employee_id;
          RETURN p_salary;
END GET_EMPLOYEE_SALARY;
Oracle - significance of Return clause in stored procedure - May 05, 2009 at 18:00 PM by Rajmeet Ghai

Explain the significance of Return clause in stored procedure.

Return statement in a stored procedure is not to return values. It simply returns control to the caller before the end of thWhat are actual and formal parameters in oracle?

Information in subprograms is passed through parameters. If the parameter declared in a subprogram is referenced in the subprogram body, it is called as a formal parameter. On the other hand, when parameters are passed from the calling subprogram, they are actual parameters. The actual and its respective formal parameter should be of the same data type.
Example:
Formal parameters being passed
PROCEDURE employee_violation(emp_id INTEGER, violation varchar(200))
{
Procedure definition
}
Calling procedure:
Employee_violation(emp_num, violation); -> Actual parameters are called.

eWhat are sequences? Explain with syntax.

A field in oracle can be kept as auto incremented by using sequence. it can be used to create a number sequence.
Syntax:
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
Example: employee_seq will cache up to 20 values for performance. Starts from one.

CREATE SEQUENCE employee_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
List the advantages of sequences.

The sequence values can be cached.
Highly scalable.
Sequence ensures that no other session or other call to nextval within the same session gets the same number from the sequence.
No special table needs to be created. Sequences also solve concurrency issues.

What is denormalization?

Denormalization is the reverse process of normalization.
It is controlled introduction of redundancy to the database design.
Database design is denormalised to improve the query performance.
It is done to reduce number of complex joins in the query.

Define SQL Server. Define Database.

SQL server is an RDBMS that uses Transact-SQL to interact with the client application. It includes database, database engine and the application that are used to manage data. Data is organizes into a table with rows and columns.

Define Database.

A database stores data and it is just like a data file. Database systems are more powerful than data files because the data is more highly organized. In a well-designed database, there is no scope for duplicate pieces of data. Related pieces of data are grouped together in a single structure or record.

How do you implement one-to-one in SQL Server?

One to one is implemented using single table by establishing relationship between same type of columns in a table.

How do you implement one-to-many in SQL Server?

Implemented using two tables with primary key and foreign key relationships

How do you implement many-to-many SQL Server?

Implemented using a junction table.

The keys from both the tables form composite primary key of the junction table.

User Defined Datatypes

User defined data types are most commonly used when consistency among several tables storing the same type of data is desired. User defined data types can also be used when several tables must store the same type of data in a column .Consistency in terms of same data type, length, and null ability is required. The user defined data type in SQL is based on system data type. When a user defined data type is created it must have a name, a system data type, whether or nor it can accept NULL values.
Syntax:
Sp_addtype is used to create a user defined data type in transact SQL.
Example:
To create a user defined data type for postal code.
Sp_addtype postal_Code, ‘varchar(10)’ , ‘NOT NULL’

Difference between a primary key and a unique key.

Both enforce uniqueness of the column.

By default primary key creates a clustered index.

Unique creates a nonclustered index by default.

Primary key doesn't allow NULLs.

Unique key allows NULL

What is bit datatype?

Bit datatype is used to store boolean information like 1 or 0 (true or false).

Bit datatype can represent a Null state

Define candidate key, alternate key, composite key.

Candidate key: A column or a set of columns can be called as candidate key if they identify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key.

Alternate key: There can be more than one keys which can identify each row of the table uniquely. One of them is defined as primary key and rest of them is called alternate keys of the table.

Composite Key:A key formed by combining at least two or more columns.

Define composite key.

A key formed by combining at least two or more columns.

Define default constraint?

Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data.

IDENTITY columns and timestamp columns can't be associated with default constraint

What is a transaction and what are ACID properties?

Transaction encapsulates SQL commands and work as a single unit.

All the SQL statements defined in the transaction should work successfully.
Every transaction follow tranaction properties ACID i.eAtomicity, Consistency, Isolation, Durability to qualify as valid transaction

Explain different isolation levels defined in SQL Sever.

Isolation levels determine the degree of isolation of data during concurrent access.

Read Uncommitted, Read Committed, Repeatable Read, Serializable are the different isolation levels defined in SQL Server.

The default SQL Server isolation level is Read Committed.

SET TRANSACTION ISOLATION LEVEL allows to define the isolation level at the connection level

What is lock escalation?

Lock escalation is the process of converting low level locks (row locks, page locks) into higher level locks (table locks).
Each lock is a memory structure. Too many locks mean more memory being occupied by locks.

SQL Server escalates the many fine-grain locks to fewer coarse-grain locks

What is SQL Profiler? Explain its purpose.

SQL Profiler: It’s a GUI interface to SQL Server Trace and allows monitoring an instance of the database engine and even Analysis services. It enables capturing sand saving data about every event into a file or table for performing analysis later. It helps understand and read all the trace information helping in identifying performance and bottleneck resolution.

Sql server - SQL Profiler - Jan 14, 2010 at 09:00 AM by Nishant Kumar

Define SQL Profiler.

SQL Profiler is used to diagnose a problem by capturing events in a trace file.
It is useful to step through queries to find the reason of slow performance.
You can monitor SQL server performance in order to tune workload.
SQL profiler also supports auditing of actions on the server

Sql server - SQL Profiler - March 06, 2009 at 11:30 AM by Rajmeet Ghai

What is SQL Profiler? Explain its purpose.

A SQL server profile is used to capture server events of the SQL server.

It helps you find out what is exactly going on in the SQL server. It is used to monitor and analyze SQL server events. It has a GUI for such activities.

SQL profiler is used when:

When some queries are performing slowly
To trace and monitor events of the SQL server
When indexes need to be fie tuned
When security is a concern, it can be used to audit and review security activities
It can be used when troubleshooting to find root cause of an issue

Explain SQL Server Service Manager.

SQL Server Service Manager is used to start, stop, and pause SQL Server services that exist as the separate components on the server.
The service components are as follows:
SQL Server service
SQL Server Agent service
Microsoft Search service
MSDTC service
MSSQLServerOLAPService service

What is SQL Query Analyzer?

SQL Query Analyzer is a tool that are used for various purposes such as

Creating and executing queries.
Creating database objects.
Executing stored procedure.
Analyzing query performance.
Inserting, updating, or deleting rows in a table

Define DTS service in SQL Server.

DTS, Data Transformation Services is used to import and export data between heterogeneous data source.
A DTS package can not only transfer data from a table to another, but can also specify query as source of data

What is Replication in SQL Server?

Replication allows creating copies of data in separate databases along with keeping all databases synchronized by replicating modification in one copy to all the copies.
Data can be effectively distributed over network using replication

Define Bulk Copying and Distributed Queries of SQL Server.

Bulk copying is used to transfer large amount of data.
Distributed Queries in SQL server are used to reference heterogeneous data source such as a Microsoft Access database or Oracle database

Define SQL Server Agent.

SQL server agent is important service of the server where instances of SQL server are running.
The agent is responsible to run tasks that are scheduled to occur at specific times or intervals.
The agent is also responsible to run replication task set by the administrators

Difference between DELETE and TRUNCATE commands in SQL Server.

DELETE TABLE is a logged operation, it is a slow process.

TRUNCATE TABLE deletes all the rows, but it won't log the deletion, conquently it is fast process.

TRUNCATE TABLE can't be rolled back

What are constraints in SQL Server?

Constraints enforce the data integrity to the database and protect columns of the table from unwanted values. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server

What is an index?

Indexes of SQL Server are similar to the indexes in books.

They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes.

Rows in the table are stored in the order of the clustered index key.

There can be only one clustered index per table.

Non-clustered indexes have their own storage separate from the table data storage.

Non-clustered indexes are stored as B-tree structures.

Leaf level nodes having the index key and it's row locater

Disadvantages of the Indexes are

Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE).

Every time data changes in the table, all the indexes need to be updated.

Indexes need disk space, the more indexes you have, more disk space is used

What is RAID?

RAID stands for Redundant Array of Inexpensive Disks.

It provide fault tolerance to database servers.

There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance

Reasons of poor performance of query.

No indexes

Excess recompilations of stored procedures.

Procedures and triggers without SET NOCOUNT ON.

Poorly written query with unnecessarily complicated joins,

Highly normalized database design.

Excess usage of cursors and temporary tables

Ways to troubleshoot performance problems in SQL Server.

SET SHOWPLAN_ALL ON

SET SHOWPLAN_TEXT ON

SET STATISTICS IO ON

SQL Server Profiler

Windows NT /2000 Performance monitor

Graphical execution plan in Query Analyzer

Steps to secure an SQL Server.

Use NT authentication.

Use server database and application roles to control access to the data.

Secure the physical database files using NTFS permissions.

Use an ungues sable SA password.

Restrict physical access to the SQL Server.

Rename the Administrator account on the SQL Server computer.

Disable the Guest account.

Enable auditing.

Use multiprotocol encryption.

Set up SSL.

Set up firewalls.

Isolate SQL Server from the web server etc

What is a deadlock and what is a live lock?

When two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely

What is blocking?

When one connection from an application holds a lock and a second connection requires a conflicting lock type

Ways of moving data/databases between servers and databases in SQL Server.

BACKUP/RESTORE,

Dettach/attach of databases,

Replication, DTS, BCP, logshipping,

INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data

Explian different types of BACKUPs avaialabe in SQL Server?

Full database backup.

Differential database backup.

Transaction log backup.

Filegroup backup

What is database replicaion?

The process of copying/moving data between databases on the same or different servers.

Snapshot replication,

Transactional replication,

Merge replication

What are cursors in SQL Server?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

Each time you fetch a row from the cursor, it results in a network roundtrip

Cursors are also costly because they require more resources and temporary storage

What is a join and explain types of joins.

Joins are used in queries to explain how different tables are related.

Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.

OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS

What are triggers in SQL Server?

Triggers are special kind of event driven stored procedures.

Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table,
Can specify which trigger fires first or fires last using sp_settriggerorder,

Triggers can't be invoked on demand,

They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens,

Triggers are generally used to implement business rules, auditing,

Triggers can also be used to extend the referential integrity checks

What is a self join in SQL Server?

Two instances of the same table will be joined in the query

Difference between UNION ALL and UNION.

UNION statement eliminates duplicate rows whereas UNION ALL statement includes duplicate rows. UNION statement can be used to combine any number of queries whereas UNION ALL statement can be used to combine a maximum of two queries. UNION statement cannot be used with aggregate functions whereas UNION ALL statement can be used with aggregate functions

Disadvantages of cursor.

Cursor manipulates records row by row. It requires temporary storage for row manipulation. Thus manipulating row using cursor is costly affair since it consumes extra resource. Fetch record row by row means server roundtrip that consumes network recourses

What is LOG Shipping?

Log shipping defines the process for automatically taking backup of the database and transaction files on a SQL Server and then restoring them on a standby/backup server. This keeps the two SQL Server instances in sync with each other. In case production server fails, users simply need to be pointed to the standby/backup server. Log shipping primarily consists of 3 operations:
Backup transaction logs of the Production server.
Copy these logs on the standby/backup server.
Restore the log on standby/backup server.

What is LOG Shipping?

This process synchronizes two SQL servers and thus provides ready server in case one fails. It automatically backup transaction log file throughout the day and restore them on the standby server.
Sql server - What is LOG Shipping? - March 04, 2009 at 15:30 PM by Rajmeet Ghai

Define Log shipping.

Log shipping is the process of shipping or automatically sending the transaction log which is already backed up at the primary server, to the secondary server. Once the log is copied to the secondary server instance, it is restored. The log can be shipped from one primary server instance to multiple secondary server instances. Log shipping increases data availability because if the primary database becomes unavailable, any of the secondary databases can be brought online manually.

What are the different types of Locks?

Types of locks:
Shared Lock: Shared locks are used for operations that read data, such as a SELECT statement. During Shared locks used, concurrent transactions can read a resource, but cannot modify the data.

Update Lock: Update locks are used when SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock before actually making the changes. The Update locks are used to prevent a deadlock.

Exclusive Lock: Exclusive locks are used for data modification operations, such as UPDATE, INSERT, or DELETE. Other transactions cannot read or modify data locked with an Exclusive lock.

What is Extent and types of Extent?

An extent is 8 continuous pages to hold server object.

Uniform extents - This type of extent contains data from one table.

Mixed extents - This type of extent contains data from two to eight different tables

What is the use of DBCC commands?

DBCC is database consistency checker. DBCC commands are used to check the consistency of the databases.
DBCC CHECKDB - Ensures that tables and the indexes are correctly linked in the database.
DBCC CHECKALLOC - Ensures all pages are correctly allocated in the database.
DBCC SQLPERF - Provides report on current usage of transaction log in percentage.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage

Define COLLATION.

Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary

Where is users names and passwords stored in SQL Server?

They are stored in master db in the s

What is BCP?

It is utility used to copy huge data from tables and views without copy schema of the server object.

How can we move data along with schema of the server object?

We can copy data along schema using DTS package.

Define sub-query.

Sub-query is a query within a Query.

Example of sub-query:

Select CustId, Custname From Customer Where Cust_Id IN (Select Doct_Id from Doctor)

sp_grantlogin, sp_denylogin and sp_revokelogin.

All these three are the system stored procedure used to manage windows account authentication. sp_grantlogin allows to add and grant access to windows account.
sp_denylogin denies user to access server without deleting windows account from SQL server. sp_revokelogin delete windows account from SQL server.

Write SQL query to retrieve all tables of the database.

Select name from sysObjects where xtype=’u’

Define Local temporary table and global temporary table.

Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name).
Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends

Define Local temporary table and global temporary table.

When do we use the UPDATE_STATISTICS command?

UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account

Difference between clustered and non-clustered index.

Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data. We can have only one clustered index in a table but we can have many non-clustered index in a table. Physical data in the table is sorted in the order of clustered index while not with the case of non-clustered data

Explain the concepts of faster differential backups.

A differential backup relies on most recent, previous complete backup. It’s also known as the base of the differential. Differential backup only includes the changed data since the last differential base. Its size is based on amount of data that has changed. Differential backup stores the state of changed extents at the time when backup was created. It is highly recommended to take new full backups periodically at small intervals of time. This ensures its size to be much smaller than that of the base which save time and storage space
During restore, base is restored prior to differential backup. Then one needs to only restore the most recent differential backup.

Explain the concepts of faster differential backups.

Differential backups are a faster mechanism of taking backups. The back up taken is only of the recently made changes in the data from the last differential backup taken. They support frequent backups which decrease the risk of data loss. The information about the differential database for each file is maintained in a catalog in the primary filegroup.

Explain the concepts of Parallel Database consistency check (DBCC).

DBCC CHECKDB verifies the allocation and structural integrity of all the database objects. It performs physical consistency check on indexed views. It is safe as it identifies and corrects maximum errors. DBCC CHECKDB does not enforce any locks on any tables; however, it does lock the schemas that prevent metadata changes. It collects information and scans the log for any changes made, merges the 2 sets of information together to produce a consistent vie of data once it completes. It also checks the linkages, sizes of text, ntext and image pages for every table, and the allocation for all the pages in the database. It performs the following operations on every table:

Verifies index and data pages are linked correctly.
Verifies indexes are as per their sort order.
Verifies consistency of pointers.
Verifies that every page has reasonable amount of data.
Verifies page offsets are reasonable.

Explain the concepts of Parallel Database consistency check (DBCC).

DBCC performs a check on the tables and views for any corruptions. The command DBCC CHECKTABLE checks for integrity of the data, index, text, ntext, and image pages for the specified table or indexed view. DBCC checks if the pointers are consistent, data on each page is rational, the offsets of pages are proper and indexes are in their proper sort order. The DBCC CHECKTABLE returns a result set.
Example: To checks the data page integrity of the authors table.
DBCC CHECKTABLE (‘sample’)
GO

Define Indexed view.

Indexed views are views which have a unique clustered index. This improves data access performance on complex queries.

Define Indexed view.

An index view has a unique clustered index created on it. They exist as rows on the disk. Because they are saved on the disk, the response time to a query is fast at the cost of space consumption. They are more commonly used in scenarios when data modification is less.
Syntax:
Create Index
CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON table_name

The view is created using the CREATE VIEW syntax

Define Distributed partitioned views.

For performance gains, one might need to distribute database work over multiple servers. This is achieved by horizontally partitioning large tables over multiple servers. I.e. splitting a table with many rows into many tables with few rows. For data that is spread across multiple servers, different types of statements are needed for retrieving data. I.e. distributed partitioned views. These statements use standard SQL statements along with UNION to retrieve data from multiple servers. One needs to define a distributed partitioned view on each of these servers, and each view having the same name. This allows the queries to run on all these servers instead of one.
Sql server - Distributed partitioned views - March 02, 2009 at 22:00 PM by Rajmeet Ghai

Define Distributed partitioned views.

Partitioned view:
Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A partioned view can either be local or distributed. Distributed may reside on a different server. The original table is replaced with several smaller member tables. Each member table is on a separate member server.
Syntax:
The view is then created by UNIONing all the tables and an updateable partitioned View results
Server 1 :
CREATE TABLE Customer1
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3

Define Full-text indexing.

Full text search is achieved using Full text indexing in SQL Server. It enables full text queries against character based data. Searches can include words, phrases, multiples forms of a word/phrase etc. To allow this, full text indexes must be created for columns referenced in the queries. A full text index is made of word tokens derived from the indexed text.

Steps to implement full text indexing:

Create a full text catalog.
Create full text index
Create a list of noise words to ignore
Create a thesaurus for the language being used.

Define Full-text indexing.

A full text index has an index of type FULL TEXT. Full text indexing can only done for CHAR, VARCHAR, or TEXT columns. Currently searching using FULL TEXT index is only available in MYISAM tables. The full text index can be specified while creating the table or altering the table.
For e.g. CREATE TABLE TEST_SAMPLE(notes TEXT, FULLTEXT(notes)) TYPE=MyISAM
For searching:
SELECT * FROM TEST_SAMPLE WHERE MATCH(notes) AGAINST('test');

Define Log shipping.

What are the three types of Database files? Explain them.

Types of database files:

Data file: This stores all the data. It has an extension .mdf
Log file: This stores all the transaction log information. It has an extension .ldf
Additional data files: Any additional data is stored in these files. It has an extension .ndf

What are the three types of Database files? Explain them.

Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:
Primary: starting point of a database. It also points to other files in database. Extension: .mdf
Secondary: All data files except primary data file is a part of secondary files. Extension: .ndf
Log files: All log information used to recover database. Extension: .ldf

User defined data type concepts, syntax and an example.

User defined data types are based on system data types. It should be used when multiple tables need to store same type of data in a column and the user needs to ensure that all these columns have exactly same data type, length and nullability.
E.g.: Postal_code can be created based on char data type. One needs to provide Name, system data type, and nullability parameters while creating a user defined data type.
Syntax
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]
Sql server - User defined data type concepts - Jan 14, 2010 at 09:00 AM by Nishant Kumar

User defined data type concepts, syntax and an example.

It extends base type datatypes functionality.

It is create when particular datatype format has to be used across many tables in the database.

If a column called EmpId appears in many tables of int type, create EmpId of int and use it across all your tables.

Sql server - User defined data type concepts - March 05, 2009 at 9:30 AM by Rajmeet Ghai

User defined data type concepts, syntax and an example.

Explain the concepts behind placing indexes with filegroups.

Indexes are created on the same filegroup as the base table on which the index is created. However, one can achieve performance gains by creating the index on a different filegroup if the filegroup makes use of different physical drives with their own controllers. Data and index information both can be thus read in parallel by multiple disk heads. It improves performance as both file groups are being completely used without any contention.
Sql server - placing indexes with filegroups - March 02, 2009 at 22:00 PM by Rajmeet Ghai

Explain the concepts behind placing indexes with filegroups.

Indexes can be placed on different file groups to increase performance. By default, indexes are created on the same file group as the base table. Placing indexes with different filegroups are more commonly used when type of access cannot be predicted. Spreading the index across different file groups can be done to achieve this. This also ensures that all disks are being accessed since all data and indexes are spread evenly across all disks.
Example of creating an index on a specific file group:
CREATE UNIQUE CLUSTERED INDEX sample ON t1.c

Explain how to make views updatable in SQL Server 2000[Hint - using Instead of Triggers and Partitioned views].

INSTEAD OF Triggers: These can be created on a view to make a view updateable. The INSTEAD OF trigger is executed instead of the data modification statement on which trigger is defined. This trigger enables the user to provide set of actions that must take place to process data modification statement. Partitioned views: It’s a view defined by a UNION ALL of member tables structured in same way but stored separately as multiple tables in either same SQL Server or multiples ones (federated database servers).
Sql server - how to make views updatable in SQL Server 2000 - March 02, 2009 at 22:00 PM by Rajmeet Ghai

Explain how to make views updatable in SQL Server 2000[Hint - using Instead of Triggers and Partitioned views].

INSTEAD OF Triggers: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.
Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF
UPDATE AS
{
TRIGGER Definition
}
Partitioned view:
Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server.
Syntax:
The view is then created by UNIONing all the tables and an updateable partitioned View results
Server 1 :
CREATE TABLE Customer1
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3

Give me your Feedback.......

Wednesday 29 August 2012

Technical Interview Questions in Oracle Apps

How do you control the constraints in forms?

When will ON-VALIDATE-FIELD trigger executed?

When will ON-VALIDATE-FIELD trigger executed?

What is the difference between system.current_field and system.cursor_field?

What is the difference between system.current_field and system.cursor_field?

What are dynamic reports? How will you create them?

Sequence of firing triggers in forms.

Explain the security features in Oracle.

What are roles? How can we implement roles?

What is user Account in Oracle database?

What is user Account in Oracle database?

What is a trace file and how is it created?

What are Tablespace Quota and default tablespace?

Explain Statement Auditing, Privilege Auditing, And Object Auditing.

What is configure command and recovery catalog?

What are different types of backup? (Hot, Cold, logical, Physical)?

What is mean live lock and deadlock? How to prevent it?

What is Partial Backup?

What is Mirrored on-line Redo Log?

What is Mirrored on-line Redo Log?

Explain block structure of PL/SQL.

Read-only replication and snapshots.

Explain how oracle optimizer works.

Explain how oracle optimizer works.

What is proactive tuning and reactive tuning?

What is proactive tuning and reactive tuning?

Explain rule-based optimizer and cost-based optimizer.

Rule Based Optimizer

Cost Based Optimizer

Describe how to reference remote tables in procedures with an example.

Explain the significance of Return clause in stored procedure.

Explain the significance of Return clause in stored procedure.

Return statement in a stored procedure is not to return values. It simply returns control to the caller before the end of thWhat are actual and formal parameters in oracle?

eWhat are sequences? Explain with syntax.

CREATE SEQUENCE employee_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 List the advantages of sequences.

What is denormalization?

Define SQL Server. Define Database.

Define Database.

How do you implement one-to-one in SQL Server?

How do you implement one-to-many in SQL Server?

How do you implement many-to-many SQL Server?

User Defined Datatypes

Difference between a primary key and a unique key.

What is bit datatype?

Define candidate key, alternate key, composite key.

Define composite key.

Define default constraint?

What is a transaction and what are ACID properties?

Explain different isolation levels defined in SQL Sever.

What is lock escalation?

Explain SQL Server Service Manager.

What is SQL Query Analyzer?

Define DTS service in SQL Server.

What is Replication in SQL Server?

Define Bulk Copying and Distributed Queries of SQL Server.

Define SQL Server Agent.

Difference between DELETE and TRUNCATE commands in SQL Server.

What are constraints in SQL Server?

What is an index?

What is RAID?

Reasons of poor performance of query.

Ways to troubleshoot performance problems in SQL Server.

Steps to secure an SQL Server.

What is a deadlock and what is a live lock?

What is blocking?

Ways of moving data/databases between servers and databases in SQL Server.

Explian different types of BACKUPs avaialabe in SQL Server?

What is database replicaion?

What are cursors in SQL Server?

What is a join and explain types of joins.

What are triggers in SQL Server?

What is a self join in SQL Server?

Difference between UNION ALL and UNION.

Disadvantages of cursor.

What is LOG Shipping?

What is LOG Shipping?

Define Log shipping.

What are the different types of Locks?

CREATE SEQUENCE employee_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
List the advantages of sequences.