Interview Q and A for Oracle Export Import Part - 2

31. What is the use of ANALYSE ( Ver 7) option in EXP command ?

A flag to indicate whether statistical information about the exported objects should be written to export dump file.

32. What is the use of CONSISTENT (Ver 7) option in EXP command ?

A flag to indicate whether a read consistent version of all the exported objects should be maintained.

When you export a table, you are guaranteed that the contents of that table will be consistent with the time that the export of that table was started. This means that if you start exporting the table at 12:00 and someone makes changes to the data in the table at 12:05 and your export of this table finishes at 12:10, then the export will not contain any of the changes made between 12:00 and 12:10. You cannot change this behavior with Oracle's export utility.

The CONSISTENT parameter controls whether or not the entire export is consistent, even between tables. If CONSISTENT=N (the default), then the export of a table will be consistent, but changes can occur between tables. If CONSISTENT=Y, then the entire dump file is consistent with the point in time that you started the export.

33. What is use of LOG (Ver 7) option in EXP command ?

The name of the file which log of the export will be written.

34.What is the use of FILE option in IMP command ?

The name of the file from which import should be performed.

35. What is the use of SHOW option in IMP command ?

A flag to indicate whether file content should be displayed or not.

36. What is the use of IGNORE option in IMP command ?

A flag to indicate whether the import should ignore errors encounter when issuing CREATE commands.

37. What is the use of GRANT option in IMP command ?

A flag to indicate whether grants on database objects will be imported.

38. What is the use of INDEXES option in IMP command ?

A flag to indicate whether import should import index on tables or not.

39. What is the use of ROWS option in IMP command ?

A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.

40. What is use of DIRECT=Y option in exp?
Normally export will follow the process of SELECT statement i.e data from disk will be copied to buffer cache and then it will be written to dump file. When we use direct path by specifying DIRECT=Y in export command, then oracle will copy data directly from disk to PGA and from there it is written to dumpfile.

41. What is the Benefits of the Data Pump Technology?
The older export/import technology was client-based. The Data Pump technology is purely server based. All dump, log, and other files are created on the server by default. Data Pump technology offers several benefits over the traditional export and import data utilities.
The following are the main benefits of the Data Pump technology:
Improved performance: The performance benefits are significant if you are transferring huge amounts of data.
Ability to restart jobs: You can easily restart jobs that have stalled due to lack of space or have
failed for other reasons. You may also manually stop and restart jobs.
Parallel execution capabilities: By specifying a value for the PARALLEL parameter, you can choose the number of active execution threads for a Data Pump Export or Data Pump Import job.
Ability to attach to running jobs: You can attach to a running Data Pump job and interact with
it from a different screen or location. This enables you to monitor jobs, as well as to modify
certain parameters interactively. Data Pump is an integral part of the Oracle database server,
and as such, it doesn’t need a client to run once it starts a job.
Ability to estimate space requirements: You can easily estimate the space requirements for
your export jobs by using the default BLOCKS method or the ESTIMATES method, before running
an actual export job

Network mode of operation: Once you create database links between two databases, you can
perform exports from a remote database straight to a dump file set. You can also perform
direct imports via the network using database links, without using any dump files. The network
mode is a means of transferring data from one database directly into another database with
the help of database links and without the need to stage it on disk.
Fine-grained data import capability: Oracle9i offered only the QUERY parameter, which enabled
you to specify that the export utility extract a specified portion of a table’s rows. With Data Pump,
you have access to a vastly improved fine-grained options arsenal, thanks to new parameters
like INCLUDE and EXCLUDE.
Remapping capabilities: During a Data Pump import, you can remap schemas and tablespaces,
as well as filenames, by using the new REMAP_ * parameters. Remapping capabilities enable
you to modify objects during the process of importing data by changing old attributes to new
values. For example, the REMAP_SCHEMA parameter enables you to map all of user HR’s schema
to a new user, OE. The REMAP_SCHEMA parameter is like the TOUSER parameter in the old
import utility

