Oracle導(dǎo)入另外user的table

本實(shí)驗(yàn)是關(guān)于data 的exp 一個(gè)用戶scott 下面imp到另外一個(gè)用戶 中。 ---------在instance = sheng 的導(dǎo)出scott.emp [Oracle@aora

本實(shí)驗(yàn)是關(guān)于data 的exp 一個(gè)用戶scott 下面imp到另外一個(gè)用戶 中。

---------
在instance = sheng 的導(dǎo)出scott.emp

[Oracle@aoracle ~]$ exp scott/oracle tables=emp file='/u01/rman/file01.txt';

Export: Release 10.2.0.1.0 - Production on Fri Dec 16 09:56:22 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

下面imp到另外一個(gè)用戶的table 中。

下面建立另外一個(gè)user：
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS2
SYSAUX
USERS
TEMP

SQL> create tablespace TB_DBS datafile '/u02/sheng/tbsdbs01.dbf' size 20m;

Tablespace created.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS2
SYSAUX
USERS
TEMP
TB_DBS

6 rows selected.

SQL> create user dbs identified by dbs default tablespace tb_dbs;

User created.

SQL> select username,account_status from dba_users where username like 'DBS%';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
DBSNMP OPEN
DBS OPEN

下面連一下：
SQL> CONN DBS/DBS
ERROR:
ORA-01045: user DBS lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> grant create session ，resource to dbs;

Grant succeeded.

SQL> conn dbs/dbs
Connected.
SQL> select * from tab;

no rows selected

SQL>

好，，下面把scott 用戶下的emp table imp 到dbs 用戶下：

[oracle@aoracle ~]$ imp system/manager file='/u01/rman/file01.txt' fromuser=scott touser=dbs;

Import: Release 10.2.0.1.0 - Production on Fri Dec 16 10:54:29 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into DBS
. . importing table "EMP" 14 rows imported
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN"
"CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""
Import terminated successfully with warnings.

SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> show user;
USER is "DBS"
SQL>

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com