Oracle interview questions and answers pdf

Tuesday, February 26, 2019 admin Comments(0)

Top 50 Oracle Interview Questions and Answers Nested table is a data type in Oracle which is used to support columns .. Download PDF. Top 50 Oracle Interview Questions and Answers pdf, You can crack the interview to achieve your Provide Videos & Scenario based. Oracle interview questions: Find top 30 oracle interview questions and answers for freshers. An interviewer may ask you these questions on is a.

Language: English, Spanish, Dutch
Country: Mexico
Genre: Science & Research
Pages: 429
Published (Last): 26.12.2015
ISBN: 262-3-45820-854-9
ePub File Size: 28.75 MB
PDF File Size: 18.64 MB
Distribution: Free* [*Regsitration Required]
Downloads: 41508
Uploaded by: KEVA

Download Complete Oracle Interview Questions PDF. Each question contains their answer also. Its a good practice to go through all of these questions before. Here we provide most common interview question and answers for Oracle and and Answers PDF |SQL | SQL SERVER | Latest Oracle Interview Questions and. Oracle interview questions and answers - Oracle FAQ - Free download as PDF File .pdf), Text File .txt) or read online for free.

Please contact Oracle Support before using it. Latest Articles You are here: Maximum of five save points are allowed. If this activity does not load, try refreshing your browser. In order to set a reserved keyword as an alias, we can use quotation marks. ON clause is equals to Self Joins.

What Is Lexical Reference? How Can It Be Created? What Is System. It represents the coordination causing event that occur on the master block in master-detail relation. What Is Synchronize? It is a terminal screen with the internal state of the form. It updates the screen display to reflect the information that oracle forms has in its internal representation of the screen.

It is a command line argument that allows you to specify a file that contain a set of arguments for r20run. It allows you to read and write information to a file in the file system. In Reports 2. In place activation, External activation. In Oracle forms, Embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.

Form module - a collection of objects and code routines. Menu modules - a collection of menus and menu item commands that together make up an application menu. For a modelless window, it determines whether oracle forms hides the window automatically when the operators navigates to an item in the another window.

What Is When-database-record Trigger? Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued.

The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode.

Pre-query trigger fires before pre-select trigger. True Or False? One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly.

One should do performance tuning for the following reasons:. The speed of computing might be wasting valuable human time users waiting for response ; Enable your system to keep-up with the speed business is conducted; and Optimize hardware usage to save money companies are spending millions on hardware. Although this FAQ is not overly concerned with hardware issues, one needs to remember than you cannot tune a Buick into a Ferrari.

A placeholder column is used to hold calculated values at a specified place rather than allowing is to appear in the actual row where it has to appear. A hidden column is used to when a column has to embed into boilerplate text. Content canvas views Stacked canvas views Horizontal toolbar vertical toolbar. Cascade Isolate Non-isolate. Canvas views are the back ground objects on which you place the interface items Text items , check boxes, radio groups etc.

Each canvas views displayed in a window. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable. By using the rectangle tool while pressing the Constraint key. By using the link file property in the layout boiler plate property sheet. Before one import rows into already populated tables, one needs to truncate or drop these tables to get rid of the old data. If not, the new data will be appended to the existing tables.

If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database. Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next. Oracle also ships some previous catexpX. Before form , After form , Before report, Between page, After report.

Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data. What Is Coordination Event? Any event that makes a different record in the master block the current record is a coordination causing event. Ole server application creates ole Objects that are embedded or linked in ole Containers ex. OLE containers provide a place to store, display and manipulate objects that are created by ole server applications.

What Is An Object Group? An object group is a container for a group of objects; you define an object group when you want to package related objects, so that you copy or reference them in other modules. What Is An Lov? An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list. After the query is executed but before the report is executed and the records are displayed.

A way in which to pass control and possibly arguments form Oracle report to another Oracle products of 3 GL and then return control and back to Oracle reports. What Is The User-named Editor? A user named editor has the same text editing functionality as the default editor, but, because it is a named object, you can specify editor attributes such as windows display size, position, and title. A static record group is not associated with a query, rather, you define its structure and row values at design time, and they remain fixed at runtime.

500 Oracle interview questions and answers - Oracle FAQ PDF.pdf

What Is A Record Group? However, unlike database tables, record groups are separate objects that belong to the form module which they are defined. What Now? Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL Disk UnLoad.

DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!!! The following INIT. ORA parameter may be required if your current redo logs are corrupted or blown away. Caution is advised when enabling this parameter as you might end-up losing your entire database.

Please contact Oracle Support before using it. What Is A Property Clause? A property clause is a named object that contains a list of properties and their settings.