42. What is COMPRESSION parameter in expdp?
The COMPRESSION parameter enables the user to specify which data to compress before writing theexport data to a dump file. By default, all metadata is compressed before it’s written out to an export dump file. You can disable compression by specifying a value of NONE for the COMPRESSION parameter, as shown here:
$ expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_comp.dmp COMPRESSION=NONE

The COMPRESSION parameter can take any of the following four values:
ALL: Enables compression for the entire operation.
DATA_ONLY: Specifies that all data should be written to the dump file in a compressed format.
METADATA_ONLY: Specifies all metadata be written to the dump file in a compressed format.
This is the default value.
NONE: Disables compression of all types.

43. What are Export Filtering Parameters in expdp?
Data Pump contains several parameters related to export filtering. Some of them are substitutes for old export parameters; others offer new functionality.
CONTENT By using the CONTENT parameter, you can filter what goes into the export dump file. The CONTENT parameter can take three values:
• ALL exports both table data and table and other object definitions (metadata).
• DATA_ONLY exports only table rows.
• METADATA_ONLY exports only metadata.

EXCLUDE and INCLUDE The EXCLUDE and INCLUDE parameters are two mutually exclusive parameters that you can use to perform what is known as metadata filtering. Metadata filtering enables you to selectively leave out or include certain types of objects during a Data Pump Export or Import job. In the old export utility, you used the CONSTRAINTS, GRANTS, and INDEXES parameters to specify whether you wanted to export those objects. Using the EXCLUDE and INCLUDE parameters, you now can include or exclude many other kinds of objects besides the four objects you could filter previously. For example, if you don’t wish to export any packages during the export, you can specify this with the help of the EXCLUDE parameter.

QUERY The QUERY parameter serves the same function as it does in the traditional export utility: it lets you selectively export table row data with the help of a SQL statement. The QUERY parameter permits you to qualify the SQL statement with a table name, so that it applies only to a particular table. Here’s an example:
QUERY=OE.ORDERS: "WHERE order_id > 100000"

In this example, only those rows in the orders table (owned by user OE) where the order_id is
greater than 100,000 are exported.

44. What is Network Link Parameter and how it works?
The Data Pump Export utility provides a way to initiate a network export. Using the NETWORK_LINK parameter, you can initiate an export job from your server and have Data Pump export data from a remote database to dump files located on the instance from which you initiate the Data Pump Export job.
Here’s an example that shows you how to perform a network export:

$ expdp hr/hr DIRECTORY=dpump_dir1 NETWORK_LINK=finance  DUMPFILE=network_export.dmp LOGFILE=network_export.log

In the example, the NETWORK_LINK parameter must have a valid database link as its value. This
means that you must have created the database link ahead of time. This example is exporting data from the finance database on the prod1 server.

Let’s say you have two databases, called local and remote. In order to use the NETWORK_LINK parameter and pass data directly over the network, follow these steps:

1. Create a database link to the remote database, which is named remote in this example:
SQL> CREATE DATABASE LINK remote  CONNECT TO scott IDENTIFIED BY tiger
 USING 'remote.world';

2. If there isn’t one already, create a Data Pump directory object:
SQL> CREATE DIRECTORY remote_dir1 AS '/u01/app/oracle/dp_dir';

3. Set the new directory as your default directory, by exporting the directory value:
$ export DATA_PUMP_DIR=remote_dir1

4. Perform the network export from the database named remote:
$ expdp system/sammyy1 SCHEMAS=SCOTT FILE_NAME=network.dmp NETWORK_LINK=finance

You’ll see that the Data Pump Export job will create the dump file network.dmp (in the directory location specified by remote_dir1) on the server hosting the database named local. However, the data within the dump file is extracted from the user scott’s schema in the remote database (named remote in our example). You can see that the NETWORK_LINK parameter carries the dump files over the network from a remote location to the local server. All you need is a database link from a database on the local server to the source database on the remote server.

45. What is use of INDEXFILE option in imp?
Will write DDLs of the objects in the dumpfile into the specified file.

