Oracle questions for Computer Science students.

List the various Oracle database objects.

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES

TABLES

Table is a set of elements organized in vertical and horizontal fashion.

TABLESPACES

Tablespace is a logical storage unit in Oracle.

VIEWS

It is a virtual table derived from one or more tables.

INDEXES

This is a performance tuning method to process the records.

SYNONYMS

It is an alternative name for a table, view, sequence, stored procedure etc.

SEQUENCES

Sequence is a database object from which multiple users may generate unique integers.
It can be used to generate primary key values automatically.

What is the purpose of Save Points in Oracle database?

Save Points are used to divide a transaction into smaller phases.
It enables rolling back part of a transaction.
Maximum 5 save points are allowed in Oracle Database.
Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

What is meant by Recursive hints in Oracle?

Number of times a dictionary table is repeatedly called by various processes is known as Recursive hint.
It occurs because of the small size of the data dictionary cache.
We can improve the size of the Data Dictionary Cache by increasing the SHARED_POOL_SIZE parameter.

Explain the functions of Optimizer in Oracle.

Optimizer is an important step in the DML (Data Manipulation Language) statement : SELECT, INSERT, UPDATE or DELETE.
Optimizer is the heart of the SQL processing engine.
It generates a set of potential plans for the SQL statement.
The optimizer estimates the cost of each plan which is based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.
It determines the most efficient way to execute a SQL statement.
It considers many factors among alternative access paths.
Optimizers are used to avoid excessive I/O resources.

Difference between open_form and call_form in oracle.

CALL_FORM	OPEN_FORM
CALL_FORM runs a specified form while keeping the parent form active.	OPEN_FORM executes the current form and new specified form as a model form.
It cannot open the indicated form, but also keeps the parent form alive.	It opens the indicated form.
When we invoke a form from CALL_FORM, we cannot move to the calling form unless we complete our work in the called form and close it.	When we invoke a form from OPEN_FORM, we can freely move to the calling form and called form.

What is the main purpose of Oracle Real Application Clusters (RAC)?

Purpose of Oracle Real Application Clusters (RAC)

Oracle Real Application (RAC) provides the interaction of executable file with the Oracle database.
It allows the running of any packaged or custom built application with the Oracle database which is running on a server pool.
It provides a very high level of availability, flexibility and scalability to run the application and store it to the database.
It creates the database that means if the pool fails, then the database is continuing to run from the remaining servers and the load can be distributed.
It makes easier to maintain many servers at the same time by load-balancing techniques for the administrator.
RAC provides provision to add more servers when the load increases.

What is Load Balancing Advisory?

Load balancing advisory is a process through which the load of the applications and resources can be managed throughout the servers.
It helps in maintaining the loads from the servers and equally distributes it among many other servers which are not currently working.
It monitors the workload of the current activities from all the clusters and the instances that is being given in the server.
The service that is being provided is active all the time to see the workload of the application on the servers.
To simplify the load balancing advisory, it provides a percentage value to show the total workload of the instance and it flags the instance according to the quality.

What are the tools provided in Oracle Enterprise Manager?

Oracle Enterprise Manager provides following tools
1. Grid Control
2. Database Control

1. Grid Control

Grid Control is used to deliver the centralized management system.
It provides configuration and administration capabilities.
This control provides the cost reduction plans and higher efficiency, which allows the time to be saved for other server control actions.

2. Database Control

Database Control is used as a graphical management tool to manage the database to make it configure automatically.
It is related to the Oracle Clusterware.
This control is used to manage the services of the Oracle RAC.
It also manages the server pools that are being created with the Oracle Clusterware and provision to manage it from a single place.

Why is Cluster Verification Utility so important in Oracle RAC?

Cluster Verification Utility is a tool in the Oracle Grid.
It is used to eliminate the errors that come up with the validations of the steps.
It provides the verification on the changes that is being made in the configuration of the files or the system.
The tools can be used with the command line interface which is used to validate the configuration input during the installation.
This tool is used to verify the system pre-requisites which are related to Oracle Clusterware, ASM and the databases.
There are few fixup scripts available, if any verification tool fails, then these scripts are used for fixing the errors automatically.

What is the function of having Virtual Internet Protocol Address (VIP) in Oracle RAC?

Oracle RAC is using the virtual IP address for each server, which is present in the cluster.
It allows an easy management of the Oracle Clusterware by taking the subnet as the LAN.
The VIP address is used by the applications and services of the Oracle RAC.
It helps in connecting to the Oracle RAC database.
Virtual IP address keeps the track of the failures and if one node fails, then the load gets shifted to another node which is present in the cluster.
This provides an easy way to manage the node down response for the incoming connections and provide the requests for that connection.
It helps in increasing the availability of the services and the applications that take longer time to run before the request fails.
It changes the server pool address if the connection fails in between while serving any request from any other source.

