export name case-sensitive tables in oracle

exp xtreme file=xtreme_tables.dmp tables=(‘\”Product\”‘,’\”Supplier\”‘,CUSTOMER,ORDERS)

keep data consistency when using oracle exp/expdp

When we were using old oracle exp, we usually will set exp consistent=y (default is n) to ensure the data consistency (the image taken of the data in the tables being exported represents the committed state of the table data at the same single point-in-time for all of the tables being exported.)

However, started from 10g they decomission this parameter. Today i happened to have a requirement for this and i searched meta link and found this useful piece:

The versions 10gR1 and 10gR2 additionally put the message in the expdp header:

FLASHBACK automatically enabled to preserve database integrity

Does this guarantee export consistency to a single point of time?

Cause
The message:

FLASHBACK automatically enabled to preserve database integrity

only means that some of the tables will be assigned special SCNs (needed for Streams and Logical Standby). There is no consistency guaranteed between exported tables.

The next example demonstrates this:

1. Create the environment

connect / as sysdba

create or replace directory flash as ‘/tmp’;
grant read, write on directory flash to system;

drop user usr001 cascade;
purge dba_recyclebin;

create user usr001 identified by usr001 default tablespace users temporary tablespace temp;
grant connect, resource to usr001;

connect usr001/usr001
create table part001
(
col001 number,
col002 date,
col003 varchar2(1000)
)
partition by range (col001)
(
partition p001 values less than (500001),
partition p002 values less than (1000001)
);

2. Populate the partitioned table: partition P001 contains 500000 rows and partition P002 contains 10 rows

connect usr001/usr001
begin
for i in 1..500010 loop
insert into part001 values (i, sysdate, lpad (to_char(i), 1000, ‘0′));
end loop;
commit;
end;

3. Start expd

#> expdp system/passwd directory=flsh dumpfile=user001_1.dmp logfile =user001_1.log schemas=usr001

4. During point 3. is running, run in a separate session:

connect usr001/usr001
delete from part001 where col001 in (500001, 500002, 500003, 500004, 500005);
commit;

This will delete 5 rows in partition P002.

5. Expdp completes with:

Export: Release 10.2.0.3.0 – 64bit Production on Friday, 05 September, 2008 13:59:59

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** directory=flash dumpfile=usr001_1.dmp logfile=exp_usr001_1.log schemas=usr001
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 568.0 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “USR001″.”PART001″:”P001″ 486.3 MB 500000 rows
. . exported “USR001″.”PART001″:”P002″ 10.50 KB 5 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/media/usbdisk/TESTS/FLASH/usr001_1.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at 14:02:47

=> From partition P002 only 5 rows were exported, so the written export dump is not consistent.
Solution
To generate consistent Data Pump’s database or schema export similar to exports generated with exp parameter CONSISTENT=Y, use Data Pump parameters FLASHBACK_SCN and FLASHBACK_TIME for this functionality.

Conform with the example above, running expdp with:

#> expdp system/passwd directory=flsh dumpfile=user001_2.dmp logfile =user001_2.log schemas=usr001 flashback_time=\”TO_TIMESTAMP \(TO_CHAR \(SYSDATE, \’YYYY-MM-DD HH24:MI:SS\’\), \’YYYY-MM-DD HH24:MI:SS\’\)\”

This will end with:

Export: Release 10.2.0.3.0 – 64bit Production on Friday, 05 September, 2008 14:15:38

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_02″: system/******** directory=flash dumpfile=usr001_2.dmp logfile=exp_usr001_2.log schemas=usr001 flashback_time=”to_timestamp (to_char (sysdate, ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY-MM-DD HH24:MI:SS’)”
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 568.0 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported “USR001″.”PART001″:”P001″ 486.3 MB 500000 rows
. . exported “USR001″.”PART001″:”P002″ 15.48 KB 10 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/media/usbdisk/TESTS/FLASH/usr001_2.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_02″ successfully completed at 14:16:55

=> Partition P002 contains all 10 rows, though 5 rows were deleted during expdp time. The parameter FLASHBACK_TIME guarantees the consistency.

Link:  “Doc ID: 377218.1″

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4374876471460387797::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,377218.1,1,0,1,helvetica

how to export/import data with LOB type from one user/schema to another user?

When you are using exp/imp moving data from one user/schema to another, if the schema contains some table with lob data type, then you will get error like this:

IMP-00017: following statement failed with ORACLE error 1950:
“CREATE TABLE “TEST” (“ID” NUMBER, “NAME” CLOB) ……..
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace ‘USERS’

So how to export/import data with LOB type from one user/schema to another user?

Here are the solutions:

1. for oracle 10g and 11g, directly using datapump can avoid this issue. Datapump support lob pretty well:

export:

expdp jehan directory=DATA_PUMP_DIR schemas=(LOUVRE_121307,DENALI10G) dumpfile=cart_dp.dmp logfile=cart_expdp.log

import:

impdp system DIRECTORY=DATA_PUMP_DIR DUMPFILE=CART_DP.DMP LOGFILE=cart_impdp.log REMAP_SCHEMA=LOUVRE_121307:cartesis_LOUVRE_121307 REMAP_SCHEMA=DENALI10G:cartesis_DENALI10G REMAP_TABLESPACE=users:cartesis REMAP_TABLESPACE=TBS_DATA_DENALI10G:cartesis REMAP_TABLESPACE=CARTESIS_DATA:cartesis REMAP_TABLESPACE=TBS_DATA_LOUVRE_121307:cartesis

2. for 9i, you have to extract the lob table DDL and create it manually, then do the importing

step1: Import with INDEXFILE parameter to generate the script that can be used to modify the LOB’s tablespace clause.
$imp system/manager fromuser=user1 tables=\(test\) indexfile=create_lob_table

step2: Edit the generated script file create_lob_table.sql:

REM CREATE TABLE “USER1″.”TEST” (“ID” VARCHAR2(8) NOT NULL
REM ENABLE, “FIC_CIRCUL” BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
REM 255 LOGGING STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS
REM 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
REM TABLESPACE “USERS” LOB (“FIC_CIRCUL”) STORE AS (TABLESPACE “USERS”
REM ENABLE STORAGE IN ROW CHUNK 2048 PCTVERSION 10 NOCACHE LOGGING
REM STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121
REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
REM … 0 rows

> Remove the REM comments
> Change the owner USER1 by the new owner USER2
> Change the tablespace USERS by the TOUSER’s tablespace USER2 in the whole statement.

step3. Run the script create_lob_table.sql to create the USER2.TEST table.

step4. Import the data only in the created table, ignoring the CREATE TABLE statement failure.

$imp user2/*** FILE=exp.dmp LOG=imp_tab.log FROMUSER=user1 TOUSER=user2 TABLES=test IGNORE=y