oracle import / export command

2010-11-04  来源:本站原创  分类:Database  人气:102 

exp / imp instance
exp help = y view help
exp
1, exp usr / pwd @ sid File = c: \ tb.dump tables = TB1
If it is to export multiple tables, tables = (tb1, tb2)
2, exp usr / pwd @ sid File = c: \ tb.dump - Export All
3, exp usr / pwd @ sid File = c: \ tb.dump owner = (System, sys)
User system and sys users to export under the table
4, exp usr / pwd @ sid File = c: \ tb.dump tables = TB1 Query = \ "WHERE name = 'ha' \"
Note the position of the semicolon

The following command to view the instructions.

c: \> imp help = y

c: \> exp help = y

About oracle Export and Import

1, Export / Import of use

Oracle Export / Import tool is used to transfer data between databases.
Export to export data from the database dump file
Import from a dump file to enter data in the database the following is a general guide to use their situation (1), to transfer data between two databases with a version of the oracle Server between the different versions of the oracle Server OS between the differences between the same between types of OS (2), for database backup and recovery (3), from one to another SCHEMA SCHEMA
(4), from one to another TABLESPACE TABLESPACE

2, DUMP file
EXPORT to a file format is binary, not hand-edited, otherwise it will damage the data.
ORACLE supports the file are the same on any platform format, you can in all common platforms.

DUMP file in the IMPORT method used upward-compatible, that ORALCE7 the DUMP files can be imported into ORACLE8, but very different versions may be a problem between the version.

3, EXPORT / IMPORT process
EXPORT DUMP exported file contains two basic types of data
- DDL
- Data
DUMP file contains all the DDL to re-create the Data Dictionary's statement is basically to read format.
It should be noted, however, do not use a text editor to edit, oracle not support to do so.

4, IMPORT into the order when the object into the data in, ORACLE has a specific order, with the database versions may vary.

This is mainly resolved by the order of dependencies between objects may have problems. TRIGGER the final import, so the INSERT
Data to the database will not stimulate the TRIGGER. There may be some after import status is INVALID the PROCEDURE, IMPORT will mainly affect some database objects, but do not recompile IMPORT PROCEDURE, resulting in this situation,
To re-compile it, you can solve this problem.

5, compatibility issues
IMPORT EXPORT 5.1.22 tools to deal with later releases of the DUMP file, so you use the IMPORT ORACL E7
Treatment ORACLE6 the DUMP file, and so on, but if the version of ORACLE could not handle a big difference. Specific issues can refer to the appropriate documentation, such as setting the parameters such as (COMPATIBLE parameter)

6, EXPORT need VIEW
VIEW EXPORT need CATEXP.SQL created by these organizations within the VIEW EXPORT DUMP file for the data format.
VIEW used to collect most of the DDL statements to create, the other primarily for developers using ORACLE.

ORACLE VIEW different versions of these may be different between each version may have new features added.
Therefore, the implementation of the new version of the old dump file there will be mistakes, the general can perform CATEXP.SQL solve these problems,
To solve the general problem of backward compatibility as follows:

Export database version than the old situation of the target database:
- The need to import the target database to perform the old CATEXP.SQL
- Use old export EXPORT DUMP file
- Use the old into the database IMPORT
- In the database implementation of the new CATEXP.SQL, to restore the version of the EXPORT VIEW

Export version of the database to new conditions than the target database:
- The need to import the target database to perform a new CATEXP.SQL
- Use the new EXPORT DUMP file export
- IMPORT into the new database
- The implementation in the database the old CATEXP.SQL, to restore the version of the EXPORT VIEW

7 Defragmentation
EXPORT / IMPORT a very important application is to defrag. Because when the initial IMPPORT, will be re-re-import the data CREATE TABLE, so the whole table is stored in a row. EXPORT other default under the DUMP file will generate a "compression (COMPRESS)" TABLE, but in many cases, this compression has been misunderstood. In fact, COMPRESS is to change the value of INITIAL STORAGE parameter. For example:

