《Oracle数据库实用教程(第二版)》第10章数据管理.ppt

Oracle数据库实用教程 (第二版),唐远新 曲卫平 李晓峰等编著 中国水利水电出版社 2009.6,第10章 数据管理,装载器是一个将其他数据源数据转换为Oracle数据库数据的工具 SQL*Loader使用的文件 控制文件 输入文件 参数文件 日志文件 从现行系统将数据转换到Oracle数据库分两步进行 使用某个软件将现有数据备份 使用SQL*Loader从备份文件中将数据装载到Oracle数据库,10.1 SQL*Loader,控制文件 是一个文本文件，描述加载哪些数据、这些数据的格式以及在加载过程中使用哪些用于加载操作的参数 控制文件由三部分组成，包括： (1) 全局选项、装载行数、跳过的记录数等 (2) INFILE子句指定的输入数据 (3) 数据特性说明 以“关键字=值”的格式向SQL*Loader提供信息 输入文件 是数据装载过程中要读取的源数据文件，又称为数据文件 一般为文本文件 各个记录放在输入文件中的单独行上,1.基本概念,日志文件 创建三种类型的日志文件 常规日志文件 错误日志 丢弃日志 定长记录 各记录有相同数量的字段，同一字段长度相同 变长记录 各记录有相同数量的字段，同一字段长度不同 应使用某一格式定界符分隔各个数据字段 字符数字数据一般使用双引号括起来,1.基本概念,SQL*Loader装载类型 (1) 对表初始装入，用INSERT语句 (2) 对表追加新记录，用APPEND语句 (3) 对装入记录作更新，用REPLACE语句 映射数据格式 一组语法规则用于说明装载的逻辑记录列表、装载的列及字段、数据类型、装载条件以及将哪些列设置为null或0，指定数据分隔符 SQL*Loader命令参数 在系统命令提示符后输入SQLLDR，按回车后显示所有参数信息 SQL*Loader命令语法 SQLLDR keyword=value ,keyword=value,.,2.SQL*Loader的语法及参数,SQL*Loader命令参数,2.SQL*Loader的语法及参数,userid control log bad data discard discardmax skip load,errors rows bindsize silent direct parfile parallel readsize,命令行参数可以由位置或关键字指定 所有按位置传递的参数应该出现在由关键字指定的参数之前 SQLLDR scott/tiger control=cf logfile=log SQLLDR scott/tiger control=cf log (不可以这样指定参数),OPTIONS (SKIP=n, LOAD=n, PARALLEL=TRUE|FALSE, ) LOAD DATA INFILE 数据文件 BADFILE 坏数据文件 DISCARDFILE 丢弃数据文件 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY “ APPEND | INSERT | REPLACE FIELDS delimiter ( column RECNUM|CONSTANT value|SEQUENCE(n|MAX|COUNT,increment)| POSITION ( start :end | * + integer ) datatype TERMINATED BY WHITESPACE | X character OPTIONALLY ENCLOSE BY X character NULLIF condition DEFAULTIF condition , ) INTO TABLE 表名 BEGINDATA ;,3.控制文件,column：定义列 列的取值 RECNUM：取逻辑记录数 CONSTANT：赋予常数 SEQUENCE：取序列值。可以从指定序号开始，格式为SEQUENCE( n | MAX |COUNT，increment)； POSITION给出列在逻辑记录中的位置，格式为POSITION(start:end| *+integer) start表示开始位置，*表示本字段在前一字段的结束位置之后立刻开始，+表示从前一字段开始位置向后的字符数所代表的位置开始,3.控制文件,例10.1 装载数据，源数据带逗号和双引号分隔符。 EXA_10_01.SQL,例10.2 使用SQL*Loader装载数据至emp表，只装载前5行数据。 EXA_10_02.SQL,逻辑备份是指用实用工具EXPORT将数据库部分或全部对象的结构及其数据导出，并存储到OS文件中的过程，该过程也被称为导出 用于恢复被意外删除或截断的数据库对象 在Oracle数据库之间移动数据 或者在将数据库升级为新版本时传输数据 启动EXPORT程序 EXP 用户名/口令主机字符串 输入EXP -help或EXP help= y显示EXPORT实用程序的可用选项和关键字,10.2 传统数据导入/导出工具,EXPORT常用参数,1.EXPort实用程序,三种方式将参数传递给EXPORT实用程序 (1) 命令行方式 (2) 参数文件方式 (3) GUI交互方式,以命令行参数方式运行EXPORT实用程序 EXP用户名/口令主机字符串 以参数文件方式运行EXPORT实用程序 先将参数编辑好，存入一个文本文件中 EXP 用户名/口令主机字符串 PARFILE = 参数文件名,1.EXPort实用程序,例10.4 用参数文件导出SCOTT用户的dept和emp表中记录到文件dept_emp.dmp中。 EXA_10_04.SQL,例10.5 用参数文件导出整个数据库结构信息(不导出数据行和索引)。 EXA_10_05.SQL,逻辑恢复是指数据库对象被意外删除或截断之后，使用实用工具IMPORT将逻辑备份文件中的对象结构以及数据导入到数据库中的过程，该过程也被称为导入 启动IMPORT程序 IMP 用户名/口令主机字符串 输入IMP -help或IMP help= y显示IMPORT实用程序的可用选项和关键字 可导入整个文件内容到数据库 可将一个用户的对象导入到另一个用户方案中 以命令行参数方式运行IMP实用程序 IMP用户名/口令主机字符串 ,2.IMPort实用程序,以参数文件方式运行IMP实用程序 IMP 用户名/口令主机字符串 PARFILE=参数文件名,2.IMPort实用程序,例10.7 用参数文件导入SCOTT的方案对象到HR方案中。 EXA_10_07.SQL,可以用其他程序设计语言编写的程序调用EXP/IMP实现数据库的逻辑备份和恢复,EXPDP可选择导出表、方案、表空间或整个数据库 提供了一组参数供用户定制灵活的数据导出方案 启动EXPDP实用程序 EXPDP 用户名/口令主机字符串 查看EXPDP可用选项和关键字 EXPDP help EXPDP help= y 两种方式将参数传递给EXPDP实用程序 命令行方式 参数文件方式 EXPDP转储文件只能存放在DIRECTORY对象对应的OS目录中,10.3 数据泵,EXPDP常用参数,1.EXPDP实用程序,ATTACH COMPRESSION CONTENT DIRECTORY DUMPFILE EXCLUDE FILESIZE FLASHBACK_SCN FLASHBACK_TIME FULL HELP INCLUDE JOB_NAME,LOGFILE NETWORK_LINK NOLOGFILE PARFILE QUERY SCHEMAS TABLES TABLESPACES TRANSPORT_FULL_CHECK TRANSPORT_TABLESPACES KILL_JOB START_JOB STOP_JOB,以命令行参数方式运行EXPDP实用程序 EXPDP 用户名/口令主机字符串 DIRECTORY=dir parameter1=value1 以参数文件方式运行EXPDP实用程序 EXPDP 用户名/口令主机字符串 PARFILE = 参数文件名 导出表空间和整个数据库需具备的角色权限 EXP_FULL_DATABASE DBA,1.EXPDP实用程序,例10.8 用命令行启动EXPDP实用程序导出SCOTT用户方案对象 EXA_10_08.SQL,例10.9 用参数文件导出SCOTT用户的dept和emp表中记录到文件dept_emp2.dmp中。 EXA_10_09.SQL,IMPDP可选择导入表、方案、表空间和整个数据库 启动IMPDP实用程序 IMPDP 用户名/口令主机字符串 查看IMPDP可用选项和关键字 IMPDP help IMPDP help= y 两种方式将参数传递给IMPDP实用程序 命令行方式 参数文件方式,2.IMPDP实用程序,IMPDP常用参数,2.IMPDP实用程序,例10.10 用参数文件导入SCOTT的方案对象到HR方案中。 EXA_10_10.SQL,ATTACH CONTENT DIRECTORY DUMPFILE EXCLUDE FLASHBACK_SCN FLASHBACK_TIME FULL HELP INCLUDE JOB_NAME,LOGFILE NETWORK_LINK NOLOGFILE PARFILE QUERY REMAP_DATAFILE REMAP_SCHEMA REMAP_TABLESPACE REUSE_DATAFILES SCHEMAS SKIP_UNUSABLE_INDEXES,SQLFILE STREAMS_CONFIGURATION TABLE_EXISTS_ACTION TABLES TABLESPACES TRANSFORM TRANSPORT_DATAFILES TRANSPORT_FULL_CHECK TRANSPORT_TABLESPACES KILL_JOB START_JOB STOP_JOB,闪回技术可以将Oracle数据库恢复到以前的时间点 Oracle 10g中闪回操作 (1) 查询闪回 (2) 表闪回 (3) 删除闪回 (4) 数据库闪回 闪回查询 Oracle 闪回能够查询过去某些时间点的数据 闪回查询的语法 SELECT select_list FROM table AS OF TIMESTAMP TO_TIMESTAMP(timepoint,format) WHERE clause ;,10.4 闪回技术,例10.11 删除雇员表emp中记录，用闪回查询恢复该表记录。EXA_10_11.SQL,提供了查看行级数据库变化的方法 支持以特定时间间隔检索所有不同版本的行 只要是在撤销段中现有的数据，都可以用于闪回版本查询 初始化参数undo_retention 撤销表空间RETENTION_GUARANTEE选项 闪回版本查询语法 SELECT select_list FROM table VERSIONS BETWEEN start_timestamp AND end_timestamp; 指定的时间段一定在当前时刻和undo_retention指定的时刻之间，否则会返回错误,2.闪回版本查询,闪回版本查询的伪列 versions_starttime versions_endtime versions_xid versions_operation versions_startscn versions_endscn 常量 minvalue：可闪回的最早修改时间 maxvalue：最晚可闪回时间,2.闪回版本查询,例：用参数文件导出SCOTT用户的dept和emp表中记录到文件dept_emp2.dmp中。 EXA_10_P274_versions.SQL,提供查看事务级数据库变化的方法 需要在SYS用户下执行闪回事务查询操作,3.闪回事务查询,例10.12 删除SCOTT雇员表中7934记录，然后查看更改该行的事务标识符，并查看该事务SQL语句的UNDO SQL。 EXA_10_12.SQL,设置参数 (1) DB_RECOVERY_FILE_DEST (2) DB_RECOVERY_FILE_DEST_SIZE (3) DB_FLASHBACK_RETENTION_TARGET 闪回数据库语法 FLASHBACK STANDBY | DATABASE database TO SCN | TIMESTAMP expr | TO BEFORE SCN | TIMESTAMP expr ; 打开数据库闪回功能 ALTER DATABASE FLASHBACK ON 关闭数据库闪回功能 ALTER DATABASE FLASHBACK OFF,4.闪回数据库,例10.13 闪回数据库orcl。 EXA_10_13.SQL,表闪回可以恢复表，取消对表进行的修改 条件 具有FLASHBACK ANY TABLE系统特权 表上SELECT、INSERT、DELETE和UPDATE权限 该表允许行迁移 初始化参数UNDO_MANAGEMENT设置为AUTO 撤销数据保持时间UNDO_RETENTION初始化参数 闪回表语法如下： FLASHBACK TABLE table TO TIMESTAMP timestamp; FLASHBACK TABLE table TO TIMESTAMP SCN scn; FLASHBACK TABLE table TO TIMESTAMP timestamp ENABLE TRIGGERS;,5.闪回表,例：闪回表emp。 EXA_10_P276_FLASHBACK_TABLE