SQL Database Interview Questions & Answers

Question 1. What Are The Steps To Take To Improve Performance Of A Poor Performing Query?

Answer :

•  Maximum use of indexes, stored procures should be done.
•  Avoid excessive use of complicated joins and cursors.
•  Avoid using conditional operators using columns of different tables.
•  Make use of computed columns and rewriting the query.

Question 2. What Is A Deadlock And What Is A Live Lock?

Answer :

Deadlock occur in interconnection n/w when group of process are unable to act because of waiting each other to release some resource.

live lock packets continue to move through n/w, but does not advance towards destination.

Question 3. What Is Blocking And How Would You Troubleshoot It?

Answer :

Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.

Troubleshooting blocking:

•  SQL scripts can be written that constantly monitor the state of locking and blocking on SQL Server
•  The common blocking scenarios must be identified and resolved.
•  The scripts output must be checked constantly.
•  The SQL profilers data must be examined regularly to detect blocking.

Question 4. Explain The Different Types Of Backups Available In Sql Server.

Answer :

Complete database backup: This type of backup will backup all the information in the database. Used most commonly for disaster recovery and takes the longest time to backup.

Differential databse backup: The database is divided into partitions that have been modified since last complete backup. Most suitable for large databases. The most recent differential backup contains the changes from previous backups.

Transaction log backups: Backups only the changes logged in the transaction log. The transaction log has all changes logged about a database. Once the changes are accommodated on the database, the log is truncated or backed up.

File/File Group backups: used to recover individual files or file groups. Each filegroup can be individually backed up. This helps in recovery only the required file or filegroup for disaster recovery.

Question 5. What Is Database Isolation In Sql Server?

Answer :

Isolation in database defines how and when changes made by one transaction can be visible to other transactions. Different isolation levels are:

•  Serializable
•  Repeatable read
•  Read committed
•  Read uncommitted

Question 6. What Is A Schema In Sql Server 2005? Explain How To Create A New Schema In A Database?

Answer :

A schema is used to create database objects. It can be created using CREATE SCHEMA statement. The objects created can be moved between schemas. Multiple database users can share a single default schema.

CREATE SCHEMA sample;

Table creation

Create table sample.sampleinfo
{
id int primary key,
name varchar(20)
}

Question 7. Explain How To Create A Scrollable Cursor With The Scroll Option.

Answer :

Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence.

Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;

The active set of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or update statements.

Question 8. Explain How To Create A Dynamic Cursor With The Dynamic Option?

Answer :

When a cursor is declared as DYNAMIC, the cursor reflects all changes made to the base tables as the cursor is scrolled around.

Declare cursor_name cursor
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
FOR select_statement

The dynamic option does not support ABSOLUTE FETCH.

Question 9. What Are Database Files And Filegroups?

Answer :

Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:

Primary: starting point of a database. It also points to other files in database.
Extension: .mdf

Secondary: All data files except primary data file is a part of secondary files.
Extension: .ndf

Log files: All log information used to recover database.
Extension: .ldf

Question 10. Describe In Brief Databases And Sql Server Databases Architecture.

Answer :

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It’s only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.

Question 11. What Are The Steps To Improve The Performance Of A Query?

Answer :

● Number of joins and use of complex views/cursors have to be reduced.
● The use of the stored procedures and indexes have to be maximized.
● The optimized use of the complex conditional checks and computer columns have to be in place.
● Tracking of performance analysis for the query helps us in identifying the right aspects to optimize.

Question 12. How Would You Use The Sp_ Functions To Identify The Blocking Problems?

Answer :

Blocking is the deadlock situation when two SQL connections race to obtain the control over the same set of rows in conflicting terms. This can be tracked by the status of WAIT present in the SP_LOCK procedure’s output. All the active LOCKS and the different rows that are being involved are shown in this output. The identification of the connections involved in the specific row contention lock can be identified with sp_who and sp_who2 procedures. This way the causal agents of the blocking is identifies. KILL command issued against the specific SQL connection causing the BLOCK can resolve the issue. But the permanent solution lies in the proper design of the application code to execute in concurrence across different connections.

Question 13. What Are The Different Types Of Backups?

Answer :

The SQL server offers 4 types of backups to suit the need of the administrator.

● Complete backup- The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed.
● Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone back up mechanism. But can save a lot of time if we already have the file system related to the DB backed up on the new deployment server.
● Differential backup: This is a subset of the complete backup, where only the modified datasets are backed up. This can save the time when we are just trying to maintain a backup server to main server.
● File backup: This is the quickest way to take the backup of entire database. Instead of taking in the data actually stored in DB, the files are backed up and the file system thus obtained when combined with the transaction logs of the original system will render the database that we are trying to back up.

Question 14. What Are The Different Levels Of Isolation?

Answer :

The isolation represents the way of separating the database from the effects of network accesses, thereby maintaining the consistency. The different levels of isolation are:

● read committed: This level of isolation uses the shared locks and the reads to the database give the constant and consistent values.
● read uncommitted: No locks implemented. This is the least effective isolation level.
● repeatable read: There are locks over the rows and values but the updates are maintained as a separate phantom row which is the next set of values for the specific record. Values can change within a specific transaction of a SQL function.
● SERIALIZABLE reads: This is the implementation of pure lock mechanism where one specific transaction is not allowed access to specific record before another one completes.

Question 15. How Can You Start The Sql Server In The Single User Mode And The Minimal Configuration Mode?

Answer :

The SQLServer.exe is the executable which can be called in the command prompt with the parameters -m and -f. These are the options that will start the SQL server in the user mode and minimal configuration mode respectively.

Question 16. How Can You Know That Statistics Should Be Updated?

Answer :

Statistics represent the uniqueness for the indexes that are being used for selecting the records. This can make the query execution pretty efficient. The tables that we are dealing with if truncated and repopulated, there is a good chance that the indexes and statistics are out of sync and this is when we have to update the statistics. There are also other situations like when the table has been modified and lot of rows have been added recently or like when a server has been updated with different version of software. These also give us the reason to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it accordingly.

Question 17. What Is Replication In Sql Server?

Answer :

Replication refers to the moving or copying of the database elements from one system to another. This can be done in the SQL Server in one of the following methods:
● Transactional.
● Snapshot.
● Merge replication.

Question 18. Can We Initiate A External Com Object From Within Sql?

Answer :

Yes we can use the stored procedure sp_OACreate to initiate the external COM object from the T-SQL.

Question 19. What Is A Schema? How Is It Useful In Sql Servers?

Answer :

The Schema refers to the overall structure of the database with all related information like users, access privileges, interaction information between the different tables etc. The CREATE SCHEMA is the command that can be used to create the schema in the SQL Server. This when done can be used to re deploy the same database in another system for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.

Question 20. What Is A Write-ahead Log?

Answer :

The write-ahead log is the logging system that just updates the buffer cache of the database for the transactions and updates the logs and only then the actual changes are incorporated in the actual database. This is the reason why it is called “write ahead”. This helps in maintaining the consistency in the database. This can also be useful in getting the actual database values even in case of failures.

Question 21. What Is The Use Of Check Points In The Transaction Logs?

Answer :

The check points are restoration points that indicate the specific state of the database. When there is some failure in the database that is occurring before the next check point, the database can be reverted back to the previous check point and thus the database would still be consistent.

Question 22. What Is A Column With Identity?

Answer :

The column with a defined identity in turn means that there is an unique value that the system assigns to the specific column. This is similar to the AUTONumber property of the Access backend.

Question 23. What Are Scrollable Cursors? How Are They Created?

Answer :

The scrollable cursors are the ones that can get the entire set of rows as single entity, within which all the rows present can be accessed in any order without the open/close of cursor done for every row access. The scrollable cursors are created with the keyword SCROLL added to the CREATE Cursor statements. The scrollable cursors are useful for the access of information from different rows but not for the delete/insert of new rows.

Question 24. What Is Raid? How Does It Help Storage Of Databases?

Answer :

The RAID stands for Redundant Array of Independent Disks. With its own RAID controllers, the RAID implements a fail-safe storage mechanism with its own backup mechanisms. There are different configurations of the RAID that all give us the ACID properties of storage along with other such facilities. This kind of storage will make the SQL Server database to be failsafe and stable. This can sometimes mean that the backup mechanisms and other such reliability measures can be taken off from the SQL Server level of operations.

Question 25. How Can You Identify The Version Number Of The Sql Server Installed?

Answer :

The global variable version has the build and version information for the SQL Server and the service packs.

Question 26. What Is The Use Of Cascade Constraints?

Answer :

cascading is used for maintaining referencial integrity rules, which says that foreign key attributes values should be either subset of primary key values or null.

Question 27. What Is The Function Of A Odbc Manager ?

Answer :

The ODBC Manager manages all the data sources that exists in the system.

Question 28. What Are The Different Types Of Indexes Available In Sql Server?

Answer :

“Clustered and Non-Clustered Indexes”. There are other types of Indexes such as Unique, XML, Spatial and Filtered Indexes. 

Question 29. What Is The Difference Between Clustered And Non-clustered Index?

Answer :

•  In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
•  In a Non-Clustered index, the leaf level pages does not contain data pages instread it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

Question 30. What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?

Answer :

There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here :

•  Database Partitioning
•  Dynamic Management Views
•  System Catalog Views
•  Resource Database
•  Database Snapshots
•  SQL Server Integration Services
•  Support for Analysis Services on a a Failover Cluster.
•  Profiler being able to trace the MDX queries of the Analysis Server.
•  Peer-toPeer Replication
•  Database Mirroring

Question 31. What Are The High-availability Solutions In Sql Server?

Answer :

Failover Clustering, Database Mirroring, Log Shipping and Replication are the High- Availability features available in SQL Server.

