Essential Oracle Database Developer Interview Questions & Answers:

1. Explain Dynamic Performance View in Oracle?

Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.

2. Static Data Dictionary in Oracle?

Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:

* An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
* A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
* A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

3. Explain Oracle Built-in Data Types?

There are 20 Oracle built-in data types, divided into 6 groups:

* Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
* Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
* Long and Row Datatypes - LONG, LONG RAW, RAW
* Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
* Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
* Row ID Datatypes - ROWID, UROWID

4. Explain Oracle Server Autotrace in Oracle?

Autotrace is Oracle server feature that generates two statement execution reports very useful for performance tuning:

* Statement execution path - Shows you the execution loop logic of a DML statement.
* Statement execution statistics - Shows you various execution statistics of a DML statement.

To turn on the autotrace feature, the Oracle server DBA need to:

* Create a special table called PLAN_TABLE.
* Create a special security role called PLUSTRACE.
* Grant PLUSTRACE role your user account

5. How many categories of Data Types in Oracle?

Oracles supports the following categories of data types:

* Oracle Built-in Datatypes.
* ANSI, DB2, and SQL/DS Datatypes.
* User-Defined Types.
* Oracle-Supplied Types.

6. How you save Query output to a Local File?

Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example:

SQL> connect HR/retneclgg

SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 1000
SQL> SPOOL empemployees.lst
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF

You should get all records in employees.lst with fixed length fields.

7. How you change SQL*Plus System Settings?

SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:

* SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
* SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
* SET HEADING OFF - Stops displaying the header line of the query output.
* SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
* SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
* SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
* SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
* SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
* SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
* SET WRAP OFF - Turns off the wrapping feature when displaying query output.

8. How you Run PL/SQL Statements in SQL*Plus?

If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to globalguideline!')
Welcome to globalguideline!

PL/SQL procedure successfully completed.

9. How you run SQL Commands in SQL*Plus?

If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example:

SQL> SELECT 'Welcome!' FROM DUAL;

'WELCOME
--------
Welcome!

SQL> SELECT 'Welcome to globalguideline.com tutorials!'
2 FROM DUAL
3 ;

'WELCOMETOglobalguideline.COMTUTORIALS!'
-----------------------------------
Welcome to globalguideline.com tutorials!

10. Which types of Commands Can Be Executed in SQL*Plus?

There are 4 types of commands you can run at the SQL*Plus command line prompt:

1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM ggl_faq;" is a SQL command.

2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to www.globalguideline.com')" runs a PL/SQL command.

SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.

OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.