Whether the DML operation affects the exported data during EXP Export

Whether the DML operation affects the exported data during EXP Export

I. Overview

With the upgrade of the database version and the increase of business testing, database or data migration has become one of the common tasks of database managers. If you export data with strict requirements for big data tables, will this operation be exported? Due to my limited personal ability, it may take some time to study logical backup. I have only conducted a small experiment for the moment. If you have other methods and more comprehensive instructions, please click here.

Ii. Operation Process

The following experiment procedure is to execute the DML operation every time a logical export is performed. The operation statement is as follows (database version 10.2.0.5 ):

The user is firsoul. The operated tables include age, big_table, and test, which are the initial data of each table.

SQL> show user

USER is "FIRSOUL"

SQL> select * from tab;

SQL> select table_name, NUM_ROWS from user_tables;

 

TABLE_NAME NUM_ROWS

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

EMP 14

DEPT 4

TEST 1

AGE 1

Big _ TABLE 3000000
 

First operation:

Exp firsoul/firsoul tables = BIG_TABLE CONSISTENT = Y file = exp_table.dmp log = exp_table.log
Insert into age values (22, 'mingli ');

Delete from big_table where id = 7;

Insert into test values (2, 'shandong ');

Commit;
 

The second operation:

 

Exp firsoul/firsoul file = exp_user.dmp log = exp_user.log consistent = y grants = y
Insert into age values (24, 'mengmeng ');

Delete from big_table where id = 14;

Insert into test values (3, 'China ');

Commit;
 

The third operation:

Exp firsoul/firsoul tables = BIG_TABLE file = exp_table2.dmp log = exp_table2.log
Insert into age values (24, 'xiaochi ');

Delete from big_table where id = 21;

Insert into test values (4, 'dezhou ');

Commit;
 

Fourth operation:

Exp firsoul/firsoul file = exp_user2.dmp log = exp_user2.log grants = y
Insert into age values (25, 'fengyin ');

Delete from big_table where id = 28;

Insert into test values (5, 'json ');

Commit;
 

Fifth operation:

Expdp firsoul/firsoul directory = dir_dmp dumpfile = expdp_table.dmp tables = BIG_TABLE logfile = expdp_table.log
Insert into age values (30, 'shubin ');

Delete from big_table where id = 35;

Insert into test values (6, 'shizhong ');

Commit;
 

Sixth operation:

Expdp firsoul/firsoul directory = dir_dmp dumpfile = expdp_user.dmp SCHEMAS = firsoul logfile = expdp_user.log
Insert into age values (32, 'liupan ');

Delete from big_table where id = 42;

Insert into test values (7, 'tianqiao ');

Commit;
 

The result is as follows ("-" indicates a few fewer rows and "+" indicates multiple rows ):

Number of DML executions	Age	Big_table	Test	Remarks
1		0		Export table (CONSISTENT = y)
2	+ 2	-2	+ 2	Export user (CONSISTENT = y)
3		-2		Export table (CONSISTENT = n)
4	+ 3	-3	+ 4	Export user (CONSISTENT = n)
5		-5		Expdp export table
6	+ 6	-6	+ 6	Expdp Export user

From the above data, we can see that

1. Export exp. The parameter CONSISTENT = y is used to export the big_table table separately. The data does not change. When exporting users, the data changes.

2. Export exp. The parameter CONSISTENT = n is used to export the big_table table separately. The data remains unchanged. When exporting User data, the table age and big_table remain unchanged. The table test has multiple rows, exp export is sorted by the first letter of the table. During export, when we execute the DML statement (the fourth operation), age has been exported, big_table is being exported, and test is still waiting for export. From this we can see that the DML operation performed on the table during the export of exp does not affect the export data.

3. Data changes during expdp export. The tables exported by expdp are sorted in ascending order of data.

Iii. Summary

This test is actually boring, and there are still some points. To sum up, just one point, when the exp task starts, when the table has executed the logical export operation (execution or export completed ), any DML operation has no effect on the export data. When the export task does not involve the table, the DML operation affects the export data of the table. In a word, the DML operation time (in sequence) affects the export of table data. I still don't quite understand the principles of the database. I hope you can take a closer look and hope. The above is just the conclusion of my sleepy status. You are welcome to make a good shot. The level is limited. Sorry for the shortcomings.

Oracle EXP export error EXP-00091 Analysis Solution

Explore Character Set problems in Oracle EXP/IMP Process

Oracle EXP imp backup and recovery of table space data

Oracle creates automatically executed EXP tasks

Oracle 11g export table reports EXP-00011: table does not exist

EXP/imp export import data reported error ORA-12154 failed to parse the specified connection identifier