Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use EXP/IMP tools, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Overview of Exp

Export tools provide an easy way to transfer data objects, and you can even use export tools to transfer data between different platforms and different hardware.

When we use export to export the database, the database object and its associated objects are extracted. For example, when you export a table, the indexes, comments, and permissions of the table are all exported.

An export file is a two-process dump file stored on disk or tape. The dump file can be moved elsewhere using FTP or other physical transfer tools, and the file can be dumped into two databases without a network connection by import. Of course, this file can also be used as part of the backup.

Files exported by exp can only be recognized by the import tool imp. The imp tool cannot be earlier than the exp import tool version.

Use the show parameter of imp to display the exported file without importing the file. SQL Loader is required to import ASCII files or restriction files.

Use EXP condition

Before using exp, you need to confirm the following conditions:

1. If the data is created manually, verify that the catexp.sql, catalog.sql script has been executed. If DBCA builds the library, you don't need to check these two scripts. ?

two。 Make sure there is enough space on the disk or tape to store the exported file. Available for use

SELECT SUM (BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE'

Find out how big the exported file is, and make sure that the exported storage space is at least that large.

? 3. Non-user schema cannot be exported, such as SYS, ORDSYS, MDSYS.

EXP mode

Exp supports four export modes:

1.Full: only users with the EXP_FULL_DATABASE role can export the entire database, using the FULL parameter to specify this schema. ?

2.Tablespace: exports the tablespace set, using the TRANSPORT_TABLESPACE parameter to specify this schema. ?

3.User: export objects that belong to the user, using the OWNER parameter to specify this mode?

4.Table: exports the specified table and partition table. Use the TABLES parameter to specify this mode?

Objects with delayed segment creation properties and no segment creation are not exported when exp is exported. Except tablespace mode, you can export in any mode using conventional path Export or direct path Export

Overview of imp

The import tool reads the exported dump file and imports the table object. Dump files include

1. Type definition

two。 Table definition

3. Table data

4. Table index

5. Integrity constraints, views, stored procedures, triggers

6. Bitmap, functional index, domain index

Import order: create tables, import data, create indexes, triggers, integrity constraints, create bitmaps, functional indexes, domain indexes.

Imp use conditions

The conditions for using imp are similar to those for exp, but it is worth noting that:

1. Expandable FULLLTATABASE-> IMP_FULL_DATABASE

two。 Import object must have permission to create object

3. Permission cascading

4. Tablespace quota

Because you need to create an object during import, the user must have permission to create the object. Can be directly assigned to resource roles

Permission cascading

When importing permissions granted by a user, the object must belong to that user or have with grant option permissions for the object. The concatenation of permissions has been explained more clearly in the chapter of cascading, so I will not repeat it here. The following is a relational table of the permissions required by the database user when the object permissions and system permissions are imported.

Grant

Conditions

Object privileges

The object must exist in the user's schema, or

The user must have the object privileges with the WITH GRANT OPTION or

The user must have the IMP_FULL_DATABASErole enabled.

System privileges

User must have the SYSTEM privilege and also the WITH ADMIN OPTION.

Import system objects

The import system object must be a full library export and have the IMP_FULL_DATABASE role. The full import includes the following objects

1.Profiles

2.Public database links

3.Public synonyms

4.Roles

5.Rollback segment definitions

6.Resource costs

7.Foreign function libraries

8.Context objects

9.System procedural objects

10.System audit options

11.System privileges

12.Tablespace definitions

13.Tablespace quotas

14.User definitions

15.Directory aliases

16.System event triggers

Considerations for import:

1. Objects in the target library should be backed up and transferred before import. Because the dump file already holds the metadata for the imported object, it is best to use this metadata to create the object.

two。 Because the associated objects may not be imported, the referential constraint may fail on import.

3. You should pay attention to the relevant triggers when you import, for example, when the import tool creates an object, the trigger blocks the event, which will cause import problems. So yin has to be tested before it is imported.

4.full exports do not export SYS triggers, you must manually go to the target library to create SYS triggers. Since the import may be affected, it is recommended that you create a trigger after the import.

The above is all the content of this article "how to use EXP/IMP tools". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.