What is Oracle Apps (ERP)?

ERP stands for Enterprise Resource Planning.
A product of Oracle India Pvt. Ltd. As the name suggests Enterprise Resource Planning.
Enterprise means a firm/organization.
Resource means available source of wealth in firm.
Planning means how you can maintain day to day resource.
Hence in short to facilitate big businesses, companies Oracle Corporation have created huge software known in the category of ERP (Enterprise Resource Planning) as Oracle Applications.

What is FSG? Explain its uses.

FSG stands for financial statement generator.
It is powerful reporting engine for clearly define our reporting requirement.
It helps to keep the report simple by the use of Row Set, Reports, Content set and Column set.
It uses ADI request set to publish standard reports.

Uses of FSG

It can produce a specific report when needed.
It produces financial reports without programming.
It provides asynchronous printing of reports.
It has an ability to print single reports for multiple firms.

What is the use of CUSTOM Library in Oracle Apps?

Uses of CUSTOM Library in Oracle Apps

CUSTOM Library is an Oracle Form PL/SQL library.
It is used for allowing an extension to the Oracle Application without any modification of an Oracle Application code.
We can do the customization ,such as zoom, enforcing business rules and disabling fields etc.
A CUSTOM library is used to validate the base application without modifying the Custom.pll.
It allows you to take full advantage of all the capabilities of the Developer/2000 suite of products, and integrate your code directly with Oracle Applications without making changes to the Oracle Applications code.
The specification of the CUSTOM package in the CUSTOM library cannot be changed in any way.
You may add your own packages to the CUSTOM library following with the proper sequence after the CUSTOM package.

What is User Exit? Explain different types of User Exit in Oracle Apps.

A user exit is a three character code that instructs the system to access a program during system processing.
SXX: S is for standard exits that are delivered by SAP. XX represents the 2-digit exit number.
UXX: U is for user exits that are defined by the user. XX represents the 2-digit exit number.
User exit is a C subroutine written by user and called by Oracle Forms to do special-purpose processing.
It helps the user to exit from the current working environment.

Types of User Exit

1. Menu Exits

Menu exit add items to the pulldown menus in standard SAP applications.
You can use these menu items to call up your own screens or to trigger entire add-on applications.

2. Screen Exits

Screen exits add fields to screens in R/3 applications.

3. Function Module Exits

Function module exits add functions to R/3 applications.
It plays a role in both menu and screen exits.

4. Field Exits

Field exits allow you to create your own programming logic for any data element in the dictionary.

Describe the Architecture of Oracle Applications.

Oracle Application follows a 3 Tier architecture,
1. Database Tier
2. Middle Tier
3. Desktop Tier

1. Database Tier

Database tier contains an RDBMS database called as oracle database server.
It stores all the data needed by oracle applications.

2. Middle Tier

Middle tier or Application Tier contains various servers configured with the listeners like web server, form server, admin server, report server.
Database server communicates with middle tier but with the desktop tier.

3. Desktop Tier

Desktop tier contains client desktop where the user accesses oracle application through web browsers.

How do you delete a responsibility from oracle applications?

Responsibility is a level of authority given to Oracle Applications users.
We can delete a responsibility from both back-end and front-end.
Responsibility is a collection of requests, forms and menus attached to the user.
Records are used for monitoring and security purpose, so we cannot disable the responsibilities.
However, responsibilities can be disabled by assigning an end date to the effective period.

What is the purpose of Cluster?

The cluster is used for storing the table data.
It contains a group of tables that share the same data blocks.
Suppose, if you cluster EMP (EMPLOYEE) and DEPT (DEPARTMENT) table, both share same DEPTNO column.
Oracle stores all the rows of each department physically from both the EMP and DEPT table in the same data blocks.
Clusters cannot be used for those tables which are frequently fetched.

What is the process of Oracle HTTP (Apache) Server?

Process of Oracle HTTP (Apache) Server

Web listener accepts the Http requests coming from the client browsers and web server services the request.
Apache server forwards advanced processing requests to the servlet engine.
Servlet engine forwards this requests to the database.
If the Http request needs parsing a JSP file then web listener checks the nature of the request, then it contacts the Jserv to run a JSP.
The JSP contacts the database for the information and returns a HTML page displayed in the web browser.