CREATE TABLE .... STORAGE (INITIAL 10K NEXT 10K ..)
Now the data has been extended to 100 EXTENT, if the use of COMPRESS = Y to EXPORT data
The resulting statement STORAGE (INITIAL 1000K NEXT 10K)

NEXT values and we can see no change in the INITIAL EXTENT sum of all. Therefore, the following situations occur, Table A has 4 100M of EXTENT, the implementation of DELETE FROM A, and then use the COMPRESS = Y export data, generated CREATE TABLE statement will have 400M of the INITIAL EXTENT. Even if it is has been no data TABLE! ! This is a DUMP file even if very small, but it'll IMPORT a huge TABLE.

In addition, the size may exceed the DATAFILE. For example, there are 4 data files 50M, of which Table A are
15 10M of EXTENT, if we adopt the way COMPRESS = Y export data, there will be INITIAL = 150M,
Then re-import, you can not assign a 150M of EXTENT, EXTENT can not cross because of a single file.

8, in the transmission of data between the USER and the TABLESPACE EXPORT data under normal circumstances should be restored to its original place. If the SCOTT user's table to TABLE or USER mode EXPORT data, IMPORT, if the SCOTT user does not exist, then an error! Way to export the data with FULL CREATE USER information, and will therefore create your own USER to store data.

Of course you can use the IMPORT parameters FROMUSER and TOUSER sure you want to import the USER, but to ensure that
TOUSER must have it exists.

9, EXPORT / IMPORT impact on SQUENCE In both cases, EXPORT / IMPORT will SEQUENCE.
(1) If the EXPORT, the user is taking the value of SEQUENCE, SEQUENCE may cause inconsistencies.
(2) Also, if using SEQUENCE CACHE, in EXPORT, those in the CACHE values will be ignored,
Obtained from the data dictionary which is the current value of EXPORT.

If the mode of conducting FULL EXPORT / IMPORT when, just before updating the table with the sequence data in a column, and not the above two cases, the export is to update the previous data.

If the conventional path method, each row of data is to use INSERT statement, the consistency check and INSERT T RIGGER
If you use DIRECT mode, some constraints and may not trigger the trigger, if used in the trigger sequence.nextval, will impact on the sequence.

Then save one thing:

View the current user space the size of each table:
Select Segment_Name, Sum (bytes) / 1024/1024 From User_Extents Group By Segment_Name

View space occupied by each table space size:
Select Tablespace_Name, Sum (bytes) / 1024/1024 From Dba_Segments Group By Tablespace_Name

