Interview Questions:Oracle

Oracle Interview Questions

1. What are the various types of queries?

The types of queries are:
Normal Queries
Sub Queries
Co-related queries
Nested queries
Compound queries

2. What is a transaction ?
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

3.. What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files.

4. What are the components of logical database structure of Oracle database?
There are tablespaces and database’s schema objects.

5. What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

6.What is implicit cursor and how is it used by Oracle ?
An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

7· Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Public synonyms

8. What is SYSTEM tablespace and when is it created?
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.

9. Explain the relationship among database, tablespace and data file.
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

10. What is schema?
A schema is collection of database objects of a user.

11·What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

12·Is there a PL/SQL Engine in SQL*Plus?
No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

13. What are Schema Objects?
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.

14. Can objects of the same schema reside in different tablespaces?
Yes.

15. Can a tablespace hold objects from different schemes?
Yes.

16·Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the followingselect statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'

17·Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

18. What is Oracle table?
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.

19.What is an Oracle view?
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.)

20.Do a view contain data?
Views do not contain or store data.

21· How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap iname=myscript.sql oname=xxxx.yyy

22· Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

23. Can a view based on another view?
Yes.

24. What are the advantages of views?
- 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.

25. What is an Oracle sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

26. How can we plan storage for very large tables?
Limit the number of extents in the table
Separate table from its indexes.
Allocate sufficient temporary storage.

27. How will you estimate the space required by a non-clustered table?
Calculate the total header size
Calculate the available data space per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.

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

29. What are the types of synonyms?
There are two types of synonyms private and public.

30. What is a private synonym?
Only its owner can access a private synonym.

31.What is implicit cursor and how is it used by Oracle ?
An implicit cursor is a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

32· Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Public synonyms

33.What is a public synonym?
Any database user can access a public synonym.

34. What are synonyms used for?
- 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.

35. What is an Oracle index?
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.

36· What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL.The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.

37· Is there a PL/SQL Engine in SQL*Plus?
No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

38. How are the indexes update?
Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

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

40. What is cluster key?
The related columns of the tables in a cluster are called the cluster key.

41· Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the followingselect statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'

42· Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

43.What is index cluster?
A cluster with an index on the cluster key.

44. What is hash cluster?
A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

45. When can hash cluster used?
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.

46. Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

47. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

48. How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.

49.How can I protect my PL/SQL source code?
Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wrap iname=myscript.sql oname=xxxx.yyy

50· Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

51. What is database link?
A database link is a named object that describes a “path” from one database to another.

52. What are the types of database links?
Private database link, public database link & network database link.

53. What is private database link?
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.

54. Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

55. Give two examples of how you might determine the structure of the table DEPT.
Use the describe command or use the dbms_metadata.get_ddl package.

56. What are the various types of Exceptions ?
User defined and Predefined Exceptions.

57. Can we define exceptions twice in same block ?
No.

58. What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not return any variable by value.Rather they return

59. What is public database link?
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

60. What is network database link?
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.

61. What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.

62. What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no.of parameters or the data type of the parameters passed to it.

63. Can functions be overloaded ?
Answer: Yes.

64. What is an extent?
An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

65. What is a segment?
A segment is a set of extents allocated for a certain logical structure.

66. What is data block?
Oracle database’s data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

67. How to define data block size?

A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

68. What is row chaining?
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.

69. Give the two types of tables involved in producing a star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

70. What type of index should you use on a fact table?
A bitmap index.

66. Where would you look for errors from the database engine?
In the alert log.

67. Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

68. Give the reasoning behind using an index.
Faster access to data blocks in a table.

69. Can you have two functions with the same name in a PL/SQL block ?
Answer: Yes.

70. Can you have two stored functions with the same name
Answer: Yes.

76. Give some examples of the types of database contraints you may find in Oracle and indicate their purpose.
A Primary or Unique Key can be used to enforce uniqueness on one or more columns.
A Referential Integrity Contraint can be used to enforce a Foreign Key relationship between two tables.
A Not Null constraint - to ensure a value is entered in a column
A Value Constraint - to check a column value against a specific set of values.

77. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

78. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

79What are the different types of segments?
Data segment, index segment, rollback segment and temporary segment.

80.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are :
· variables and constants
· cursors
· exceptions

81. What command would you use to create a backup control file?
Alter database backup control file to trace.

82. Give the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened

83. What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from.

84· Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.

85· Can you pass parameters in packages ? How ?
Yes.You can pass parameters to procedures or functions in a package

86.What is a data segment?
Each non-clustered table has a data segment. All of the table’s data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment.

87. What is an index segment?
Each index has an index segment that stores all of its data.

88. What is rollback segment?
A database contains one or more rollback segments to temporarily store “undo” information.

89. How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql

90. How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

91.What are the uses of rollback segment?
To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

92. What is a temporary segment?
Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

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

94.Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

95.What are the characteristics of data files?
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.

96· What are the parts of a database trigger ?
The parts of a trigger are:
· A triggering event or statement
· A trigger restriction
· A trigger action

97· What are the various types of database triggers ?
There are 12 types of triggers, they are combination of :
· Insert, Delete and Update Triggers.
· Before and After Triggers.
· Row and Statement Triggers.

98· What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

99. What is a redo log?
The set of redo log files for a database is collectively known as the database redo log.

100. What is the function of redo log?
The primary function of the redo log is to record all changes made to data.