[Translated from MOS] export or import tables with case-insensitive table names without using PAR File

Export or import tables with case-insensitive table names without using the par File

Reference Original:
How to export or import case sensitive tables without using a par file (docid 1622134.1) 1

Applicable:
Oracle Database-Enterprise Edition-version 10.2.0.1 to 11.2.0.4 [release 10.2 to 11.2]
Information in this document applies to any platform.


Objectives:

Export or import tables with case-insensitive table names without using the par File

Solution:

An expdp with PAR file can be used for tables with case-sensitive table names. However, for exp from the command line, the double quotation marks must be escaped (escaped)

The following example shows how to use a table with a case-insensitive table name.


Create a test table:
Connect/As sysdba
Create use test identified by test;
Grant connect, resource, DBA to test;
Create Table Test. "test" as select * From dba_users;


Run the following command to export the table:
Expdp test/test directory = data_pump_dir dumpfile = a1.dmp tables = test. "test"


Expdp test/test directory = data_pump_dir dumpfile = a1.dmp tables = test. "test"

Export: Release 11.2.0.2.0-production on Sat Feb 8 00:17:17 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-Production
With the partitioning, OLAP, data mining and real application testing options
Starting "test". "sys_export_table_01": Test/********* directory = data_pump_dir dumpfile = a1.dmp tables = test. Test
Estimate in progress using blocks method...
Processing object type table_export/table/table_data
Total estimation using blocks method: 0 KB
A ORA-39166: Object test. test was not found. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> it says table not found.
ORA-31655: no data or metadata objects selected for job
Job "test". "sys_export_table_01" completed with 2 error (s) at 00:19:05


In order to successfully export the table from the export, You must escape double quotation marks.
Expdp test/test directory = data_pump_dir dumpfile = A. dmp tables = test. \ "test \"


Export: Release 11.2.0.2.0-production on Sat Feb 8 00:04:13 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-Production
With the partitioning, OLAP, data mining and real application testing options
Starting "test". "sys_export_table_01": Test/********* directory = data_pump_dir dumpfile = A. dmp tables = Amit. "test"
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
... Exported "test". "test" 16.10 kb 51 rows
Master table "test". "sys_export_table_01" successfully Loaded/unloaded
**************************************** **************************************
 
/Home/Oracle/APP/Oracle/admin/orcl/dpdump/a. dmp
Job "test". "sys_export_table_01" successfully completed at 00:05:35