 |
|
|
|
|
|
|
|
| |
| |
| |
Technique |
|
Domaines |
|
Divers | |
Contact | |
[06/06/2005]
Export/import en mode direct ou pas – rollback …
La taille du rollback généré par Oracle lors d’un import des données exportées en mode direct ou non est IDENTIQUE ! L’exemple suivant le montre, une table ext d’abord exportée en mode conventionnel, ensuite en mode direct, les rollback segments utilisés sont mesurés dans les deux cas, la différence est nulle.
Création de l’utilisateur
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> create user a identified by a; User created. SQL> grant connect, resource to a; Grant succeeded. SQL> alter user a default tablespace users; User altered.
Remplissage d’une table avec ~ 2 000 000 de lignes
... SQL> select count(*) from a.test; COUNT(*) ---------- 2097152
Export en mode conventionnel de l’utilisateur
SQL> host Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. C:\Documents and Settings\Administrateur>exp system@ulib9 owner=a Export: Release 9.2.0.1.0 - Production on Lu Jun 6 18:52:55 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user A . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user A About to export A's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export A's tables via Conventional Path ... . . exporting table TEST 2097152 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. C:\Documents and Settings\Administrateur>exit
Suprimmer l’utilisateur
SQL> drop user a cascade; User dropped. SQL> create user a identified by a; User created. SQL> grant connect, resource to a; Grant succeeded. SQL> alter user a default tablespace users; User altered.
Mesurer la quantité de rollback avant
SQL> select writes from v$rollstat;
WRITES
----------
6900
1607006
702336
522118
1139444
2953422
6148308
2401606
567506
437022
585598
11 rows selected.
L’import
SQL> host imp system@ulib9 fromuser=a touser=a Import: Release 9.2.0.1.0 - Production on Lu Jun 6 18:55:34 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export file created by EXPORT:V09.02.00 via conventional path import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set . importing A's objects into A . . importing table "TEST" 2097152 rows imported Import terminated successfully without warnings.
Mesurer la quantité de rollback après
SQL> select writes from v$rollstat;
WRITES
----------
6900
1608728
702336
522118
1139444
2953422
6148308
8005268
567506
437022
585598
11 rows selected.
SQL> host
La différence de writes montre la quantité de rollback segment générée:
Avant Apres Diff ------- ---------- 6900 6900 0 1607006 1608728 1722 702336 702336 0 522118 522118 0 1139444 1139444 0 2953422 2953422 0 6148308 6148308 0 2401606 8005268 5603662 567506 567506 0 437022 437022 0 585598 585598 0
Le rollback a utilisé pendant l’import 5603662 bytes.
Le même export avec direct=true
Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. ---- C:\Documents and Settings\Administrateur>exp system@ulib9 owner=a direct=true Export: Release 9.2.0.1.0 - Production on Lu Jun 6 18:57:03 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user A . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user A About to export A's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export A's tables via Direct Path ... . . exporting table TEST 2097152 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. C:\Documents and Settings\Administrateur>exit
Suprimmer l’utilisateur
SQL> drop user a cascade; User dropped. SQL> create user a identified by a; User created. SQL> grant connect, resource to a; Grant succeeded. SQL> alter user a default tablespace users; User altered. SQL> select writes from v$rollstat;
Mesurer la quantité de rollback avant
WRITES
----------
6900
1609216
705116
522344
1141354
2953706
6149454
8007482
568464
437260
585598
11 rows selected.
L’import
SQL> host imp system@ulib9 fromuser=a touser=a Import: Release 9.2.0.1.0 - Production on Lu Jun 6 18:59:00 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export file created by EXPORT:V09.02.00 via direct path import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set . importing A's objects into A . . importing table "TEST" 2097152 rows imported Import terminated successfully without warnings. SQL>
Mesurer la quantité de rollback après
SQL> select writes from v$rollstat;
WRITES
----------
6900
1609216
705116
522344
1141354
2953706
6149454
8007482
570186
6040922
585598
La différence de writes montre la quantité de rollback segment générée:
Avant Apres Diff ----- ---------- 6900 6900 0 1609216 1609216 0 705116 705116 0 522344 522344 0 1141354 1141354 0 2953706 2953706 0 6149454 6149454 0 8007482 8007482 0 568464 570186 1722 437260 6040922 5603662 585598 585598 0
Le rollback a utilisé pendant l’import 5603662 bytes, exactement comme dans le cas précedent, sans l’option direct=true.
Radu Caulea, TAFORA,Senior Oracle Consultant
| Copyright © 1998-2002 Radu Caulea, TAFORA | MAJ 06/11/2006 |  |
| Commentaires et suggestions radu[CHEZ]tafora.fr | ||