Oracle Interview Questions
 
Q: What is an Oracle view?
Ans : A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Partial Backup ?
Ans : A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Mirrored on-line Redo Log ?
Ans : A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Full Backup ?
Ans : A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a View based on another View ?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a Tablespace hold objects from different Schemes ?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can objects of the same Schema reside in different tablespace ?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the use of Control File ?
Ans : When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Do View contain Data ?
Ans : Views do not contain or store data.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the Referential actions supported by FOREIGN KEY integrity constraint ?
Ans : UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the type of Synonyms?
Ans : There are two types of Synonyms Private and Public.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Redo Log ?
Ans : The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Index Segment ?
Ans : Each Index has an Index segment that stores all of its data.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the relationship among Database, Tablespace and Data file?
Ans : Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the different type of Segments ?
Ans : Data Segment, Index Segment, Rollback Segment and Temporary Segment.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are Clusters ?
Ans : Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Integrity Constrains ?
Ans : An integrity constraint is a declarative way to define a business rule for a column of a table.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Index ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Extent ?
Ans : An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a View ?
Ans : A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Table ?
Ans : A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a view based on another view?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Oracle sequence?
Ans : A sequence generates a serial list of unique numbers for numerical columns of a database's tables.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a synonym?
Ans : A synonym is an alias for a table, view, sequence or program unit.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the types of synonyms?
Ans : There are two types of synonyms private and public.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a private synonym?
Ans : Only its owner can access a private synonym.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a public synonym?
Ans : Any database user can access a public synonym.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Oracle index?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How are the index updates?
Ans : Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Tablespace?
Ans : A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Rollback Segment ?
Ans : A Database contains one or more Rollback Segments to temporarily store "undo" information.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the Characteristics of Data Files ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How to define Data Block size ?
Ans : A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data blocks. Block size is specified in INIT.ORA file and can't be changed latter.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
Ans : A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Index Cluster ?
Ans : A Cluster with an index on the Cluster Key
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: When does a Transaction end ?
Ans : When it is committed or Rollbacked.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
Ans : Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
Ans : The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What database block size should I use? (for DBA)
Ans : Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the different approaches used by Optimizer in choosing an execution plan ?
Ans : Rule-based and Cost-based.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What does ROLLBACK do ?
Ans : ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Define Transaction ?
Ans : A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Read-Only Transaction ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a deadlock ? Explain .
Ans : 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
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is default tablespace ?
Ans : The Tablespace to contain schema objects created without specifying a tablespace name.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the Characteristics of Data Files ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How to define Data Block size ?
Ans : A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data blocks. Block size is specified in INIT.ORA file and can't be changed latter.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
Ans : A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Index Cluster ?
Ans : A Cluster with an index on the Cluster Key
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: When does a Transaction end ?
Ans : When it is committed or Rollbacked.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
Ans : Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
Ans : The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What database block size should I use? (for DBA)
Ans : Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the different approaches used by Optimizer in choosing an execution plan ?
Ans : Rule-based and Cost-based.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What does ROLLBACK do ?
Ans : ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is COST-based approach to optimization ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What does COMMIT do ?
Ans : COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction is committed.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Define Transaction ?
Ans : A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Read-Only Transaction ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a deadlock ? Explain .
Ans : 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
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Schema ?
Ans : The set of objects owned by user account is called the schema.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a cluster Key ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Parallel Server ?
Ans : Multiple instances accessing the same database (Only In Multi-CPU environments)
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is clusters ?
Ans : Group of tables physically stored together because they share common columns and are often used together is called Cluster.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Index ? How it is implemented in Oracle Database ?
Ans : 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.0)
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Database instance ? Explain
Ans : 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. This helps up to im
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is default tablespace ?
Ans : The Tablespace to contain schema objects created without specifying a tablespace name.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the system resources that can be controlled through Profile ?
Ans : The number of concurrent sessions the user can establish the CPU processing time available to the user's session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user's session
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Tablespace Quota ?
Ans : The collective amount of disk space available to the objects in a schema on a particular tablespace.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the different Levels of Auditing ?
Ans : Statement Auditing, Privilege Auditing and Object Auditing.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Statement Auditing ?
Ans : Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the database administrators utilities available ?
Ans : SQL * DBA - This allows DBA to monitor and control an ORACLE database. SQL * Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables. Export (EXP) and Import (imp) utilities allow you to move existing data in O
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are roles? How can we implement roles ?
Ans : 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 individ
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are Roles ?
Ans : Roles are named groups of related privileges that are granted to users or other roles.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How can a button be used in a report to give a drill down facility?
Ans : By setting the action associated with button to Execute pl/sql option and using the SRW.Run_report function.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are different types of images?
Ans : Boiler plate imagesImage Items
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Give the sequence of execution of the various report triggers?
Ans : Before form , After form , Before report, Between page, After report.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Why is it preferable to create a fewer no. of queries in the data model?
Ans : Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Where is the external query executed at the client or the server?
Ans : At the server.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Where is a procedure return in an external pl/sql library executed at the client or at the server?
Ans : At the client.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is coordination Event?
Ans : Any event that makes a different record in the master block the current record is a coordination causing event.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the difference between OLE Server & Ole Container?
Ans : An Ole server application creates ole Objects that are embedded or linked in ole Containers ex. Ole servers are ms_word & ms_excel. OLE containers provide a place to store, display and manipulate objects that are created by ole server applications. Ex. or
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an object group?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an LOV?
Ans : An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: At what point of report execution is the before Report trigger fired?
Ans : After the query is executed but before the report is executed and the records are displayed.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an user exit used for?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the User-Named Editor?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Static Record Group?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a record group?
Ans : A record group is an internal Oracle Forms that structure that has a column/row framework similar to a database table. However, unlike database tables, record groups are separate objects that belong to the form module which they are defined.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: My database is down and I cannot restore. What now? (for DBA )
Ans : 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). This utility reads data in the data files and unloads it into SQL*Loader or exp
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: I've lost my REDOLOG files, how can I get my DB back? (for DBA)
Ans : 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. _allow_
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a property clause?
Ans : 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 se
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a physical page ? & What is a logical page ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the differences between EBU and RMAN? (for DBA)
Ans : Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility. The Oracle Recovery Manager (RMAN) utility that ships with Oracl
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How can a group in a cross products be visually distinguished from a group that does not form a cross product?
Ans : A group that forms part of a cross product will have a thicker border.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the frame & repeating frame?
Ans : A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not known before.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a combo box?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: To display the page no. for each page on a report what would be the source & logical page no. or & of physical page no.?
Ans : & physical page no.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: It is possible to use raw devices as data files and what is the advantages over file. system files ?
Ans : Yes. The advantages over file system files. I/O will be improved because Oracle is bye-passing the kernnel which writing into disk. Disk Corruption will be very less.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are disadvantages of having raw devices ?
Ans : We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the significance of having storage clause ?
Ans : We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.,
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the use of INCTYPE option in EXP command ?
Ans : Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL. 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. e. will be wri
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the use of FILE option in IMP command ?
Ans : The name of the file from which import should be performed.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Shared SQL pool?
Ans : The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is hot backup and how it can be taken?
Ans : Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the difference between a data block, an extent and a segment.
Ans : A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents t
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Give two examples of how you might determine the structure of the table DEPT.
Ans : Use the describe command or use the dbms_metadata.get_ddl package.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Where would you look for errors from the database engine?
Ans : In the alert log.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Compare and contrast TRUNCATE and DELETE for a table.
Ans : Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The del
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Give the reasoning behind using an index.
Ans : Faster access to data blocks in a table.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Give the two types of tables involved in producing a star schema and the type of data they hold.
Ans : Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What type of index should you use on a fact table?
Ans : A Bitmap index.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Give two examples of referential integrity constraints.
Ans : A primary key and a foreign key.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Ans : Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
Ans : ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What command would you use to create a backup control file?
Ans : Alter database backup control file to trace.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What column differentiates the V$ views to the GV$ views and how?
Ans : The INST_ID column which indicates the instance in a RAC environment the information came from.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How would you go about increasing the buffer cache hit ratio?
Ans : Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain an ORA-01555
Ans : You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
Ans : ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How would you determine the time zone under which a database was operating?
Ans : select DBTIMEZONE from dual;
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the use of setting GLOBAL_NAMES equal to TRUE.
Ans : Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What command would you use to encrypt a PL/SQL application?
Ans : WRAP
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
Ans : A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the use of table functions.
Ans : Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Name three advisory statistics you can collect.
Ans : Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Where in the Oracle directory tree structure are audit traces placed?
Ans : In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain materialized views and how they are used.
Ans : Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: When a user process fails, what background process cleans up after it?
Ans : PMON
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What background process refreshes materialized views?
Ans : The Job Queue Processes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How would you determine what sessions are connected and what resources they are waiting for?
Ans : Use of V$SESSION and V$SESSION_WAIT
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Describe what redo logs are.
Ans : Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How would you force a log switch?
Ans : ALTER SYSTEM SWITCH LOGFILE;
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Give two methods you could use to determine what DDL changes have been made.
Ans : You could use Logminer or Streams
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What does coalescing a tablespace do?
Ans : Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
Ans : A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Name a tablespace automatically created when you create a database.
Ans : The SYSTEM tablespace.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the components of physical database structure of Oracle database?
Ans : Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a tablespace?
Ans : A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is SYSTEM tablespace and when is it created?
Ans : Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the relationship among database, tablespace and data file ?
Ans : Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is schema?
Ans : A schema is collection of database objects of a user.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are Schema Objects?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can objects of the same schema reside in different tablespaces?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a tablespace hold objects from different schemes?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Oracle table?
Ans : A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the system resources that can be controlled through Profile ?
Ans : The number of concurrent sessions the user can establish the CPU processing time available to the user's session the CPU processing time available to a single call to ORACLE made by a SQL statement the amount of logical I/O available to the user's session
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Tablespace Quota ?
Ans : The collective amount of disk space available to the objects in a schema on a particular tablespace.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the different Levels of Auditing ?
Ans : Statement Auditing, Privilege Auditing and Object Auditing.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Statement Auditing ?
Ans : Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the database administrators utilities available ?
Ans : SQL * DBA - This allows DBA to monitor and control an ORACLE database. SQL * Loader - It loads data from standard operating system files (Flat files) into ORACLE database tables. Export (EXP) and Import (imp) utilities allow you to move existing data in O
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How can we specify the Archived log file name format and destination?
Ans : By setting the following values in init.ora file. LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S - Log sequence number and is zero left paded, %s - Log sequence number not padded. %T - Thread number lef-zero-paded and %t - Thread number not padded). The file n
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is user Account in Oracle database?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: When will the data in the snapshot log be used?
Ans : We must be able to create a after row trigger on table (i.e., it should be not be already available) After giving table privileges. We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects th
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What dynamic data replication?
Ans : Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Two-Phase Commit ?
Ans : 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 C
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How can you Enforce Referential Integrity in snapshots ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a SQL * NET?
Ans : SQL *NET is ORACLE's mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a SNAPSHOT ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the mechanism provided by ORACLE for table replication ?
Ans : Snapshots and SNAPSHOT LOGs
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is snapshots?
Ans : Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. In ver 7.0 they are read only.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the various type of snapshots?
Ans : Simple and Complex.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Describe two phases of Two-phase commit ?
Ans : 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, t
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is snapshot log ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a SNAPSHOT LOG ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Distributed database ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the Built-ins used for sending Parameters to forms?
Ans : You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can you have more than one content canvas view attached with a window?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Is the After report trigger fired if the report execution fails?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Does a Before form trigger fire when the parameter form is suppressed?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is SGA?
Ans : 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 informat
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a shared pool?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is mean by Program Global Area (PGA)?
Ans : It is area in memory that is used by a single Oracle user process.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a data segment?
Ans : Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the components of physical database structure of Oracle database?
Ans : Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the components of logical database structure of Oracle database?
Ans : There are tablespaces and database's schema objects.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a tablespace?
Ans : A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is SYSTEM tablespace and when is it created?
Ans : Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the relationship among database, tablespace and data file ?
Ans : Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is schema?
Ans : A schema is collection of database objects of a user.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are Schema Objects?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can objects of the same schema reside in different tablespaces?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a tablespace hold objects from different schemes?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Oracle table?
Ans : A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Oracle view?
Ans : A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Partial Backup ?
Ans : A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Mirrored on-line Redo Log ?
Ans : A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Full Backup ?
Ans : A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a View based on another View ?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a Tablespace hold objects from different Schemes ?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can objects of the same Schema reside in different tablespace ?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the use of Control File ?
Ans : When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Do View contain Data ?
Ans : Views do not contain or store data.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the Referential actions supported by FOREIGN KEY integrity constraint ?
Ans : UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the type of Synonyms?
Ans : There are two types of Synonyms Private and Public.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Redo Log ?
Ans : The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Index Segment ?
Ans : Each Index has an Index segment that stores all of its data.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Explain the relationship among Database, Tablespace and Data file?
Ans : Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the different type of Segments ?
Ans : Data Segment, Index Segment, Rollback Segment and Temporary Segment.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are Clusters ?
Ans : Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Integrity Constrains ?
Ans : An integrity constraint is a declarative way to define a business rule for a column of a table.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Index ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Extent ?
Ans : An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a View ?
Ans : A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Table ?
Ans : A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can a view based on another view?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Oracle sequence?
Ans : A sequence generates a serial list of unique numbers for numerical columns of a database's tables.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a synonym?
Ans : A synonym is an alias for a table, view, sequence or program unit.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the types of synonyms?
Ans : There are two types of synonyms private and public.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a private synonym?
Ans : Only its owner can access a private synonym.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a public synonym?
Ans : Any database user can access a public synonym.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is an Oracle index?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How are the index updates?
Ans : Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a Tablespace?
Ans : A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Rollback Segment ?
Ans : A Database contains one or more Rollback Segments to temporarily store "undo" information.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Privilege Auditing ?
Ans : Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Object Auditing ?
Ans : Object auditing is the auditing of accesses to specific schema objects without regard to user.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Auditing ?
Ans : Monitoring of user access to aid in the investigation of database use.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How do I find used/free space in a TEMPORARY tablespace? (for DBA )
Ans : Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:SELECT tablespace_name, SUM (bytes used), SUM (bytes free) FROM V$temp_space_header GROUP BY tablespace_name;
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a profile ?
Ans : Each database user is assigned a Profile that specifies limitations on various system resources available to the user.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How will you enforce security using stored procedures?
Ans : 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
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How does one get the view definition of fixed views/tables?
Ans : Query v$fixed_view_definition. Example: SELECT * FROM v$fixed_view_definition WHERE view_name='V$SESSION';
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the dictionary tables used to monitor a database spaces ?
Ans : DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How can we specify the Archived log file name format and destination?
Ans : By setting the following values in init.ora file. LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S - Log sequence number and is zero left paded, %s - Log sequence number not padded. %T - Thread number lef-zero-paded and %t - Thread number not padded). The file n
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is user Account in Oracle database?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: When will the data in the snapshot log be used?
Ans : We must be able to create a after row trigger on table (i.e., it should be not be already available) After giving table privileges. We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects th
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What dynamic data replication?
Ans : Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Two-Phase Commit ?
Ans : 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 C
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: How can you Enforce Referential Integrity in snapshots ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a SQL * NET?
Ans : SQL *NET is ORACLE's mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a SNAPSHOT ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is the mechanism provided by ORACLE for table replication ?
Ans : Snapshots and SNAPSHOT LOGs
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is snapshots?
Ans : Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. In ver 7.0 they are read only.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the various type of snapshots?
Ans : Simple and Complex.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Describe two phases of Two-phase commit ?
Ans : 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, t
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is snapshot log ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the benefits of distributed options in databases?
Ans : 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
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the options available to refresh snapshots ?
Ans : 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
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a SNAPSHOT LOG ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is Distributed database ?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the Built-ins used for sending Parameters to forms?
Ans : You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Can you have more than one content canvas view attached with a window?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Is the After report trigger fired if the report execution fails?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: Does a Before form trigger fire when the parameter form is suppressed?
Ans : Yes.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is SGA?
Ans : 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 informat
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a shared pool?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is mean by Program Global Area (PGA)?
Ans : It is area in memory that is used by a single Oracle user process.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What is a data segment?
Ans : Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are the factors causing the reparsing of SQL statements in SGA?
Ans : 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.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
Q: What are clusters?
Ans : Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
Date submitted :8/8/2009 1:03:38 AM
Submitted by : shabir@myfaithsolution.com
1