Datapump expdp/impdp utility

ORACLE provides two external utilities to transfer database objects from one database to another database. Traditional exports (exp /imp) are introduced before 10g. Then from 10g, ORACLE introduced datapump (expdp / impdp) as an enhancement to traditional export utility.

Traditional Export (exp/ imp)

This is an ORACLE database external utility, which is used to transfer database objects from one database server to another database server. It allows transferring the database objects over different platforms, different hardware and software configurations. When an export command is executed on a database, database objects are extracted with their dependency objects. That means if it extracts a table, the dependences like indexes, comments, and grants are extracted and written into an export file (binary format dump file). Following is the command to export a full database,

Cmd> exp userid=username/password@exportdb_tns file=export.dmp log=export.log full=y statistics=none

The above command will be exported the database to a binary dump file named export.dmp. Then imp utility can be used to import this data to another database. Following is the command to import,

cmd> imp userid=username/password@importdb_tns file=export.dmp log=import.log full=y statistics=none

Datapump Export (expdp/ impdp)

This is also an ORACLE database external utility, which is used to transfer objects between databases. This utility is coming from ORACLE 10g database. It has more enhancements than the traditional exp/ imp utilities. This utility also makes dump files, which are in binary formats with database objects, object metadata and their control information. The expdp and impdp commands can be executed in three ways,

Command line interface (specify expdp/impdp parameters in command line)

Parameter file interface (specify expdp/impdp parameters in a separate file)

Interactive-command interface (entering various commands in export prompt)

There are five different modes of data unloading using expdp. They are,

• Full Export Mode (entire database is unloaded)
• Schema Mode (this is the default mode, specific schemas are unloaded)
• Table Mode (specified set of tables and their dependent objects are unloaded)
• Tablespace Mode (the tables in the specified tablespace are unloaded)
• Transportable Tablespace Mode (only the metadata for the tables and their dependent objects within a specified set of tablespaces are unloaded)

Following is the way to export a full database using expdp,

cmd> expdp userid=username/password dumpfile=expdp_export.dmp logfile=expdp_export.log full=y directory=export

Then impdp utility should be used to import this file to another database.

Performing various type of Export/Import using Data Pump

In order to work with oracle data pump we will have to first unlock the scott user and create a directory object. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system.

First startup the database and its associated services. Then from the root prompt, do the following.

su - oracle

export ORACLE_SID=orcl

sqlplus / as sysdba

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/orcl';

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Note: Directory must be created physically before executing the above command.

TABLE BASED IMPORT AND EXPORT

$ export ORACLE_SID=orcl

$ expdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expEMP_DEPT.log

$ export ORACLE_SID=orcl

$ impdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impEMP_DEPT.log

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

• All loads all the metadata as well as data from the source dump file.
• DATA_ONLY only loads row data into the tables no database objects are created.
• METADATA_ONLY only creates database objects, no data is inserted.

SCHEMA BASED IMPORT AND EXPORT

$ export ORACLE_SID=orcl

$ expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expSCOTT.log

$ export ORACLE_SID=orcl

$ impdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impSCOTT.log

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

•  All loads all the metadata as well as data from the source dump file.
•  DATA_ONLY only loads row data into the tables no database objects are created.
•  METADATA_ONLY only creates database objects, no data is inserted.

FULL DATABASE IMPORTS AND EXPORTS

For this to work you must login as the system user not as the sys user.

$ export ORACLE_SID=orcl

$ expdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expDB10G.log

$ export ORACLE_SID=orcl

$ impdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impDB10G.log

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

In the above commands you can also add a additional clause that is job_name.

Example syntax :

$ expdp system/password full=y directory=TEST_DIR dumpfile=DB10G.dmp job_name=qq

You can view the datapump jobs from the following view:

Sql> select * from dba_datapump_jobs;

OWNER_NAME                     JOB_NAME                       OPERATION
------------------------------ ------------------------------ ------------------------------
JOB_MODE                       STATE                              DEGREE ATTACHED_SESSIONS
------------------------------ ------------------------------ ---------- -----------------
SYSTEM                         SYS_EXPORT_FULL_01             EXPORT
FULL                           EXECUTING                               1                 1

