Top 50 Oracle Interview Questions You Must Prepare 29.Nov.2023

Procedure Specification and Procedure Body.

Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

A Database contains one or more Rollback Segments to temporarily store "undo" information.

  • Each block contains entries as follows
  • Fixed block header
  • Variable block header
  • Row Header,row date (multiple rows may exists)
  • PCTEREE (% of free space for row updation in future)

  • Normal Queries
  • Sub Queries
  • Co-related queries
  • Nested queries
  • Compound queries.

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.

A trigger giving a SELECT on the table on which the trigger is written.

Each server and background process can write an associated trace file. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.

A flag to indicate whether indexes on tables will be exported.

The dll can be created using the Visual C++ / Visual Basic Tools and then the dll is put in the path that is defined the registry.

The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

Stacked canvas view is displayed in a window on top of, or "stacked" on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.

select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where rownum<&Start);

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.

The different type of Segments are:

  • Data Segment,
  • Index Segment,
  • Rollback Segment and
  • Temporary Segment.

PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

Alias is temporary and used with one query. Synonym is permanent and not used as alias.

Yes, I customized the Work Flow For Credit Memo Request approval in AR through Workflow Builder.

There are basically 6 types of sql statments.They are

  • Data Definition Language(DDL) :: The DDL statements define and maintain objects and drop objects.
  • Data Manipulation Language(DML) :: The DML statements manipulate database data.
  • Traction Control Statements :: Manage change by DML
  • Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g. :: Alter Statements, Set Role
  • System Control Statements :: Change Properties of Oracle Instance .e.g.:: Alter System
  • Embedded Sql :: Incorporate DDL, DML and T.C.S in Programming Language.e.g:: Using the Sql Statements in languages such as 'C', Open, Fetch, execute and close

Copy is package procedure and writes values into a field. Name in is a package function and returns the contents of the variable to which you apply.

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

The use of Roles are:

  • REDUCED GRANTING OF PRIVILEGES - Rather than explicitly granting the same set of privileges to many users a database administrator can grant the privileges for a group of related users granted to a role and then grant only the role to each member of the group.
  • DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
  • SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted to a user can be selectively enable (available for use) or disabled (not available for use). This allows specific control of a user's privileges in any given situation.
  • APPLICATION AWARENESS - A database application can be designed to automatically enable and disable selective roles when a user attempts to use the application.

Use fnd_log.string , i.e. FND Logging. Behind the scenes Oracles FND Logging uses autonomous traction 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.


Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example: 

  sqlplus sys
SQL>create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;

Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script. rman catalog rman/rman

  RMAN>create catalog tablespace tools;
RMAN> exit;

You can now continue by registering your databases in the catalog. Look at this example: 
rman catalog rman/rman target backdba/backdba

  RMAN> register database;

On-validate-field trigger fires, when the field Validation status New or changed. Post-field-trigger whenever the control leaving form the field, it will fire.

The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

A piece of logic that is executed at or triggered by a SQL *forms event.

This procedure invokes a method.


  PROCEDURE OLE2.INVOKE  (object obj_type,  method VARCHAR2,  list list_type := 0);  


  • object Is an OLE2 Automation Object.
  • method Is a method (procedure) of the OLE2 object.
  • list Is the name of an argument list assigned to the OLE2.CREATE_ARGLIST function.

We have done data conversion for1.Suppliers2.AP Invoices3.Customers@AR Invoices.

Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according to a user's security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

Yes Distributed database system based on 2 phase commit, one query is independent of 2nd query so of course we can run.

Yes . You obtain a return value from a foreign function by assigning the return value to an Oracle Forms variable or item. Make sure that the Oracle Forms variable or item is the same data type as the return value from the foreign function.

After assigning an Oracle Forms variable or item value to a PL/SQL variable, pass the PL/SQL variable as a parameter value in the PL/SQL interface of the foreign function. The PL/SQL variable that is passed as a parameter must be a valid PL/SQL data type; it must also be the appropriate parameter type as defined in the PL/SQL interface.

PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

Integrity constraint is a rule that restricts values to a column in a table.

OEM is a set of systems management tools provided by Oracle Corporation for managing the Oracle environment. It provides tools to monitor the Oracle environment and automate tasks (both one-time and repetitive in nature) to take database administration a step closer to "Lights Out" management.

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

Sub-query is a query whose return values are used in filtering conditions of the main query.

Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window.

You can eiter use wf_engine. start_process or you can attach a runnable process such ghat it subscribes to a workflow event.

After running an UPDATE statement, the database server returns a count of updated rows. You can retrieve this count from a special predefined variable called SQL%ROWCOUT, as shown in the following tutorial:

  CREATE TABLE emp_temp AS SELECT * FROM employees;  BEGIN  UPDATE emp_temp SET salary = salary * 1.05  WHERE salary < 5000;  DBMS_OUTPUT.PUT_LINE('# of rows updated: ' ||  SQL%ROWCOUNT);  END;  /  # of rows updated: 49  

we integrate AR or AP to GL through Set of Books.

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.