Home » RDBMS Server » Server Utilities » DATAPUMP example, appending rows to existing table (parent-child relation)
| DATAPUMP example, appending rows to existing table (parent-child relation) [message #226174] |
Thu, 22 March 2007 13:19  |
sebastianR
Messages: 33
Registered: August 2005
|
Member |
|
|
Hi,
I am currently trying to evaluate the behaviour when importing from a dump-file when the tables which to import already exist in the destination-DB; additionally I have a parent-child table relation (2 tables);
So what I have done is, I exported the tables from a schema S_2, then imported them into another schema S_1 where they did not exist.
-> WORKED
Then, I've added some rows to the tables in the export-schema, again made an export, and tried to import it again (with TABLE_EXISTS_ACTION = APPEND).
-> WORKED but WRONG
While the export works out fine, I keep having problems when importing. The problem is, that instead of ignoring the rows which already exist and NOT append them, they get insert with a new key_id (generated using the trigger);
The goal of course, would be for me to have the tables updated after import, containing the same rows as in the schema I exported from.
So what I am looking for, is a basic example of how to achive this simple task.
In the following, I will post my table structures, the export code, the import code and the log messages:
========== TABLES ============
CREATE TABLE TEST_A
(
KEY_ID NUMBER,
DATA NUMBER
)
CREATE UNIQUE INDEX TEST_A_PK ON TEST_A
(KEY_ID);
CREATE OR REPLACE TRIGGER TR_TEST_A#BI
BEFORE INSERT
ON TEST_A
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
SELECT SQ_TEST_DP1.NEXTVAL INTO :NEW.key_id FROM dual;
END;
CREATE TABLE TEST_B
(
KEY_ID NUMBER,
FK_ID NUMBER,
DATA NUMBER
);
CREATE UNIQUE INDEX TEST_B_PK ON TEST_B
(KEY_ID);
CREATE OR REPLACE TRIGGER TR_TEST_B#BI
BEFORE INSERT
ON TEST_B
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
SELECT SQ_TEST_DP2.NEXTVAL INTO :NEW.key_id FROM dual;
END;
ALTER TABLE TEST_B ADD (
CONSTRAINT TEST_B_PK
PRIMARY KEY
(KEY_ID)
USING INDEX
TABLESPACE FDMT_DEVL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
ALTER TABLE TEST_B ADD (
CONSTRAINT TEST_B_FK1
FOREIGN KEY (FK_ID)
REFERENCES TEST_A (KEY_ID));
Table-Structure, Relation:
________
Table_A
________
KEY_ID
DATA
________
Table_B
_______
FK_ID
KEY_ID
DATA
with Table_A.KEY_ID = Table_B.FK_ID
Content of Tables (SOURCE-Schema S_2):
S_2.Table_A
==============
KEY_ID DATA
==============
6 151515
0 555
1 844
2 569
3 139
4 546
5 0
S_2.Table_B
=====================
KEY_ID FK_ID DATA
=====================
0 0 99994
1 0 99993
2 0 99992
3 0 99991
4 1 888761
5 1 45761
6 1 45463
7 2 662166
8 2 77102
Content of Tables (DESTINATION-Schema S_1)
(after importing with import-code multiple times)
S_1.Table_A
============
KEY_ID DATA
============
0 555
1 844
2 569
27 555
28 844
29 569
30 555
31 844
32 569
S_1.Table_B
======================
KEY_ID FK_ID DATA
======================
0 0 99994
1 0 99993
2 0 99992
3 0 99991
4 1 888761
5 1 45761
6 1 45463
7 2 662166
8 2 77102
15 0 99994
16 0 99993
17 0 99992
18 0 99991
19 1 888761
20 1 45761
21 1 45463
22 2 662166
23 2 77102
24 0 99994
25 0 99993
26 0 99992
27 0 99991
28 1 888761
29 1 45761
30 1 45463
31 2 662166
32 2 77102
Since I only exported records which have a child, respectively records which have a parent, some records are not imported(Table_A, key_ids IN (3,4,5,6)) -> okay
But why are they added on each import? See bold lines in CODE, those records are in table 3 times; shouldn't they have been skipped since they already exist?
Two other questions:
1) I have discovered, that exporting the way I do at the moment, no sequences will be exported, which leads to not compilable triggers - so I've added those sequences by hand -> is this normal or should the sequences have been exported?
2) I had to execute the following code (EXPORT-Code) with the user S_2, the user whose schema contains the tables I want to export, but shouldn't it work with every other user too since I defined a METADATA_FILTER with SCHEMA_EXPR => 'S_2', it should be known from which schema to export?
I am asking, because when I used S_1 to export from S_2, I did not gain any rows (at the time I tried, the two tables were present in Schema S_1 too, but empty).
-- EXPORT --
-- Tables TEST_A, TEST_B --
-- From Schema S_2 --
declare
l_dp_handle NUMBER;
begin
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'TABLE',
remote_link => NULL,
job_name => 'SR_TEST',
version => 'LATEST'
);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SR_TEST.dmp',
directory => 'SR_TEST'
);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'SR_TESTLOG.log',
directory => 'SR_TEST',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => 'IN (''S_2'')');
DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'NAME_EXPR',
value => 'IN (''TEST_A'', ''TEST_B'')');
DBMS_DATAPUMP.DATA_FILTER(
handle => l_dp_handle,
name => 'SUBQUERY',
value => 'WHERE KEY_ID IN (SELECT fk_id FROM TEST_B)',
table_name => 'TEST_A'
);
DBMS_DATAPUMP.DATA_FILTER(
handle => l_dp_handle,
name => 'SUBQUERY',
value => 'WHERE FK_ID IN (SELECT key_id FROM TEST_A)',
table_name => 'TEST_B'
);
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END;
Note, the two Data filters which I inserted; the first one is supposed to only retrieve rows from table TEST_A which have childs in table TEST_B, and the second filter is supposed to retrieve only rows from table TEST_B which have parents in TEST_A;
The idea is, that I want to be able to export records with their child-records, but I doubt this is the best approach -> please coach me! 
3) Do you know a better way of exporting a list of objects with all their related child-records?
-- IMPORT --
-- Table TEST_A, TEST_B --
-- Schema S_2 -> S_1 --
declare
l_handle NUMBER;
begin
l_handle := DBMS_DATAPUMP.open(
operation => 'IMPORT',
job_mode => 'TABLE',
job_name => 'SR_TEST');
DBMS_DATAPUMP.add_file(
handle => l_handle,
filename => 'SR_TEST.dmp',
directory => 'SR_TEST'
);
DBMS_DATAPUMP.add_file(
handle => l_handle,
filename => 'SR_TESTLOG_IMP.log',
directory => 'SR_TEST',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
DBMS_DATAPUMP.METADATA_REMAP
(handle => l_handle,
name => 'REMAP_SCHEMA',
old_value => 'S_2',
value => 'S_1');
DBMS_DATAPUMP.set_parameter(
handle => l_handle,
name => 'TABLE_EXISTS_ACTION',
value => 'APPEND');
DBMS_DATAPUMP.start_job(l_handle);
DBMS_DATAPUMP.detach(l_handle);
end;
Export-Log
"S_2"."SR_TEST": wird gestartet
Schätzung erfolgt mit Methode BLOCKS...
Objekttyp TABLE_EXPORT/TABLE/TABLE_DATA wird verarbeitet
Gesamte Schätzung mit BLOCKS Methode: 128 KB
Objekttyp TABLE_EXPORT/TABLE/TABLE wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/INDEX/INDEX wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/TRIGGER wird verarbeitet
. . "S_2"."TEST_A" 5.242 KB 3 Zeilen exportiert
. . "S_2"."TEST_B" 5.648 KB 9 Zeilen exportiert
Master-Tabelle "S_2"."SR_TEST" erfolgreich geladen/entladen
******************************************************************************
Import-Log
Master-Tabelle "S_1"."SR_TEST" erfolgreich geladen/entladen
"SR"."SR_TEST": wird gestartet
Objekttyp TABLE_EXPORT/TABLE/TABLE wird verarbeitet
ORA-39152: Tabelle "S_1"."TEST_A" ist vorhanden. Daten werden an bestehende Tabelle angehängt, alle abhängigen Metadaten werden jedoch wegen table_exists_action von Append übersprungen.
ORA-39152: Tabelle "S_1"."TEST_B" ist vorhanden. Daten werden an bestehende Tabelle angehängt, alle abhängigen Metadaten werden jedoch wegen table_exists_action von Append übersprungen.
Objekttyp TABLE_EXPORT/TABLE/TABLE_DATA wird verarbeitet
. . "S_1"."TEST_A" 5.242 KB 3 Zeilen importiert
. . "S_1"."TEST_B" 5.648 KB 9 Zeilen importiert
Objekttyp TABLE_EXPORT/TABLE/INDEX/INDEX wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/TRIGGER wird verarbeitet
Job "S_1"."SR_TEST" mit 2 Fehler(n) um 18:43:44 abgeschlossen
Please provide _any_ useful information, for example: which objects get exported when exporting the way I did?
triggers, constraints, NOT sequences? ...
why are the rows from the dump-file inserted into the tables even if they already exist? is it because of the constraints/triggers?
How do I export specific objects with their child-relations?
In general: Is it practical/thinkable to use DATAPUMP for migration/deployment, using data_filters to attain individual dump files? What would your evaluation be, what experience did you make with it?
Thank you very much for reading this post, and please feel free to give me feedback, advise, answers, questions, etc.
Yours truely,
sebastianR
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 01:53:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
