oracle EXP & IMP 维护 + 字符集的处理

来源：互联网发布：c语言程序停止运行编辑：程序博客网时间：2024/05/26 15:58

This time I will introduce the Oracle import and export utilities:
After Completing this lesson, You should be able to do the following:

1: Describe the use of the export and Import utilities.
2: Describe Export and Import concepts and structres.
3: Perform simple Export and Import operations.
4: List guidelines for using Export and Import

Usually, the Export and Import file is *.dmp file which is usually binary file, comparing the
other sqload tool which will import or expor the text file.

--------
Export and Import useful instruction:
1: Archive historical data.
  (if you want to archvie or backup some important file,you can use the import and export to do this),
  But the weekness is that this is only for the small database.
2: Save table definitions to protect them from user failure.
     (This action can to export the table structrue, not for all data file).
3: move Data between machine and data platform.
     (The data can be perfomed on any platform: Aix ,linux, or windows)
4: Transport tablespaces between databases.

---------

Before you begin using Export, be sure you take care of the following items:

Step1: Run the catexp.sql or catalog.sql script
(If you create the database manually, be sure to execute the above scripts)
(If you use DBCA to create the database, you have not execute the above scripts)
Step2: Ensure there is sufficient disk or tape storage to write the export file.

Step3: Verify that you have the required access privileges.

Attention:
   For above Step3, I will intruce details:
   To use Export, you must have the CREATE SESSION privilege on a database ,which belong
   yourself, if you want to export or import any other user table or schema,you must have
   EXP_FILL_DATABASE privilege, this role is granted to all DBAs.

   To use Import, you should also have the privilege of "create session" ,"IMP_FULL_DATABASE".

----
Methods to invoke Exp or Imp
1: command line
2: parfile contain all command.

Example:
    create user u1 identified by u1 default tablespace users;
    grant connect, resource to u1
    conn u1/u1
    create table u1t1(no number);
    insert into u1t1 values(1);
    commit;

C:\Documents and Settings\db2admin>exp u1/u1@primary file=u1.dmp owner=u1 log=u1.log

Export: Release 10.2.0.1.0 - Production on 星期五 2月 10 19:47:05 2012

ok, let us look at the database characterset (no differece)
otherwise, below line : 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器正使用AL32UTF8 字符集（可能的字符集转换）

use command "type " to review:
C:\Documents and Settings\db2admin>type u1.log

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 U1 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 U1 的对象类型定义
即将导出 U1 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 U1 的表通过常规路径...
. . 正在导出表 U1T1导出了 1 行
. 正在导出同义词
. 正在导出视图
. 正在导出存储过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
成功终止导出, 没有出现警告。

C:\Documents and Settings\db2admin>

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

Export Modes：
1：Table Mode
Table definitions,
Table data (all or selected rows)
Owner's table grants
Owner's table indexes
Table constraints

2：User Mode
Table definitions,
Table data
Owner's table grants
Owner's table indexes
Table constraints

3：Tablespace Mode
Table definitions
Grants
Indexes
Table constraints
Triggers

example:
   exp hr/hr tables=employees,departments rows=y file=exp1.dmp
     (This command is to issue export two tables: employee, department)

   exp system/manager owner=hr direct=y file=expdat.dmp
     (This command is to issue to export all the schema of user: hr)

   exp \ 'username/password as sysdba\' TRANSPORT_TABLESPACE=Y TABLESPACES=ts_emp log=ts_emp.log
     (This command is to issue transport the tablespace: ts_emp)

Notes:

If you want to export or Import the database, below 3steps is important:

Step1: Check the database nls_characterset :

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ -------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK

Step2: set Check the client characterset :
  Method1:
  regedit> HKEY_LOCAL_MACHINE -- SOFTWARE -- ORACLE --NLS_LANG (SIMPLIFIED CHINESE_CHINA.ZHS16GBK)(this should be changed)
  method2:
  cmd > set nls_lang=AMERICAN_AMERICA.ZHS16GBK

Step3: If you want to import one dmp file, and you do not know the file contains which characterset:
    you can use below command:
    imp hr/hr@instance show=y file=8.dmp
    (this time, you will see which characterset the file contains)


-------
Please see below example:

Export session in full database mode:
exp system/passwd full=y file=dba.dmp grants=y rows=y

Export session in user Mode:
exp scott/passwd File=scott.dmp owner=scott grants=y rows=y

Export session in Table Mode:
exp system/passwd file=t1.dmp tables(scott.emp,blake.dept) grants=y indexes=y
exp blake/peper file=blake.dmp tables=(dept,manager) rows=y compress=y
exp system/passwd file=misc.dmp tables=(scott.%P%,black.%S%)