Even if you exit the prompt or press ctrl+c at the command prompt or exit from the client side the datapump jobs will continue to run at the server.

To reattach with the running job enter the following command.

$ expdp system/password attach=qq

If the import or export job is to be stopped temporarily then type the following command.

press CTRL+C

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([y]/n): y

In order to resume the job do the following.

export ORACLE_SID=orcl

$expdp hr/hr ATTACH=qq

Export> CONTINUE_CLIENT

DATAPUMP EXCLUDE/INCLUDE parameters

The exclude and include parameters availbale with expdp,impdp can be used as metadata filters so that one can specify any objects like tables,indexes,triggers, procedure to be excluded or included during export or import operation

syntax:

EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]

examples:

expdp SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN (’EMP’,'DEPT’)”;

impdp SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION, PROCEDURE,TABLE:”=’EMP’”

The name_clause is a SQL expression that is used as a filter on the object names of the object. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. If no name_clause is provided, all objects of the specified type are excluded/included. The name clause must be separated from the object type with a colon.

Examples of operator-usage:


EXCLUDE=SEQUENCE
or:EXCLUDE=TABLE:”IN (’EMP’,'DEPT’)”
or:EXCLUDE=INDEX:”= ‘MY_INDX’”
or:INCLUDE=PROCEDURE:”LIKE ‘MY_PROC_%’”
or:INCLUDE=TABLE:”> ‘E’”

The parameter can also be stored in a par (parameter file) as shown
Parameter file:exp.par
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:”IN (’EMP’, ‘DEPT’)”

expdp system/manager parfile=exp.par


If parameter file is not used then in unix special care needs to be taken in syntax of expdp and impdp, in particular all the single quotes and double quotes needs to be preceded with the special character ‘\’ .The syntax for windows and unix

Windows:
D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\”

Unix:

% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”

Any improper use of exclude or include can give you errors and hence to avoid this error please be careful and read the entire post carefully .

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read.

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

Exp/Imp Vs Datapump Expdp/Impdp

Here are some advantages of Datapump:

• Data Pump Export and Import operations are processed in the database as a Data Pump job, which is much more efficient that the client-side execution of original Export and Import. 
• Data Pump operations can take advantage of the server’s parallel processes to read or write multiple data streams simultaneously.
• Data Pump differs from original Export and Import in that all jobs run primarily on the server using server processes. These server processes access files for the Data Pump jobs using directory objects that identify the location of the files. The directory objects enforce a security model that can be used by DBAs to control access to these files.
• Datapump has a very powerful interactive command-line mode which allows the user to monitor and control Data Pump Export and Import operations.Datapump allows you to disconnect and reconnect to the session
• Because Data Pump jobs run entirely on the server, you can start an export or import job, detach from it, and later reconnect to the job to monitor its progress.
• Data Pump gives you the ability to pass data between two databases over a network (via a database link), without creating a dump file on disk. 
• Datapump uses the Direct Path data access method (which permits the server to bypass SQL and go right to the data blocks on disk) has been rewritten to be much more efficient and now supports Data Pump Import and Export.
• Another amazing feature is that you can "PAUSE" and "RESUME" data pump jobs on demand.

Difference between Traditional Export and Datapump

• Datapump operates on a group of files called dump file sets. However, normal export operates on a single file.
• Datapump access files in the server (using ORACLE directories). Traditional export can access files in client and server both (not using ORACLE directories).
• Exports (exp/imp) represent database metadata information as DDLs in the dump file, but in datapump, it represents in XML document format.
• Datapump has parallel execution but in exp/imp single stream execution.
• Datapump does not support sequential media like tapes, but traditional export supports.

Export Import Using Data Pump

1. Create Directory:
SQL> CREATE DIRECTORY EXPORT_EXAMPLE AS '/var/tmp/karhmm';

Directory created.

2. Connect to database and export both the tables:

oracle@incs5vs1a>expdp VSOWNER/VSOWNER@VSDB_S1  directory=EXPORT_EXAMPLE tables=VOUCHERT DUMPFILE=VOUCHERS.DMP

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 18 May, 2013 8:15:54

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "VSOWNER"."SYS_EXPORT_TABLE_01":  VSOWNER/********@VSDB_S1 directory=EXPORT_EXAMPLE tables=VOUCHERT DUMPFILE=VOUCHERS.DMP 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "VSOWNER"."VOUCHERT"                        118.8 KB    3063 rows

Master table "VSOWNER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for VSOWNER.SYS_EXPORT_TABLE_01 is:

  /var/tmp/karhmm/VOUCHERS.DMP

Job "VSOWNER"."SYS_EXPORT_TABLE_01" successfully completed at 08:15:58

oracle@incs5vs1a>expdp VSOWNER/VSOWNER@VSDB_S1  directory=EXPORT_EXAMPLE tables=VOUCHERDATAT DUMPFILE=VOUCHERSDATA.DMP

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 18 May, 2013 8:19:03

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Starting "VSOWNER"."SYS_EXPORT_TABLE_01":  VSOWNER/********@VSDB_S1 directory=EXPORT_EXAMPLE tables=VOUCHERDATAT DUMPFILE=VOUCHERSDATA.DMP 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "VSOWNER"."VOUCHERDATAT"                    8.335 KB       7 rows

Master table "VSOWNER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for VSOWNER.SYS_EXPORT_TABLE_01 is:

  /var/tmp/karhmm/VOUCHERSDATA.DMP

Job "VSOWNER"."SYS_EXPORT_TABLE_01" successfully completed at 08:19:06

oracle@incs5vs1a>cd /var/tmp/karhmm/

oracle@incs5vs1a>ls

export.log         VOUCHERS.DMP       VOUCHERSDATA.DMP

Now go to other server where you want to Import these two tables:

==============================================

3. Create user and assign tablespace to it

SQL> create user vsowner identified by vsowner;

User created.

SQL> alter user vsowner default tablespace users;

User altered.

SQL> alter user vsowner quota unlimited on users;

User altered.

SQL> alter user vsowner temporary tablespace temp;

User altered.

4. GRANT needful permissions to new user.

SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE VIEW, CREATE SYNONYM, ALTER SESSION TO VWOWNER;

Grant succeeded.

5. Create Import directory:

SQL> CREATE DIRECTORY IMPORT_EXAMPLE AS 'C:\LOGS\SQL\VOUCHERS';

Directory created.

6. Now we need to Grant permission of directory to user in which tables are created.

SQL> GRANT READ on directory imports to vsowner;

Grant succeeded.

Even after this we get following errors:

c:\Logs\SQL\Vouchers>impdp vsowner/vsowner DIRECTORY=IMPORTS TABLES=VOUCHERT DUMPFILE=VOUCHERS.DMP

Import: Release 11.2.0.1.0 - Production on Sat May 18 13:17:23 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-31631: privileges are required

This shows both read and write permission is needed on the directory. Grant the required permissions:

SQL> GRANT ALL on directory import_example to vsowner;

Grant succeeded.

After this import run successfully:

c:\Logs\SQL\Vouchers>impdp vsowner/vsowner DIRECTORY=IMPORTS TABLES=VOUCHERT DUMPFILE=VOUCHERS.DMP

Import: Release 11.2.0.1.0 - Production on Sat May 18 13:19:33 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "VSOWNER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "VSOWNER"."SYS_IMPORT_TABLE_01":  vsowner/******** DIRECTORY=IMPORT_EXAMPLE TABLES=VOUCHERT DUMPFILE=VOUCHERS.DMP 

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "VSOWNER"."VOUCHERT"  

We got errors while creation of index so creating index manually:

SQL> ALTER TABLE VoucherT ADD CONSTRAINT  VoucherT_PK PRIMARY KEY (SerialNumber) USING INDEX TABLESPACE USERS INITRANS 5;

Table altered.

SQL> CREATE unique INDEX ActivationCodeI ON VoucherT(ActivationCode) TABLESPACE USERS INITRANS 5;

Index Created.

SQL> select index_name from user_indexes;

INDEX_NAME

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

VOUCHERT_PK

ACTIVATIONCODEI

PL/SQL Quick Notes

Define PL/SQL:

PL/SQL is a procedural language and it is an extension to Standard SQL. It enables us to execute procedural logic on the database. We use PlSql to perform processing on the server. We also use plsql to create stored procedure and Function.

Advantages of PL/SQL:

PL/SQL is a development tool and it is an extension to Standard SQL. We can also use condition branching, checking and looping in plsql. In Plsql we can send a block of statement to server which reduces network traffic. PlSql provides set of error handling mechanism. PlSql support portability, which means code written in DOS version can run on Unix version.

Explain Exception:

An Exception occurs when unwanted situation arises. The situation can be exceptional to normal function of the program. It can occur due to system error, user error and application error. In PlSql we can anticipate and trap these errors by means of Exception handling codes.

Type of Exception and Pre-defined Oracle Exception and User defined Exception.

Define User Defined Exception:

We use user defined exceptions only when oracle doesn't raise its own exception. In this procedure we raise an exception by using the RAISE command.

What is a Cursor. Define Implicit and Explicit cursors

Oracle engine open a work area for each SQL operation for its internal processinig in order to execute SQL statements. This area is private to SQL operations and it is called a cursor. 

Implicit Cursor: If Oracle enginer has open a cursor for its internal processing then its an Implicit Cursor.

Explicit Cursor: Also known as user defined cursors. When a user open a cursor for processing of the data then it is explicit cursor.

Explain about cursor attribute:

Each cursor or cursor variable has four attributes. FOUND, ISOPEN, NOTFOUND and ROWCOUNT. When appended to the cursor these attributes return useful information about the execution of the data manipulation statement.

What are the restiction of using cursor variables?

PlSql table cannot store cursor variable and a remote subprogram cannot return the value of a cursor variable.

What is a trigger.

A Trigger is a PlSql block that executes when a event occurs. It fires implicitly when a triggering event happens. A trigger never accept arguments. It cannot be used for a SELECT Statement. 

What are the trigger supported in Oracle:

DML Trigger, INSTEAD OF Triggers, DDL Trigger and Database Event Triggers.

DML Trigger: It is defined on a table and fires in response to the event like when a row is inserted to the table, when a row is updated and when a row is deleted.

INSTEAD OF Triggers: This trigeers are created on Views. You can either use INSERT OR UPDATE OR DELETE or all three actions. 

What are triggering attributes:

Triggering attributes are used to catch event when you want to identify to perform certain actions. They are as follows: INSERTING, UPDATING, DELETING.

List the difference between a function and procedure in oracle.

A Function always return a value back to the calling block. 

Explain the difference between GRANT and REVOKE command.

GRANT command is used to allow a user to perform an activity on the database. The REVOKE command disallow a user from perform certain activity.

Explain the difference between ROLLBACK and COMMIT command:

The COMMIT command is used to save the modification done to the database value by the DML commands. 

ROLLBACK command is to undo the changes made by DML commands. This assure value prior to changes is achieved.

Define Row Level Trigger:

Row level trigger is fired each time a row is affected by a DML statement like INSERT, UPDATE OR DELETE. In the case when now row affected the Trigger is not executed at all.

Statement level Trigger:

Statement level Trigger is fired when Statement affect rows in a table but the procesing required is completely independent of the number of rows affected.

Define Join and Its Type:

A join is a query that extract corresponding rows from two or more tables, views or snapshots.

Types: Equijoin, Non-Equijoin, Self Join and Outer Join. 

Equijoin: Information from two or more tables are retrieved by using equality condition. 

Self Join is a join that relate to itself. 

Outer Join: Fetch the row from two tables which matches the join condition and the rows the do not matches the join condition.

What are Packages:

A package is encapsulated collection of related schema objects. It is compiled and then stored in the data dictionary as a schema object. These objects can be procedures, functions, variables, constraints, cursors and exceptions. 

What are VARRAYS:

Variable length array is quite similar to array of c++ or java. It has fixed upper bound size that has to be specified when it is declated. The maximum length is defined in the declaration itself. This can only be used when you know in advance the maximum number of items to be stored.

Explain the Constraints that can be applied to oracle table:

Oracle Constraints are mainly used to maintain integrity of data. The different type of constraints includes CHECK Constraints, NULL constraints, PRIMARY KEY constraints, UNIQUE Constraints. 

Check Constraint: This constraints specify the condition for a row. 

NOT NULL Constraint: This specifies that a column cannot accept NULL values.

PRIMARY KEY Constraints: This constraint ensures that each row is identified by a unique key. 

UNIQUE Constraints: This constraint ensure no column value has repeatative values. 

List out advantages of Views:

Complex queries that need to be executed often can be saved in a view. Views is extremely useful when query uses multiple tables. If tables are changed only the view definition need to be changed. 

Views are visually virtual and occupy no space. They prevent undesire access by providing security as the data that not of interest to user can be left out of the view. A view displays different data for different types of users.

List out guidelines to create a view:

The views column names are same as tables column names. New column name has to be explicitly listed in the CREATE VIEW. New column name should be used while selecting column for a view. ORDER BY clause cannot be used in the definition of a view.

What are LOB Data Type:

The LOB Data Types such as BLOB, CLOB, NCLOB and BFILE can store large block of unstructured data such as graphics, image, video Clip etc. They provide random and piece wise access to the data.

What is the difference between PlSql Triggers and Stored procedures:
A stored procedure can accept parameter while a trigger cannot. A trigger cannot return any value while a Stored Procedure can. A trigger is executed automatically on some event while stored procedure need to be explicitly called. Trigger are used for insertion, updation and deletion on database, while stored procedure are often work independently on the database. 
A Trigger cannot be written in a stored procedure however the reverse is not possible. 

Why do we use cursor variables: 
Cursor variable allow you to pass them as argument to a function which in turn enable you to share results. They allow you to assign content of one cursor to another variable. Using a single cursor variable you can fetch from different result set.

Delete Vs TRUNCATE Vs DROP

• DELETE command is used to remove some or all rows from a table. After performing a DELETE operation you need to perform COMMIT or ROLLBACK a transaction to make the change permanent or undo it. It causes all delete trigger on table to fire. 
• TRUNCATE removes all rows from a table. The operation cannot be rollbacked and no trigger is fired. TRUNCATE is faster and doesn't use as much space as delete.
• DROP command removes a table from the database. All the tables to indexes and privileges will also be removed. No DML Trigger will be fired. The operation cannot be rollbacked.

Q. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?
-> Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

Q. What is CYCLE/NO CYCLE in a Sequence?-> CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

Q. What are the advantages of VIEW?- To protect some of the columns of a table from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

Q. Can a view be updated/inserted/deleted? If Yes - under what conditions?
->A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible. Also View should not have GROUP BY, DISTINCT and START WITH clauses in it, for it to be updatable.

Q. What are the data types allowed in a table?
-> CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

Q. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?-> CHAR pads blank spaces to the maximum length.
VARCHAR2 does not pad blank spaces.
For CHAR the maximum length is 255 and 2000 for VARCHAR2.

Q. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?->Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

Find indexes for a table

It is not unusual to wonder what indexes a specific table might have, and what columns in the table are assigned to those indexes. The following query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:

SQL> column table_owner format a15

SQL> column table_name format a20

SQL> column index_name format a20

SQL> column column_name format a20

 

SQL> Select owner, table_name, index_name, column_name

2  FROM dba_ind_columns

3  Order by owner, table_name, column_name

4  Where owner=’SCOTT’

5  AND table_name=’EMP’;

 

TABLE_OWNER     TABLE_NAME           INDEX_NAME           COLUMN_NAME

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

SCOTT           EMP                  PK_EMP               EMPNO

 

In this example we find that the EMP table in the SCOTT schema has one index called PK_EMP. This index is built on a single column, EMPNO.