IMP Example Import of Selected Tables for a Specific User

最新推荐文章于 2021-05-17 15:00:01 发布

cnk45946

最新推荐文章于 2021-05-17 15:00:01 发布

阅读量82

点赞数

文章标签：数据库操作系统

一描述

将官档中imp实验一一转为自己的内容.
使用全库导出文件,全库导出文件中包含的scott下的dept与emp表导入到目的库scott_test用户下.
全库导出基础环境参考文档:
http://space.itpub.net/?uid-11780477-action-viewspace-itemid-741460

二操作环境

OS info

windows server2003 32bit

DB info

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  9.0.1.1.1       Production
Oracle9i Enterprise Edition                             9.0.1.1.1       Production
PL/SQL                                                  9.0.1.1.1       Production
TNS for 32-bit Windows:                                 9.0.1.1.0       Production

SQL> col value for a50
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
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              9.0.1.1.1

20 rows selected.

SQL>

other

脚本文件存储目录:C:\oracle\product\9.2.0\db_1\db_script.

三过程设计

3.1 imp参数文件内容imp_parameter_full.dat
userid=scott
FILE=dba.dmp
SHOW=n
IGNORE=n
GRANTS=y
FROMUSER=scott
TOUSER=scott_test
TABLES=(dept,emp)

3.2 imp命令
> imp PARFILE=imp_parameter_full.dat

3.3 imp日志信息

四详细步骤操作

4.1 创建用户,权限授予

C:\oracle\product\9.2.0\db_1\db_script>set nls_lang=american_america.zhs16gbk

C:\oracle\product\9.2.0\db_1\db_script>set ORACLE_SID=testc

C:\oracle\product\9.2.0\db_1\db_script>sqlplus "/ as sysdba

SQL*Plus: Release 9.0.1.0.1 - Production on Thu Aug 23 00:20:18 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> grant connect,resource to scott_test identified by tiger;

Grant succeeded.

SQL> grant imp_full_database to scott;

Grant succeeded.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\oracle\product\9.2.0\db_1\db_script>

4.2 创建imp_parameter_full.dat文件(略)

userid=scott
FILE=dba.dmp
SHOW=n
IGNORE=n
GRANTS=y
FROMUSER=scott
TOUSER=scott_test
TABLES=(dept,emp)

4.3 执行imp操作

C:\oracle\product\9.2.0\db_1\db_script>imp parfile=imp_parameter_full.dat

Import: Release 9.0.1.1.1 - Production on Thu Aug 23 00:25:27 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

Export file created by EXPORT:V09.00.01 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT_TEST
. . importing table "DEPT" 6 rows imported
. . importing table "EMP" 6 rows imported
Import terminated successfully without warnings.

C:\oracle\product\9.2.0\db_1\db_script>

五个人总结

使用scott用户从全库备份中导入某个用户下的某个表到其他用户时,需要有imp_full_database角色的授予,否则会报下面的错误:

C:\oracle\product\9.2.0\db_1\db_script>imp parfile=imp_parameter_full.dat

Import: Release 9.0.1.1.1 - Production on Thu Aug 23 00:22:03 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

Export file created by EXPORT:V09.00.01 via direct path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

C:\oracle\product\9.2.0\db_1\db_script>

(注:虽然报错信息说只有DBA能够import,但只要授予imp_full_database权限即可.)