相关文章
  • oracle import / export command 2010-11-04

    exp / imp instance exp help = y view help exp 1, exp usr / pwd @ sid File = c: \ tb.dump tables = TB1 If it is to export multiple tables, tables = (tb1, tb2) 2, exp usr / pwd @ sid File = c: \ tb.dump - Export All 3, exp usr / pwd @ sid File = c: \ t

  • oracle import export command imp.exp 2011-01-11

    Yesterday, as my colleagues into the database table structure and data, he gave me a dmp file that is included inside the table structure and views, stored procedures and so on. I would like to think that he should be under the command of the form wi

  • Oracle create delete users. Role. Tablespace. Import Export command summary .... 2010-03-20

    / / Create a temporary table space create temporary tablespace zfmi_temp tempfile 'D: \ oracle \ oradata \ zfmi \ zfmi_temp.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; / / Tempfile parameter must be / / Create a data t

  • Oracle create user. Tablespace. Import Export command .... 2010-05-07

    / / Create a temporary table space create temporary tablespace test_temp tempfile 'E: \ oracle \ product \ 10.2.0 \ oradata \ testserver \ test_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; / / Create a data table

  • oracle import export data 2010-08-25

    Oracle data import and export imp / exp is equivalent to oracle data restore and backup. exp command to export data from a remote database server to the local dmp file, imp command to import the dmp file to the distance from the local database server

  • Oracle Import / Export database syntax 2010-11-25

    In addition to the Oracle oracle using the graphical editor, the way way to import data files Arts, but also can be used to import the database command line, real example of its syntax is as follows: imp [username [/ password [@ service]]] Step: 1, f

  • oracle database export command EXP 2011-07-14

    Export: Release 10.2.0.1.0 - Production on Thursday July 14 08:14:41 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. By entering the EXP command and your username / password, the export operation will prompt you to input parameters: For e

  • oracle import export exclusion table 2010-08-06

    imp to import data can not rule out a table into which data import in the IMP, can not lead to the one or several tables without importing Oracle9i and earlier imp, exp import and export tool can only select the table, the table can not be excluded,

  • oracle data export command 2011-04-14

    First, enter sqlplus Enter the user name and password to enter the If prompted to enter an unknown command exp Then add a $ symbol in front Export all the data manager system user name password @ orcl database name export the database file path name

  • Oracle database import and export command (backup and recovery) are summarized 2010-09-27

    Oracle database import and export command (Backup and Recovery) Toad a good oralce database operations and management tool, using it can easily import and export data tables, users and the entire database. Here mainly to talk about the command line o

  • Oracle create delete users. Role. Tablespace. Import Export Database command line summary 2010-09-19

    Oracle create delete users, roles, table space, import and export database summarizes the command line method: when you create the password database is to amend the system default password to the default system and other systems sysman logged in to m

  • ORACLE database import. Export (backup restore) 2011-02-15

    ORACLE database export command: Export the entire database exp username / pwd @ sid file = d: \ databak.dmp full = y Export data from a table specified exp username / pwd @ sid file = d: \ databak.dmp tables (TableName1, TableName2, ... TableNameN) E

  • Oracle DBA common command summary 2010-04-01

    / / Create a temporary table space create temporary tablespace zfmi_temp tempfile 'D: \ oracle \ oradata \ zfmi \ zfmi_temp.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; / / Tempfile parameter must be / / Create a data t

  • oracle database files in the import \ export (imp / exp command) 2010-04-15

    Oracle data import and export imp / exp is equivalent to oracle data restore and backup. exp command to export data from a remote database server to the local dmp file, imp command to import the dmp file to the distance from the local database server

  • Oracle create delete users. Role. Tablespace. Import and Export .... command summary [transfer] 2010-10-23

    / / Create a temporary table space create temporary tablespace zfmi_temp tempfile 'D: \ oracle \ oradata \ zfmi \ zfmi_temp.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; / / Tempfile parameters must have / / Create a dat

  • Oracle create delete users. Role. Tablespace. Import and Export command summary .... 2010-11-27

    / / Create a temporary table space create temporary tablespace zfmi_temp tempfile 'D: \ oracle \ oradata \ zfmi \ zfmi_temp.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; / / Tempfile parameters must have / / Create a dat

  • Oracle exp / imp command to import and export database backup (transfer) is more 2011-01-05

    Oracle data import and export imp / exp is equivalent to oracle data restore and backup. exp command to export the data from the remote database server to the local dmp file, imp command to import the dmp file to the distance from the local database

  • Oracle create user. Table space. Import and Export command .... 2011-01-12

    / / Create a temporary table space create temporary tablespace test_temp tempfile 'E: \ oracle \ product \ 10.2.0 \ oradata \ testserver \ test_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; / / Create a data table

  • oracle import and export command (turn) 2011-01-14

    One. Export Tool exp 1. It is the operating system, an executable file storage directory / ORACLE_HOME / bin exp Export tool will compress the database backup file into a binary system. You can move between different OS It has three modes: a. User mo

  • Oracle database import and export command (Backup and Recovery) summarized 2010-09-27

    Oracle database import and export command (Backup and Recovery) Toad a good oralce database operations and management tools, you can easily use it to import and export data tables, users and the entire database. Here mainly to talk about using the co