Question 32. What Is Denormalization And When Would You Go For It?

Answer :

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

Question 33. How Do You Implement One-to-one, One-to-many And Many-to-many Relationships While Designing Tables?

Answer :

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Question 34. What's The Difference Between A Primary Key And A Unique Key?

Answer :

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Question 35. What Are User Defined Datatypes And When You Should Go For Them?

Answer :

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

Question 36. What Is Bit Datatype And What's The Information That Can Be Stored Inside A Bit Column?

Answer :

Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

Question 37. Define Candidate Key, Alternate Key, Composite Key.

Answer :

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.

Question 38. What Are Defaults? Is There A Column To Which A Default Can't Be Bound?

Answer :

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.

Question 39. What Is A Transaction And What Are Acid Properties?

Answer :

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

Question 40. Explain Different Isolation Levels?

Answer :

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
CREATE INDEX myIndex ON myTable(myColumn)

Question 41. What Type Of Index Will Get Created After Executing The Above Statement?

Answer :

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

Question 42. What's The Maximum Size Of A Row?

Answer :

8060 bytes.

Question 43. Differences Between Active/active And Active/passive Cluster Configurations?

Answer :

Active/Active :

• It is the bassically use for the default nodes, Here first node will be default and second node will be named instance. Both node will be active.
• Its Move group from cluster administration is possible for both side.
• System performance will go down, if both resources are in one node.

Active/Passive :

• Its also basically use for nodes But in this case Only one Active node with default instance. No system performance degradation will be there for this case even if we switchover to the other node. Both have same configuration.

Question 44. What Is Lock Escalation?

Answer :

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

Question 45. What's The Difference Between Delete Table And Truncate Table Commands?

Answer :

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

Question 46. What Are Constraints? Explain Different Types Of Constraints.

Answer :

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

Question 47. Whar Is An Index? What Are The Types Of Indexes? How Many Clustered Indexes Can Be Created On A Table? I Create A Separate Index On Each Column Of A Table. What Are The Advantages And Disadvantages Of This Approach?

Answer :

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

Question 48. How To Restart Sql Server In Single User Mode? How To Start Sql Server In Minimal Configuration Mode?

Answer :

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode.

Question 49. What Are Statistics, Under What Circumstances They Go Out Of Date, How Do You Update Them?

Answer :

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

1. If there is significant change in the key values in the index
2. If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3. Database is upgraded from a previous version

Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats.

Question 50. What Is Database Replicaion? What Are The Different Types Of Replication You Can Set Up In Sql Server?

Answer :

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

•  Snapshot replication
•  Transactional replication (with immediate updating subscribers, with queued updating subscribers)
•  Merge replication

Question 51. What Are The Components Of Physical Database Structure Of Oracle Database?

Answer :

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.

Question 52. What Are The Components Of Logical Database Structure Of Oracle Database?

Answer :

There are tablespaces and database's schema objects.

Question 53. What Is A Tablespace?

Answer :

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to group related logical structures together.

Question 54. What Is System Tablespace And When Is It Created?

Answer :

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.

Question 55. Explain The Relationship Among Database, Tablespace And Data File.

Answer :

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

Question 56. What Is Schema?

Answer :

A schema is collection of database objects of a user.

Question 57. What Are Schema Objects?

Answer :

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.

Question 58. Can Objects Of The Same Schema Reside In Different Tablespaces?

Answer :

Yes.

Question 59. Can A Tablespace Hold Objects From Different Schemes?

Answer :

Yes.

Question 60. What Is Oracle Table?

Answer :

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.

Question 61. What Is An Oracle View?

Answer :

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

Question 62. What Is Partial Backup ?

Answer :

A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

Question 63. What Is Mirrored On-line Redo Log ?

Answer :

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.

Question 64. What Is Full Backup ?

Answer :

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.

Question 65. Can A View Based On Another View ?

Answer :

Yes.

Question 66. Can A Tablespace Hold Objects From Different Schemes ?

Answer :

Yes.

Question 67. Can Objects Of The Same Schema Reside In Different Tablespaces.?

Answer :

Yes.

Question 68. What Is The Use Of Control File ?

Answer :

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.

Question 69. Do View Contain Data ?

Answer :

Views do not contain or store data.

Question 70. What Are The Referential Actions Supported By Foreign Key Integrity Constraint ?

Answer :

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.

Question 71. What Are The Type Of Synonyms?

Answer :

There are two types of Synonyms Private and Public.

Question 72. What Is A Redo Log ?

Answer :

The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

Question 73. What Is An Index Segment ?

Answer :

Each Index has an Index segment that stores all of its data.

Question 74. Explain The Relationship Among Database, Tablespace And Data File?

Answer :

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

Question 75. What Are The Different Type Of Segments ?

Answer :

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

Question 76. What Are Clusters ?

Answer :

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

Question 77. What Is An Integrity Constrains ?

