Some case studies in export/import

1)   Import in data tablespace from diffrent tablespace 

If you want to import  from one tablespace to other .then INDEXFILE can solve your purpose.

Issue is here  how to change the tablespace name in which table has to be import . firstly  using INDEXFILE we have to create the file can give statements of create the tables and indexes.

Now we have to  make some changes in the file  in unix “sed “   commands can help to trim the file

At window environment  can simply use find replace from a file.

EXAMPLE : -

 imp  impuser/impuser@mediamat FILE=/home/oracle/expdrec/exp.dmp LOG=/home/oracle/expdrec/impuser.LOG FROMUSER=scott TOUSER=impuser commit=y indexFILE=/home/oracle/expdrec/index.txt

1)Find the name of tablespace from indexfile using following command

[oracle@mediamath-server ~]$ cat /home/oracle/expdrec/index.txt|grep 'TABLESPACE "'  

2)Following command  replace tablespace name [Source tablespace  :exp_tablespace and destination tablespace :imp_tablespace]

sed -e "s/exp_tablespace/imp_tablespace/g" /home/oracle/expdrec/index.txt >/home/oracle/expdrec/index1

3)Replace the  string REM to blank space[comments]

sed -e "s/REM//g" /home/oracle/expdrec/index1.sql >/home/oracle/expdrec/index.sql                                         

4)Delete the rows having the rows counts number

sed '/rows/d' /home/oracle/expdrec/index.sql >/home/oracle/expdrec/index1.sql

Run the script after connecting the user 

SQL>@/home/oracle/expdrec/index1.sql

SQL> table created

SQL>index created

SQL> select tablespace_name ,table_name from user_tables;

TABLESPACE_NAME                TABLE_NAME

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

             imp_tablespace                BONUS

              mp_tablespace               COMP

2)  export and import of table  case sensitive

Export

[oracle@mediamath-server ~]$ exp scott/tiger@mediamat file=/home/oracle/expdrec/exp.dmp log=/home/oracle/expdrec/exp.log statistics=ESTIMATE tables=MM_STG.TBL_TEST,SCOTT.EMP,SCOTT.COMP,SCOTT.\"TEST_data\"  consistent=y

Import

If table name is case sensitive like  TEST_data  then import table name shoud be  in sigle quotes( ' ' )  followed by double quotes ( “”) .by default it  reads  table name in the upper case.

Example:-

[oracle@mediamath-server expdrec]$  imp impuser/impuser@mediamat FILE=/home/oracle/expdrec/exp.dmp LOG=/home/oracle/expdrec/imp.log tables=TBL_TEST,EMP,COMP,'\"TEST_data\"' FROMUSER=MM_STG  FROMUSER=SCOTT FROMUSER=impuser ignore=y

Import: Release 10.2.0.4.0 - Production on Thu May 5 14:28:25 2011

. . importing table                    "TEST_data"         11 rows imported

. importing MM_STG's objects into IMPUSER

. importing SCOTT's objects into SCOTT

About to enable constraints...

Import terminated successfully with warnings.

3)  

Import  tables in of different users.

 Dump file contains the data of multiple user . And have to import data in multiple or single  users.

i.e dump file contain data of  user MM_STG  and SCOTT  and want to import data in the IMPUSER

the we have to specifies  fromuser parameter for  both the users  MM_STG and SCOTT and specifies touser=IMPUSER  both the above users.if you specifies touser only once then first user import data in impuser and all other search the schema same as their name .if exists then insert data otherwise raise error

 Import the data from different user  specifies  multiple "fromuser" and "touser" options in pair.

EXAMPLE:-

[oracle@mediamath-server expdrec]$  imp impuser/impuser@mediamat FILE=/home/oracle/expdrec/exp.dmp LOG=/home/oracle/expdrec/imp.log tables=TBL_TEST,EMP,COMP   FROMUSER=MM_STG  TOUSER=impuser   FROMUSER=SCOTT TOUSER=impuser ignore=y

     

Import: Release 10.2.0.4.0 - Production on Thu May 5 14:28:25 2011

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

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

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

Export FILE created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing MM_STG's objects into IMPUSER

. . importing table                     "TBL_TEST"          4 rows imported

. importing SCOTT's objects into IMPUSER

. . importing table                          "EMP"

. . importing table                         "COMP"        232 rows imported

. . importing table                    "TEST_data"         11 rows imported

4)  Export tables  from using query 

To export the tables  with selected  rows with specific conditions  we simply use QUERY clause with export command.

{oracle@mediamath-server expdrec]$  exp scott/tiger  FILE=/home/oracle/expdrec/exp.dmp LOG=/home/oracle/expdrec/imp.log STATISTICS=none TABLES=emp QUERY==\"where HIREDATE \< to_date\(\'03-DEC-1981\',\'dd-mm-yyyy\'\)\"