What should I do for debugging when I login to the oracle applications from PHP based applications to avoid hanging?

First check the Apache log files.
Then in the Apache log directory, check for the access_log and error_log.
If you are unable to find any information from the above steps, then check for the Jserv logs and JVM.
Analyze all the errors that you are getting from log files and fix them.
Still, you are not able to debug, then perform AOL diagnostics test.

What is Cloning? And why is it required?

Cloning is the process of creating an identical copy of the Oracle application system.
Cloning can be done on separate hosts or on the same host.
It is different from standby database.
It is the process of copying an existing Oracle installation to a different location and updating the copied bits to work in the new environment.

Cloning is required due to following reasons:

Creating a test copy of your production system before upgrading.
Moving an existing system to a different machine.
To test some patches.
Creating a development copy of your environment to be used by the developers.

Explain:
a. Integrity Constraints
b. Index
c. Extent

a. Integrity Constraints

Integrity constraints can be called as a declarative way in order to define a business rule for a table's column.

b. Index

Index can be called as an optional structure, which is associated with a table for direct access to the rows.
It can be created for one or more columns in a table.

c. Extent

Extent can be defined as a specific number of contiguous data blocks in a single allocation.
It is used for storing a specific type of information.

What do you mean by a deadlock?

When two processes are waiting to update the rows of a table which are locked by the another process, the situation is called a deadlock.
It reduces the performance of the server severely.
Deadlock is a condition, in which two or more threads are blocked forever because they are waiting for each other.
The Thread Analyzer detects deadlocks that are caused by the inappropriate use of mutual exclusion locks.
These locks get automatically released when a commit/rollback operation is performed or any process is killed externally.

Causes of Deadlock

Lack of proper row lock commands.
Poor design of front-end application.

What is a sub query? What are its various types?

Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
A sub query is added in the where clause of the main query.

There can be two types of sub queries:

1. Correlated sub query
It can reference column in a table listed in the from list of the outer query but is not as independent as a query.

2. Non-Correlated sub query
Results of this sub query are submitted to the main query or parent query. It is independent like a query.

What is hot backup and logical backup?

Hot backup

Hot backup is also called as dynamic backup.
If the database is in Archive log mode, then we can take hot backup without any database outage.
It provides a convenient solution in multi-user systems, because they do not require downtime.

Logical backup

In logical backup technique, the IMPORT/EXPORT utilities are used to create the backup of the database.
It can be used to restore the database to the last backup.
Logical backup involves reading a set of database records and writing them into a file.
An Export utility is required to take the backup and an Import utility is used to recover from the backup.

Explain the different types of Record groups in Oracle.

Record group is an internal Oracle forms data structure having a similar column-row structure and relationship as a database table.

Types of Record Groups

1. Query record group

This record group is based on an attached SELECT query.
In this group, columns are derived from their default name, data types, length, etc. from the table.

2. Static record group

This record group is not associated with any query.
Their structure and values are defined at design time and thus remain fixed at run time.

3. Non query record group

This record group does not have an associated query.
However, its structure and values can be defined during the run time programmatically.

What do you mean by Redo Log file mirroring?

The process of having a copy of redo log files is called mirroring.
It is done by creating a group of log files together.
This ensures that LGWR automatically writes them to all the members of the current Online redo log group.
In case a group fails, the database automatically switches over to the next group. It diminishes the performance.

Explain the use of Online redo log files in oracle.

Every Oracle database has a redo log, which records all the changes that have been made in the data files.
These files then enable us to replay the SQL Statements.
Oracle writes all the changes to the redo log before making any changes in the data files.
If something happens to any data file, a backed up data file is restored and the redo log helps in doing so.
However, a database can only be recovered if it runs under Archive log mode.

What are the different types of Export backups?

1. Full/Complete backup
Backup of the whole database.

2. Incremental backup
Only backup the data that has been modified since last incremental backup.

3. Cumulative backup
Only affected tables from the last cumulative backup date.

Explain pre-select and pre-query.

Pre-select

Pre-select fires during the execute and count query processing after an oracle form builds the select statement to be executed, but before its execution.

Pre-query

Pre-query fires before an oracle form issues the select statement.
It executes before the pre-select trigger.

What is a trace file?

Trace file means dump file that Oracle database creates.
It helps you to diagnose and resolve operating problems.<.li>
These files are used to store details of exceptions thrown by Oracle background processes, i.e. dbwr, lgwr, pmon, smon etc.
Each server and background process writes to a trace file. When a process detects an internal error, it writes information about the error to its trace file.