Once you create a property clause you can base other object on it. An object based on a property can inherit the setting of any property in the clause that makes sense for that object. A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer. Start by creating a database schema usually called rman. Look at this example: Next, log in to rman and create the catalog schema.

Prior to Oracle 8i this was done by running the catrman. You can now continue by registering your databases in the catalog.

Pdf and oracle questions interview answers

A group that forms part of a cross product will have a thicker border. A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. What Is A Combo Box? A combo box style list item combines the features found in list and text item.

Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value. Source pane. Navigator pane.

Interpreter pane. Using static values. Writing select statement. Perform the calculation in the SQL statements itself. To Display The Page No. System Files? The advantages over file system files. Disk Corruption will be very less. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment toexpand into anotion Completes. The name of the file from which import should be performed.

Taking backup of archive log files when database is open. The following files need to be backed up. All data files. All Archive log, redo log files. All control files. DATA - Standard operational tables. Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control file.

Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk. Why Query Fails Sometimes? Rollback segment dynamically extent to handle larger transactions entry loads.

A single transaction may wipeout all available free space in the Rollback Segment Tablespace. This prevents other user using Rollback segments. How It Can Be Achieved? Process of having a copy of redo log files is called mirroring. This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group.

It degrades performance. Of Rows Per Block? Row size also reduces no of rows per block. Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small.

Name of the parameter file to be passed for export. What Is A Logical Backup? Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup. It is the set of before image data blocks that contain rows that are modified by a transaction. Each Rollback Segment entry must be completed within one rollback segment.

A single rollback segment can have multiple rollback segment entries. Free extents cannot be merged together in Ver 6. Free extents are periodically coalesces with the neighboring free extent in Ver 7. For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.

Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the database objects will be created.

Flag to indicate whether export should compress fragmented segments into single extents. What Is Dictionary Cache? Dictionary cache is information about the databse objects stored in a data dictionary table. What Is A Control File? Database overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable. What Is Database Buffers? ORA decides the size.

Make new rollback segment available After shutdown, modify init. Deactivate Rollback Segment R0 and activate the newly created rollback segments. What Is Cold Backup? Cold backup is taking backup of all physical files after normal shutdown of database. We need to take. Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently.

Calculate the total header size Calculate the available dataspace per data block Calculate the combined column lengths of the average row Calculate the total average row size. Calculate the average number rows that can fit in a block Calculate the number of blocks and bytes required for the table.

A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free. A flag to indicate whether statistical information about the exported objects should be written to export dump file. A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.

A flag to indicate whether indexes on tables will be exported. A flag to indicate whether import should import index on tables or not. A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'. A flag to indicate whether grants on database objects will be imported.

A flag to indicate whether full databse export should be performed. A flag to indicate whether file content should be displayed or not.

Important Oracle Interview Questions with Answers

A flag to indicate whether constraints on table need to be exported. A flag to indicate whether a read consistent version of all the exported objects should be maintained. When you changes the Existing value to null, the On-validate field trigger will fire post change trigger will not fire. At the time of execute-query post-change trigger will fire, on-validate field trigger will not fire. When Is Pre-query Trigger Executed? When Execute-query or count-query Package procedures are invoked.

These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively. For Eg, instead of inserting a row in a table an existing row can be updated in the same table.

Select the use constraint property is ON Block definition screen. It cannot used in all triggers except key triggers. Pause suspends processing until the operator presses a function key. Pages are collection of display information, such as constant text and graphics. 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. INP Source File. FRM Executable File.

Function Key Key-function Key-others Key-startup. These keys referred as key F0 through key F9. If one table involved in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really want for all tables to have statistics right away; it won't help much to just have the larger tables analyzed. This can be done by specifying hints. If the index is physically smaller than the table which is usually the case it will take less time to scan the entire index than to scan the entire table.

What Enter Package Procedure Does? Enter Validate-data in the current validation unit.

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.

Anchor-view Resize -View Move-View. What Is An Alert? An alert is window that appears in the middle of the screen overlaying a portion of the current display. What Is An User Exits? What Is A Trigger? What Is A Package Procedure? Restricted package procedure.

Unrestricted package procedure. Navigational Triggers. Transaction Triggers. When-window-activated, when-window-closed, when-window-deactivated, when-window-resized.

When-Image-activated Fires when the operator double clicks on an image Items. When-image-pressed fires when the operator selects or deselects the image item. What Is A Visual Attribute? Visual Attributes are the font, color and pattern characteristics of objects that operators see and intract with in our application.

Unlimited no of radio buttons can be assigned to a radio group. Create a parameter list to execute the add parameter built-in procedure to add one or more parameters list. What Is A Layout Editor?