Answer :

An integrity constraint is a declarative way to define a business rule for a column of a table.

Question 78. What Is An Index ?

Answer :

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.

Question 79. What Is An Extent ?

Answer :

An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

Question 80. What Is A View ?

Answer :

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

Question 81. What Is Table ?

Answer :

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.

Question 82. Can A View Based On Another View?

Answer :

Yes.

Question 83. What Are The Advantages Of Views?

Answer :

• Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
• Hide data complexity.
• Simplify commands for the user.
• Present the data in a different perspective from that of the base table.
• Store complex queries.

Question 84. What Is An Oracle Sequence?

Answer :

A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

Question 85. What Is A Synonym?

Answer :

A synonym is an alias for a table, view, sequence or program unit.

Question 86. What Are The Types Of Synonyms?

Answer :

There are two types of synonyms private and public.

Question 87. What Is A Private Synonym?

Answer :

Only its owner can access a private synonym.

Question 88. What Is A Public Synonym?

Answer :

Any database user can access a public synonym.

Question 89. What Are Synonyms Used For?

Answer :

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

Question 90. What Is An Oracle Index?

Answer :

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.

Question 91. How Are The Index Updates?

Answer :

Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

Question 92. What Is Rollback Segment ?

Answer :

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

Question 93. What Are The Characteristics Of Data Files ?

Answer :

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.

Question 94. How To Define Data Block Size ?

Answer :

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 datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.

Question 95. What Does A Control File Contain ?

Answer :

A Control file records the physical structure of the database. It contains the following information.

• Database Name
• Names and locations of a database's files and redolog files.
• Time stamp of database creation.

Question 96. What Is Difference Between Unique Constraint And Primary Key Constraint ?

Answer :

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.

Question 97. What Is Index Cluster ?

Answer :

A Cluster with an index on the Cluster Key.

Question 98. When Does A Transaction End ?

Answer :

When it is committed or Rollbacked.

Question 99. What Is The Effect Of Setting The Value "choose" For Optimizer_goal, Parameter Of The Alter Session Command ?

Answer :

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.

Question 100. 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 ?

Answer :

The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary. The OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

Question 101. How Does One Create A New Database?

Answer :

One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software. One can also create databases manually using scripts. This option, however, is falling out of fashion, as it is quite involved and error prone. Look at this example for creating and Oracle 9i database:

CONNECT SYS AS SYSDBA
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
CREATE DATABASE;

Question 102. What Database Block Size Should I Use?

Answer :

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 you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size. If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

Question 103. What Are The Different Approaches Used By Optimizer In Choosing An Execution Plan ?

Answer :

Rule-based and Cost-based.

Question 104. What Does Rollback Do ?

Answer :

ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

Question 105. What Is Cost-based Approach To Optimization ?

Answer :

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.

Question 106. What Does Commit Do ?

Answer :

COMMIT makes permanent 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.

Question 107. Define Transaction ?

Answer :

A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

Question 108. What Is Read-only Transaction ?

Answer :

A Read-Only transaction ensures that the results of each query executed in the transaction are consistent with respect to the same point in time.

Question 109. What Is A Deadlock ? Explain .

Answer :

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.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

Question 110. What Is A Schema ?

Answer :

The set of objects owned by user account is called the schema.

Question 111. What Is A Cluster Key ?

Answer :

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.

Question 112. What Is Parallel Server ?

Answer :

Multiple instances accessing the same database (Only In Multi-CPU environments).

Question 113. What Is Cluster?

Answer :

Group of tables physically stored together because they share common columns and are often used together is called Cluster.

Question 114. What Is An Index ? How It Is Implemented In Oracle Database ?

Answer :

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)

Question 115. What Is A Database Instance ? Explain

Answer :

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 improve database performance by decreasing the amount of I/O performed against data file.

Question 116. What Is The Use Of Analyze Command ?

Answer :

To perform one of these function on an index,table, or cluster:
- To collect statistics about object used by the optimizer and store them in the data dictionary.
- To delete statistics about the object used by object from the data dictionary.
- To validate the structure of the object.
- To identify migrated and chained rows of the table or cluster.

Question 117. What Is Default Tablespace ?

Answer :

The Tablespace to contain schema objects created without specifying a tablespace name.

Question 118. What Are The System Resources That Can Be Controlled Through Profile ?

Answer :

• 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.
• The amout of logical I/O available to a single call to ORACLE made by a SQL statement.
• The allowed amount of idle time for the user's session.
• The allowed amount of connect time for the user's session.

Question 119. What Is Tablespace Quota ?

Answer :

The collective amount of disk space available to the objects in a schema on a particular tablespace.

Question 120. What Are The Different Levels Of Auditing ?

Answer :

Statement Auditing, Privilege Auditing and Object Auditing.

Question 121. What Is Statement Auditing ?

Answer :

Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.

Question 122. What Are The Database Administrators Utilities Avaliable ?

