Basic Oracle Concepts and Programming Interview Preparation Guide

Download PDF

If you are running Windows IIS Web server and serving ASP Web pages, you can get data from Oracle servers into your ASP pages through ODBC drivers. To do this, you need to install the correct Oracle ODBC driver and define a DSN on the IIS Web server.

Then you can use ADODB objects to connect to the Oracle server over the ODBC driver in your ASP pages. The tutorial example below gives you a good example:

<pre><%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=ggl_DSN;UID=ggl;PWD=retneclgg"
Set oRS = oConn.Execute("SELECT * FROM dev_faq")
Response.write("<p>Data from Oracle server via ODBC:")
Response.write("</pre>")
Do While NOT oRS.EOF
Response.Write(oRS("ID") & vbcrlf)
oRS.MoveNext
Loop
Response.write("</pre>")
oRS.close
oConn.close
%></pre>

Once you got a DSN defined in the ODBC manager that connects to an Oracle server, you can connect a normal MS Access document to the Oracle server, and link an Access table to Oracle table. The tutorial below gives you a good example:

► Start MS Access with a new database file.
► Go to File menu.
► Select Get External Data.
► Select Import.... The Import dialog box shows up.
► Select Files of type: ODBC Database(). The Select Data Source dialog box shows up.
► Click the Machine Data Source tab. You should see the DSN name "ggl_DSN" you defined earlier.
► Select "ggl_DSN".
► Enter User Name: ggl.
► Enter Password: retneclgg.

You should see the Oracle ODBC Driver Connect dialog box as shown in the picture below:
Import Oracle tables to MS Access

Click the OK button to continue. You should see a list of tables available for you to import from the Oracle server as shown in the picture below:
Importing Oracle tables via ODBC

DSN (Data Source Name) is an ODBC connection identifier for Windows applications. Here is how you can define a DSN on your Windows system:

► Go to Control Panel.
► Go to Administrative Tools.
► Run Data Sources (ODBC).
► Go to System DSN tab.
► Click the Add button.
► Select the "Oracle in XE" driver.
► Enter Data Source Name: ggl_DSN.
► Enter Description: globalguideline DSN Oracle Setting.
► Enter TNS Service Name: XE.
► Click the Test Connection button.
► Enter User Name: ggl.
► Enter Password: retneclgg.
► Click the OK button.

You should see a "Connection successful" as shown in the following picture:
DSN Setting for Oracle ODBC Driver

If you have installed an Oracle server or an Oracle client tool on your local system, the TNS is automatically installed with a simple configuration file, tnsnames.ora, to define Oracle connect identifiers.

For example, if you have Oracle XE server installed, you will have the tnsnames.ora located at oraclexeapporacleproduct10.2.0serverNETWORKADMIN. It should contain a connect identifier called XE defined as:

XE =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = localhost)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

If you want to follow the tutorial exercises in the sections below, you need to create a user account and a table for ODBC connection testing as shown here:

SQL> CONNECT system/retneclgg
Connected.

SQL> CREATE USER ggl IDENTIFIED BY retneclgg ACCOUNT UNLOCK;
User created.

SQL> GRANT CREATE SESSION TO ggl;
Grant succeeded.

SQL> GRANT CREATE TABLE TO ggl;
Grant succeeded.

SQL> ALTER USER ggl DEFAULT TABLESPACE USERS;
User altered.

SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.

SQL> connect ggl/retneclgg;
Connected.

SQL> CREATE TABLE dev_faq (id NUMBER);
SQL> INSERT INTO dev_faq VALUES (3);
SQL> INSERT INTO dev_faq VALUES (5);
SQL> INSERT INTO dev_faq VALUES (7);

A Windows application can connect to an Oracle server directly, if it knows how to use the Oracle TNS technology.

A Windows application can connect to an Oracle server indirectly through Windows ODBC manager, be caused offers ODBC drivers to support the ODBC API. The diagram below shows how MS Access can connect to an Oracle server through the ODBC driver:
Oracle ODBC Connection

To find out what Oracle ODBC drivers are installed on your Windows system, you can use the ODBC manager to look at them:

► Go to Control Panel.
► Go to Administrative Tools.
► Run Data Sources (ODBC).
► Go to System DSN tab.
► Click the Add button.

You should a list of all ODBC drivers installed on your system. Oracle ODBC drivers should be on the list. For example, your installed ODBC driver list could look like the one the picture bellow with two Oracle ODBC drivers, "Oracle in OraClient10g_home1" and "Oracle in XE":
Oracle ODBC Drivers

Oracle offers different ODBC drivers for different versions of Oracle servers. When you install an Oracle server or a client tools on your Windows system, the appropriate ODBC driver will be installed for you automatically.

If you want to install a specific ODBC driver, you need to go to http://www.oracle.com/technology/software/tech/windows/odbc/index.html. Download the right ODBC driver and follow the instructions to install it.

ODBC, Open Database Communication, a standard API (application program interface) developed by Microsoft for Windows applications to communicate with database management systems.

Oracle offers ODBC drivers to allow Windows applications to connect Oracle server through ODBC.

If you really want to run the original export import utilities, you can still go to "bin" directory of the Oracle server path and run the "exp" or "imp" command. The tutorial exercise below tells you how to run the export and import utilities in help modes:

>cd oraclexeapporacleproduct10.2.0serverBIN

>exp help=y
You can let Export prompt you for parameters by entering the
EXP command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP
command followed by various arguments. To specify parameters,
you use:

Format: EXP KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned
table
......

>imp help=y
......

Oracle original Export and Import utilities are standalone programs that provide you a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file.

An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.

Export and Import utilities are now being replaced by Data Pump Export and Import utilities in Oracle 10g. But you can still use them.

If you only want to import one table back to the database, you can use a dump file that was created by full export, schema export or a table export. The following tutorial exercise shows you how to import the "ggl_links" table from a dump file created by a schema export:

>cd oraclexeapporacleproduct10.2.0serverBIN

>sqlplus /nolog
SQL> connect HR/globalguideline

SQL> DROP TABLE ggl_links;
Table dropped.

SQL> exit;

>impdp hr/globalguideline TABLES=ggl_links DIRECTORY=hr_dump
DUMPFILE=schema.dmp LOGFILE=tables.log

Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=ggl_links
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=tables.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."ggl_LINKS" 6.375 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTI...
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TAB...
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed.

If the import process tries to import a table that already exists, the Data Pump Import utility will return an error and skip this table. The following exercise shows you a good example:

>cd oraclexeapporacleproduct10.2.0serverBIN
>impdp hr/globalguideline TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log

Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent
metadata and data will be skipped due to table_exists_action
of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
......

If you don't want to export the entire schema and only want to export several tables only, you can use the "expdp" command with the "TABLES" parameter as shown in the following tutorial exercise:

>cd oraclexeapporacleproduct10.2.0serverBIN

>expdp hr/globalguideline TABLES=employees,departments
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log