The Layout Editor is a graphical design facility for creating and arranging items and boilerplate text and graphics objects in your application's interface. List The Types Of Items? Text item. Chart item. Check box. Display item. Image item. List item. Radio Group. User Area item. What Is A Radio Group? Radio groups display a fixed no of options that are mutually Exclusive. User can select one out of n number of options. Query record group Static record group Non query record group.

Page, Page 0? Page - Canvas-View Page 0 - Canvas-view null. Only when-radio-changed trigger associated with radio group Visual Attributes. Only When-checkbox-activated Trigger associated with a Check box. What Is A List Item? If yes, give the name of the built-in to change the alert messages at run-time. Form Menu Library. Fmb 2. Mmb 3. Default Block facility. Layout Editor. Menu Editor. Object Lists. Property Sheets.

Tables Columns Browser. Built-ins Browser. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL system first. GL is relatively easy to implement. You should go live with it first to give your implementation team a chance to be familiar with Oracle Financials. MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications.

This keeps each operating unit's transaction data separate and secure. A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field. A flexfield is an Oracle Applications field made up of segments.

Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization.

There are two types of flexfields: Base table Blocks: A base table block is one that is associated with a specific database table or view. Control Blocks: A control block is a block that is not associated with a database table. An alert is a modal window that displays a message notifies the operator of some application condition.

What Is A Navigable Item? A navigable item is one that operators can navigate to with the keyboard during default navigation, or that Oracle forms can navigate to by executing a navigational built-in procedure. A content view is the "Base" view that occupies the entire content pane of the window in which it is displayed.

A stacked view differs from a content canvas view in that it is not the base view for the window to which it is assigned. What Is A Check Box? A Check Box is a two state control that indicates whether a certain condition or value is on or off, true or false. The display state of a check box is always either "checked" or "unchecked".

What Is A Canvas-view? A canvas-view is the background object on which you layout the interface items text-items, check boxes, radio groups, and so on.

At run-time, operators can see only those items that have been assigned to a specific canvas. Each canvas, in term, must be displayed in a specific window. Database Triggers. There are two types of cursors, Implicit Cursor and Explicit Cursor. User defined cursors are called Explicit Cursors. They can be declared and used. 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.

This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it.

Select from SYS. What Is Oracle Label Security? Access to rows are restricted according to a user's security sensitivity tag or label. What Is Oem oracle Enterprise Manager? 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. It is not possible. In the standard package. Return datatype is local variable declarations Begin executable statements Exception execution handlers End;.

Oracle Interview Questions & Answers

Function is called as part of an expression. Extensibility,Modularity, Reusability, Maintainability and one time compilation. What Is An Exception? What Are Types Of Exception? The types are Predefined and user defined.

Some of Predefined exceptions are. What Is A Database Trigger? Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations.

Maintain replicate tables. What Is A Cursor? Why Cursor Is Required? Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows. A function can not be called. What Is A Stored Procedure? A stored procedure is a sequence of statements that perform specific function. A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.

A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures. True if rows are fetched. True if no rows are featched. Procedures and Functions,Packages and Database Triggers. Increased functionality for example,global package variables can be declared and used by any proecdure in the package and performance for example all objects of the package are parsed compiled, and loaded into memory once.

Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations. What Is A Procedure? What Is A Package? A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database. A Function returns a value to the caller where as a Procedure does not. What Is Database Trigger?

What Are Oracle Precompilers? The output must be compiled and linked with this library to creator an executable. The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters.

Oracle-sql Interview Questions And Answers in pdf - Oracle

The return type for a cursor must be a record type. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. Self join -Its a join foreign key of a table references the same table. Outer Join --Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.

Equi-join -Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table. The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. IS statement. INTO statements. What Is A Outer Join? Outer Join: Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS.

What Is Oci. What Are Its Uses? Oracle Call Interface is a method of accesing database from a 3GL program. The OCI library provides.

This dictionary maintains all the metadata related to the database and owned by the SYS user. The purpose of using these attributes is to provide data independence and integrity. A stored procedure is a set of SQL statements that are written to perform a specific task.

These statements can be saved as a group in the database with an assigned name and can be shared with different programs if permissions are there to access the same. Functions are again subprograms that are written to perform specific tasks but there are differences between both of them.

Stored Procedures Functions SPs may or may not return a value and can return multiple values as well. Function will always return only single value. We cannot use DML statements in a function. SPs can call functions. Functions cannot call stored procedures. Oracle is a company. Oracle is also a database server, which manages data in a very structured way.

It allows users to store and retrieve related data in a multi user environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. A standby database is a database replica created by taking a backup of a primary database.

The following are some of the products, tools, and utilities you can use in achieving your goals as a database administrator. It can automatically launch the Database Configuration Assistant to install a database. It enables you to copy a preconfigured seed database, thus saving the time and effort of customizing and generating a database from scratch. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager as the single interface for managing your database.

