19 oracle apps technical interview questions and answers



Last updated on November 8th, 2016 at 05:56 pm

This is a compilation of most commonly asked 19 oracle apps technical interview questions and answers
oracle apps technical interview questions and answers
Question 1 What is difference between AD_BUGS & AD_APPLID_PATCHES
Ans:
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.
AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records.
Question 2 What exactly happens when you put an Oracle Apps instance in maintenance mode ?
Ans:
Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for maintenance. Enabling the maintenance mode feature
a) shuts down the Workflow Business Events System and
b) sets up function security so that no Oracle Applications functions are available to users.Used only during AutoPatch sessions, maintenance mode ensures optimal performance and reduces downtime when applying a patch.Question 3 Why appsutil directory under Database ORACLE_HOME used for ?

Ans:

All the template files, startup scripts , XML files are maintained here .

Question 4 How to create User in Oracle Applications ? Can you delete a User ?
Ans:

New User can be created using security–>Define–>User menu. No , user cannot be deleted but can be end-dated.

Question 5

How frequent you run Gather Schema Statistics program & with what option ? Why you need to run it ? What is cost based optimizer ?

Ans:

Generally Gather schema statistics should be run every week atleast with 30% estimation to calculate statistics so that optimizer can choose better plan for sql . Oracle cost based optimizer is sql optimization  based on statistics of the table  and indexes

Question 6 How to defragmented the table?

Ans:

By moving the table using  Alter table move and then rebuild all its index

Related article

How to rebuild the tables

Online reorganization of tables using Dbms_redefination

Question 7

Which table stored the concurrent program definition?

Ans:

FND_CONCURRENT_PROGRAM and FND_CONCURRENT_PROGRAM_TL

Related articles

Oracle Concurrent Manager

Question 8

How to find the process which is taking highest CPU from Unix command?

Ans:

using top command

Related articles

Useful Unix command for DBA

Question 9

How will you find Invalid Objects in apps schema in database ?
Ans:

Using query

col object_name format a50
col owner format a10
set lines 300
set pages 1000
select object_name,
object_type,
owner,
status
from dba_objects
where status = ‘INVALID’  and owner=’APPS’
/

Question 10 How to compile Invalid Objects in database ?
Ans:

You can use adadmin utility to compile or you can use utlrp.sql script shipped with Oracle Database to compile Invalid Database Objects.

Question 11  What is sub query and correlated sub query?

Ans:

Sub Query A Sub Query is a SELECT statement that is embedded in a clause of other SQL statements called the parent statement.

A Sub query (Inner Query) returns a value that is used by the outer query.

A Scalar sub query is a sub query that returns exactly one column value from one row.

Correlated Sub Query Correlated sub query are used for row – by –row  processing.

Each sub query is executed once for every row of the outer query.

A correlated sub query is one way of reading (data) every row in a table and comparing values in each row against related data.

Oracle server performs correlated sub query when the sub query references a column from a table in the parent query.

The inner query is driven by the outer query in correlated sub queries.

Related articles

What are Oracle Joins, Set and Subqueries

Question 12 How duplicate rows are deleted?

Ans:

Duplicate rows are deleted by using ROWID

Syntax  delete from <Table>

Where ROWID not in (Select max (ROWID) from  <Table>

Group by <Column_name>);

Related articles

How to delete duplicate rows from a table

Question 13 What is Appstand Form.

Ans:

Appstand form contains the Following.

1). Object Group STANDARD_PC_AND_VA.

Which contain the visual attribute and property class.

2). Object group STANDARD_TOOLBAR which contains the windows

Canvasses blocks and item of application toolbar.

3). Object group STANDARD_CALENDER which contains the windows

Canvasses blocks and item of application calendar.

4). Object groups QUERY_FIND, which contains a window, blocks and item

Used as a starting point for coding a find window.

Question 14  What is set of books.

Ans:

A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calendar. You must define at least one set of books for each business location

 

Question 15

What is the Significance of US Folder?
Ans:

It is nothing but language specification by default it is in american language. We can have multiple languages folders based on installed languages. from backend we can get it from
FND_LANGUAGES — COL –INSTALLED_FLAG I,B,D

I–INSTALLED,
B–BASE,
D–DISABLE
select language_code,nls_language from fnd_languages where installed_flag like ‘B’

Question 16

What are the steps involved in adding a custom program to Oracle Applications?

Ans:

a) Develop a concurrent program or report.

b) Identify the corresponding executable and register it with the application.

c) Create a concurrent program and its parameters.

d) Add a concurrent program to a request set.

 

Question 17 What are the primary underlying tables for concurrent processing

Ans:

FND_CONCURRENT_PROGRAMS
FND_CONCURRENT__REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUES .

Question 18 What is the Diff between APPS Schema and other Schemas?
Ans:

Apps schema contains only Synonyms we can’t create tables in apps schema, where as other schemas contains tables, & all the objects. Here only we will create the tables and giving grants on created tables. Almost all every time we will connect to apps schema only

Question 19

Define  Oracle database Architecture in short?

Answer

First let take a look at instance ,server and database

Oracle Instance: It isa  means to access an Oracle database,always opens one and only one database and consists of memory structures and background process.
Oracle server:  It is aa DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database.
Oracle database: It is a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)

Now Instance memory Structures:

System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance.

SGA Memory structures:

Shared Pool Consists of two key performance-related memory structures Library Cache and Data Dictionary Cache.
Library Cache  Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.
Data Dictionary Cache Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
Database Buffer Cache Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.
Redo Log Buffer Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.

User process:
Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.

Server process:
Connects to the Oracle Instance and is Started when a user establishes a session.
fulfills calls generated and returns results.
Each server process has its own nonshared PGA when the process is started.
Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information.
In some situations when the application and Oracle Database operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead.


Leave a Reply