Top Oracle Database Developer Interview Preparation Guide

Download PDF

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.

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.

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

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

Oracles supports the following categories of data types:

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

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.

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.

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.

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!

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.

If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how:

(Terminal server to the Oracle server machine)
(Start SQL*Plus)
SQL>CONNECT / AS SYSDBA
Connected.

SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_lgg;
User altered.

Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method.

Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:

SQL> CONNECT ggl/retneclgg@WRONG;
ERROR:
ORA-12154: TNS:could not resolve the connect identifier
specified

Warning: You are no longer connected to ORACLE.

What you need to do in this case:

* Check the CONNECT command to make sure that the connect identifier is entered correctly.
* Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.
* Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.
* Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora.

In order to connect a SQL*Plus session to an Oracle server, you need to:

1. Obtain the connection information from the Oracle server DBA.

2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information.

3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below:

>cd c:oraclexeapporacleproduct10.2.0server
>.insqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> CONNECT ggl/retneclgg@ggl_XE;
Connected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
05-MAR-06

A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.

Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:

ggl_XE =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = www.globalguideline.com)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

The above "connect identifier" defines "TNS_XE" with the following connection information:

* The network hostname: www.globalguideline.com.
* The network port number: 1521.
* The name of the target database instance: XE.

If you want to connect your SQL*Plus session to an Oracle server, you need to know the following information about this server:

* The network hostname, or IP address, of the Oracle server.
* The network port number where the Oracle server is listening for incoming connections.
* The name of the target database instance managed by the Oracle server.
* The name of your user account predefined on in the target database instance.
* The password of your user account predefined on in the target database instance.

f you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:

1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>

2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:

>cd c:oraclexeapporacleproduct10.2.0server
>.insqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Oracle server version information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this:

>.insqlplus
Enter user-name: SYSTEM/globalguideline AS SYSDBA
Connected to an idle instance

SQL> COL PRODUCT FORMAT A35
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;


PRODUCT VERSION STATUS

----------------------------------- ----------- ----------

NLSRTL 10.2.0.1.0 Production

Oracle Database 10g Express Edition 10.2.0.1.0 Product

PL/SQL 10.2.0.1.0 Production

TNS for 32-bit Windows: 10.2.0.1.0 Production

Let's say the SPFile for the default instance is a binary file, and some settings are wrong in the SPFile, like SGA setting is bellow 20MB, how do you change a setting in the binary file? This seems to be a hard task, because the binary SPFile is not allowed to be edited manually. It needs to be updated by the server with instance started. But you can not start the instance because the SPFile has a wrong setting.

One way to solve the problem is to stop using the binary SPFile, and use a text version of the a parameter file to start the instance. Here is an example of how to use the backup copy (text version) of the parameter file for the default instance to start the instance:

>.insqlplus
Enter user-name: SYSTEM/globalguideline AS SYSDBA
Connected to an idle instance

Settings for each instance are stored in a file called Server Parameter File (SPFile). Oracle supports two types of parameter files, Text type, and Binary type. parameter files should be located in $ORACLE_HOMEdatabase directory. A parameter file should be named like "init$SID.ora", where $SID is the instance name.

If you logged in to the server as a SYSDBA, you start the default instance with the "startup" command. Here is how to start the default instance in SQL*Plus in SYSDBA mode:

>.insqlplus
Enter user-name: SYSTEM/globalguideline AS SYSDBA
Connected to an idle instance

SQL> show instance
instance "local"

SQL> startup
ORA-00821: Specified value of sga_target 16M is too small,
needs to be at least 20M

Now the server is telling you more details about the memory problem on your default instance: your SGA setting of 16MB is too small. It must be increased to at least 20MB.

If your default instance is in trouble, and you can not use the normal login process to reach the server, you can use a special login to log into the server without any instance. Here is how to use SQL*Plus to log in as as a system BDA:

>cd (OracleXE home directory)
>.instartdb
>.insqlplus
Enter user-name: SYSTEM/globalguideline AS SYSDBA
Connected to an idle instance

SQL> show instance
instance "local"

The trick is to put user name, password and login options in a single string as the user name. "AS SYSDBA" tells the server to not start any instance, and connect the session the idle instance.

Log in as SYSDBA is very useful for performing DBA tasks.

If StartBD.bat failed to start the XE instance, you need to try to start the instance with other approaches to get detail error messages on why the instance can not be started.

One good approach to start the default instance is to use SQL*Plus. Here is how to use SQL*Plus to start the default instance in a command window:

>cd (OracleXE home directory)
>.instartdb
>.insqlplus
Enter user-name: SYSTEM
Enter password: globalguideline
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

The first "cd" is to move the current directory the 10g XE home directory. The second command ".instartdb" is to make sure the TNS listener is running. The third command ".insqlplus" launches SQL*Plus. The error message "ORA-27101" tells you that there is a memory problem with the default instance.

So you can not use the normal login process to the server without a good instance. See other tips on how to log into a server without any instance.

Let's you made a mistake and changed to SGA to 16MB from the SYSTEM admin home page. When you run the batch file StartDB.bat, it will return a message saying server stated. However, if you try to connect to your server home page: http://localhost:8080/apex/, you will get no response. Why? Your server is running, but the default instance XE was not started.

If you go the Control Panel and Services, you will see service OracleServiceXE is listed not in the running status.

Your 10g XE server has a default setting for Program Global Area (PGA) of 40MB. The PGA size can be changed to a new value depending on how much data a single session should be allocated. If you think your session will be short with a small amount of data, you should change the PGA size to 16MB by:

* Log into the server home page as SYSTEM.
* Go to Administration, then Memory.
* Click Configure PGA.
* Enter the new memory size: 16
* Click Apply Changes to save the changes.
* Re-start your server.

Your 10g XE server has a default setting for System Global Area (SGA) of 140MB. The SGA size can be changed to a new value depending on how many concurrent sessions connecting to your server. If you are running this server just for yourself to improve your DBA skill, you should change the SGA size to 32MB by:

* Log into the server home page as SYSTEM.
* Go to Administration, then Memory.
* Click Configure SGA.
* Enter the new memory size: 32
* Click Apply Changes to save the changes.
* Re-start your server.