46. What are the differences between expdp and exp (Data Pump or normal exp/imp)?
Data Pump is server centric (files will be at server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if user doesn’t exist.

48. Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?
Data Pump is block mode, exp is byte mode.
Data Pump will do parallel execution.
Data Pump uses direct path API.

49. How to improve expdp performance?
Using parallel option which increases worker threads. This should be set based on the number of cpus.

50. How to improve impdp performance?
Using parallel option which increases worker threads. This should be set based on the number of cpus.

51. In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?
Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.
Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.

52. What is the order of importing objects in impdp?
 Tablespaces
 Users
 Roles
 Database links
 Sequences
 Directories
 Synonyms
 Types
 Tables/Partitions
 Views
 Comments
 Packages/Procedures/Functions
 Materialized views

53. How to import only metadata?
CONTENT= METADATA_ONLY

54. How to import into different user/tablespace/datafile/table?
REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE
REMAP_DATA

Interview Q and A for Oracle Export Import Part - 1

1. In which cases imp/exp is used?
Eliminate database fragmentation
-Schema refresh (move the schema from one database to another)
-Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption)
-Transporting tablespaces between databases
-Backup database objects

2.  Which are the common IMP/EXP problems?
ORA-00001: Unique constraint ... violated - Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh.
IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time).
ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter) while importing.

3. How can we check DATAPUMP file is corrupted or not?

