Oracle Developer interview questions and answers
ANS: Database buffers store the most recently used blocks of database data. It can also contain modified data that has not yet been permanently written to disk.
2. What packages has Oracle provided for use by developers?
ANS: Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.
3. What is the purpose of database links in Oracle?
ANS: Database links are created to establish communication between different databases or different environments such as development, test and production of the same database. The database links are usually designed to be read-only to access other database information
They are also useful when you want to copy production data into test environment for testing
4. What is Oracle’s data dictionary used for?
ANS: Data dictionary in Oracle contains information about all database objects such as tables, triggers, stored procedures, functions, indexes, constraints, views, users, roles, monitoring information, etc.
5. Explain use of SYSDATE and USER keywords?
ANS: SYSDATE is a pseudo column and refers to the current server system date. USER is a pseudo column and refers to the current user logged onto the oracle session. These values come handy when you want to monitor changes happening to the table.
6. Can you change the inserted value in one of the columns in AFTER insert trigger code?
ANS: This is not possible as the column values supplied by the insert SQL query are already inserted into the table. If you try to assign new value to the column in AFTER insert trigger code then oracle error would be raised. To alter any values supplied by insert SQL query create BEFORE insert trigger.
7. Why does a query in Oracle run faster when ROWID is used as a part of the where clause?
ANS: ROWID is the logical address of a row – it is not a physical column. It is composed of file number, data block number and row number within data block. Therefore I/O time is minimized retrieving the row, resulting in a faster query.
8. Give one method for transferring a table from one schema to another?
ANS: There are several possible methods, export-import, CREATE TABLE… AS SELECT, or COPY.
What happens if the constraint name is left out of a constraint clause?
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
9. When can Hash Cluster used?
ANS: Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
10. What constitute an ORACLE Instance?
ANS: SGA and ORACLE background processes constitute an ORACLE instance. (Or) Combination of memory structure and background process.
11. Explain Database Link?
ANS: A database link is a named object that describes a path from one database to another.
12. What is Network Database link?
ANS: Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.
13. What is Data Block?
ANS: ORACLE databases data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.
14. What are the components of Physical database structure of Oracle Database?
ANS: ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one or more Control files.
15. 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.
16. Explain the relationship among Database, Table space and Data file?
Each databases logically divided into one or more table spaces one or more data files are explicitly created for each table space.
17. What is a Public Synonyms?
ANS: A Public synonyms can be accessed by any user on the database.
18. What is Row Chaining?
ANS: In Circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.