oracle临时表空间存储过程.doc

Oracle11g的驱动的位置 D:appAdministratorproduct11.1.0db_1jdbclib下的ojdbc6.jar文件。临时表空间：用来存放用户的临时数据，临时数据就是在需要时被覆盖，关闭数据库后自动删除，其中不能存放永久性数据。 例如当用户对很多数据行进行排序时，排序在PGA中进行。但是如果排序的数据过多，导致内存不足时，oracle会把要排序的数据分成多份，每次只取一份放在PGA中进行排序，其他的部分都放到临时表空间中，当PGA里的部分排序完成后，把排序好的部分交换到临时表空间中，同时再从临时表空间里取一份没有排序的数据到PGA中进行排序，这样直到所有数据排序完成为止。 临时表空间组是一组由临时表空间组成的组，临时表空间组与临时表空间不能同名。临时表空间组不能显式地创建与删除；当把第一个临时表空间分配给某个临时表空间组时，会自动创建这个临时表空间组；将临时表空间组的最后一个临时表空间删除时，会自动删除临时表空间组。查看临时文件信息： select * from v$tempfile; select * from dba_temp_files; 查看临时表空间组的信息： select * from dba_tablespace_groups; 查看临时表空间的信息： select * from dba_tablespaces; 查找默认的临时表空间： select property_name, property_value from database_properties where property_name = DEFAULT_TEMP_TABLESPACE;创建临时表空间(不属于组)： create temporary tablespace temp2 tempfile D:appAdministratororadataorcltemp2a.dbf size 10m autoextend on; 创建临时表空间(属于组)： create temporary tablespace temp3 tempfile D:appAdministratororadataorcltemp3a.dbf size 10m autoextend on tablespace group temp_grp; 把某个临时表空间关联到组里： alter tablespace temp2 tablespace group temp_grp; 把某个临时表空间移出组： alter tablespace temp2 tablespace group ; 给一个临时表空间增加临时文件： alter tablespace temp2 add tempfile D:appAdministratororadataorcltemp2b.dbf size 20m autoextend on; 设置数据库的默认临时表空间为一个组： alter database default temporary tablespace temp_grp; 对于DML语句来说，只要修改了数据块，Oracle数据库就会将修改前的数据保留下来，保存在undo segment里，而undo segment则保存在undo表空间里。 对于UNDO的管理，有手工Undo管理与自动Undo管理，9i前只能使用手工Undo管理，11g默认使用自动Undo管理。UNDO的作用：提供一致性读、回滚事务、实例恢复。 UNDO表空间：undo segment则保存在undo表空间里，数据库中可以同时存在多个undo表空间，但是在一个时间点上，数据库只能使用一个undo表空间。如果我们将undo_tablespace参数设置为 另外一个undo表空间的名字，则这叫做undo表空间的切换。 undo_retention：该参数以秒为单位，表示当事务提交或回滚以后，该事务所使用的undo块里的数据需要保留多长时间；当保留的时间超过undo_retention所指定的时间以后，该undo块才能够被其他事务覆盖。 rentention garentee：从Oracle 10g开始，我们可以通过为undo表空间设置retention guarantee属性，可以达到的效果是：当undo数据文件不能自动扩展，并且undo块不够用时，直接报错，而不是覆盖那些 inactive而又没有expired的undo块。 查看系统中有哪些UNDO表空间： select tablespace_name, CONTENTS from dba_tablespaces; 查看UNDO表空间的管理方式：手工管理与自动管理 show parameter undo_management; 增加一个回滚表空间Undo Tablespace，叫undotbs2，用来存放回滚段中的数据，记录数据改变的旧值。 create undo tablespace undotbs2 datafile D:appAdministratororadataorclundotbs201.dbf size 10m; 给回滚表空间 undotbs2 增加一个数据文件： alter tablespace undotbs2 add datafile D:appAdministratororadataorclundotbs202.dbf size 10M autoextend on; 查看数据文件以及其所属的表空间： select * from dba_data_files; 查看系统的默认的UNDO表空间： show parameter undo_tablespace 切换UNDO表空间： alter system set undo_tablespace = UNDOTBS2;启用rentention garentee： alter tablespace undotbs1 retention guarantee; 查看了是否启用了rentention garentee ： select tablespace_name, retention from dba_tablespaces; 取消rentention garentee： alter tablespace undotbs1 retention noguarantee;重要视图：v$undostat每十分钟更新一次，记录了UNDO表空间的使用情况。在Oracle Database 11g中，Oracle引入了一个新的特性RMAN UNDO备份优化。在RMAN备份UNDO表空间时，提交事务的UNDO信息将不备份，这个特性随RMAN强制启用。 在一个繁忙的生产环境中，UNDO表空间可能占用几十GB的空间，全部备份显然并不合理，这一特性是许多DBA期待已久的，现在Oracle 11g解决了这个问题。备份是数据库中数据的副本，它可以保护数据在出现意外损失时最大限度的恢复。 Oracle数据库的备份包括以下两种类型： 物理备份是对数据库的操作系统物理文件（如数据文件、控制文件与日志文件等）的备份 逻辑备份是对数据库逻辑组件（如表、视图与存储过程等数据库对象）的备份导致数据库操作中止的故障包括四种类型： 语句故障：在执行 SQL 语句无效可导致语句故障。 用户进程故障：当用户程序出错而无法访问数据库时发生用户进程故障。导致用户进程故障的原因是异常断开连接或异常终止进程 实例故障：当 Oracle 的数据库实例由于硬件或软件问题而无法继续运行时，就会发生实例故障 介质故障：在数据库无法正确读取或写入某个数据库文件时，会发生介质故障 q 传统的导出导入程序用于实施数据库的逻辑备份与恢复q 导出程序将数据库中的对象定义与数据备份到一个操作系统二进制文件中q 导入程序读取二进制导出文件并将对象与数据载入数据库中q 传统的导出导入程序是客户端工具。q 导出与导入实用程序的特点有：q 可以按时间保存表结构与数据q 允许导出指定的表，并重新导入到新的数据库中q 可以把数据库迁移到另外一台异构服务器上q 在两个不同版本的Oracle数据库之间传输数据q 在联机状态下进行备份与恢复q 可以重新组织表的存储结构，减少链接及磁盘碎片用以下三种方法调用导出与导入实用程序：1，交互提示符：以交互的方式提示用户逐个输入参数的值。2，命令行参数：在命令行指定执行程序的参数与参数值。3，参数文件：允许用户将运行参数与参数值存储在参数文件中，以便重复使用参数导出与导入数据库对象的四种模式是：1，数据库模式：导出与导入整个数据库中的所有对象2，表空间模式：导出与导入一个或多个指定的表空间中的所有对象3，用户模式：导出与导入一个用户模式中的所有对象4，表模式：导出与导入一个或多个指定的表或表分区q 导出实用程序有以下常用命令参数：参数说明USERID确定执行导出实用程序的用户名与口令BUFFER确定导出数据时所使用的缓冲区大小，其大小用字节表示FILE指定导出的二进制文件名称，默认的扩展名是.dmpFULL指定是否以全部数据库方式导出，只有授权用户才可使用此参数OWNER要导出的数据库用户列表HELP指定是否显示帮助消息与参数说明ROWS确定是否要导出表中的数据TABLES按表方式导出时，指定需导出的表与分区的名称PARFILE指定传递给导出实用程序的参数文件名TABLESPACES按表空间方式导出时，指定要导出的表空间名按用户方式导出数据exp scott/tigeraccp file=scott_back owner=scott按表方式导出数据exp scott/tigeraccp tables=(emp, dept) file=scott_back_tab按表空间方式导出数据exp system/aptechaccp tablespaces=(users) file=tbs_users使用参数文件导出数据exp system/aptech parfile=C:parameters.txt传统的导入实用程序参数说明USERID指定执行导入的用户名与密码BUFFER指定用来读取数据的缓冲区大小，以字节为单位COMMIT指定是否在每个数组（其大小由BUFFER参数设置）插入后进行提交FILE指定要导入的二进制文件名FROMUSER指定要从导出转储文件中导入的用户模式TOUSER指定要将对象导入的用户名。FROMUSER与TOUSER可以不同FULL指定是否要导入整个导出转储文件TABLES指定要导入的表的列表ROWS指定是否要导入表中的行PARFILE指定传递给导入实用程序的参数文件名，此文件可以包含这里列出的所有参数IGNORE导入时是否忽略遇到的错误，默认为NTABLESPACES按表空间方式导入，列出要导入的表空间名将整个文件导入数据库imp accp/accpaccp file=item_back.dmp ignore=y full=y将scott用户的表导入到martin用户imp system/aptechaccp file=scott_back fromuser=scott touser=martin tables=(emp,dept)使用参数文件导入数据imp system/oracle parfile=C:parameters.txt可传输表空间如果迁移的数据量很大，可以使用可传输表空间：1，检查要传输的表空间是否是自包含的。2，将表空间设置成只读。3，exp进行可传输表空间模式的导出。4，将导出文件与数据文件复制到目标数据库上。5，目标数据库上，imp进行可传输表空间模式的导入。6，目标数据库上，把表空间设置成读写状态。exp/imp的缺点是速度太慢，在大型生产库中尤其明显。从10g开始，oracle设计了数据泵，这是一个服务器端的工具，它为Oracle数据提供高速并行及大数据的迁移。 imp/exp可以在客户端调用，但是expdp/impdp只能在服务端，因为在使用expdp/impdp以前需要在数据库中创建一个 Directory 。在expdp进行导出时，先创建了MT表，并把对象的信息插入到MT表，之后进行导出动作；导出完成后，MT表也导出到转储文件中；导出任务完成后、或者删除了导出任务后，MT表自动删除；如果导出任务异常终止，MT表仍然保留。 expdp也具有四种模式：表、用户、可传输表空间、全库。部分的exp中的参数仍然可用，有的不能使用，如index。2，directory：供转储文件与日志文件使用的目录对象。3，job_name：指定的任务的名称。4，content：指定要导出的数据, 其中有效关键字值为: (ALL), DATA_ONLY 与 METADATA_ONLY，当设置content为ALL 时,将导出对象定义及其所有数据； DATA_ONLY时,只导出对象数据；为METADATA_ONLY时,只导出对象定义 。5，reuse_dumpfiles：如果导出文件已经存在，是否覆盖。6，compression：压缩导出文件。7，estimate：指定估算被导出表所占用磁盘空间分方法.默认值是BLOCKS estimate_only：是否只估算导出占用的磁盘空间，而不进行真正的导出，默认是N。9，exclude：用于指定执行操作时要排除对象类型或相关对象，用法：EXCLUDE=object_type:name_clause ,. 10，include：用于指定执行操作时要包含的对象类型或相关对象，用法：INCLUDE=object_type:name_clause ,.11，query：导出符合条件的行。 12，attch：连接到现有的作业，可以用在中断导出任务后重新启动导出任务。 11g中的impdp的重要参数1，content：指定要加载的数据, 其中有效关键字值为: (ALL), DATA_ONLY 与 METADATA_ONLY，当设置content为ALL 时,将加载对象定义及其所有数据； DATA_ONLY时,只加载对象数据；为METADATA_ONLY时,只加载对象定义 。2，estimate：估算所占用磁盘空间分方法.默认值是BLOCKS3，remap_schema：用于将对象从一个用户下导入到另一个用户下。 4，remap_tablespace：用于将对象从一个表空间下导入到另一个表空间下。 5，remap_datafile：用于在不同文件系统的平台间，切换数据文件路径。 数据的装载 SQL*LOADER 外部表 导入/导出 q SQL*LOADER是一个ORACLE工具，能够将数据从外部数据文件装载到数据库中。q 运行SQL*LOADER的命令是sqlldr。q Sqlldr的两种使用方式：q 1. 只使用一个控制文件，在这个控制文件中包含数据2. 使用一个控制文件(作为模板) 与一个数据文件q 一般采用第二种方式，数据文件可以是 CSV 文件、txt文件或者以其他分割符分隔的。SQL*LOADER中控制文件的格式：options(skip=2)load datainfile 1.txtinto table empnew insert(a char terminated by ,b char terminated by ,c date yyyy-mm-dd nullif (c=NULL)说明：操作类型 可用以下中的一值：1) insert -为缺省方式，在数据装载开始时要求表为空2) append -在表中追加新记录3) replace -删除旧记录(用 delete from table 语句)，替换成新装载的记录4) truncate -删除旧记录(用 truncate table 语句)，替换成新装载的记录 q 外部表中的数据不装入数据库中，数据库中只存储外部表的定义信息，实际的数据位于操作系统的平面文件中，但是，可以在数据库中，像访问正常表那样，通过select语句来访问操作系统中的平面文件中所含有的数据。q 外部表是只读的。q 可以使用SQL，PL/SQL与JAVA访问外部表。q 外表表分为2种：使用数据泵引擎生成的外部表、根据文本文件创建的外部表。create table dept_ext( new_deptno , new_dname, new_loc) organization external ( type oracle_datapump default directory my_dir location (dept1.dmp,dept2.dmp) ) parallel as select deptno, dname, loc from dept;create table emp_ext_txt( employee_id number, first_name varchar2(20), last_name varchar2(20) )organization external( type oracle_loader default directory my_dir access parameters ( records delimited by newline fields terminated by , ) location (emp.txt);闪回查询从9i引入，可以按照时间点或者SCN向前查询，获取修改 前的数据。 闪回查询依赖于回滚段中存储的数据前镜像，通过设置undo_retention参数设置前镜像的保留时间。 查询的语法： select as of scn | timestamp 对于9i的闪回查询进行增强，Oracle10g提供了闪回版本查询，因为9i的闪回查询仅仅能够得到过去某个时间点上的数据，但是无法反映出一段时间内数据表中数据的变化，10g的闪回版本查询可以对时间段内数据表的不同版本进行查询。 闪回版本查询的语法： select from versions between 其中，select后面可以选择伪列，来获得事务的开始、结束时间、SCN号、ID号等。Oracle10g可以进行基于闪回版本查询的恢复，就是闪回事务查询。 从flashback_transaction_query中查询引起数据变化的事务，与撤销事务的SQL语句，就是查询 operation与 undo_sql 列。Oracle10g的闪回表是把表里的数据回退到以前的某个时刻或者SCN上。 特点：可以在线操作；自动恢复相关的属性，包括索引、触发器等。 前提：对表启用行迁移。 语法： flashback table to timestamp | scn 注意：sys的表不能闪回。Oracle10g的闪回删除：可以恢复一个被drop的对象，因为进行drop时，Oracle先把它放到回收站中。 回收站内的信息：show recyclebin 闪回删除：flashback table to before drop; 彻底删除：drop table purge; 清空回收站: purge recyclebin; 通过参数recyclebin来启用、禁用回收站。如果数据库出现逻辑错误，无法采用闪回表的方式进行恢复，或者数据库的结构发生了改变，可以通过闪回数据库的方式把整个数据库回退到出错前的时间点上。步骤： 1，配置数据库为归档模式 2，配置闪回恢复区 3，配置闪回保留时间 4，启用数据库闪回 alter database flashback on; 5，进行闪回数据库 flashback database to timestamp |scn 在分布式的海量数据环境中，信息查询的速度问题显得尤为重要。传统的查询方式，即根据用户的要求，每次都重新的进入基表或视图查询，所需的时间太长。例如移动通信行业，即使客户需要查询很少的信息，也可能会花费很多时间，可能在30分钟左右，如果数据库主机稍有繁忙，这个时间会更长，客户难以忍受。为了解决这种问题，ORACLE中设计了物化视图（又称为MV）。物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求与的汇总表。物化视图存储基于远程表的数据，也可以称为快照。 物化视图可以查询表，视图与其它的物化视图。 通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。 对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图与连接视图。 物化视图有很多方面与索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加与删除物化视图不会影响应用程序中SQL语句的正确性与有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。 1. 物化视图与表一样可以直接进行查询。物化视图可以基于分区表，物化视图本身也可以分区。 2. 数据仓库中的物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。在数据仓库中，还经常使用查询重写（query rewrite）机制，这样不需要修改原有的查询语句，Oracle会自动选择合适的物化视图进行查询，完全对应用透明。3. 除了在数据仓库中使用，物化视图还用于复制、移动计算等方面。1. 如果创建基于主键的物化视图，则必须具有访问主表、访问主表的日志、create MATERIALIZED VIEW这三个权限。2. 如果创建基于rowid的物化视图，则必须具有访问主表、create MATERIALIZED VIEW这两个权限。查询重写（Query Rewrite）：包括ENABLE QUERY RE