Oracle 8i Interview Questions & Answers

  1. Question 1. How Will You Convert A Date To Char In Oracle Give One Example

    Answer :

    to_char() function is used to convert date to character we can specify format also in which we want the output.

    SELECT to_char( to_date(’11-01-2012′, ‘DD-MM-YYYY’) , ‘YYYY-MM-DD’) FROM dual;
    or
    SELECT to_char( to_date(’11-01-2012, ‘DD-MM-YYYY’) , ‘DD-MM-YYYY’) FROM dual;

  2. Question 2. How Will You Convert String To A Date In Oracle Database?

    Answer :

    This Oracle Interview questions is some time asked as follow up of previous Oracle Interview questions related to converting date to char in Oracle. By the way  to_ date function is used to convert string to a date function.
    Syntax :  to_date(string, format)

    Example:   to_date(‘2012/06/12’, ‘yyyy/mm/dd’)  It will return June 12, 2012

  3. Oracle 10g Interview Questions

  4. Question 3. What Is Database Structure And Space Management?

    Answer :

    An Oracle database is a collection of data that is treated as a unit. The purpose of a database is to store and retrieve related information. The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

  5. Question 4. What Is Tablespaces In Logical Database Structure?

    Answer :

    A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application’s objects to simplify some administrative operations.

  6. Oracle 10g Tutorial

  7. Question 5. What Is Oracle Data Blocks?

    Answer :

    At the finest level of granularity, Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. A data block size is specified for each Oracle database when the database is created. A database uses and allocates free database space in Oracle data blocks.

  8. Oracle 9i Interview Questions

  9. Question 6. How Rollback Segments For A Database Are Created?

    Answer :

    rollback segments for a database are created by the database administrator to temporarily store undo information. The information in a rollback segment is used:

    •To generate read-consistent database information
    •During database recovery
    •To rollback uncommitted transactions for users

  10. Question 7. What Is Physical Database Structures Explain?

    Answer :

    The following sections explain the physical database structures of an Oracle database, including datafiles, redo log files, and control files.

    Datafiles
    Every Oracle database has one or more physical datafiles. A database’s datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

    Redo Log Files
    Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database’s redo log. A redo log is made up of redo entries (also called redo records), each of which is a group of change vectors describing a single atomic change to the database.

    Control Files
    Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database.

  11. Oracle 9i Tutorial
    Informatica Interview Questions

  12. Question 8. Which Types Of Information Can Contains A Control Files?

    Answer :

    Control files  contains the following types of information:

    •Database name
    •Names and locations of datafiles and redo log files
    •Time stamp of database creation

  13. Question 9. What Is Memory Structure And Processes?

    Answer :

    Memory and process structures used by an Oracle server to manage a database. Among other things, the architectural features discussed in this section provide an understanding of the capabilities of the Oracle server to support:

    •Many users concurrently accessing a single database
    •The high performance required by concurrent multi-user, multi-application database systems
    An Oracle server uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database system.

  14. PL/SQL Interview Questions

  15. Question 10. What Is System Global Area?

    Answer :

    The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. An SGA and the Oracle background processes constitute an Oracle instance.

    Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area.

    Users currently connected to an Oracle server share the data in the system global area. For optimal performance, the entire system global area should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and minimize disk I/O.

  16. Oracle 8i Tutorial

  17. Question 11. What Is Process Architecture?

    Answer :

    A process is a “thread of control” or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.

  18. Oracle 11g Interview Questions

  19. Question 12. What Is User (client) Processes?

    Answer :

    A user process is created and maintained to execute the software code of an application program (such as a Pro*C/C++ program) or an Oracle tool (such as Oracle Enterprise Manager). The user process also manages the communication with the server processes.

  20. Oracle 10g Interview Questions

  21. Question 13. What Is Oracle Process Architecture?

    Answer :

    Oracle processes are called by other processes to perform functions on behalf of the invoking process. 

  22. Informatica Tutorial

  23. Question 14. What Is Background Processes?

    Answer :

    Oracle creates a set of background processes for each instance. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. The background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

  24. Question 15. What Is The Program Interface?

    Answer :

    The program interface is the mechanism by which a user process communicates with a server process. It serves as a method of standard communication between any client tool or application (such as Oracle Forms) and Oracle software. Its functions are to:

    •Act as a communications mechanism, by formatting data requests, passing data, and trapping and returning errors
    •Perform conversions and translations of data, particularly between different types of computers or to external user program datatypes

  25. SQL Interview Questions

  26. Question 16. What Are The Major Aspects Of The Relational Model?

    Answer :

    The relational model has three major aspects:

    Structures: Structures are well-defined objects (such as tables, views, indexes, and so on) that store or access the data of a database. Structures and the data contained within them can be manipulated by operations.

    Operations: Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations on a database must adhere to a predefined set of integrity rules.

    integrity rules :Integrity rules are the laws that govern which operations are allowed on the data and structures of a database. Integrity rules protect the data and the structures of a database.

  27. Oracle 11g Tutorial

  28. Question 17. What Is Data Concurrency?

    Answer :

    A primary concern of a multiuser database management system is how to control concurrency, or the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity.

    If many people are accessing the same data, one way of managing data concurrency is to make each user wait his or her turn. The goal of a database management system is to reduce that wait so it is either nonexistent or negligible to each user. All  data manipulation language statements should proceed with as little interference as possible and destructive interactions between concurrent transactions must be prevented. Destructive interaction is any interaction that incorrectly updates data or incorrectly alters underlying data structures. Neither performance nor data integrity can be sacrificed.

    Oracle resolves such issues by using various types of locks and a multiversion consistency model. Both features are discussed later in this section. These features are based on the concept of a transaction. It is the application designer’s responsibility to ensure that transactions fully exploit these concurrency and consistency features.

  29. Oracle apps Interview Questions

  30. Question 18. What Is Read Consistency?

    Answer :

    Read consistency, as supported by Oracle, does the following:

    •Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution (statement-level read consistency)
    •Ensures that readers of database data do not wait for writers or other readers of the same data
    •Ensures that writers of database data do not wait for readers of the same data Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions

  31. Oracle 9i Interview Questions

  32. Question 19. Why Is Recovery Important?

    Answer :

    In every database system, the possibility of a system or hardware failure always exists. Should a failure occur and affect the database, the database must be recovered. The goals after a failure are to ensure that the effects of all committed transactions are reflected in the recovered database and to return to normal operation as quickly as possible while insulating users from problems caused by the failure.

  33. Question 20. What Is Recovery Manager?

    Answer :

    Recovery Manager (RMAN) is an Oracle utility that manages backup and recovery operations, creating backups of database files (datafiles, control files, and archived redo log files) and restoring or recovering a database from backups. Recovery Manager maintains a repository called the recovery catalog, which contains information about backup files and archived log files. Recovery Manager uses the recovery catalog to automate both restore operations and media recovery.

    The recovery catalog contains:
    •Information about backups of datafiles and archive logs
    •Information about datafile copies
    •Information about archived redo logs and copies of them
    •Information about the physical schema of the target database
    •Named sequences of statements called stored scripts

  34. Sybase Interview Questions