Oracle数据库逻辑备份与恢复（1） |
您所在的位置：网站首页 › Oracle导出数据库命令 › Oracle数据库逻辑备份与恢复（1） |
|
Oracle数据库逻辑备份与恢复（1）——exp、imp命令
逻辑备份是指从数据库中导出数据并写入一个输出文件。逻辑备份只能用来对数据库进行逻辑恢复，即数据导入，而不能按数据库原来的存储特征进行物理恢复。逻辑备份可以使用exp命令来完成，逻辑导入可以使用imp命令来完成。exp和imp命令的用法如下： 一、使用exp命令进行数据备份该命令的常用参数如下： 1、USERID：指定用户名和口令，格式为： exp userid=用户名/口令 exp 用户名/口令说明：关键字userid可省略。 2、FULL：导出整个数据库，只有拥有exp_full_database角色的用户或者特权用户如sys，system等才能进行全库导出。格式如下： exp system/system full=y3、BUFFER：指定数据缓冲区大小，单位为字节，不能写成buffer=1m的形式。格式如下： exp ... buffer=n #n为字节数4、OWNER：需要导出的用户，如果需要导出多个用户可以使用逗号隔开。格式如下： exp system/system ... owner=scott,Jack,...说明：普通用户只能导出自己的数据，不能导出其它用户的数据。只有system用户才能导出其它用户的数据。 5、FILE：指定输出文件。格式如下： exp ... file=/home/oracle/bak/emp20.dmp #文件的扩展名为.dmp6、TABLES：指定需要导出的表，如果需要导出多个表可以使用逗号隔开。格式如下： exp ... tables=emp,dept,...7、ROWS：指定导出后的表是否带数据（取值为N或Y，默认为Y）。格式如下： exp ... rows=N8、QUERY：导出表时指定条件以导出部分数据。格式如下： exp ... query=\'where ...\' 二、exp命令举例 1、导出表数据 （1）导出一张表中的数据 [oracle@wgx bak]$ exp scott/tiger tables=emp file=/home/oracle/bak/emp.dmp buffer=1000000 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 07:46:42 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table EMP 17 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. （2）导出多张表中的数据 [oracle@wgx bak]$ exp scott/tiger tables=emp,dept file=/home/oracle/bak/emp_dept.dmp buffer=1000000 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 07:49:57 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table EMP 17 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table DEPT 5 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. （3）导出一张表中满足条件的部分数据导出emp表中的10部门数据： [oracle@wgx bak]$ exp scott/tiger tables=emp query=\'where deptno=10\' file=/home/oracle/bak/emp10.dmp buffer=1000000 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 07:51:27 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table EMP 3 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.导出emp表中姓名以字母M开头的员工信息： #注意：query参数中的双引号和单引号需要使用\转义。 [oracle@wgx bak]$ exp scott/tiger tables=emp query=\"where ename like \'%M%\'\" file=/home/oracle/bak/emp_ename.dmp buffer=1000000 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 08:14:00 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table EMP 5 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. （4）导出表结构 [oracle@wgx bak]$ exp scott/tiger tables=emp,dept,salgrade rows=N file=/home/oracle/bak/scott_stru.dmp Export: Release 11.2.0.1.0 - Production on Sat Apr 4 08:33:07 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) Note: table data (rows) will not be exported About to export specified tables via Conventional Path ... . . exporting table EMP EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table DEPT EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table SALGRADE EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. 2、导出用户数据 （1）scott用户导出自己的数据 [oracle@wgx bak]$ exp scott/tiger owner=scott file=/home/oracle/bak/scott.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 09:54:14 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SCOTT . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user SCOTT About to export SCOTT's objects ... .................. Export terminated successfully with warnings. （2）system用户导出其它用户的数据 [oracle@wgx bak]$ exp system/system owner=scott,jack file=/home/oracle/bak/user.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 10:00:27 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user SCOTT . exporting foreign function library names for user JACK . exporting PUBLIC type synonyms . exporting private type synonyms . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user SCOTT . exporting object type definitions for user JACK About to export SCOTT's objects ... ..................... About to export JACK's objects ... ................... Export terminated successfully with warnings. 3、导出整个数据库中的数据 [oracle@wgx bak]$ exp system/system full=y file=/home/oracle/bak/full.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 12:18:13 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export the entire database ... ............. 三、使用imp命令导入数据imp命令的参数和exp命令大部分是相同的，USERID、TABLES、OWNER、FILE、BUFFER等参数的含义和exp命令完全相同。下面是imp命令独有的参数： 1、IGNORE：在恢复数据的过程中，当导入某个表时，如果该表已经存在，则根据ignore参数的设置来决定如何操作。 若ignore=y，不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，比如主键约束，唯一索引等，则出错的记录不会插入，但合法的记录会添加到表中。 若ignore=n，不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续导入下一个表。 2、INDEXES：在恢复数据的过程中，若indexes=n，则表上的索引不会被恢复，但是对 LOB 索引, OID索引和 主键索引等系统自动生成的索引将无条件恢复。 3、FROMUSER，TOUSER：这两个参数可以组合使用，也可以分开使用。可以实现将源用户的对象数据，导入到目标用户schema下面的功能。格式如下： （1）导入一个或一组指定用户所属的全部对象 imp system/manager fromuser=jack file=... log=.... $imp system/manager fromuser=scott,jack file=.... log=....（2）将一个或一组指定用户所属的全部对象导入到另一个用户下 imp scott/tiger fromuser=scott touser=jack file=.... imp system/manager fromuser=scott,jack touser=mark file=.... log=....（3）导入一个或一组指定用户所属的部分对象 imp scott/tiger tables=emp,dept fromuser=scott touser=jack file=.... 四、imp命令举例 1、数据情况（1）scott用户的表 SQL> show user; USER is "SCOTT" SQL> select * from tab; TNAME TABTYPE CLUSTERID ---------------------------------------------------------------------------------- DEPT TABLE E01 TABLE E02 TABLE EMP TABLE SALGRADE TABLE（2）jack用户的表 SQL> show user USER is "JACK" SQL> select * from tab; TNAME TABTYPE CLUSTERID -------------------------------------------------------------------------------------- DEPT TABLE EMP TABLE SALGRADE TABLE T1 TABLE 2、备份scott用户的e01和e02表并进行恢复（1）查看e01和e02表的数据 SQL> select count(*) from e01; COUNT(*) ---------- 229376 SQL> select * from e02; EMPNO ENAME JOB SAL COMM DEPTNO ---------- ------------------------------ --------------------------- ---------- -- 7369 SMITH CLERK 800 20 7499 ALLEN SALESMAN 1600 300 30 7521 WARD SALESMAN 1250 500 30 7566 JONES MANAGER 2975 20 7654 MARTIN SALESMAN 1250 1400 30 7698 BLAKE MANAGER 2850 30 7782 CLARK MANAGER 2450 10 7788 SCOTT ANALYST 3000 20 7839 KING PRESIDENT 5000 10 7844 TURNER SALESMAN 1500 0 30 7876 ADAMS CLERK 1100 20 7900 JAMES CLERK 950 30 7902 FORD ANALYST 3000 20 7934 MILLER CLERK 1300 10 14 rows selected.（2）导出e01和e02表的数据 [oracle@wgx bak]$ exp scott/tiger tables=e01,e02 file=/home/oracle/bak/e01_02.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 12:44:15 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table E01 229376 rows exported EXP-00091: Exporting questionable statistics. . . exporting table E02 14 rows exported EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.（3）删除e01和e02表 SQL> drop table e01 purge; Table dropped. SQL> drop table e02 purge; Table dropped. SQL> select * from tab; TNAME TABTYPE CLUSTERID ----------------------------------------------------------------------------------------- DEPT TABLE EMP TABLE SALGRADE TABLE（4）导入e01和e02表 [oracle@wgx bak]$ imp scott/tiger tables=e01,e02 file=/home/oracle/bak/e01_02.dmp buffer=1048576 Import: Release 11.2.0.1.0 - Production on Sat Apr 4 12:51:34 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing table "E01" 229376 rows imported . . importing table "E02" 14 rows imported Import terminated successfully without warnings.（5）查看e01和e02表的数据 SQL> select count(*) from e01; COUNT(*) ---------- 229376 SQL> select * from e02; EMPNO ENAME JOB SAL COMM DEPTNO ---------- ------------------------------ --------------------------- ---------- ---- 7369 SMITH CLERK 800 20 7499 ALLEN SALESMAN 1600 300 30 7521 WARD SALESMAN 1250 500 30 7566 JONES MANAGER 2975 20 7654 MARTIN SALESMAN 1250 1400 30 7698 BLAKE MANAGER 2850 30 7782 CLARK MANAGER 2450 10 7788 SCOTT ANALYST 3000 20 7839 KING PRESIDENT 5000 10 7844 TURNER SALESMAN 1500 0 30 7876 ADAMS CLERK 1100 20 7900 JAMES CLERK 950 30 7902 FORD ANALYST 3000 20 7934 MILLER CLERK 1300 10 14 rows selected. 2、通过数据导入追加数据（1）导出e02表中的10部门员工 [oracle@wgx bak]$ exp scott/tiger tables=e02 query=\'where deptno=10\' file=/home/oracle/bak/e02_10.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 12:55:58 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table E02 3 rows exported EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings.（2）把e02表清空，然后插入记录 SQL> select * from e02; EMPNO ENAME JOB SAL COMM DEPTNO ---------- ------------------------------ --------------------------- ---------- 1111 ZHANG SALESMAN 1500 200 40 2222 WANG SALESMAN 1200 500 40 3333 LIU SALESMAN 1400 300 40（3）把导出的10部门的员工追加到e02表中 [oracle@wgx bak]$ imp scott/tiger tables=e02 ignore=y file=/home/oracle/bak/e02_10.dmp buffer=1048576 Import: Release 11.2.0.1.0 - Production on Sat Apr 4 13:01:18 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing table "E02" 3 rows imported Import terminated successfully without warnings.（4）查看e02表的数据 SQL> select * from e02; EMPNO ENAME JOB SAL COMM DEPTNO ---------- ------------------------------ --------------------------- ---------- 1111 ZHANG SALESMAN 1500 200 40 2222 WANG SALESMAN 1200 500 40 3333 LIU SALESMAN 1400 300 40 7782 CLARK MANAGER 2450 10 7839 KING PRESIDENT 5000 10 7934 MILLER CLERK 1300 10 6 rows selected. 3、导出并恢复一个用户下的所有数据（1）导出scott用户下的所有数据 [oracle@wgx bak]$ exp scott/tiger owner=scott file=/home/oracle/bak/scott.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 13:04:00 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ........ Export terminated successfully with warnings.（2）删除scott用户及其数据 SQL> drop user scott cascade; User dropped.（3）重新创建scott用户并授予权限 SQL> grant connect,resource to scott identified by tiger; Grant succeeded.（4）导入scott用户数据 [oracle@wgx bak]$ imp scott/tiger full=y file=/home/oracle/bak/scott.dmp buffer=1048576 Import: Release 11.2.0.1.0 - Production on Sat Apr 4 13:09:02 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SCOTT's objects into SCOTT . . importing table "DEPT" 4 rows imported . . importing table "E01" 229376 rows imported . . importing table "E02" 6 rows imported . . importing table "EMP" 14 rows imported . . importing table "SALGRADE" 5 rows imported About to enable constraints... Import terminated successfully without warnings.（5）查看scott用户的数据 SQL> show user; USER is "SCOTT" SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------------ ---------- DEPT TABLE E01 TABLE E02 TABLE EMP TABLE SALGRADE TABLE 4、导出并恢复多个用户的数据（此操作必须有system用户完成）（1）导出scott用户和jack用户的数据 [oracle@wgx bak]$ exp system/system owner=scott,jack file=/home/oracle/bak/scott_jack.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 13:13:30 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) About to export specified users ... ...................... About to export SCOTT's objects ... ..................... . about to export JACK's tables via Conventional Path ... ..................... Export terminated successfully with warnings.（2）删除scott用户和jack用户及其数据 SQL> drop user jack cascade; User dropped. SQL> drop user scott cascade; User dropped.（3）创建scott用户和jack用户并授予权限 SQL> create user jack identified by jack; User created. SQL> create user scott identified by tiger; User created. SQL> grant connect,resource to jack; Grant succeeded. SQL> grant connect,resource to scott; Grant succeeded.（4）导入scott用户和jack用户的数据 [oracle@wgx bak]$ imp system/system full=y file=/home/oracle/bak/scott_jack.dmp buffer=1048576 Import: Release 11.2.0.1.0 - Production on Sat Apr 4 13:19:50 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SYSTEM's objects into SYSTEM . importing SCOTT's objects into SCOTT . importing JACK's objects into JACK . importing SCOTT's objects into SCOTT . . importing table "DEPT" 4 rows imported . . importing table "E01" 229376 rows imported . . importing table "E02" 6 rows imported . . importing table "EMP" 14 rows imported . . importing table "SALGRADE" 5 rows imported . importing JACK's objects into JACK . . importing table "DEPT" 4 rows imported . . importing table "EMP" 14 rows imported . . importing table "SALGRADE" 5 rows imported . . importing table "T1" 3 rows imported . importing SCOTT's objects into SCOTT . importing JACK's objects into JACK . importing SCOTT's objects into SCOTT . importing JACK's objects into JACK . importing SCOTT's objects into SCOTT About to enable constraints... . importing JACK's objects into JACK Import terminated successfully without warnings.（5）查看scott用户和jack用户的数据 SQL> show user USER is "SCOTT" SQL> select * from tab; TNAME TABTYPE CLUSTERID -------------------------------------------------------------------------------- DEPT TABLE E01 TABLE E02 TABLE EMP TABLE SALGRADE TABLE SQL> show user USER is "JACK" SQL> select * from tab; TNAME TABTYPE CLUSTERID ---------------------------------------------------------- ----------------- DEPT TABLE EMP TABLE SALGRADE TABLE T1 TABLE 5、把一个用户的所有数据导入到另一个用户（1）导出scott用户的数据 [oracle@wgx bak]$ exp scott/tiger owner=scott file=/home/oracle/bak/scott.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 13:25:44 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) ............. Export terminated successfully with warnings.（2）删除用户jack及其数据 SQL> drop user jack cascade; User dropped.（3）创建用户jiack并授予权限 SQL> create user jack identified by jack; User created. SQL> grant connect,resource to jack; Grant succeeded.（4）把scott用户的数据导入到jack用户 [oracle@wgx bak]$ imp system/system fromuser=scott touser=jack file=/home/oracle/bak/scott.dmp buffer=1048576 Import: Release 11.2.0.1.0 - Production on Sat Apr 4 13:29:36 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path Warning: the objects were exported by SCOTT, not by you import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SCOTT's objects into JACK . . importing table "DEPT" 4 rows imported . . importing table "E01" 229376 rows imported . . importing table "E02" 6 rows imported . . importing table "EMP" 14 rows imported . . importing table "SALGRADE" 5 rows imported About to enable constraints... Import terminated successfully without warnings.（5）查看jack用户的数据 SQL> show user USER is "JACK" SQL> select * from tab; TNAME TABTYPE CLUSTERID ---------------------------------------------------------------------------------------- DEPT TABLE E01 TABLE E02 TABLE EMP TABLE SALGRADE TABLE（6）查看scott用户的数据 SQL> show user USER is "SCOTT" SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------------------------------- DEPT TABLE E01 TABLE E02 TABLE EMP TABLE SALGRADE TABLE可以看到jack用户的数据和scott用户完全相同。 6、把一个用户的部分数据导入到另一个用户（1）导出scott用户的数据 [oracle@wgx bak]$ exp scott/tiger owner=scott file=/home/oracle/bak/scott.dmp buffer=1048576 Export: Release 11.2.0.1.0 - Production on Sat Apr 4 13:25:44 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) ............. Export terminated successfully with warnings.（2）删除用户jack及其数据 SQL> drop user jack cascade; User dropped.（3）创建用户jiack并授予权限 SQL> create user jack identified by jack; User created. SQL> grant connect,resource to jack; Grant succeeded.（4）把scott用户的e01和e02表导入到jack用户 [oracle@wgx bak]$ imp system/system fromuser=scott touser=jack tables=e01,e02 file=/home/oracle/bak/scott.dmp buffer=1048576 Import: Release 11.2.0.1.0 - Production on Sat Apr 4 13:35:34 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path Warning: the objects were exported by SCOTT, not by you import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SCOTT's objects into JACK . . importing table "E01" 229376 rows imported . . importing table "E02" 6 rows imported Import terminated successfully without warnings.（5）查看jack用户的数据 SQL> show user; USER is "JACK" SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------------------------------ E01 TABLE E02 TABLE（6）查看scott用户的数据 SQL> show user USER is "SCOTT" SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------------------------------------------------------- DEPT TABLE E01 TABLE E02 TABLE EMP TABLE SALGRADE TABLE 7、恢复整个数据库中的数据 [oracle@wgx bak]$ imp system/system full=y file=/home/oracle/bak/full.dmp buffer=1048576 Import: Release 11.2.0.1.0 - Production on Sat Apr 4 13:39:09 2020 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |