Oracle Interview Questions – Part 4

Q.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.

Q.What is snapshots?

Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. In ver 7.0 they are read only.

Q.Describe two phases of Two-phase commit ?

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.

Q.What are the benefits of distributed options in databases?

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.

Q.What is a SNAPSHOT LOG ?

A snapshot log is a table in the master database that is associated with the master table. ORACLE uses a snapshot log to track the rows that have been updated in the master table. Snapshot logs are used in updating the snapshots based on the master table.

Q.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.

Q.What are the various type of snapshots?
Simple and Complex.

Q.How can we reduce the network traffic?

-Replication of data in distributed environment.
-Using snapshots to replicate data.
-Using remote procedure calls.

Q.What is the mechanism provided by ORACLE for table replication ?

Snapshots and SNAPSHOT LOGs

Q.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.

Q.Differentiate simple and complex, snapshots?

-A simple snapshot is based on a query that does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.
-A complex snapshots contain atleast any one of the above.

Q.What are the Built-ins used for sending Parameters to forms?

You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.

Q.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.

Q.Can you have more than one content canvas view attached with a window?
Yes. 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.

Q.Is the After report trigger fired if the report execution fails?


Q.If a parameter is used in a query without being previously defined, what diff. exist between. report 2.0 and 2.5 when the query is applied?

While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.

Q.What are the sql clauses supported in the link property sheet?

Where start with having.

Q.Does a Before form trigger fire when the parameter form is suppressed?

Q.What are clusters?

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

Q.What is a shared pool?

The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.

Q.What are the options available to refresh snapshots ?

COMPLETE – Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
FAST – If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
FORCE – Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.

Q.What is mean by Program Global Area (PGA)?

It is area in memory that is used by a single Oracle user process.

Q.What is SGA?

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.

Q.What are the factors causing the reparsing of SQL statements in SGA?

Due to insufficient shared pool size.
Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

Q.Do a view contain data?

Views do not contain or store data.

Q.What is user Account in Oracle database?

A 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.

Q.How will you enforce security using stored procedures?

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.

Q.What are the dictionary tables used to monitor a database space?


Q.What is cluster key?
The related columns of the tables in a cluster are called the cluster key.

Q.Can a property clause itself be based on a property clause?



This site uses Akismet to reduce spam. Learn how your comment data is processed.