Transportable Tablespace et TABLE_EXIST=APPEND
Bonjour,
J'essaye de generer beaucoup de donnees (plus ou moins random). Ca, ca va. Pour aller plus vite, je me suis dit que c'etait peut-etre possible de copier/coller directement les datafiles, ca me ferait des doublons, mais vu le temps gagne je m'en accomode. Bien entendu, un bete copie colle ne fonctionne pas.
Du coup je me suis penche du cote de datapump.
En gros, pour le moment j'ai ca:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| SYS@minilhc AS SYSDBA>select table_name, tablespace_name,blocks from dba_tables where table_name='TMP' and owner='TMP';
TABLE_NAME TABLESPACE_NAME BLOCKS
------------------------------ ------------------------------ ----------
TMP TEST_IMPORT 33883
SYS@minilhc AS SYSDBA>select count(unique n) from tmp.tmp;
COUNT(UNIQUEN)
--------------
99999
SYS@minilhc AS SYSDBA>select tablespace_name, file_name from dba_data_files where tablespace_name='TEST_IMPORT';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST_IMPORT C:\DEMO-ORACLE\TEST_IMPORT_01.DF |
Et a la fin de mon import je souhaiterai obtenir quelque chose dans ce style:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| SYS@minilhc AS SYSDBA>select table_name, tablespace_name,blocks from dba_tables where table_name='TMP' and owner='TMP';
TABLE_NAME TABLESPACE_NAME BLOCKS
------------------------------ ------------------------------ ----------
TMP TEST_IMPORT 67866 --Deux fois plus de blocks
SYS@minilhc AS SYSDBA>select count(unique n) from tmp.tmp;
COUNT(UNIQUEN)
--------------
99999 --Toujours le meme nombre, vu que ca fait des doublons
SYS@minilhc AS SYSDBA>select tablespace_name, file_name from dba_data_files where tablespace_name='TEST_IMPORT';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST_IMPORT C:\DEMO-ORACLE\TEST_IMPORT_01.DF
TEST_IMPORT C:\DEMO-ORACLE\TEST_IMPORT_02.DF --Un second datafile |
J'ai essaye de faire la chose suivante:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| C:\>EXPDP tmp/xxxx DIRECTORY='DATA_PUMP_DIR' DUMPFILE='test_import.dump' TRANSPORTABLE=ALWAYS TABLES='TMP.TMP'
Export: Release 11.2.0.1.0 - Production on Mon Nov 14 16:30:42 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Real Application Testing options
Starting "TMP"."SYS_EXPORT_TABLE_01": tmp/******** DIRECTORY='DATA_PUMP_DIR' DUMPFILE='test_import.dump' TRANSPORTABLE=ALWAYS TABLES='TMP.TMP'
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "TMP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TMP.SYS_EXPORT_TABLE_01 is:
C:\...\TEST_IMPORT.DUMP
******************************************************************************
Datafiles required for transportable tablespace TEST_IMPORT:
C:\DEMO-ORACLE\TEST_IMPORT_01.DF
Job "TMP"."SYS_EXPORT_TABLE_01" successfully completed at 16:30:50 |
Ce qui me parait pas trop mal par rapport a ce que je souhaite faire.
Ensuite, faire un copie/colle manuel du datafile, en le renomant TEST_IMPORT_02.df.
Et apres, lors de l'import, il me dit (avec raison) que le tablespace existe... Ligne 15
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| C:\>impdp tmp/xxxx directory='data_pump_dir' dumpfile='test_import.dump' TABLES='TMP.TMP' transport_datafiles='C:\DEMO
-ORACLE\TEST_IMPORT_02.DF' TABLE_EXISTS_ACTION=APPEND
Import: Release 11.2.0.1.0 - Production on Mon Nov 14 16:34:49 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Real Application Testing options
Master table "TMP"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TMP"."SYS_IMPORT_TABLE_01": tmp/******** directory='data_pump_dir' dumpfile='test_import.dump' TABLES='TMP.TMP' transport_datafiles='C:\DEM
O-ORACLE\TEST_IMPORT_02.DF' TABLE_EXISTS_ACTION=APPEND
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace 'TEST_IMPORT' already exists
Job "TMP"."SYS_IMPORT_TABLE_01" stopped due to fatal error at 16:34:51 |
Est-ce que c'est simplement pas possible, et je dois necessairement passer par un dumpfile pour faire ca? Ou alors je m'y prends comme un manche?
Merci!:P