Starting "HR"."SYS_EXPORT_TABLE_01": hr/********
TABLES=employees,departments DIRECTORY=hr_dump
DUMPFILE=tables.dmp
LOGFILE=tables.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTI...
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF...
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TAB...
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
Master table "HR".

If you have a non-system user account and you want to export all data objects in the schema associated with your account, you can use the "expdp" command with the SCHEMAS parameter. Running "expdp" command with a non-system user account requires a directory object granted to this user account. The following tutorial exercise shows you how to define a directory object and export a schema:

>mkdir oraclexehr_dump

>cd oraclexeapporacleproduct10.2.0serverBIN

>sqlplus /nolog
SQL> connect SYSTEM/globalguideline

SQL> CREATE DIRECTORY hr_dump AS 'oraclexehr_dump';
Directory created.

SQL> GRANT READ ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY hr_dump TO hr;
Grant succeeded.

SQL> quit

>expdp hr/globalguideline SCHEMAS=hr
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=schema.log

If you are not specifying the dump directory and file name, the dump file will be stored in the default dump directory with the default file name. The tutorial exercise below tells you find what is your default dump directory and locate the dump file.

>sqlplus /nolog

SQL> connect SYSTEM/globalguideline

SQL> COL owner FORMAT A8;
SQL> COL directory_name FORMAT A16;
SQL> COL directory_path FORMAT A40;
SQL> SELECT * FROM dba_directories;
<pre>OWNER DIRECTORY_NAME DIRECTORY_PATH
----- -------------- -------------------------------------
SYS DATA_PUMP_DIR oraclexeapporacleadminXEdpdump
SYS TEST_DIR /oraclexe/test
SYS ORACLECLRDIR oraclexeapporacleproduct10.2.0
serverinclr</pre>
Obviously, the default dump directory is directory object defined to oraclexeapporacleadminXEdpdump. If you go to that directory, you will find the full database dump file is called "expdat.dmp".

If you are ready to do a full database export, you can use the FULL=y parameter on the expdp command, as shown in the following tutorial exercise:

>expdp SYSTEM/globalguideline FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01": SYSTEM/**** FULL=y
Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 169.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
......<pre>
. . exported FLOWS_020100.WWV_FLOW_PAGE_PLUGS 32.51MB
. . exported FLOWS_020100.WWV_FLOW_STEP_ITEMS 21.68MB
. . exported FLOWS_020100.WWV_FLOW_STEP_PROCESSING 11.17MB</pre>
......
Master table "SYSTEM"."SYS_EXPORT_FULL_01" unloaded
**********************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
C:ORACLEXEAPPORACLEADMINXEDPDUMPEXPDAT.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed

If you just want to know how much disk space for the dump without actually exporting any data, you can use the ESTIMATE_ONLY=y parameter on the expdp command. The following tutorial exercise shows you how a system user wants to see the disk space estimates on a full database export:

>cd oraclexeapporacleproduct10.2.0serverBIN

>expdp SYSTEM/globalguideline FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01": SYSTEM/**** FULL=y
ESTIMATE_ONLY=y
<pre>Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "FLOWS_020100"."WWV_FLOW_PAGE_PLUGS" 42 MB
. estimated "FLOWS_020100"."WWV_FLOW_STEP_ITEMS" 27 MB
. estimated "FLOWS_020100"."WWV_FLOW_STEP_PROCESSING" 16 MB
......
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
. estimated "TSMSYS"."SRS$" 0 KB
Total estimation using BLOCKS method: 169.8 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed</pre>
Now you know that you need 170 MB disk space to export the entire data base.

Oracle also records the screen output in a log file called export.log at oraclexeapporacleadminXEdpdump.

Data pump export and import modes are used to determine the type and portions of database to be exported and imported. Oracle 10g supports 5 export and import modes:

► Full: Exports and imports a full database. Use the FULL parameter to specify this mode.
► Schema: Enables you to export and import all objects that belong to a schema. Use the SCHEMAS parameter to specify this mode. This is the default mode.
► Table: Enables you to export and import specific tables and partitions. Use the TABLES parameter to specify this mode.
► Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TABLESPACES parameter to specify this mode.
► Tablespace: Enables a privileged user to move metadata from the tables within a specified set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACES parameter to specify this mode.

The Data Pump Import utility is distributed as executable file called "impdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "impdp" command. Here is tutorial exercise on how to invoke the import utility:

>cd oraclexeapporacleproduct10.2.0serverBIN

>impdp help=y
Import: Release 10.2.0.1.0 -

The Data Pump Import utility provides a mechanism for
transferring data objects between Oracle databases. The
utility is invoked with the following command:

Example: impdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp'
command followed by various parameters.

Format: impdp KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)

Example: impdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp

The Data Pump Export utility is distributed as executable file called "expdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "expdp" command. Here is tutorial exercise on how to invoke the export utility:

>cd oraclexeapporacleproduct10.2.0serverBIN

>expdp help=y
Export: Release 10.2.0.1.0 -

The Data Pump export utility provides a mechanism for
transferring data objects between Oracle databases. The
utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp'
command followed by various parameters:

Format: expdp KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)

Example: expdp scott/tiger DUMPFILE=scott.dmp
DIRECTORY=dmpdir
SCHEMAS=scott or TABLES=(T1:P1,T1:P2)

Oracle Data Pump Import utility is a standalone programs that allows you to import data objects from an Oracle dump file set into Oracle database. Oracle dump file set is written in a proprietary binary format by the Data Pump Export utility.

Import can also be used to load a target database directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time. This is known as a network import. Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands.

Oracle Data Pump Export utility is a standalone programs that allows you to export data objects from Oracle database to operating system files called dump file set, which can be imported back to Oracle database only by Oracle Data Pump Import utility.

The dump file set can be imported on the same system or it can be moved to another system and loaded there.

The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set. Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects.

Once you have your data entered in a text file, and an external table defined to this text file, you can easily load data from this text file to a regular table. The following tutorial exercise shows you how to load data from the text file and the external table defined in the previous exercises to a regular table:

SQL> CREATE TABLE ggl_links (
id NUMBER(4) PRIMARY KEY,
url VARCHAR2(16) NOT NULL,
notes VARCHAR2(16),
counts NUMBER(4),
created DATE DEFAULT (sysdate)
);

SQL> INSERT INTO ggl_links SELECT * FROM ext_ggl_links;
2 rows created.

SQL> SELECT * FROM ggl_links;
<pre> ID URL NOTES COUNTS CREATED
--------- ---------------- ----------- -------- ---------
101 globalguideline.com Session 1 07-MAY-06
110 centerggl.com Session 1 07-MAY-06
1101 www.globalguideline Link #1 88 07-MAY-06
1110 www.globalguideline Link #2 88 07-MAY-06</pre>

If you have an external table defined as a text file with the ORACLE_LOADER driver, you can add data to the text file, and query the text file through the external table. By default, data fields in the text file should be terminated by ','. The tutorial exercise below shows you how add data to the external table defined in the previous exercise:

>edit /oraclexe/test/ext_ggl_links.txt
1101,www.globalguideline,Link #1,88,07-MAY-06
1110,www.globalguideline,Link #2,88,07-MAY-06

>sqlplus /nolog

SQL> connect HR/globalguideline

SQL> SELECT * FROM ext_ggl_links;
<pre> ID URL NOTES COUNTS CREATED
--------- ---------------- ----------- -------- ---------
1101 www.globalguideline Link #1 88 07-MAY-06
1110 www.globalguideline Link #2 88 07-MAY-06</pre>