Labels

Wednesday, May 11, 2011

Questions of Oracle Export and Import


Questions regarding export and import?
1)What is the difference between traditional export and data pump export?
Ans:-


1)Impdp/Expdp has self-tuning unities. Tuning parameters that were used in original Export and Import, such as BUFFER and RECORDLENGTH, are neither required nor supported by Data Pump Export and Import.
2)Data Pump represent metadata in the dump file set as XML documents rather than as DDL commands.
3)Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.
4)In Data Pump expdp full=y and then impdp schemas=prod is same as of expdp schemas=prod and then impdp full=y where in original export/import does not always exhibit this behavior.
5)Expdp/Impdp access files on the server rather than on the client.
6)Expdp/Impdp operate on a group of files called a dump file set rather than on a single sequential dump file.
7)Sequential media, such as tapes and pipes, are not supported in oracle data pump.But in original export/import we could directly compress the dump by using pipes.
8)The Data Pump method for moving data between different database versions is different than the method used by original Export/Import.
9)When you are importing data into an existing table using either APPEND or TRUNCATE, if any row violates an active constraint, the load is discontinued and no data is loaded. This is different from original Import, which logs any rows that are in violation and continues with the load.
10)Expdp/Impdp consume more undo tablespace than original Export and Import.
11)If a table has compression enabled, Data Pump Import attempts to compress the data being loaded. Whereas, the original Import utility loaded data in such a way that if a even table had compression enabled, the data was not compressed upon import.
12)Data Pump supports character set conversion for both direct path and external tables. Most of the restrictions that exist for character set conversions in the original Import utility do not apply to Data Pump. The one case in which character set conversions are not supported under the Data Pump is when using transportable tablespaces.
13)There is no option to merge extents when you re-create tables. In original Import, this was provided by the COMPRESS parameter. Instead, extents are reallocated according to storage parameters for the target table.
2)What is the meaning of compress=y in export command ?
Ans:-

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.
For more information please visit below site
http://oracleadmins.wordpress.com/2008/08/05/understanding-compress-parameter-in-export/
3)How to know the content of dump without importing the dump in oracle 10g?
Answer:-
Three way to get DDL information of a dump.
1)Export Data Pump client (release 10.1.x to 11.1.x)Oracle Version
% impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y
A sample output of the sqlfile impdp_s.sql is: 
-- CONNECT SYSTEM 
-- new object type path is: SCHEMA_EXPORT/USER 
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67' 
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP" 
PASSWORD EXPIRE; 
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT 
GRANT UNLIMITED TABLESPACE TO "SCOTT"; 
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT 
GRANT "CONNECT" TO "SCOTT"; 
GRANT "RESOURCE" TO "SCOTT"; 
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE 
ALTER USER "SCOTT" DEFAULT ROLE ALL; 
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 
-- CONNECT SCOTT 
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), 
export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2303625'); 
COMMIT; 
END; 

-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE 
-- CONNECT SYSTEM 
...


2)classic export client (release 7.3.x to 10.2.x)Oracle Version
% imp system/manager FILE=exp_s.dmp LOG=imp_show.log FULL=y SHOW=y
. importing SYSTEM's objects into SYSTEM 
. importing SCOTT's objects into SCOTT 
"BEGIN " 
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','" 
"CURRENT_SCHEMA'), export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2301" 
"131');" 
"COMMIT; END;" 
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" 
"CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER" 
", "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI" 
"TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U" 
"SERS" LOGGING NOCOMPRESS" 
. . skipping table "BONUS" 
... 
Import terminated successfully without warnings. 
3)10g release2 and higher version of oracle
SET serveroutput on SIZE 1000000   
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_s.dmp')  
A sample screen output of this procedure in Oracle11g for an Export DataPump dumpfile is:
-- call procedure SHOW_DUMPFILE_INFO in SQL*Plus:


SET serveroutput on SIZE 1000000   
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_s.dmp')  
----------------------------------------------------------------------------  
Purpose..: Obtain details about export dumpfile.        Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher  
.          Export dumpfile version: 7.3.4.0.0 or higher  
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher  
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');  
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')  
----------------------------------------------------------------------------  
Filename.: expdp_s.dmp  
Directory: my_dir  
Disk Path: /bugmnt7/em/celclnx7/user/expdp/work  
Filetype.: 1 (Export Data Pump dumpfile)  
----------------------------------------------------------------------------  

4)What is transportable tablespace and how to use it?
5)how to import data without metadata?
6)how to import metadata only?
7)How to import or export specific objects in export/import command?
8)How to use database link in oracle 10g?
9)How to check the status of the export or import job?
10)how to speed up the export or import process?
11)How to export can be done to multiple files?
12)How to perform export utility without using export commands?
13)How to upload data to external files?
14)What are the different types of export mode available in oracle 10g?
15)How to make version compatibility while performing import in oracle?
16)How to configure for stream data loading in import?
17)How to perform truncate data in oracle import command ?
18)How do you perform export if your export command contains many tables or schemas ?
19)What is the difference between SQL loader and import utilities?
20)How to import to a different schema in oracle 10g?

1 comment: