我不会使用TOAD,而是在命令提示符下,在命令提示符下使用数据泵导出和导入。下面是一个例子。
SQL> show user
USER is "SYS"
SQL> create directory my_dir as 'c:\temp';
Directory created.
SQL> grant read, write on directory my_dir to mdp;
Grant succeeded.
What are roles and privileges to give a user in order to perform CRUD(on Oracle 12)
),创建一些对象:
SQL> connect mdp/pdm@xe
Connected.
SQL> create table test (id number);
Table created.
SQL> create view v_test as select * From test;
View created.
SQL> insert into test
2 select level from dual
3 connect by level <= 5;
5 rows created.
SQL> commit;
Commit complete.
C:\>expdp mdp/pdm@xe directory=my_dir file=mdp.dmp log=mdp_exp.log
Export: Release 11.2.0.2.0 - Production on Ned Vel 3 18:00:54 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=mdp.dmp" Location: Command Line, Replaced with: "dumpfile=mdp.dmp"
Legacy Mode Parameter: "log=mdp_exp.log" Location: Command Line, Replaced with: "logfile=mdp_exp.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "MDP"."SYS_EXPORT_SCHEMA_01": mdp/********@xe directory=my_dir dumpfile=mdp.dmp logfile=mdp_exp.log reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported "MDP"."TEST" 5.031 KB 5 rows
Master table "MDP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MDP.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\MDP.DMP
Job "MDP"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:01:24
C:\>
不错;导出成功。
现在,使用SYS Oracle user,我将删除MDP user;使用层叠是因为用户有一些对象也必须删除。我为什么要掉下来?模拟您的情况,即将DMP文件移动到另一台不包含该用户的计算机/数据库。
C:\>sqlplus sys@xe as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Ned Vel 3 18:02:32 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> drop user mdp cascade;
User dropped.
SQL>
假装我们现在在另一台电脑上;首先,我要创建MDP用户:
SQL> show user
USER is "SYS"
SQL> create user mdp identified by test
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;
User created.
SQL> grant create session to mdp;
Grant succeeded.
SQL>
回到操作系统命令提示符,因为我现在要导入DMP文件的内容。
C:\>impdp system@xe directory=my_dir dumpfile=mdp.dmp logfile=mdp_imp.log
Import: Release 11.2.0.2.0 - Production on Ned Vel 3 18:09:47 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@xe directory=my_dir dumpfile=mdp.dmp logfile=mdp_imp.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MDP"."TEST" 5.031 KB 5 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 18:09:51
C:\>
C:\>sqlplus mdp/test@xe
SQL*Plus: Release 11.2.0.2.0 Production on Ned Vel 3 18:12:52 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> select * from v_test;
ID
----------
1
2
3
4
5
SQL>
别忘了我在另一个主题中说过的话:只授予MPD所需的特权。目前,它只能创建会话和使用现有对象,但不能
创造
什么都行。
差不多就是这样。不要太依赖GUI(你知道它是怎样的;
以鬼为生,以鬼为死
),单击
在这里
那里