Answer :

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 ORACLE format to and from ORACLE database.

Question 123. How Can You Enable Automatic Archiving ?

Answer :

• Shut the database
• Backup the database
• Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file.
• Start up the database.

Question 124. What Are Roles? How Can We Implement Roles ?

Answer :

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.

Question 125. What Are Roles ?

Answer :

Roles are named groups of related privileges that are granted to users or other roles.

Question 126. What Are The Uses Of Roles ?

Answer :

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

Question 127. What Is Privilege Auditing ?

Answer :

Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects.

Question 128. What Is Object Auditing ?

Answer :

Object auditing is the auditing of accesses to specific schema objects without regard to user.

Question 129. What Is Auditing ?

Answer :

Monitoring of user access to aid in the investigation of database use.

Question 130. Where Are My Tempfiles, I Don't See Them In V$datafile Or Dba_data_file?

Answer :

Tempfiles, unlike normal datafiles, are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files:

SELECT * FROM v$tempfile;
SELECT * FROM dba_temp_files;

Question 131. How Do I Find Used/free Space In A Temporary Tablespace?

Answer :

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;

Question 132. What Is A Profile ?

Answer :

Each database user is assigned a Profile that specifies limitations on various system resources available to the user.

Question 133. How Will You Enforce Security Using Stored Procedures?

Answer :

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.

Question 134. How Does One Get The View Definition Of Fixed Views/tables?

Answer :

Query v$fixed_view_definition.
Example: SELECT * FROM v$fixed_view_definition WHERE view_name='V$SESSION';

Question 135. What Are The Dictionary Tables Used To Monitor A Database Spaces ?

Answer :

• DBA_FREE_SPACE
• DBA_SEGMENTS
• DBA_DATA_FILES.

Question 136. What Is User Account In Oracle Database?

Answer :

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.

Question 137. What Is Dynamic Data Replication?

Answer :

Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.

Question 138. What Is Two-phase Commit ?

Answer :

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.

Question 139. How Can You Enforce Referential Integrity In Snapshots ?

Answer :

Time the references to occur when master tables are not in use. Peform the reference manually immediately locking the master tables. We can join tables in snapshots by creating a complex snapshots that will be based on the master tables.

Question 140. What Is A Sql * Net?

Answer :

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.

Question 141. What Is A Snapshot ?

Answer :

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.

Question 142. What Is The Mechanism Provided By Oracle For Table Replication ?

Answer :

Snapshots and SNAPSHOT LOGs.

Question 143. What Is Snapshot?

Answer :

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

Question 144. What Are The Various Type Of Snapshots?

Answer :

Simple and Complex.

Question 145. Describe Two Phases Of Two-phase Commit ?

Answer :

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.

Question 146. What Is Snapshot Log ?

Answer :

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.

Question 147. What Are The Benefits Of Distributed Options In Databases?

Answer :

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.

Question 148. What Are The Options Available To Refresh Snapshots ?

Answer :

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.

Question 149. What Is A Snapshot Log ?

Answer :

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.

Question 150. What Is Distributed Database ?

Answer :

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.

Question 151. How Can We Reduce The Network Traffic?

Answer :

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

Question 152. Differentiate Simple And Complex, Snapshots ?

Answer :

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

Question 153. What Are The Built-ins Used For Sending Parameters To Forms?

Answer :

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

Question 154. Can You Have More Than One Content Canvas View Attached With A Window?

Answer :

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.

Question 155. Is The After Report Trigger Fired If The Report Execution Fails?

Answer :

Yes.

Question 156. Does A Before Form Trigger Fire When The Parameter Form Is Suppressed?

Answer :

Yes.

Question 157. What Is Sga?

Answer :

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.

Question 158. What Is A Shared Pool?

Answer :

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.

Question 159. What Is Mean By Program Global Area (pga)?

Answer :

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

Question 160. What Is A Data Segment?

Answer :

Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

Question 161. What Are The Factors Causing The Reparsing Of Sql Statements In Sga?

Answer :

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.

Question 162. What Are Clusters?

Answer :

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

Question 163. What Is Cluster Key?

Answer :

The related columns of the tables in a cluster is called the cluster key.

Question 164. Do A View Contain Data?

Answer :

Views do not contain or store data.

Question 165. If A Parameter Is Used In A Query Without Being Previously Defined, What Difference Exist Between Report 2.0 And 2.5 When The Query Is Applied?

Answer :

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

Question 166. What Is Trigger Associated With The Timer?

Answer :

When-timer-expired.

Question 167. What Are The Triggers Associated With Image Items?

Answer :

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.

Question 168. What Are The Different Windows Events Activated At Runtimes?

Answer :

When_window_activated
When_window_closed
When_window_deactivated
When_window_resized
Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.

Question 169. When Do You Use Data Parameter Type?

Answer :

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. Data parameters are used to pass data to produts invoked with the run_product built-in subprogram.

Question 170. What Is Difference Between Open_form And Call_form?

