Oracle DBA interview questions - Free download as Word Doc .doc), PDF File . pdf), Text File .txt) or read online for free. Oracle DBA Interview Question. MID LEVEL ORACLE DBA INTERVIEW QUESTIONS. SAMPLE Question 1. what is difference between Co-related sub query and nested sub query? Answer: . Oracle DBA Interview Questions Answers. By. Gitesh Trivedi. Publisher: Kendba Services. Copy right protected @ Kendba Services.
|Language:||English, Spanish, Indonesian|
|Distribution:||Free* [*Registration Required]|
This post has awesome oracle dba interview questions and answers Download of oracle dba interview questions as PDF is also given below. Oracle DBA Interview Questions 1. Explain the difference between a hot backup and a cold backup and the benefits associated with each. A hot backup is. + Oracle Dba Interview Questions and Answers, Question1: What is the difference between RMAN and a traditional hotbackup? Question2: What are bind.
Mask the real name and owner of an object. Provide public access to an object. Provide location transparency for tables, views or program units of a remote database. Simplify the SQL statements for database users. What Is An Oracle Index? 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.
How Are The Index Updates? Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes. What Is Rollback Segment? A Database contains one or more Rollback Segments to temporarily store "undo" information. A data file can be associated with only one database. Once created a data file can't change size.
One or more data files form a logical unit of database storage called a tablespace. Block size is specified in INIT. ORA file and can't be changed latter. What Is Index Cluster? When Does A Transaction End? Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed by the statement and their associated clusters and indexes. What Does Commit Do? The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
What Is Read-only Transaction? A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time. What Is A Deadlock? Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing proper row lock commands.
Poor design of front-end application may cause this situation and the performance of server will reduce drastically. What Is A Schema? The set of objects owned by user account is called the schema. What Is A Cluster Key? The related columns of the tables are called the cluster key.
The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster. What Is Parallel Server? What Is Clusters? Group of tables physically stored together because they share common columns and are often used together is called Cluster. An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table comman Ver 7.
What Is A Database Instance? A database instance Server is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database.
To perform one of these function on an index,table, or cluster:. What Is Default Tablespace? The Tablespace to contain schema objects created without specifying a tablespace name. What Is Tablespace Quota? The collective amount of disk space available to the objects in a schema on a particular tablespace. What Is Statement Auditing? Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.
Shut the database. Backup the database. Start up the database. What Are Roles? How Can We Implement Roles? Roles are the easiest way to grant and manage common privileges needed by different groups of database users. Creating roles and assigning provides to roles. Assign each role to group of users. This will simplify the job of assigning privileges to individual users.
Roles are named groups of related privileges that are granted to users or other roles. What Is Privilege Auditing? Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects. What Is Object Auditing? Object auditing is the auditing of accesses to specific schema objects without regard to user.
What Is A Profile? Each database user is assigned a Profile that specifies limitations on various system resources available to the user. Don't grant user access directly to tables within the application.
Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure. An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.
We must be able to create a after row trigger on table i. We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects that support its snapshot log. The master table name should be less than or equal to 23 characters.
What Dynamic Data Replication? Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
What Is Two-phase Commit? Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase. Time the references to occur when master tables are not in use.
Peform the reference the manually immdiately locking the master tables. We can join tables in snopshots by creating a complex snapshots that will based on the master tables. It is used in Clint-Server and Server-Server communications. What Is A Snapshot? Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
What Is Snapshots? Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals.
In ver 7. Prepare phase - The global coordinator initiating node ask a participants to prepare to promise to commit or rollback the transaction, even if there is a failure Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.
What Is Snapshot Log? It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.
Database on other servers can be updated and those transactions can be grouped together with others in a logical unit. Database uses a two phase commit. Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables. Default value. If possible it performs a FAST refresh. Otherwise it will perform a complete refresh. What Is A Snapshot Log?
A snapshot log is a table in the master database that is associated with the master table. Snapshot logs are used in updating the snapshots based on the master table. What Is Distributed Database? A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
Replication of data in distributed environment. Using snapshots to replicate data. Using remote procedure calls. Differentiate Simple And Complex, Snapshots? A complex snapshots contain atleast any one of the above. Each window you create must have atleast one content canvas view assigned to it.
You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time. The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database.
The structure is database buffers, dictionary cache, redo log buffer and shared pool area. It is area in memory that is used by a single Oracle user process. What Is A Data Segment? Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
Due to insufficient shared pool size. Monitor the ratio of the reloads takes place while executing SQL statements. What Is Cluster Key? The related columns of the tables in a cluster are called the cluster key.
Do A View Contain Data? Exist Betw. Report 2. While both reports 2. When-image-activated fires when the operators double clicks on an image item when-image pressed fires when an operator clicks or double clicks on an image item. When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form.
That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded. Whenever the validation event occurs.
If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.
What Is The Diff? When flex mode is on, reports automatically resizes the parent when the child is resized. When confine mode is on, an object cannot be moved outside its parent in the layout. What Are Visual Attributes? Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.
What Are The Vbx Controls? Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.
Using transactional triggers we can control or modify the default functionality of the oracle forms. Use reports profile executable statement.
Use SQL trace facility. Two group that is above are the left most rank higher than the group that is to right or below it. 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. To specify the order of individual group evaluation in a cross products. What Is A Timer? Timer is an "internal time clock" that you can programmatically create to perform an action each time the times. There are two phases of block coordination: During, the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records.
During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with detail records associated with the new master record.
These operations are accomplished through the execution of triggers. There are three most common types of complex master-detail relationships:. What Is A Text List? The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values. The term is terminal definition file that describes the terminal form which you are using r20run.
What Is Use Of Term? The term file which key is correspond to which oracle report functions. What Is Pop List?
The pop list style list item appears initially as a single field similar to a text item field. When the operator selects the list icon, a list of available choices appears.
The maximum no of chars the parameter can store is only valid for char parameters, which can be upto 64K. No parameters default to 23 Bytes and Date parameter default to 7 Bytes.
The default file extensions indicate the library module type and storage format. The coordination properties are: The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.
Text Parameters and, Data Parameters. Summary, Formula, Placeholder column. Explain About Stacked Canvas Views? 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.
Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Rep file and, Rdf file. Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode.
In backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas changes are logged to the redo logs.
This is done to enable reconstruction of a block if only half of it was backed up split blocks. Because of this, one should notice increased log activity and archiving during on-line backups. Record group column names cannot exceed 30 characters. Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command.
The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions. What Are Parameters? Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type char, number, date that you define at design time.
Oracle Precompiler exits, Oracle call interface, NonOracle user exits. Only one window in a form can display the console, and you cannot change the console assignment at runtime. What Is An Administrative privileged User?
Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. Control of these privileges is managed outside of the database via password files and special operating system groups. This password file is created with the orapwd utility. One down repeating frame below one across repeating frame. What Are The Master-detail Triggers? One can select from the SYS. Modalless windows, Modal windows.
What Are Modal Windows? Modal windows are usually used as dialogs, and have restricted functionality compared to modalless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.
Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns.
Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library.
Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications. 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 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.
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? 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. 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.
By using SRW. 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. 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.
An 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 3GL 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. 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 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. What Is A Physical Page? 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. This option can be shortened to TERM.
This is created using the utlxplan.
Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. Explain plans can also be run using tkprof.
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. What Is A Cartesian Product? Cartesian product is the result of an unrestricted join of two or more tables.
By use of the exclamation point "! They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. They can be used to hold values for use in later queries or calculations. This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
INI file to load the required drivers to access that particular back end database. The name of the DBMS, the location of the source and the database dependent information.
ODBC driver. What Is Inheritance? Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it. A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output. Because an event driven program is always waiting for something to happen before processing.
The application cannot use any special features of the backend server. The application can be connected to any back end server that is supported by the API. Manage resources optimally across multiple clients. Controlling database access and security. Protecting the databse and recovering it from crashes.
Enforcing integrity rules globally. A client process at first establishes connection with the Server.
Star, Bus, Ring. Heterogeneity of the system results in reduced reliablity, it May not be suitable for all applications. Managing and tuning networks becomes difficult. Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked. Because it is easier to define an integrity constraint than a database trigger. What Is A Transaction? A transaction is a set of operations that begin when the first DML is issued and end when a commit or rollback is issued. Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.
What Is An Integrity Constraint? An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table. What Is A Event Handler? An event handler is a routine that is written to respond to a particular event. Foreground process and, Background process. When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
What Is Event Trigger? An event trigger , a segment of code which is associated with each event and is fired when the event occurs.
A host based application is re-engineered to run in smaller or LAN based environment. With rightsizing , one would move applications to the most appropriate server platforms. Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing. To reduce the network traffic and for application sharing and implementing business rules.
The client deals with the user interface part of the system. Preemptive refers to the fact that each task is alloted fixed time slots and at the end of that time slot the next task is started. To protect some of the columns of a table from other users. To hide complexity of a query. To hide complexity of calculations. CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value.
After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
What Is A Database Link? Database link is a named path through which a remote database can be accessed. To modify the datatype of a column the column must be empty. Only one LONG column is allowed.
CHAR pads blank spaces to the maximum length. What Is On Delete Cascade? It enables rolling back part of a transaction. What Is Network Database Link? Answer : Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.
What Is Data Block? Answer : Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. A tablespace can have multiple datafiles. It contains read only data dictionary tables which contains the information about the database.
Answer:The datafiles contain all the database data. The data of logical database structures, such as tables and indexes , is physically stored in the datafiles allocated for a database. Answer:Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database such as Database name and the Names and locations of datafiles and redo log files.
Answer The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. This prevents oracle from overwriting the redo log files before they have been safely archived to another location.
Answer:A user account and its associated data including tables, views , indexes, clusters, sequences ,procedures, functions, triggers,packages and database links is known as Oracle schema.
Answer:Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk. The data blocks can be 4 K,8 K size depending on the requirement.