Sometimes we may be in situation, to check whether the dumpfile exported long time back is VALID or not or our application team is saying that the dumpfile provided by us is corrupted.
Use SQLFILE Parameter with import script to detect corruption. The use of this parameter will read the entire datapump export dumpfile and will report if corruption is detected.
impdp system/*** directory=dump_dir dumpfile=expdp.dmp logfile=corruption_check.log sqlfile=corruption_check.sql
This will write all DDL statements (which will be executed if an import is performed) into the file which we mentioned in the command.

4. How can we find elapsed time for particular object during Datapump or Export?

We have an undocumented parameter ‘metrics’ in DATAPUMP to check how much it took to export different objects types.
Expdp system/passwd directory=dump_dirdumpfile=expdp_full.dmp logfile=expdp_full.log full=y metrics=y;

5. How to move table from one tablespace to another tablespace?
You can use any of the below method:
1.Export the table, drop the table, create definition of table in new tablespace and then import the data using (imp ignore=y).
2.Create new table in new tablespace then drop the original table and rename temporary table with original table name.
CREATE TABLE temp_name TABLESPACE new_tablespace as select * from 'source_table';
DROP TABLE real_table;
RENAME temp_name to real_table;

6. What is the difference between SQL*loader and Import utilities?
Both these utilities are used for loading the data into the database. The difference is that the import utility relies on the data being produced by another oracle utility Export while SQL*Loader is a high speed data loading mechanism allows data to be loaded that has been produced by other utilities from different data source.SQL * Loader loads data from standard OS files or flat file in oracle database tables.Export/Import allows moving existing data in oracle format to and from oracle database.

7. How to re-organize schema?
We can use dbms_redefinition package for online re-organization of schema objects. Otherwise using import/export and data pump utility you can recreate or re-organize your schema.

8. How we can improve the EXP Performance?
1.Set the BUFFER parameter to a high value (e.g. 2M)
2.If you run multiple export sessions, ensure they write to different physical disks.

3. Stop unnecessary applications to free the resources.
4. Do not export to NFS (Network File Share). Exporting to disk is faster.
5. Set the RECORDLENGTH parameter to a high value.
6. Use DIRECT=yes (direct mode export).

9. How we can improve the IMP performance?
1.Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated), import the data and recreate the indexes
2.Store the dump file to be imported on a separate physical disk from the oracle data files
3.If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import
4.Set the BUFFER parameter to a high value, Default is 256KB (ex. BUFFER=30000000 (~30MB)  ) and COMMIT =y  or set COMMIT=n (is the default behavior: import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.)
5.Use the direct path to import the data (DIRECT=y)
6.(if possible) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init<SID>.ora file
7.(if possible) Set the LOG_BUFFER to a big value and restart oracle.

8. Stop redo log archiving, if possible.

9. Use COMMIT=n, if possible.

10. It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
11. Use STATISTICS=NONE
12. Disable the INSERT triggers, as they fire during import.
13. Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle  11g) during import.

10. How does NLS affect import/export(exp/imp)??

Import and export are client products, in the same way as SQL*Plus or Oracle Forms, and will therefore translate characters from the database character set to that defined by NLS_LANG. The character set used for  the export will be stored in the export file and, when the file is imported, the import will check the character set that was used. If it is different to that defined by NLS_LANG at the import site, the characters will be translated to the import character set and then, if necessary, to the database character set.

11. How should NLS_LANG be set when using export?

Oracle recommends to set the character set part of NLS_LANG environment parameter

to ALWAYS the same character set as the character set of the database you are exporting.

select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

That way no conversion will take place and the exportfile will be created in the same character set as the original database and contain ALL data from original database (even incorrectly stored data if that would be the case).

Even if the plan is to import this into a database with a different character set later the conversion can be postponed until the import. Note that this has no relation with the Operating system. If your have a

WE8MSWIN1252 database on a unix server (which is totally supported) then you should set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before export.

During *interaction* with the database (= sqlplus) you need to configure your *unix* client properly and that cannot be 1252 seen *unix* does not has a 1252 characterset

Note:264157.1 The correct NLS_LANG setting in Unix Environments

12. How should NLS_LANG be set when using import?

If the source and target database have the same character set, the character set part of the NLS_LANG should be set to that same character set on both the export and the import.

Even if the character sets of the exporting and importing databases are not the same the best (preferred) value to use for the character set part of NLS_LANG on both export and import is still

the character set of the source database.

select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

Setting the NLS_LANG to the character set of the target database during import is also correct as such, but import has some limitations when going to a multibyte characterset (like UTF8), hence using the SOURCE NLS_CHARACTERSET during both imp and exp session is simply the best option, avoiding any problems like IMP-16 "Required character set conversion (type %lu to %lu) not supported".

So, the preferred place to do the conversion is between the import executable and the target database.

Note that this has no relation with the Operating system. If your source database is a WE8MSWIN1252 database then you simply should set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before import, even on a Unix server.

Note that during *interaction* with the database (= sqlplus) you need to configure your *unix* client properly and that cannot be 1252 seen *unix* does not has a 1252 characterset

Note:264157.1 The correct NLS_LANG setting in Unix Environments

Example: you want to go from an WE8MSWIN1252 to an UTF8 db:

Note that this is only the exp/imp example, if you want to migrate to AL32UTF8

Or UTF8 check Note:260192.1 Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode)

for the full story.

1) double check the NLS_CHARACTERSET on the SOURCE database

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

and export with the NLS_LANG set to AMERICAN_AMERICA.<NLS_CHARACTERSET>

In this case we want to create a export file containing WE8MSWIN1252 data.

(This is also the setting you want to use if you take an

export as backup)

on unix this is:

$ set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

$ export NLS_LANG

$ exp ....

on windows this is:

c:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

c:\>exp ....

2) import with the NLS_LANG set to American_america.WE8MSWIN1252 (= source NLS_CHARACTERSET)

into the new UTF8 db.

on unix this is:

$ set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

$ export NLS_LANG

$ imp ....

on windows this is:

c:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

c:\>imp ....

The conversion to UTF8 is done while inserting the data

in the UTF8 database by the imp connection.

We recommend to set the NLS_LANG explicit in the current shell for unix

(-> Note:131207.1 How to Set Unix Environment Variable )

or in the dos box used for the exp or imp tool on windows.

(-> "c:\>set NLS_LANG=AMERICAN_AMERICA.<characterset of the source database>")

13. How is import affected by the NLS_LANGUAGE and NLS_TERRITORY ?

Not. Normally you use the AMERICAN_AMERICA default, but if you imported with NLS_LANG set to FRENCH_FRANCE for example then you will not have problems, even if the originating environment

used GERMAN_GERMANY or so.

14. How to know in what characterset a dmp (export) file is created?

simply issue: imp system/oracle@database show=yes file=test.dmp the output gives you import done in US7ASCII character set and AL16UTF16 NCHAR character set

-> this is the current NLS_LANG value set in the environment and the NCHAR characterset of the target database import server uses WE8MSWIN1252 character set (possible charset conversion)

-> this is only shown if the NLS_LANG during this import session is different from the target database characterset, so if you see 3 lines you might have problems :-)

export client uses UTF8 character set (possible charset conversion)

-> this is the characterset used during the export session and the characterset used in the dmp file.

15. How does NLS affect datapump (expdp/impdp)?

Datapump does not use the NLS_LANG to do conversion between databases.

Conversion between 2 database charactersets is done purely based on the NLS_CHARACTERSET ( or NLS_NCHAR_CHARACTTERSET for Nchar,Nvarchar and Nclob datatypes) of the source and target database.

However, if you specify a parameter file then the NLS_LANG *is* used. This is only important if you use non-English characters (e.g. for the QUERY parameter) in the parameter file.

If you use non-English characters in the parameter file then the NLS_LANG environment variable should be set (in the session where the Data Pump job is started) to the correct encoding of the parameter file (!).

16. What causes ORA-01401 or ORA-12899 during import (imp and impdp) ?

9i and lower gives ORA-01401: inserted value too large for column 10g and up gives ORA-12899: value too large for column

This is seen when exporting from a database with a 8 bit NLS_CHARACTERSET (like WE8ISO8859P1, WE8MSWIN1252 , WE8DEC ...) to a database with a 16 bit NLS_CHARACTERSET (like JA16SJIS , ZHS16GBK, KO16MSWIN949) or NLS_CHARACTERSET set to AL32UTF8 or UTF8

17. What are the different kind of export backups ?

Full back - Complete database.

Incremental - Only affected tables from last incremental date/full backup date.

Cumulative backup - Only affected table from the last cumulative date/full backup date.

18. What is the use of FILE option in EXP command ?

To give the export file name.

19. What is the use of COMPRESS option in EXP command ?

Flag to indicate whether export should compress fragmented segments into single extents.

If we specify COMPRESS=y during export then at the time of table creation while importing, the INITIAL extent of the table would be as large as the sum of all the extents allocated to the table in the original database.
If we specify COMPRESS=n during export then while creating table in the import, it will use the same values of INITIAL extent as in the original database.

Now lets say I have a table of 100 MB. There have been some deletions and updations and only 50 MB of actual data is there. I export the table with COMPRESS=y and recreate it in some other database. It will sum all the extents and assign as INITIAL extent while creating the table. There is only 50 MB of data in the table but it has allocated 100 MB already. In case, you have limited space this is not a very good option.
If I do with COMPRESS=N and then import the table, its INITIAL extent will be as large as INITIAL extent in the original database and then as required, new extents will be allocated. So now my table in the new database would be approximately 50 MB in size.

20. What is the use of GRANT option in EXP command ?

A flag to indicate whether grants on database objects will be exported or not. Value is 'Y' or 'N'.

21. What is the use of INDEXES option in EXP command ?

A flag to indicate whether indexes on tables will be exported.

22. What is the use of ROWS option in EXP command ?

Flag to indicate whether table rows should be exported. If 'N' only DDL statements for

the databse objects will be created.

23. What is the use of CONSTRAINTS option in EXP command ?

A flag to indicate whether constraints on table need to be exported.

24. What is the use of FULL option in EXP command ?

A flag to indicate whether full database export should be performed.

25. What is the use of OWNER option in EXP command ?

List of table accounts should be exported.

26. What is the use of TABLES option in EXP command ?

List of tables should be exported.

27. What is the use of RECORD LENGTH option in EXP command ?

Record length in bytes.

28. What is the use of INCTYPE option in EXP command ?

Type export should be performed COMPLETE, CUMULATIVE, INCREMENTAL.

29. What is the use of RECORD option in EXP command ?

For Incremental exports, the flag indirect whether a record will be stores data dictionary tables recording the export.

30. What is the use of PARFILE option in EXP command ?

Name of the parameter file to be passed for export.