Answer :

when one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as desired. when one form invokes another form by executing call_form, the called form is modal with respect to the calling 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.

Question 171. What Is New_form Built-in?

Answer :

When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.

Question 172. What Is The Diff. When Flex Mode Is Mode On And When It Is Off?

Answer :

When flex mode is on, reports automatically resizes the parent when the child is resized.

Question 173. What Is The Diff. When Confine Mode Is On And When It Is Off?

Answer :

When confine mode is on, an object cannot be moved outside its parent in the layout.

Question 174. What Are Visual Attributes?

Answer :

Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.

Question 175. What Are The Two Types Of Views Available In The Object Navigator(specific To Report 2.5)?

Answer :

View by structure and view by type .

Question 176. What Are The Vbx Controls?

Answer :

Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the ms visual basic environments and include such items as sliders, rides and knobs.

Question 177. What Is The Use Of Transactional Triggers?

Answer :

Using transactional triggers we can control or modify the default functionality of the oracle forms.

Question 178. How Do You Create A New Session While Open A New Form?

Answer :

Using open_form built-in setting the session option Ex. Open_form('Stocks ',active,session). when invoke the mulitiple forms with open form and call_form in the same application, state whether the following are true/False

Question 179. What Are The Ways To Monitor The Performance Of The Report?

Answer :

Use reports profile executable statement. Use SQL trace facility.

Question 180. An Open Form Can Not Be Execute The Call_form Procedure If You Chain Of Called Forms Has Been Initiated By Another Open Form?

Answer :

True.

Question 181. Explain About Horizontal, Vertical Tool Bar Canvas Views?

Answer :

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

Question 182. What Is The Purpose Of The Product Order Option In The Column Property Sheet?

Answer :

To specify the order of individual group evaluation in a cross products.

Question 183. What Is The Use Of Image_zoom Built-in?

Answer :

To manipulate images in image items.

Question 184. What Is A Timer?

Answer :

Timer is an "internal time clock" that you can programmatically create to perform an action each time the times.

Question 185. What Are The Two Phases Of Block Coordination?

Answer :

There are two phases of block coordination: the clear phase and the population phase. 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.

Question 186. What Are Most Common Types Of Complex Master-detail Relationships?

Answer :

There are three most common types of complex master-detail relationships:

• master with dependent details
• master with independent details
• detail with two masters

Question 187. What Is A Text List?

Answer :

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.

Question 188. What Is Term?

Answer :

The term is terminal definition file that describes the terminal form which you are using r20run.

Question 189. What Is Use Of Term?

Answer :

The term file which key is correspond to which oracle report functions.

Question 190. What Is Pop List?

Answer :

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.

Question 191. What Is The Maximum No. Of Chars The Parameter Can Store?

Answer :

The maximum no. of chars the parameter can store is only valid for char parameters, which can be upto 64K. No parameters default to 23Bytes and Date parameter default to 7Bytes.

Question 192. What Are The Default Extensions Of The Files Created By Library Module?

Answer :

The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary.

Question 193. How Do You Display Console On A Window ?

Answer :

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.

Question 194. What Are The Coordination Properties In A Master-detail Relationship?

Answer :

The coordination properties are

• Deferred
• Auto-Query

These Properties determine when the population phase of block coordination should occur.

Question 195. What Are The Different Parameter Types?

Answer :

• Text Parameters
• Data Parameters

Question 196. What Are The Types Of Calculated Columns Available?

Answer :

Summary, Formula, Placeholder column.

Question 197. Explain About Stacked Canvas Views?

Answer :

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.

Question 198. What Is The Difference Between Show_editor And Edit_textitem?

Answer :

Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.

Question 199. What Are The Different File Extensions That Are Created By Oracle Reports?

Answer :

Rep file and Rdf file.

Question 200. What Is The Basic Data Structure That Is Required For Creating An Lov?

Answer :

Record Group.

Question 201. What Is The Maximum Allowed Length Of Record Group Column?

Answer :

Record group column names cannot exceed 30 characters.

Question 202. Which Parameter Can Be Used To Set Read Level Consistency Across Multiple Queries?

Answer :

Read only.

Question 203. What Are The Different Types Of Record Groups?

Answer :

• Query Record Groups
• NonQuery Record Groups
• State Record Groups

Question 204. From Which Designation Is It Preferred To Send The Output To The Printed?

Answer :

Previewer.

Question 205. What Is Difference Between Post Database Commit And Post-form Commit?

Answer :

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.

Question 206. With Which Function Of Summary Item Is The Compute At Options Required?

Answer :

percentage of total functions.

Question 207. What Are Parameters?

Answer :

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.

Question 208. What Are The Three Types Of User Exits Available ?

Answer :

Oracle Precompiler exits, Oracle call interface, NonOracle user exits.

Question 209. How Many Windows In A Form Can Have Console?

Answer :

Only one window in a form can display the console, and you cannot change the console assignment at runtime.

Question 210. Is It Possible To Modify An External Query In A Report Which Contains It?

Answer :

No.

Question 211. Does A Grouping Done For Objects In The Layout Editor Affect The Grouping Done In The Data Model Editor?

Answer :

No.

Question 212. If A Break Order Is Set On A Column Would It Affect Columns Which Are Under The Column?

Answer :

No.

Question 213. Do User Parameters Appear In The Data Modal Editor In 2.5?

Answer :

No.

Question 214. Can You Pass Data Parameters To Forms?

Answer :

No.

Question 215. Is It Possible To Link Two Groups Inside A Cross Products After The Cross Products Group Has Been Created?

Answer :

No.

Question 216. What Are The Different Modals Of Windows?

Answer :

Modalless windows
Modal windows

Question 217. What Are Modal Windows?

Answer :

Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

Question 218. What Is The Advantage Of The Library?

Answer :

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.

Question 219. What Is Lexical Reference? How Can It Be Created?

Answer :

Lexical reference is place_holder for text that can be embedded in a sql statements. A lexical reference can be created using & before the column or parameter name.

Question 220. What Is System.coordination_operation?

Answer :

It represents the coordination causing event that occur on the master block in master-detail relation.

Question 221. What Is Synchronize?

Answer :

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.

Question 222. What Use Of Command Line Parameter Cmd File?

Answer :

It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.

Question 223. What Is A Text_io Package?

Answer :

It allows you to read and write information to a file in the file system.

Question 224. What Is Forms_ddl?

Answer :

Issues dynamic Sql statements at run time, including server side pl/SQl and DDL

Question 225. How Is Link Tool Operation Different Bet. Reports 2 & 2.5?

Answer :

In Reports 2.0 the link tool has to be selected and then two fields to be linked are selected and the link is automatically created. In 2.5 the first field is selected and the link tool is then used to link the first field to the second field.

Question 226. What Are The Different Styles Of Activation Of Ole Objects?

Answer :

In place activation
External activation

Question 227. What Are The Built-ins Used For Processing Rows?

Answer :

Get_group_row_count(function)
Get_group_selection_count(function)
Get_group_selection(function)
Reset_group_selection(procedure)
Set_group_selection(procedure)
Unset_group_selection(procedure)

Question 228. What Are The Built-ins Used For Getting Cell Values?

Answer :

GET_GROUP_CHAR_CELL (function)
GET_GROUPCELL(function)
GET_GROUP_NUMBET_CELL(function)

Question 229. Atleast How Many Set Of Data Must A Data Model Have Before A Data Model Can Be Based On It?

Answer :

Four.

Question 230. To Execute Row From Being Displayed That Still Use Column In The Row Which Property Can Be Used?

Answer :

Format trigger.

Question 231. What Is The Remove On Exit Property?

Answer :

For a modelless window, it determines whether oracle forms hides the window automatically when the operators navigates to an item in the another window.

Question 232. What Is When-database-record Trigger?

Answer :

Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. It generally occurs only when the operators modifies the first item in the record, and after the operator attempts to navigate out of the item.

Question 233. What Is A Difference Between Pre-select And Pre-query?

Answer :

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.

Question 234. What Are The Built-ins Used For Finding Object Id Function?

Answer :

FIND_GROUP(function)
FIND_COLUMN(function)

Question 235. Any Attempt To Navigate Programmatically To Disabled Form In A Call_form Stack Is Allowed?

Answer :

False

Question 236. How Can A Break Order Be Created On A Column In An Existing Group? What Are The Various Sub Events A Mouse Double Click Event Involves?

Answer :

By dragging the column outside the group.

Question 237. What Is The Use Of Place Holder Column? What Are The Various Sub Events A Mouse Double Click Event Involves?

Answer :

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.

Question 238. What Are The Built-ins Used For Creating And Deleting Groups?

Answer :

CREATE-GROUP (function)
CREATE_GROUP_FROM_QUERY(function)
DELETE_GROUP(procedure)

Question 239. What Are Different Types Of Canvas Views?

Answer :

• Content canvas views
• Stacked canvas views
• Horizontal toolbar
• vertical toolbar.

Question 240. What Are The Different Types Of Delete Details We Can Establish In Master-details?

Answer :

• Cascade
• Isolate
• Non-isolate

Question 241. What Is Relation Between The Window And Canvas Views?

Answer :

Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.

Question 242. What Is A User_exit?

Answer :

Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.

Question 243. How Is It Possible To Select Generate A Select Set For The Query In The Query Property Sheet?

Answer :

By using the tables/columns button and then specifying the table and the column names.

Question 244. How Can A Square Be Drawn In The Layout Editor Of The Report Writer?

Answer :

By using the rectangle tool while pressing the (Constraint) key.

Question 245. How Can A Text File Be Attached To A Report While Creating In The Report Writer?

Answer :

By using the link file property in the layout boiler plate property sheet.

