DBMS Basics technical interview question

49. Explain the three levels of transaction isolation supported by Oracle.

Oracle supports read committed, serializable, and read-only transaction isolation levels. Because of the way Oracle System Change Number (SCN) values are processed, Oracle never reads dirty data. Serializable isolation is possible, but the application program must be written to process the “Cannot serialize” exception. Applications can place locks explicitly using SELECT FOR UPDATE commands but this is not recommended.

50. What are the types of files used in Oracle recovery?

Datafiles, control files and two types of ReDo log files: OnLine Redo and Offline ReDo (which is also known as Archive ReDo).

51. What is the difference between SQL Server 2000 complete and differential backups?

A complete backup makes a copy of the entire database. A differential backup makes a copy of the changes that have been made to the database since the last complete backup. A complete backup must be made before the first differential backup. Because differential backups are faster, they can be taken more frequently and the chance of data loss is reduced. Complete backups take longer but are slightly simpler to use for recovery.

52. Explain the meaning of each of the transaction levels supported by SQL Server.

The strictest isolation level is SERIALIZABLE. With it, SQL Server places a range lock on the rows that have been read. This level is the most expensive to use and should only be used when absolutely required. The next most restrictive level is REPEATABLE READ, which means SQL Server places and holds locks on all rows that are read. It is possible to make dirty reads by setting the isolation level to READ UNCOMMITTED, which is the least restrictive level. READ COMMITTED is the default isolation level.

53. Explain the difference between the SQL Server 2000 simple, full, and bulk-logged recovery models.

With the simple recovery model, no logging is done. The only way to recover a database is to restore the database to the last backup. With full recovery, all database changes are logged. With bulk-logged database recovery, all changes are logged except those that cause large log entries.

54. What is the difference between SQL Server 2000 clustered and nonclustered indexes?

With a clustered index, the data are stored in the bottom level of the index and in the same order as that index. With a nonclustered index, the bottom level of an index does not contain data; it contains pointers to the data. For data retrieval, clustered indexes are faster than nonclustered indexes.