The process of having a copy of redo log files is called mirroring. It is done by creating group of log files together. In case a group fails, the database automatically switches over to the next group.

It diminishes the performance. Hot backup vs. A cold backup is a backup while it is shut down. The database does not require being in archive log mode in this mode. The benefit of a hot backup is that the database is still available for use while the backup is occurring. A cold backup is easier to administer the backup and recovery process. Cold backups does not require being in archive log mode and thus slight performance gain as the database is not writing archive logs to disk.

Oracle Training. SQL functions can take arguments but always return some value. There are two distinct types of SQL functions:. These functions operate on a single row to give one result per row. These functions operate on groups of rows to give one result per group of rows.

Converts a null value to an actual value. NVL exp1, exp2. If exp1 is null then NVL function return value of exp2. If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. Compares two expressions and returns null if they are equal or the first expression if they are not equal.

Returns the first non-null expression in the expression list. Conditional Expressions: This action is illegal in Oracle SQL. In order to set a reserved keyword as an alias, we can use quotation marks. Primary key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.

The primary key can be only one on the table while unique keys can be multiple. The primary key cannot hold null value at all while unique key can hold one null value per column. The primary key is a clustered index while a unique key is a non-clustered index. Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables.

With the help of the below-mentioned constraints, relationships can be maintained between the tables Top 50 Oracle Interview Questions and Answers pdf. Set operator facilitates the user to fetch the data from two or more than two tables at once if the columns and relative data types are same in the source tables.

UNION operator returns all the rows from both the tables except the duplicate rows. MINUS returns rows from the first table, which does not exist in the second table.

What do you understand by a database object? Can you list a few of them? An object used to store the data or references of the data in a database is known as a Database object. The database consists of various types of DB objects such as tables, views, indexes, constraints, stored procedures, triggers etc. Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema.

The schema can contain any DB objects like table, view, indexes, clusters, stored procs, functions etc. Oracle database executes it automatically when certain conditions are satisfied. This is the frequently asked Oracle Interview Questions in an interview.

An Oracle database contains one or more logical storage units called tablespaces.

These tablespaces collectively store whole data of databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical structure that confirm with the operating system in which Oracle is running Top 50 Oracle Interview Questions and Answers pdf.

What is the difference between hot backup and cold backup in Oracle? Tell about their benefits also. Hot backup Online Backup: A hot backup is also known as online backup because it is done while the database is active.

Some sites can not shut down their database while making a backup copy, they are used for 24 hour a day, 7 days a week. Cold backup Offline Backup: All datafiles, All control files, All online redo log files optional and the init.

A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger you can modify the where clause part dynamically. Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i. Transaction Control Statements that use a set of statements top 50 oracle interview questions and answers pdf. Used to make a transaction permanent. Used to roll back the state of DB to last the commit point. Weblogic Interview Questions. A view is a user-defined database object that is used to store the results of a SQL query, which can be referenced later.

Views do not store this data physically but as a virtual table, hence it can be referred as a logical table. Global variable is the one, which is defined at the beginning of the program and survives until the end. It can be accessed by any methods or procedures within the program, while the access to the local variable is limited to the procedure or method where it is declared. Varchar can store up to bytes and varchar2 can store up to bytes.

Interview answers oracle questions pdf and

Oracle provides software to create and manage the Oracle database. The database consists of physical and logical structures in which system, user, and control information is stored. The software that manages the database is called the Oracle database server. Collectively, the software that runs oracle and the physical database are called the Oracle database system. A database buffer cache stores the data in memory for quicker access. The redo logs track and store all the changes made to the database.

A Data Guard ensures data protection and high availability of data and control file records the physical structure of the database.

What do you know about this organization? This question is one reason to do some research on the organization before the interview. Find out where they have been and where they are going. What are the current issues and who are the major players? Are you a team player? You are, of course, a team player. Be sure to have examples ready. Specifics that show you often perform for the good of the team rather than for yourself are good evidence of your team attitude.

Do not brag, just say it in a matter-of-fact tone. This is a key point. Tell me about your dream job. Stay away from a specific job. You cannot win. If you say the job you are contending for is it, you strain credibility. If you say another job is it, you plant the suspicion that you will be dissatisfied with this position if hired. The best is to stay genetic and say something like: What has been your biggest professional disappointment?

Be sure that you refer to something that was beyond your control. Show acceptance and no negative feelings. Tell me about your ability to work under pressure. You may say that you thrive under certain types of pressure. Give an example that relates to the type of position applied for. Do you have any blind spots? Trick question.

If you know about blind spots, they are no longer blind spots.