Question 246. How Can I Message To Passed To The User From Reports?

Answer :

By using SRW.MESSAGE function.

Question 247. What Is Bind Reference And How Can It Be Created?

Answer :

Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.

Question 248. Give The Sequence Of Execution Of The Various Report Triggers?

Answer :

Before form , After form , Before report, Between page, After report.

Question 249. Why Is It Preferable To Create A Fewer No. Of Queries In The Data Model?

Answer :

Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.

Question 250. Where Is The External Query Executed At The Client Or The Server?

Answer :

At the server.

Question 251. Where Is A Procedure Return In An External Pl/sql Library Executed At The Client Or At The Server?

Answer :

At the client.

Question 252. What Is Coordination Event?

Answer :

Any event that makes a different record in the master block the current record is a coordination causing event.

Question 253. What Is The Difference Between Ole Server & Ole Container?

Answer :

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. oracle forms is an example of an ole Container.

Question 254. What Is An Object Group?

Answer :

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.

Question 255. What Is The Difference Between The Conventional And Direct Path Loader?

Answer :

The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. 

Question 256. How Does One Load Multi-line Records?

Answer :

One can create one logical record from multiple physical records using one of the following two clauses:

•  CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
•  CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

Question 257. Why Is A Where Clause Faster Than A Group Filter Or A Format Trigger?

Answer :

Because, in a where clause the condition is applied during data retrieval then after retrieving the data.

Question 258. How Can A Cross Product Be Created?

Answer :

By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.

Question 259. What Is Sql*loader And What Is It Used For?

Answer :

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

Question 260. How Is Possible To Restrict The User To A List Of Values While Entering Values For Parameters?

Answer :

By setting the Restrict To List property to true in the parameter property sheet.

Question 261. What Is A Display Item?

Answer :

Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.

Question 262. Where Can One Get More Information About Tcl?

Answer :

One can write custom event checking routines for OEM using the TCL (Tool Command Language) language. Check the following sites for more information about TCL:

•  The Tcl Developer Xchange - download and learn about TCL
•  OraTCL at Sourceforge - Download the OraTCL package
•  Tom Poindexter's Tcl Page - Oratcl was originally written by Tom Poindexter

Question 263. What Is The Oracle Intelligent Agent?

Answer :

The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is responsible for:

•  Discovering targets that can be managed (Database Servers, Net8 Listeners, etc.);
•  Monitoring of events registered in Enterprise Manager; and
•  Executing tasks associated with jobs submitted to Enterprise Manager.

Question 264. What Kind Of Jobs Can One Schedule With Oem?

Answer :

OEM comes with pre-defined jobs like Export, Import, run OS commands, run sql scripts, SQL*Plus commands etc. It also gives you the flexibility of scheduling custom jobs written with the TCL language.

Question 265. Difference Between Substr And Instr ?

Answer :

INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.

Question 266. Should The Oem Console Be Displayed At All Times (when There Are Scheduled Jobs)?

Answer :

When a job is submitted the agent will confirm the status of the job. When the status shows up as scheduled, you can close down the OEM console. The processing of the job is managed by the OIA (Oracle Intelligent Agent). The OIA maintains a .jou file in the agent's subdirectory. When the console is launched communication with the Agent is established and the contents of the .jou file (binary) are reported to the console job subsystem. Note that OEM will not be able to send e-mail and paging notifications when the Console is not started.

Question 267. What Is Rman ?

Answer :

Recovery Manager is a tool that manages the process of creating backups and also manages the process of restoring and recovering from them.

Question 268. What Are The Two Parts Of A Procedure ?

Answer :

Procedure Specification and Procedure Body.

Question 269. What Are The Datatypes Available In Pl/sql ?

Answer :

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

Question 270. What Is Overloading Of Procedures ?

Answer :

The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures. e.g. DBMS_OUTPUT put_line

Question 271. What Is The Optimal Parameter?

Answer :

It is used to set the optimal length of a rollback segment.

Question 272. What Is The Use Of Parfile Option In Exp Command ?

Answer :

Name of the parameter file to be passed for export.

Question 273. What Is Mean By Program Global Area (pga) ?

Answer :

It is area in memory that is used by a Single Oracle User Process.

Question 274. What Erase Package Procedure Does ?

Answer :

Erase removes an indicated global variable.

Question 275. What Is A Library?

Answer :

A library is a collection of subprograms including user named procedures, functions and packages.

Question 276. What Is A Master Detail Relationship?

Answer :

A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.

Question 277. What Does The Term Panel Refer To With Regard To Pages?

Answer :

A panel is the no. of physical pages needed to print one logical page.

Question 278. How Many Number Of Columns A Record Group Can Have?

Answer :

A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of column does not exceed 64K.

Question 279. What Is A Trace File And How Is It Created ?

Answer :

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

Question 280. What Are The Values That Can Be Specified For Optimizer Mode Parameter ?

Answer :

COST and RULE.