数据的移动

最新推荐文章于 2023-05-03 15:16:51 发布

congwei2855

最新推荐文章于 2023-05-03 15:16:51 发布

阅读量216

点赞数

学习内容

1	导入和导出的工作原理导出程序从oracle数据库中抽取数据，之后再将这些数据存在二进制格式的操作系统文件中。这种格式的数据文件只有导入程序能读入，导入程序将其中的数据装入oracle数据库。
2	利用导入导出工具可以完成哪些工作 1.重建表 2.在不同的数据库用户之间移动数据 3.在不同的计算机之间，不同的数据库之间和不同的版本的oracle服务器之间移动数据 4.在不同的数据库之间移动数据表空间 5.将表的定义存入二进制的操作系统文件以防止用户操作系统失误造成数据的丢失。 6.为某一数据对象或整个数据库建立历史档案，因为数据库的结构和数据都是随着商业需求不停的发生变化的。
3	什么是逻辑备份逻辑备份包括数据库对象、表空间和整个数据库的逻辑备份逻辑备份是不能对数据库进行完全恢复的，即数据的丢失是在所难免的。
4	逻辑备份与物理备份的区别逻辑备份的优势： 1、与任何一种物理备份和恢复方法相比，它都十分简单 2、它可以防止用户错误，如用户意外地删除或截断了某个表。在这种情况下，如果没有逻辑备份要想恢复此表就必须进行不完全恢复。 3、在某个表上进行了很多错误的DML操作并已经提交。如果没有逻辑备份而又想恢复此表则要进行不完全恢复。 4、在某个表逻辑崩溃的情况下，如果没有逻辑备份就想恢复此表的话，则也要进行不完全恢复。
5	导入导出程序的应用实例 1、首先以scott用户登录，使显示输出更加清晰； SQL> alter user scott account unlock identified by oracle; User altered. SQL> connect scott/oracle Connected. SQL> set line 120 SQL> set pagesize 30 2、使用DDL命令创建两个用作逻辑备份实验的表，他们分别是emp_dump和dept_dump SQL> create table emp_dump 2 as select * from emp; Table created. SQL> create table dept_dump 2 as select * from dept; Table created. 3、验证刚刚创建的表是否创建成功。 SQL> select count() from emp_dump; COUNT() ---------- 14 SQL> select count() from dept_dump; COUNT() ---------- 4 4、当确认了这两个表已经创建成功的时候还应创建一个存放逻辑备份文件的目录。 SQL> conn / as sysdba Connected. SQL> create or replace directory junxiao as '/home/oracle/datadump'; Directory created. SQL> grant read,write on directory junxiao to scott; Grant succeeded. 5、然后对刚刚建立的两个表做一个逻辑备份 [oracle@prod1 Desktop]$ exp scott/oracle file=emp_dump.dmp log=emp_dump.log tables=emp_dump; Export: Release 11.2.0.4.0 - Production on Sat Jun 18 23:16:12 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII 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_DUMP 14 rows exported Export terminated successfully without warnings. [oracle@prod1 Desktop]$ exp scott/oracle file=dept_dump.dmp log=dept_dump.log tables=dept_dump; Export: Release 11.2.0.4.0 - Production on Sat Jun 18 23:17:02 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII 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 DEPT_DUMP 4 rows exported Export terminated successfully without warnings. 6、做完逻辑备份之后查看一下所做的逻辑备份是否存在 [oracle@prod1 Desktop]$ ls dept_dump.dmp dept_dump.log emp_dump.dmp emp_dump.log 7、然后用SQL语句查询一下刚刚建立的表emp_dump的数据；----------------scott用户下，因为我刚刚弄错了用户，所以做错了实验，以为导入导出后原表不存在了，现在明白了导出不影响原数据，还有一定要仔细，认真，搞清楚在什么用户下操作。 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. 8、下面更新一条语句。 SQL> update emp_dump set job='CEO'; 14 rows updated. SQL> commit; Commit complete. 9、接下来查询一下刚刚所作的修改是否已经成功完成。 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CEO 7902 17-DEC-80 800 20 7499 ALLEN CEO 7698 20-FEB-81 1600 300 30 7521 WARD CEO 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES CEO 7839 02-APR-81 2975 20 7654 MARTIN CEO 7698 28-SEP-81 1250 1400 30 7698 BLAKE CEO 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK CEO 7839 09-JUN-81 2450 10 7788 SCOTT CEO 7566 19-APR-87 3000 20 7839 KING CEO 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER CEO 7698 08-SEP-81 1500 0 30 7876 ADAMS CEO 7788 23-MAY-87 1100 20 7900 JAMES CEO 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD CEO 7566 03-DEC-81 3000 20 7934 MILLER CEO 7782 23-JAN-82 1300 10 14 rows selected. 10、现在使用DDL语句删除刚刚创建的两个表 SQL> drop table emp_dump; Table dropped. SQL> drop table dept_dump; Table dropped. 11、验证上述的两个表是否删除成功 SQL> select * from emp_dump; select * from emp_dump * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from dept_dump; select * from dept_dump * ERROR at line 1: ORA-00942: table or view does not exist 12、当确定这两个表已经不存在的时候，现在我们使用导入命令将刚刚所删除的表导入进来（逻辑恢复） [oracle@prod1 Desktop]$ imp scott/oracle file=emp_dump.dmp log=emp_dump.log tables=emp_dump; Import: Release 11.2.0.4.0 - Production on Sat Jun 18 23:51:25 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 US7ASCII 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 "EMP_DUMP" 14 rows imported Import terminated successfully without warnings. [oracle@prod1 Desktop]$ imp scott/oracle file=dept_dump.dmp log=dept_dump.log tables=dept_dump; Import: Release 11.2.0.4.0 - Production on Sat Jun 18 23:52:59 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 US7ASCII 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 "DEPT_DUMP" 4 rows imported Import terminated successfully without warnings. 13、返回到scott用户下，验证是否导入成功 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> select * from dept_dump; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 由此可以看出导入成功；但是又可以发现它导入的是导出时的数据，因为所有员工的职位仍然是以前的，不是更新后的数据。
6	数据泵的工作原理数据泵是oracle10g引入的一个工具，他不但包括了导入导出工具的功能，而且还进行了不少的扩充和加强。速度快，操作也更加的安全。
7	怎样使用数据泵进行数据的导入和导出（逻辑备份和恢复） 1.创建目录-----------------------不创建目录，使用oralce数据库系统自动创建的一个叫 DATA_PUMP_DIR的目录对象，该目录就是expdp和impdp应用程序默认的工作目录。 SQL> set line 120 SQL> col owner for a6 SQL> col DIRECTORY_NAME for a20 SQL> col DIRECTORY_PATH for a65 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------ -------------------- ----------------------------------------------------------------- SYS SUBDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002 /Sep SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/ SYS JUNXIAO /home/oracle/datadump SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/ SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/ SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/ SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/11.2.0/db_1/ccr/hosts/prod1/state R OWNER DIRECTORY_NAME DIRECTORY_PATH ------ -------------------- ----------------------------------------------------------------- SYS DATA_PUMP_DIR /u01/app/oracle/admin/prod1/dpdump/ SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/11.2.0/db_1/ccr/state R2 10 rows selected. 2、使用DDL语句将目录DATA_PUMP_DIR 的读和写的权限授予scott用户。 SQL> grant read,write on directory DATA_PUMP_DIR to scott; Grant succeeded. 3、然后切换到scott用户下，查询获取emp_dump表的全部信息-------------------按job和sal的升息排列 SQL> select * from emp_dump 2 order by job,sal; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 14 rows selected. 4、使用expdp命令导出数据 [oracle@prod1 Desktop]$ expdp scott/oracle directory=DATA_PUMP_DIR dumpfile=junxiao.dmp log=junxiao.log tables=emp_dump query="'where empno=7499'"; Export: Release 11.2.0.4.0 - Production on Sun Jun 19 00:21:10 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "log=junxiao.log" Location: Command Line, Replaced with: "logfile=junxiao.log" Legacy Mode has set reuse_dumpfiles=true parameter. Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/****** directory=DATA_PUMP_DIR dumpfile=junxiao.dmp logfile=junxiao.log tables=emp_dump query='where empno=7499' reuse_dumpfiles=true Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."EMP_DUMP" 8.046 KB 1 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded **************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/prod1/dpdump/junxiao.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jun 19 00:21:19 2016 elapsed 0 00:00:06 directory=DATA_PUMP_DIR-----------存放导出文件的目录为DATA_PUMP_DIR dumpfile=junxiao.dmp-----------------导出操作系统文件的名为junxiao.dmp tables=emp_dump-------------------------导出的表为emp_dump query="'where empno=7499'"---------------只输出表中满足某些特定条件的数据的子集。 5、现在删除刚刚创建的那两个表 SQL> drop table emp_dump; Table dropped. SQL> drop table dept_dump; Table dropped. SQL> commit;----------------这个地方也可以设置为自动提交，比如更新完一条语句，它会自动的提交，而不用手动commit； Commit complete. 6、验证此表是否已经删除成功 SQL> select * from emp_dump; select * from emp_dump * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from dept_dump; select * from dept_dump * ERROR at line 1: ORA-00942: table or view does not exist 7、删除之后，现在我们可以利用刚刚所作的逻辑备份来进行逻辑恢复（数据泵） [oracle@prod1 Desktop]$ impdp scott/oracle directory=DATA_PUMP_DIR dumpfile=junxiao.dmp log=junxiao.log tables=emp_dump query="'where empno=7499'"; Import: Release 11.2.0.4.0 - Production on Sun Jun 19 00:32:58 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "log=junxiao.log" Location: Command Line, Replaced with: "logfile=junxiao.log" Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=DATA_PUMP_DIR dumpfile=junxiao.dmp logfile=junxiao.log tables=emp_dump query='where empno=7499' Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."EMP_DUMP" 8.046 KB 1 rows Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jun 19 00:33:05 2016 elapsed 0 00:00:04 8、现在验证一下是否恢复成功 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
8	怎么样以最简单的方式将oracle的数据传给其他软件（系统）
9	什么是oracle的外表外部表是指不存在与数据库中的表。通过oracle提供描述外部表的元数据，我们可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样进行访问。外部表是对数据库表的延伸。外部表的特性：位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。 ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。创建外部表 create table emp_new ( emp_id number(4), ename varchar2(15), job varchar2(12) , mgr_id number(4) , hiredate date, salary number(8), comm number(8), dept_id number(2) ) organization external------------------------该表是一个外部表 ( type oracle_loader------------------------所使用的驱动程序是oracle_loder，这是oralce数据库系统自带的 default directory dat_dir-----------------默认目录为dat_dir，即存放数据文件的目录 access parameters------------------------后面括号中定义的是访问参数 ( records delimited by newline fields terminated by ',' ) location ('1.dat','2.dat') ); 验证外部表 SQL> select * from emp_new; EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID ---------- --------------- ------------ ---------- --------- ---------- ---------- ---------- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30 .................................................................... 外部表不能执行DML SQL> delete from emp_new; delete from emp_new * ERROR at line 1: ORA-30657: operation not supported on external organized table 查看外部表信息 SQL>select owner,table_name,type_name,default_directory_name,access_parameters 2 from dba_external_tables;

问题：

为某一数据对象或整个数据库建立历史档案-----------------------------不太理解这句话的意思

因为数据库的结构和数据都是随着商业需求不停的发生变化的。

没能做出来将oracle数据移动到其他系统中，比如没能移动到excels 表中。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30606702/viewspace-2120523/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30606702/viewspace-2120523/

1	导入和导出的工作原理导出程序从oracle数据库中抽取数据，之后再将这些数据存在二进制格式的操作系统文件中。这种格式的数据文件只有导入程序能读入，导入程序将其中的数据装入oracle数据库。
2	利用导入导出工具可以完成哪些工作 1.重建表 2.在不同的数据库用户之间移动数据 3.在不同的计算机之间，不同的数据库之间和不同的版本的oracle服务器之间移动数据 4.在不同的数据库之间移动数据表空间 5.将表的定义存入二进制的操作系统文件以防止用户操作系统失误造成数据的丢失。 6.为某一数据对象或整个数据库建立历史档案，因为数据库的结构和数据都是随着商业需求不停的发生变化的。
3	什么是逻辑备份逻辑备份包括数据库对象、表空间和整个数据库的逻辑备份逻辑备份是不能对数据库进行完全恢复的，即数据的丢失是在所难免的。
4	逻辑备份与物理备份的区别逻辑备份的优势： 1、与任何一种物理备份和恢复方法相比，它都十分简单 2、它可以防止用户错误，如用户意外地删除或截断了某个表。在这种情况下，如果没有逻辑备份要想恢复此表就必须进行不完全恢复。 3、在某个表上进行了很多错误的DML操作并已经提交。如果没有逻辑备份而又想恢复此表则要进行不完全恢复。 4、在某个表逻辑崩溃的情况下，如果没有逻辑备份就想恢复此表的话，则也要进行不完全恢复。
5	导入导出程序的应用实例 1、首先以scott用户登录，使显示输出更加清晰； SQL> alter user scott account unlock identified by oracle; User altered. SQL> connect scott/oracle Connected. SQL> set line 120 SQL> set pagesize 30 2、使用DDL命令创建两个用作逻辑备份实验的表，他们分别是emp_dump和dept_dump SQL> create table emp_dump 2 as select * from emp; Table created. SQL> create table dept_dump 2 as select * from dept; Table created. 3、验证刚刚创建的表是否创建成功。 SQL> select count() from emp_dump; COUNT() ---------- 14 SQL> select count() from dept_dump; COUNT() ---------- 4 4、当确认了这两个表已经创建成功的时候还应创建一个存放逻辑备份文件的目录。 SQL> conn / as sysdba Connected. SQL> create or replace directory junxiao as '/home/oracle/datadump'; Directory created. SQL> grant read,write on directory junxiao to scott; Grant succeeded. 5、然后对刚刚建立的两个表做一个逻辑备份 [oracle@prod1 Desktop]$ exp scott/oracle file=emp_dump.dmp log=emp_dump.log tables=emp_dump; Export: Release 11.2.0.4.0 - Production on Sat Jun 18 23:16:12 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII 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_DUMP 14 rows exported Export terminated successfully without warnings. [oracle@prod1 Desktop]$ exp scott/oracle file=dept_dump.dmp log=dept_dump.log tables=dept_dump; Export: Release 11.2.0.4.0 - Production on Sat Jun 18 23:17:02 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII 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 DEPT_DUMP 4 rows exported Export terminated successfully without warnings. 6、做完逻辑备份之后查看一下所做的逻辑备份是否存在 [oracle@prod1 Desktop]$ ls dept_dump.dmp dept_dump.log emp_dump.dmp emp_dump.log 7、然后用SQL语句查询一下刚刚建立的表emp_dump的数据；----------------scott用户下，因为我刚刚弄错了用户，所以做错了实验，以为导入导出后原表不存在了，现在明白了导出不影响原数据，还有一定要仔细，认真，搞清楚在什么用户下操作。 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. 8、下面更新一条语句。 SQL> update emp_dump set job='CEO'; 14 rows updated. SQL> commit; Commit complete. 9、接下来查询一下刚刚所作的修改是否已经成功完成。 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CEO 7902 17-DEC-80 800 20 7499 ALLEN CEO 7698 20-FEB-81 1600 300 30 7521 WARD CEO 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES CEO 7839 02-APR-81 2975 20 7654 MARTIN CEO 7698 28-SEP-81 1250 1400 30 7698 BLAKE CEO 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK CEO 7839 09-JUN-81 2450 10 7788 SCOTT CEO 7566 19-APR-87 3000 20 7839 KING CEO 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER CEO 7698 08-SEP-81 1500 0 30 7876 ADAMS CEO 7788 23-MAY-87 1100 20 7900 JAMES CEO 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD CEO 7566 03-DEC-81 3000 20 7934 MILLER CEO 7782 23-JAN-82 1300 10 14 rows selected. 10、现在使用DDL语句删除刚刚创建的两个表 SQL> drop table emp_dump; Table dropped. SQL> drop table dept_dump; Table dropped. 11、验证上述的两个表是否删除成功 SQL> select * from emp_dump; select * from emp_dump * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from dept_dump; select * from dept_dump * ERROR at line 1: ORA-00942: table or view does not exist 12、当确定这两个表已经不存在的时候，现在我们使用导入命令将刚刚所删除的表导入进来（逻辑恢复） [oracle@prod1 Desktop]$ imp scott/oracle file=emp_dump.dmp log=emp_dump.log tables=emp_dump; Import: Release 11.2.0.4.0 - Production on Sat Jun 18 23:51:25 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 US7ASCII 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 "EMP_DUMP" 14 rows imported Import terminated successfully without warnings. [oracle@prod1 Desktop]$ imp scott/oracle file=dept_dump.dmp log=dept_dump.log tables=dept_dump; Import: Release 11.2.0.4.0 - Production on Sat Jun 18 23:52:59 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 US7ASCII 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 "DEPT_DUMP" 4 rows imported Import terminated successfully without warnings. 13、返回到scott用户下，验证是否导入成功 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> select * from dept_dump; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 由此可以看出导入成功；但是又可以发现它导入的是导出时的数据，因为所有员工的职位仍然是以前的，不是更新后的数据。
6	数据泵的工作原理数据泵是oracle10g引入的一个工具，他不但包括了导入导出工具的功能，而且还进行了不少的扩充和加强。速度快，操作也更加的安全。
7	怎样使用数据泵进行数据的导入和导出（逻辑备份和恢复） 1.创建目录-----------------------不创建目录，使用oralce数据库系统自动创建的一个叫 DATA_PUMP_DIR的目录对象，该目录就是expdp和impdp应用程序默认的工作目录。 SQL> set line 120 SQL> col owner for a6 SQL> col DIRECTORY_NAME for a20 SQL> col DIRECTORY_PATH for a65 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------ -------------------- ----------------------------------------------------------------- SYS SUBDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002 /Sep SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/ SYS JUNXIAO /home/oracle/datadump SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/ SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/ SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/ SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/11.2.0/db_1/ccr/hosts/prod1/state R OWNER DIRECTORY_NAME DIRECTORY_PATH ------ -------------------- ----------------------------------------------------------------- SYS DATA_PUMP_DIR /u01/app/oracle/admin/prod1/dpdump/ SYS ORACLE_OCM_CONFIG_DI /u01/app/oracle/product/11.2.0/db_1/ccr/state R2 10 rows selected. 2、使用DDL语句将目录DATA_PUMP_DIR 的读和写的权限授予scott用户。 SQL> grant read,write on directory DATA_PUMP_DIR to scott; Grant succeeded. 3、然后切换到scott用户下，查询获取emp_dump表的全部信息-------------------按job和sal的升息排列 SQL> select * from emp_dump 2 order by job,sal; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 14 rows selected. 4、使用expdp命令导出数据 [oracle@prod1 Desktop]$ expdp scott/oracle directory=DATA_PUMP_DIR dumpfile=junxiao.dmp log=junxiao.log tables=emp_dump query="'where empno=7499'"; Export: Release 11.2.0.4.0 - Production on Sun Jun 19 00:21:10 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "log=junxiao.log" Location: Command Line, Replaced with: "logfile=junxiao.log" Legacy Mode has set reuse_dumpfiles=true parameter. Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/****** directory=DATA_PUMP_DIR dumpfile=junxiao.dmp logfile=junxiao.log tables=emp_dump query='where empno=7499' reuse_dumpfiles=true Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."EMP_DUMP" 8.046 KB 1 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded **************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/prod1/dpdump/junxiao.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jun 19 00:21:19 2016 elapsed 0 00:00:06 directory=DATA_PUMP_DIR-----------存放导出文件的目录为DATA_PUMP_DIR dumpfile=junxiao.dmp-----------------导出操作系统文件的名为junxiao.dmp tables=emp_dump-------------------------导出的表为emp_dump query="'where empno=7499'"---------------只输出表中满足某些特定条件的数据的子集。 5、现在删除刚刚创建的那两个表 SQL> drop table emp_dump; Table dropped. SQL> drop table dept_dump; Table dropped. SQL> commit;----------------这个地方也可以设置为自动提交，比如更新完一条语句，它会自动的提交，而不用手动commit； Commit complete. 6、验证此表是否已经删除成功 SQL> select * from emp_dump; select * from emp_dump * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from dept_dump; select * from dept_dump * ERROR at line 1: ORA-00942: table or view does not exist 7、删除之后，现在我们可以利用刚刚所作的逻辑备份来进行逻辑恢复（数据泵） [oracle@prod1 Desktop]$ impdp scott/oracle directory=DATA_PUMP_DIR dumpfile=junxiao.dmp log=junxiao.log tables=emp_dump query="'where empno=7499'"; Import: Release 11.2.0.4.0 - Production on Sun Jun 19 00:32:58 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "log=junxiao.log" Location: Command Line, Replaced with: "logfile=junxiao.log" Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=DATA_PUMP_DIR dumpfile=junxiao.dmp logfile=junxiao.log tables=emp_dump query='where empno=7499' Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."EMP_DUMP" 8.046 KB 1 rows Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jun 19 00:33:05 2016 elapsed 0 00:00:04 8、现在验证一下是否恢复成功 SQL> select * from emp_dump; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
8	怎么样以最简单的方式将oracle的数据传给其他软件（系统）
9	什么是oracle的外表外部表是指不存在与数据库中的表。通过oracle提供描述外部表的元数据，我们可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样进行访问。外部表是对数据库表的延伸。外部表的特性：位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。 ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。创建外部表 create table emp_new ( emp_id number(4), ename varchar2(15), job varchar2(12) , mgr_id number(4) , hiredate date, salary number(8), comm number(8), dept_id number(2) ) organization external------------------------该表是一个外部表 ( type oracle_loader------------------------所使用的驱动程序是oracle_loder，这是oralce数据库系统自带的 default directory dat_dir-----------------默认目录为dat_dir，即存放数据文件的目录 access parameters------------------------后面括号中定义的是访问参数 ( records delimited by newline fields terminated by ',' ) location ('1.dat','2.dat') ); 验证外部表 SQL> select * from emp_new; EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID ---------- --------------- ------------ ---------- --------- ---------- ---------- ---------- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30 .................................................................... 外部表不能执行DML SQL> delete from emp_new; delete from emp_new * ERROR at line 1: ORA-30657: operation not supported on external organized table 查看外部表信息 SQL>select owner,table_name,type_name,default_directory_name,access_parameters 2 from dba_external_tables;