oracle临时表空间的存储过程.docx
- 文档编号:6469431
- 上传时间:2023-01-06
- 格式:DOCX
- 页数:15
- 大小:24.38KB
oracle临时表空间的存储过程.docx
《oracle临时表空间的存储过程.docx》由会员分享,可在线阅读,更多相关《oracle临时表空间的存储过程.docx(15页珍藏版)》请在冰豆网上搜索。
oracle临时表空间的存储过程
Oracle11g的驱动的位置
D:
\app\Administrator\product\11.1.0\db_1\jdbc\lib\
下的ojdbc6.jar文件。
临时表空间:
用来存放用户的临时数据,临时数据就是
在需要时被覆盖,关闭数据库后自动删除,其中不能存放永
久性数据。
例如当用户对很多数据行进行排序时,排序在PGA中进
行。
但是如果排序的数据过多,导致内存不足时,oracle会
把要排序的数据分成多份,每次只取一份放在PGA中进行排
序,其他的部分都放到临时表空间中,当PGA里的部分排序
完成后,把排序好的部分交换到临时表空间中,同时再从临
时表空间里取一份没有排序的数据到PGA中进行排序,这样
直到所有数据排序完成为止。
临时表空间组是一组由临时表空间组成的组,临时表空
间组和临时表空间不能同名。
临时表空间组不能显式地创建
和删除;当把第一个临时表空间分配给某个临时表空间组
时,会自动创建这个临时表空间组;将临时表空间组的最后
一个临时表空间删除时,会自动删除临时表空间组。
查看临时文件信息:
select*fromv$tempfile;
select*fromdba_temp_files;
查看临时表空间组的信息:
select*fromdba_tablespace_groups;
查看临时表空间的信息:
select*fromdba_tablespaces;
查找默认的临时表空间:
selectproperty_name,property_valuefrom
database_propertieswhereproperty_name=
'DEFAULT_TEMP_TABLESPACE';
创建临时表空间(不属于组):
createtemporarytablespacetemp2tempfile
'D:
\app\Administrator\oradata\orcl\temp2a.dbf'size10m
autoextendon;
创建临时表空间(属于组):
createtemporarytablespacetemp3tempfile
‘D:
\app\Administrator\oradata\orcl\temp3a.dbf’size10m
autoextendontablespacegrouptemp_grp;
把某个临时表空间关联到组里:
altertablespacetemp2tablespacegrouptemp_grp;
把某个临时表空间移出组:
altertablespacetemp2tablespacegroup'';
给一个临时表空间增加临时文件:
altertablespacetemp2addtempfile
'D:
\app\Administrator\oradata\orcl\temp2b.dbf'size20m
autoextendon;
设置数据库的默认临时表空间为一个组:
alterdatabasedefaulttemporarytablespacetemp_grp;
对于DML语句来说,只要修改了数据块,Oracle数据库
就会将修改前的数据保留下来,保存在undosegment里,而
undosegment则保存在undo表空间里。
对于UNDO的管理,有手工Undo管理和自动Undo管理,
9i前只能使用手工Undo管理,11g默认使用自动Undo管理。
UNDO的作用:
提供一致性读、回滚事务、实例恢复。
UNDO表空间:
undosegment则保存在undo表空间里,
数据库中可以同时存在多个undo表空间,但是在一个时
间点上,数据库只能使用一个undo表空间。
如果我们将
undo_tablespace参数设置为另外一个undo表空间的名字,
则这叫做undo表空间的切换。
undo_retention:
该参数以秒为单位,表示当事务提交
或回滚以后,该事务所使用的undo块里的数据需要保留多长
时间;当保留的时间超过undo_retention所指定的时间以
后,该undo块才能够被其他事务覆盖。
rententiongarentee:
从Oracle10g开始,我们可以通
过为undo表空间设置retentionguarantee属性,可以达到的
效果是:
当undo数据文件不能自动扩展,并且undo块不够
用时,直接报错,而不是覆盖那些inactive而又没有expired
的undo块。
查看系统中有哪些UNDO表空间:
selecttablespace_name,CONTENTSfrom
dba_tablespaces;
查看UNDO表空间的管理方式:
手工管理和自动管理
showparameterundo_management;
增加一个回滚表空间UndoTablespace,叫undotbs2,用
来存放回滚段中的数据,记录数据改变的旧值。
createundotablespaceundotbs2datafile
'D:
\app\Administrator\oradata\orcl\undotbs201.dbf'size10m;
给回滚表空间undotbs2增加一个数据文件:
altertablespaceundotbs2adddatafile
‘D:
\app\Administrator\oradata\orcl\undotbs202.dbf’size10M
autoextendon;
查看数据文件以及其所属的表空间:
select*fromdba_data_files;
查看系统的默认的UNDO表空间:
showparameterundo_tablespace
切换UNDO表空间:
altersystemsetundo_tablespace=UNDOTBS2;
启用rententiongarentee:
altertablespaceundotbs1retentionguarantee;
查看了是否启用了rententiongarentee:
selecttablespace_name,retentionfrom
dba_tablespaces;
取消rententiongarentee:
altertablespaceundotbs1retentionnoguarantee;
重要视图:
v$undostat每十分钟更新一次,记录了UNDO
表空间的使用情况。
在OracleDatabase11g中,Oracle引入了一个新的特性
RMANUNDO备份优化。
在RMAN备份UNDO表空间时,提
交事务的UNDO信息将不备份,这个特性随RMAN强制启
用。
在一个繁忙的生产环境中,UNDO表空间可能占用几十
GB的空间,全部备份显然并不合理,这一特性是许多DBA
期待已久的,现在Oracle11g解决了这个问题。
备份是数据库中数据的副本,它可以保护数据在出现意外
损失时最大限度的恢复。
Oracle数据库的备份包括以下两种类型:
物理备份是对数据库的操作系统物理文件(如数据文件、
控制文件和日志文件等)的备份
逻辑备份是对数据库逻辑组件(如表、视图和存储过程等
数据库对象)的备份
导致数据库操作中止的故障包括四种类型:
语句故障:
在执行SQL语句无效可导致语句故障。
用户进程故障:
当用户程序出错而无法访问数据库时发
生用户进程故障。
导致用户进程故障的原因是异常断开连接
或异常终止进程
实例故障:
当Oracle的数据库实例由于硬件或软件问题
而无法继续运行时,就会发生实例故障
介质故障:
在数据库无法正确读取或写入某个数据库文
件时,会发生介质故障
❑传统的导出导入程序用于实施数据库的逻辑备份和恢复
❑导出程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中
❑导入程序读取二进制导出文件并将对象和数据载入数据库中
❑传统的导出导入程序是客户端工具。
❑导出和导入实用程序的特点有:
❑可以按时间保存表结构和数据
❑允许导出指定的表,并重新导入到新的数据库中
❑可以把数据库迁移到另外一台异构服务器上
❑在两个不同版本的Oracle数据库之间传输数据
❑在联机状态下进行备份和恢复
❑可以重新组织表的存储结构,减少链接及磁盘碎片
用以下三种方法调用导出和导入实用程序:
1,交互提示符:
以交互的方式提示用户逐个输入参数的值。
2,命令行参数:
在命令行指定执行程序的参数和参数值。
3,参数文件:
允许用户将运行参数和参数值存储在参数文件中,以便重复使用参数
导出和导入数据库对象的四种模式是:
1,数据库模式:
导出和导入整个数据库中的所有对象
2,表空间模式:
导出和导入一个或多个指定的表空间中的所有对象
3,用户模式:
导出和导入一个用户模式中的所有对象
4,表模式:
导出和导入一个或多个指定的表或表分区
❑导出实用程序有以下常用命令参数:
参数
说明
USERID
确定执行导出实用程序的用户名和口令
BUFFER
确定导出数据时所使用的缓冲区大小,其大小用字节表示
FILE
指定导出的二进制文件名称,默认的扩展名是.dmp
FULL
指定是否以全部数据库方式导出,只有授权用户才可使用此参数
OWNER
要导出的数据库用户列表
HELP
指定是否显示帮助消息和参数说明
ROWS
确定是否要导出表中的数据
TABLES
按表方式导出时,指定需导出的表和分区的名称
PARFILE
指定传递给导出实用程序的参数文件名
TABLESPACES
按表空间方式导出时,指定要导出的表空间名
按用户方式导出数据
expscott/tiger@accpfile=scott_backowner=scott
按表方式导出数据
expscott/tiger@accptables=(emp,dept)file=scott_back_tab
按表空间方式导出数据
expsystem/aptech@accptablespaces=(users)file=tbs_users
使用参数文件导出数据
expsystem/aptechparfile='C:
\parameters.txt
传统的导入实用程序
参数
说明
USERID
指定执行导入的用户名和密码
BUFFER
指定用来读取数据的缓冲区大小,以字节为单位
COMMIT
指定是否在每个数组(其大小由BUFFER参数设置)插入后进行提交
FILE
指定要导入的二进制文件名
FROMUSER
指定要从导出转储文件中导入的用户模式
TOUSER
指定要将对象导入的用户名。
FROMUSER与TOUSER可以不同
FULL
指定是否要导入整个导出转储文件
TABLES
指定要导入的表的列表
ROWS
指定是否要导入表中的行
PARFILE
指定传递给导入实用程序的参数文件名,此文件可以包含这里列出的所有参数
IGNORE
导入时是否忽略遇到的错误,默认为N
TABLESPACES
按表空间方式导入,列出要导入的表空间名
将整个文件导入数据库
impaccp/accp@accpfile=item_back.dmpignore=yfull=y
将scott用户的表导入到martin用户
impsystem/aptech@accpfile=scott_backfromuser=scotttouser=martintables=(emp,dept)
使用参数文件导入数据
impsystem/oracleparfile='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:
估算所占用磁盘空间分方法.默认值是BLOCKS
3,remap_schema:
用于将对象从一个用户下导入到另一个用户下。
4,remap_tablespace:
用于将对象从一个表空间下导入到另一个表空间下。
5,remap_datafile:
用于在不同文件系统的平台间,切换数据文件路径。
数据的装载
•SQL*LOADER
•外部表
•导入/导出
❑SQL*LOADER是一个ORACLE工具,能够将数据从外部数据文件装载到数据库中。
❑运行SQL*LOADER的命令是sqlldr。
❑Sqlldr的两种使用方式:
❑1.只使用一个控制文件,在这个控制文件中包含数据
2.使用一个控制文件(作为模板)和一个数据文件
❑一般采用第二种方式,数据文件可以是CSV文件、txt文件或者以其他分割符分隔的。
SQL*LOADER中控制文件的格式:
options(skip=2)
loaddata
infile'1.txt'
intotableempnewinsert
(acharterminatedby',',
bcharterminatedby',',
cdate"yyyy-mm-dd"nullif(c="NULL")
)
说明:
操作类型可用以下中的一值:
1)insert --为缺省方式,在数据装载开始时要求表为空
2)append --在表中追加新记录
3)replace --删除旧记录(用deletefromtable语句),替换成新装载的记录
4)truncate--删除旧记录(用truncatetable语句),替换成新装载的记录
❑外部表中的数据不装入数据库中,数据库中只存储外部表的定义信息,实际的数据位于操作系统的平面文件中,但是,可以在数据库中,像访问正常表那样,通过select语句来访问操作系统中的平面文件中所含有的数据。
❑外部表是只读的。
❑可以使用SQL,PL/SQL和JAVA访问外部表。
❑外表表分为2种:
使用数据泵引擎生成的外部表、根据文本文件创建的外部表。
createtabledept_ext(new_deptno,new_dname,new_loc)
organizationexternal
(
typeoracle_datapump
defaultdirectorymy_dir
location('dept1.dmp','dept2.dmp')
)
parallel
as
selectdeptno,dname,locfromdept;
createtableemp_ext_txt
(employee_idnumber,first_namevarchar2(20),
last_namevarchar2(20))
organizationexternal
(typeoracle_loaderdefaultdirectorymy_dir
accessparameters
(recordsdelimitedbynewline
fieldsterminatedby','
)location('emp.txt')
);
闪回查询从9i引入,可以按照时间点或者SCN向前查
询,获取修改前的数据。
闪回查询依赖于回滚段中存储的数据前镜像,通过设置
undo_retention参数设置前镜像的保留时间。
查询的语法:
select…asofscn|timestamp
对于9i的闪回查询进行增强,Oracle10g提供了闪回版本
查询,因为9i的闪回查询仅仅能够得到过去某个时间点上的
数据,但是无法反映出一段时间内数据表中数据的变化,
10g的闪回版本查询可以对时间段内数据表的不同版本进行
查询。
闪回版本查询的语法:
select…from…versionsbetween
其中,select后面可以选择伪列,来获得事务的开始、结
束时间、SCN号、ID号等。
Oracle10g可以进行基于闪回版本查询的恢复,就是闪回
事务查询。
从flashback_transaction_query中查询引起数据变化的
事务,和撤销事务的SQL语句,就是查询operation和
undo_sql列。
Oracle10g的闪回表是把表里的数据回退到以前的某个时
刻或者SCN上。
特点:
可以在线操作;自动恢复相关的属性,包括索引、
触发器等。
前提:
对表启用行迁移。
语法:
flashbacktable
注意:
sys的表不能闪回。
Oracle10g的闪回删除:
可以恢复一个被drop的对象,因
为进行drop时,Oracle先把它放到回收站中。
回收站内的信息:
showrecyclebin
闪回删除:
flashbacktable
彻底删除:
droptable
清空回收站:
purgerecyclebin;
通过参数recyclebin来启用、禁用回收站。
如果数据库出现逻辑错误,无法采用闪回表的方式进行恢
复,或者数据库的结构发生了改变,可以通过闪回数据库的
方式把整个数据库回退到出错前的时间点上。
步骤:
1,配置数据库为归档模式
2,配置闪回恢复区
3,配置闪回保留时间
4,启用数据库闪回alterdatabaseflashbackon;
5,进行闪回数据库
flashbackdatabasetotimestamp|scn
在分布式的海量数据环境中,信息查询的速度问题
显得尤为重要。
传统的查询方式,即根据用户的要求,
每次都重新的进入基表或视图查询,所需的时间太长。
例如移动通信行业,即使客户需要查询很少的信息,也
可能会花费很多时间,可能在30分钟左右,如果数据库
主机稍有繁忙,这个时间会更长,客户难以忍受。
为了
解决这种问题,ORACLE中设计了物化视图(又称为
MV)。
物化视图是包括一个查询结果的数据库对像,它是
远程数据的的本地副本,或者用来生成基于数据表求和
的汇总表。
物化视图存储基于远程表的数据,也可以称
为快照。
物化视图可以查询表,视图和其它的物化视图。
通常情况下,物化视图被称为主表(在复制期间)
或明细表(在数据仓库中)。
对于复制,物化视图允许你在本地维护远程数据的
副本,这些副本是只读的。
如果你想修改本地副本,必须
用高级复制的功能。
当你想从一个表或视图中抽取数据
时,你可以用从物化视图中抽取。
对于数据仓库,创建的物化视图通常情况下是聚合
视图,单一表聚合视图和连接视图。
物化视图有很多方面和索引很相似:
使用物化视图
的目的是为了提高查询性能;物化视图对应用透明,增
加和删除物化视图不会影响应用程序中SQL语句的正确
性和有效性;物化视图需要占用存储空间;当基表发生
变化时,物化视图也应当刷新。
1.物化视图和表一样可以直接进行查询。
物化视图可以基于分区表,物化视图本身也可以分区。
2.数据仓库中的物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。
在数据仓库中,还经常使用查询重写(queryrewrite)机制,这样不需要修改原有的查询语句,Oracle会自动选择合适的物化视图进行查询,完全对应用透明。
3.除了在数据仓库中使用,物化视图还用于复制、移动计算等方面。
1.如果创建基于主键的物化视图,则必须具有访问主表、访问主表的日志、createMATERIALIZEDVIEW这三个权限。
2.如果创建基于rowid的物化视图,则必须具有访问主表、createMATERIALIZEDVIEW这两个权限。
查询重写(QueryRewrite):
包括ENABLEQUERYREW
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 临时 空间 存储 过程