oracle创建临时表

oracle创建临时表（精选9篇）

oracle创建临时表 第1篇

Oracle数据库临时表管理心得

我们在创建数据表的时候，若没有特殊的指明，那么我们创建的表是一个永久的关系型表格，也就是说，这个表格中对应的数据，除非是我们显示的删除的话，表中的数据是永远都存在的。相对应的，在Oracle数据库中还有一种类型的表，叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远的存在。当一个会话结束或者事务结束的时候，这个临时表中的数据，不用用户自己删除，数据库自己会自动清

除。

1、事务临时表的管理。

(1)事务临时表的创建。

Oracle数据库根据临时表的性质不同，可以分为事务临时表与会话临时表。事务临时表是指数据只有在当前事务内有效。一般情况下，如果在创建数据表的时候，没有特殊指明这表是会话临时表的话，则该表默认为事务临时表。

我们可以以下面的语句创建事务临时表。

Create global temporary table Temp_user

(ID NUMBER(12)Primary key,name varchar2(10));

笔者建议：

这个创建临时表的语句中，虽然没有显性的指明该表是事务临时表，但是，默认的情况下，若没有指明是什么临时表的话，系统默认是事务临时表。我们要创建事务临时表时，可以不指定关键字。但是，这查看起来比较麻烦。我建议，无论在建立什么临时表，都要利用具体的关键字来显形的指明，这大家看起来都方便。一般可以利用ON COMMIT DELETE ROWS关键字来说明该表就是事务性的临时表，而不是会话性质的临时表。

(2)事务临时表数据的变化分析。

事务临时表的话，当事务结束的时候，就会清空这个事务临时表。所以，当我们在数据库临时表中插入数据后，只要事务没有提交的话，该表中的数据就会存在。但是，当事务提交以后，该表中的数据就会被删除。而且，这个变化不会在重做日志中

显示。

具体事务临时表与会话临时表有什么区别，我们在介绍完会话临时表后会详细介

绍。

2、会话临时表的管理。

会话临时表，顾名思义，是指数据只在当前会话内有效的临时表。关闭当前会话或者进行新的连接之后，数据表中的内容就会被清除。那会话临时表跟事务临时表到底有什么区别呢?我们以一个实例来看其中的区别。

(1)首先，创建一个会话临时表。

CREATE GLOBAL TEMPOPARY TABLE TEMP_USER

(ID NUMBER(12)Primary key,name varchar2(10))

ON COMMIT PRESERVE ROWS;

也就是说，会话临时表跟事务临时表的创建语法大致相同，只有最后的关键字有区别。不过两个表虽然类似，但是其内部的处理机制还是有比较大的区别。

(2)往该表中插入数据。

Insert into TEMP_USER values(1001,’victor’);

往数据库临时表中插入数据的方法，跟往普通表中插入数据的方法是一样的，都利用insert into语句进行操作。该临时表的数据在会话结束之前都是存在这个表格

中的。

(3)提交该事务并查询相关记录。

我们利用COMMIT的语句把该事务提交以后，再用SELECT查询语句进行查询。我们知道，若该表是事务临时表的话，则当该事务结束以后，该表中的内容就会被删除。但是，这是会话临时表，所以即使该事务提交了，但是，利用SELECT语句进行查询

时，仍然可以查到该条员工记录。

(4)结束当前会话，并重新连接数据库。

关闭当前会话，从新连接到数据库后，再利用SELECT语句查询时，会有什么结果呢?此时，就查不到我们刚才插入的数据。这也就是说，在关闭对话的时候，数据库系统已经把原有的数据删除了。从以上的分析我们可以看中，会话临时表与事务临时表主要的差异就在于删除数据时机的不同。事务性临时表是在事务提交的时候清除数据，而会话性临时表则是在关闭当前会话的时候清除临时表。只要当前会话没有关闭，即使事务完成了，会话临时表中的数据仍然存在，不会被清除。

3、临时表管理需要注意的地方。

临时表相对与其他表来说，是一种比较特殊的表结构，但是，作用又比较大，Oracle数据库若没有这种表的话，还真是不行。为了管理好这种特殊的表，我们需要

注意几个细节。

一是要注意临时表不能永久的保存数据。只所以称为临时表，就是因为该表中的内容只是临时存在的。当一个会话或者事务结束时，该表中的内容就会被自动清空。所以，在临时表中，一般不要保存永久数据。在实务中，有个不好的操作习惯，就是有些人在测试数据库的时候，喜欢把测试的数据放在临时数据表中。其实，这是对Oralce临时数据表认识的错误。若我们在数据库中，把要测试的数据，如销售定单的内容放在数据库的临时表中的话，则在其他功能中，如要测试销售定单日报表的功能时，就会找不到相关的定单内容。因为离开特定的会话或者事务的话，临时表中的内容就会不存在了。所以，Oralce数据库中所讲的临时表不是给我们来存储测试数据的。

二是临时表中的数据不会备份、恢复，对其的修改也不会有任何的日志信息。若我们在操作数据库的时候，往数据库的临时表中存入了一些信息。此时突然服务器出现当机。此时，我们想通过数据库备份文件恢复数据库临时表中的内容，或者查看临时表的日志信息，都是无法实现的。也就是说，当服务器以外死机重新启动后，临时表中的内容就会被清空。在数据库的任何地方，如数据库备份文件或者日志信息中，都查不到在重新启动之前数据库临时表中保存了哪些内容，就好象根本没有对临时表

进行操作一样。

三是临时表表空间的管理。临时表在Oracle数据库中，也是表的一种，其也有对应的表空间。在创建临时表的时候，若我们不指定表空间的话，默认的表空间是SYSTEM。对于临时表的表空间管理的话，我们需要注意一个小的细节。若我们把临时表的表空间归属为SYSTEM的话，也就是说，在创建临时表的时候不具体指定具体的表空间，则这个默认的表空间是不能被删除的。而若我们在创建临时表表空间的时候，指定为SYSTEM以外的表空间的话，则在不需要这表空间的时候，我们可以删除。所以，为了后续管理的方便，笔者还是建议大家在创建临时表的时候，要指定表空间。

四是要注意一个问题，临时表只是数据是临时的，而表仍然是永久的。也就是说，当一个会话结束或者一个事务完成时，其临时表中的数据虽然删除了，但是，临时表本身仍然是存在的。也就是说。Oracle数据库中的临时表表是全局的，只是数据是临时的。这跟SQL Server数据库系统具有比较大的区别。其实，这两个数据库在临时表的处理上有很大的不同，各有各的特色。在以后的文章中，我会专门叙述这两种数据库在临时表管理机制上的不同，欢迎大家关注。

五是要注意Oracle数据库在给临时表填入数据的时候，不会对相应的记录加锁。也就是说，当在临时表上执行DML语句的操作时，不会给记录加锁，也不会将数据的变化内容写到重做(REDO)日志中。所以不能用临时表保存永久的数据，也不能对临时表进行共同的操作。这是新手在管理数据库临时表经常会碰到的问题。

六是临时表与普通表之间不能相互转换。在一般情况下，临时表建立后，该表就不能被转换成永久表。所以，这也说明一个道理，利用临时表作为数据库设计时候的测试表不合适。这个临时表可能跟我们按字面意思理解的临时表有误，不是我们所认为的为了测试表结构而建立的临时表。这一点是我们在刚开始接触ORACLE数据库时，经常会犯的错误。

oracle创建临时表 第2篇

1.案例

前段时间报表中心有一存储过程执行速度过慢，由于另外一个存储过程中用到了那个存储过程中的中间表，因此如果前面的一个过程没有执行完而执行后面的那个过程，后面的过程执行完之后还是没有数据。四月份那个过程执行大约需要两个小时的时间，本以为是过程里面的业务太复杂导致。可前段时间执行的时间超过了十小时。后来才发现是中间表中数据量越来越多导致，大约有五百多万数据，而且每天会增加两万左右的数据，慢的地方主要是在中间表中插入当天的数据之后需要更新某些字段，由于中间表中数据量过大，更新这些字段是速度才很慢（已经建了索引）。

解决方案：创建一个和中间表一摸一样的临时表，先把数据插入到临时表中，在临时表中更新需要更新的字段，更新完后再把临时表中所有的数据插入到中间表中，然后再使用中间表中的数据汇总出报表。

2.如何创建临时表

创建Oracle 临时表，可以有两种类型的临时表：会话级的临时表，事务级的临时表。2.1会话级临时表

因为这个临时表中的数据和你的当前会话有关系，当你当前SESSION 不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION 的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION 登陆的时候是看不到另外一个SESSION 中插入到临时表中的数据的。

即两个不同的SESSION 所插入的数据是互不相干的。当某一个SESSION 退出之后临时表中的数据就被截断（truncate table，即数据清空）了。会话级的临时表创建方法：

Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...)On Commit Preserve Rows ； 举例：

create global temporary table Student(Stu_id Number(5), Class_id Number(5), Stu_Name Varchar2(8), Stu_Memo varchar2(200))on Commit Preserve Rows ； 2.2 事务级临时表

事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，Oracle临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION 的时候，事务级的临时表也会被自动截断）。事务级临时表的创建方法：

Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...)On Commit Delete Rows ； 举例：

create global temporary table Classes(Class_id Number(5), Class_Name Varchar2(8), Class_Memo varchar2(200))on Commit delete Rows;2.3 两种类型临时表的区别

会话级临时表采用 on commit preserve rows ；而事务级则采用 on commit delete rows ；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是 commit、rollback 或者是会话结束，Oracle临时表中的数据都将被截断 2.4临时表的不足之处

1）不支持 lob 对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。）不支持主外键关系

3.示例

1.会话级临时表

create global temporary table emp_temp_preserve on commit preserve rows

as select * from emp where 1=2;

insert into emp_temp_preserve select * from emp;commit;

select * from emp_temp_preserve;

在同一个会话中查询有数据，不在同一个会话中查询没数据

在不同会话中查询：

2.事务级临时表

create global temporary table emp_temp_delete on commit delete rows

as select * from emp where 1=2;

oracle创建临时表 第3篇

应医疗信息化发展的需要, 医院“军卫一号”信息系统 (HIS) 后台数据库2010年从Oracle8升级到Oracle10, 显著的提高了医疗工作效率。但近段时间发现系统出现了相应滞后的现象, 部分计价、结算、查询等需要长时间的等待。经过对数据库系统的检查分析, 发现随着患者数量的增加, 系统数据量积聚增大, 目前已经超过300G, 其中部分表数据已超过1亿条, 对数据查询检索造成了较大的压力, 影响了数据库的运行效率, 同时加大了维护难度。根据Oracle官方建议, 在不更改前端应用的情况下拟采用Oracle表分区技术进行彻底优化解决[2]。

2. Ora cle表分区技术

表分区技术是在超大型数据库 (VLDB) 中将大表及其索引通过分区 (patition) 的形式分割为若干较小、可管理的小块, 并且每一分区可进一步划分为更小的子分区 (sub partition) , 而这种分区对于应用来说是透明的[4]。通常, 表分区可以使某些查询以及维护操作的性能大大提高, 极大简化日常管理任务。通过对表进行分区, 可以获得以下的好处:

(1) 增强可用性, 如果表的一个分区由于系统故障而不能使用, 表的其余好的分区仍可以使用[1];

(2) 减少关闭时间, 如果系统故障只影响表的一部份分区, 那么只有这部份分区需要修复, 比整个大表修复花的时间更少;

(3) 维护轻松, 独产管理分区比管理大表要轻松得多;

(4) 均衡I/O, 把不同分区分配到不同的磁盘来均衡I/O;

(5) 改善性能, 对大表的查询、增加、修改等操作可以分解到表的不同分区并行执行, 可使运行速度更快。

(6) 分区对用户透明, 最终用户感觉不到分区的存在。

3. 使用Ora cle分区表技术对HIS系统进行优化

3.1. 分区策略的选择

Oracle 10g提供了以下几种分区类型[2]:范围分区 (range) 、哈希分区 (hash) 、列表分区 (list) 以及复合分区 (Composite) 。根据医院的实际业务情况, 在线使用的主要是近段时间的医疗信息, 对历史信息只是极少量的的查询和统计, 故对单表数据量超过1000万行的业务表以时间范围为分区条件进行范围分区, 将记录存放到对应时间所在的range分区中 (如图1) 。

3.2. 分区实施

实施分区采用Oracle在线重定义功能, 在大部分实施时间内表都可以正常的进行DML操作, 只是在切换的瞬间锁表[4], 以尽量保证数据的一致性以及临床业务的连贯性, 而且这种方案具有很强的灵活性, 能满足不同表的需求, 亦可在实施前进行相应的授权并建立各种约束, 实施完成后也不需要任何额外的管理操作。

(1) 检查表是否可以在线重定义

(2) 创建在线重定义需要的过渡表, 这是一个分区表, 原表数据在线转移到该表中

(3) 执行在线重定义过程

(4) 执行重定义完成的过程

4. 效果评测

经过表分区改造后的数据库和HIS系统在使用和维护上都有了非常大的进步:

(1) 大大改善了系统性能, 成倍提高了临床系统响应的速度。对收费数据表进行分区管理后, 表的插入、查询、统计等操作可以分解到表的不同分区来并行执行, 跳过无关分区数据的访问, 大大提高了运行速度, 患者结算时间缩短了约5~8倍。

(2) 无需更改前端应用。分区对用户透明, 临床操作人员无需更改操作习惯即可快速的完成医疗业务。

(3) 系统可维护性提高。数据备份恢复都可以只针对单个分区进行, 降低了磁盘故障带来的系统故障率。

5. 总结

随数据量的不断增加, HIS数据库里的巨型表会越来愈多, 通过Oracle表分区技术, 将巨型表分割成较小的可独立管理的分区, 提高了临床系统响应速度, 增强了HIS的稳定性, 简化了系统维护管理工作。

摘要：为应对HIS系统数据量急剧增加, 提高HIS系统的响应速度;应用Oracle表分区技术对HIS系统进行优化;大大改善HIS的读写性能, 大幅度提升查询和统计速度;显著的提高HIS数据的读写性能和可维护性。

关键词：Oracle分区,HIS,优化

参考文献

[1]徐小燕, 张桂珠.Oracle表分区技术在税务纳税申报系统的应用研究〔J〕.电脑知识与技术, 2009, 5 (21) :5615-5617.

[2] (美) Thomas Kyte著, 苏金国, 王小振译.Oracle9i&10g编程艺术数据库体系结构〔M〕.北京:人民邮电出版社, 2006:900.

[3]曹彤, 程君.Oracle分区技术在LIS中的应用研究〔J〕.医疗卫生装备, 2012.33 (4) :60-61.

创建Oracle数据库的原则 第4篇

关键词：Oracle；数据库；原则

中图分类号：TP311.13 文献标识码：A文章编号：1007-9599 (2010) 14-0000-03

The Principle of Creating Oracle Database

Liu Yin

(China Airlines Clearing Co.,Ltd.,Beijing100028,China)

Abstract:In the China Aviation Clearing Corporation six years of work has been engaged in the Oracle applications database development and maintenance.Application process in the establishment of a database and found that many applications do not meet the Oracle database creation application of the principle database settings. This problem is prevalent in many systems,we consider the availability of more,while ignoring the performance of the database problems.I would like to work according to their own practical experience,according to Oracle applications to talk about the process of database creation and consideration should be noted that some basic principles and requirements.

Keywords:Oracle;Database;Principle

首先我想要申明的是，这个过程是根据NT上Oracle应用数据库建立过程来说明的，但是所说的原则同样适用于其他的操作系统平台（如UNIX）。虽然对于Oracle方面的高手来说，有一点班门弄斧的嫌疑，但是对于一个需要全面了解Oracle建立过程的初、中级用户，仍然还是大有益处的（这个例子中将要建立的实例名和数据库名设为VETO）。

一、初始化文件（initSID.ora）的建立

大家都知道要建立一个初始化文件（例如initAOMN.ora），这个文件可以用写字板进行编辑和修改。这个文件决定了实例的基本配置和性能，中间参数的大小需要根据具体的应用要求决定。用户还可以根据情况的变化，以后进行调整修改。

许多关键的参数Oracle公司都给了大、中、小三个标准，用户可以根据需要确定。除了要修改DB_NAME，将它改为将要建立的数据库名外，还有以下的几个参数要注意：

（一）CONTROL_FILES是指定控制文件的名称和位置。控制文件必须小于8个。但是为了防止控制文件毁坏而导致数据库的崩溃，建议将控制文件指定在不同硬盘上互做备份。文件名最好让人望名知意。

（二）SHARE_POOL_SIZE指的是共享池的大小，如果想加快SQL语句的处理速度，而且操作系统的内存足够的话，可以开大些加快速度

（三）DB_BLOCK_SIZE这个参数设置Oracle块的大小。对于OLTP（在线事务处理系统），应该设置得小点，而对于DDS（数据仓库）而言，应该设置得大点。一旦设定并且数据库建立以后时没有办法修改的。这个问题值得注意。

二、建立实例（Instance）

数据库的实例指的是为处理数据库而分配的内存结构和后台处理过程，它的配置和性能由上面的初始化文件决定的。为了在操作系统中建立这个实例，必须使用Oracle提供的Oradim工具。我们可以在NT Windows菜单的运行中输入：

ORADIM73 -NEW -SID VETO -USRPWD VETO -STARTTYPE SVRC，INST -STARTMODE AUTO -PFILE=D：ORANTDATABASEVETOINITVETO.ORA

这个命令的结果是在操作系统中建立了一个实例，并且实例的启动方式是自动。internal用户的密码是VETO，在系统的服务中建立了OracleStartVeto和OracleServiceVeto两个服务。在开始的时候OracleServiceVeto已经启动，但是OracleStartVeto可能没有启动。所以可用Net Start OralceStartVeto直接将OralceStartVeto和OralceServiceVeto启动，确保这个实例所需的服务都正常启动。即：C：NET START OracleServiceVeto

三、建立数据库（database）

实例的建立只是为数据库的建立做了一个准备，我们可以将实例付给任何一个数据库使用，只要数据库的参数MaxInstances没有被超过。建数据库需要用CREATE DATABASE命令，最好事先将参数写入一个SQL文件中，以被使用和今后的维护。我们需要了解建立数据库时，一些参数的意义，有利于我们对数据库性能的把握。

例如：这里用到的CREATE_DB_VETO.SQL

CREATE DATABASE VETO

CONTROLFILE REUSE

MAXLOGFILES 32MAXLOGMEMBERS 5MAXDATAFILES 50 MAXINSTANCES 2

LOGFILE GROUP 1 （'D：ORANTDATABASEVETOLOGVETO11.RDO'，

'D：ORANTDATABASEVETOLOGVETO12.RDO'）SIZE 1M，

GROUP 2 （'D：ORANTDATABASEVETOLOGVETO21.RDO'，

'D：ORANTDATABASEVETOLOGVETO22.RDO'）SIZE 1M

DATAFILE 'D：ORANTDATABASEVETOSYSTEMVETO.DBF' SIZE 100M

CHARACTER SET ZHS16cgb231280；

在这里我们需要注意以下几点要求：

（一）如果initVeto.ora文件中指定的controlfile已经存在才可以在建立数据库时使用CONTROLFILE REUSE，否则不能使用.

（二）MAXLOGFILES指最大的redo log file的group数。MAXLOGMEMBERS指最大的redolog file的group中的member数。MAXDATAFILES指数据库中最大的datafile数。MAXINSTANCES指数据库可被多少个instance使用数。这五个参数将会影响controlfile的大小

（三）LOGFILE指的是redo log file的设置，每个GROUP中的MEMBER文件的大小必须是大小相同的。由于redo log file是循环使用的，所以最少必须有两个GROUP，否则数据库无法建立。虽然每个GROUP的MEMBER文件可以只有一个，但是为了防止某个MEMBER文件的毁坏而导致数据库的不可用，必须在每个GROUP中有多个MEMBER，最好是每个GROUP有相同数目的MEMBER文件。建立数据库的SQL语句写好后，以internal用户进入，重新启动实例并进入数据库的nomount状态，建立数据库并打开数据库。

结果：INSTANCE启动，数据库是OPEN状态，数据库可以被使用。在建立数据库的过程中，数据库中所用到的基表都在这个过程由系统运行sql.bsq自动建立了。

四、建立动态性能视图（data dictionary view）

当数据库建立后，用户仍然不能用system用户的身份进入系统。这是由于数据库的基表虽然已经建立，但是动态性能视图没有建立，所以system的身份无法被系统确认。必须以sys或者internal用户的身份重新进入系统，运行catalog.sql和catproc.sql（这两个文件存在于目录%Oracle_homerdbms73admin中）。catalog.sql的目的是建立动态性能视图。catproc.sql的目的是建立运行SQL，PL/SQL所需的一些函数包和其他帮助工具包。虽然这个时候system用户可以登录数据库，但是可能系统要求运行pupbld.SQL，目的是防止系统设置的确省的profile不能被访问。

五、建立监听器（LISTENER.ORA）

数据库的基本结构已经建立，但是用户仍然无法远程访问数据库，因为操作系统，需要为新建的实例建立一个监听器，监听用户发出的连接请求。首先，我们必须手动的修改D：ORANTNTWORKADMINLISTENER.ORA的最后部分，然后，我们进入操作系统的服务中重启OracleTNSListener服务使修改生效，新的监听器建立。但是我们还是不能远程访问数据库，必须建立数据库别名。

六、建立数据库别名

监听器中的SID_NAME指的VETO是监听器监听的连接串的值。这个值必须等于数据库别名，所以我们必须在SQL NET EASY CONFIG中建立数据库别名，使数据库的能被远程访问数据库。例如：VETOTCP/IP10.1.3.57VETO。上面句中的前一个VETO指的是数据库别名，而后一个VETO指的是实例名。由于为了方便使用和理解，推荐使数据库别名等于实例名。

七、表空间（tablespace）

上面的六步将数据库建立了起来，但是对于一个Oracle应用数据库来说仅仅是将基本的结构建立了起来，为了使数据库能够被应用程序所使用，必须为不同的应用建立不同属性的表空间。首先，建立一个存放应用程序的数据的表空间，在建立这类表空间的时候存在很多问题。我们先来建立一个数据表空间，再讨论一下建立的原则，这些原则是避免问题的关键：

create tablespace DATA

datafile 'd：orantdatabasevetodatveto.ora' size 600M

storage（initial 1600K next 1600K pctincrease 100 minextents 1 maxextents unlimited）autoextend on next 100M maxsize 1000M；

（一）由于许多系统在建表生成的SQL语句中并没有指定表的存储参数， 建表时会使用表空间的存储参数，所以并且在建立表空间时最好指定存储参数。这需要根据具体系统的特点来设置，对于一个数据表空间，由于不同的表的定义和存放的数据量一般有很大的差异，所以建议采取中等的值。

（二）为了防止在分配extent时在表空间的数据文件中产生碎片，initial和next的值必须是db_block_size的整数倍。而且是db_file_multiblock_read_count*db_block_size的整数倍，目的是在全表搜索时速度快。

（三）数据表空间中的数据表比较多，而大量的表的数据比较小，所以initial和next不能太小，也不能太大，minextents设置为1。而且个别表中的数据多，所以maxextents设置为unlimited，pctincrease=100保证数据可以全部导入表中。pctincrease的设置必须满足上面的第二条，也就是接下来建立的extent不会在数据文件中建立碎片，在这里设为100，设所有的分配的EXTENT满足条件。

接下来建立一个存放应用程序代码的表空间，这类表空间中的数据变化不大，并且数据量不大，所以initial和next也不必太大，pctincease设置为0.其他的原则与建立数据表空间的原则一致.

八、建会滚表空间和回滚段（rollback segment）

回滚段是用来记录是数据被DML（数据处理语言）处理前的值，如果事务被回滚，可以将数据恢复到修改前。它还为其他的用户提供读一致性（read consistency），就是数据被一个用户进行DML处理但还没有提交时，其他用户读取数据时看到的值。数据库建立时，会在system表空间中建立一个系统回滚段，这个回滚段是为system/sys/internal用户操作动态性能视图及其基表而使用，其他用户不能使用这个回滚段。所以我们必须为其他用户操作数据库数据而建立回滚段。为了减少因使用回滚段产生的碎片，最好建立一个专门的回滚段表空间。然后就可以建立回滚段了，但有几点必须注意：

（一）在创建时回滚段可以指定为PUBLIC或PRIVATE（默认），在创建后不能更改。一般建立的回滚段为PUBLIC。不能为回滚段指定PCTINCREASE参数，总是设置为0。这一点很多时候都被忽略了。对于回滚段而言，总是使用INITIAL=NEXT，以保证所有的扩展有相同的大小。

（二）OPTIMAL参数，如果设置了，必须至少和回滚读段的初始值相等，而且最好设置，可以保证回滚段在没有事务处理时可以自动的回到OPTIMAL指定的值，释放空间。

（三）按照事务所需的平均空间设置OPTIMAL参数。如果没有适当的数值，把它设置为初始值，以后再调整。不要把MAXEXTENTS设置为UNLIMITED。这可能会导致不必要的回滚段扩展，可能会由于程序错误造成数据文件的扩展。

（四）总是把回滚段放在分离的、独占的表空间中，以便把竞争和碎片降到最低。所以必须专门的建立一个回滚段表空间。回滚段的数量没有详细的规定，但是对于OLTP系统，一般四个事务对应使用一个回滚段。回滚段必须ONLINE后才可以被使用。

九、建临时表空间

很多时候我们忽略了临时表空间的建立，因为它的影响仅仅限于排序查询时，对速度的影响。临时段是在排序结果大于PGA中SORT_AREA_SIZE指定的排序区，在指定的临时表空间存放排序的中间结果时产生的，在实例关闭时才释放。由于临时段产生的碎片比任何段都多，所以必须建立一个专门的临时表空间，虽然在临时表空间中只有一个临时段。

十、建立用户并授权

对于一个应用程序，必须建立不同于SYSTEM用户的其他用户来管理应用程序的数据和代码。所以我们将上面建立的数据表空间和代码表空间分别给用户DATA和CODE，如下：

CREATE USER UNIFACE IDENTIFIED BY ACCA DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TMP PROFILE DEFAULT；

GRANT RESOURCE TO UNIFACE；

CREATE USER CODE IDENTIFIED BY ACCA DEFAULT TABLESPACE CODE TEMPORARY TABLESPACE TMP PROFILE DEFAULT；

GRANT RESOURCE TO CODE；

要求和原则：

（一）建用户时必须指定用户的确省表空间和临时表空间，确省表空间保存用户所有对象。临时表空间是在排序时作为当排序所需的空间大于SORT_AREA_SIZE时存放临时过程使用的。

（二）用户建立时，系统自动将角色CONNECT付给用户，但是用户如果想拥有建立对象的权利，就必须将角色RESOURCE付给用户。

虽然第二步到第四步在NT上都可以在INSTANCE MANAGER中完成，但是由于不能给大家更加清晰和灵活的配置，建议不要用。而且根据以上十步关于建立Oracle应用数据库的过程，可以将这个过程写成批处理过程，这样不仅有助于保留建立应用数据库的参数，加快数据库的建立过程，而且还有助于日后的对数据库进行性能调整。所以我推荐根据具体的应用系统要求，建立一套标准的建立过程，是很有必要。

oracle创建临时表 第5篇

临时表在数据库设计中，有着举足轻重的作用，

如我们可以利用临时表的功能来限制同一个用户名多次登陆到同一个系统中去。例如现在有一个财务管理系统，企业希望同一个用户在同一时间里只能登陆一次，这主要是用来限制每位员工都以自己的用户名与密码登陆。如此限制的目的主要是为财务管理系统中的每张单据找到其主人。

原始方案：

在以前数据库设计的时候，也有人不用临时表进行这方面的限制，而用实体数据库表来登记相关的信息。如在用户信息表中有一列专门用来记录用户的当前登陆状态。当用户登陆系统后，该用户登陆状态的字段就改为Y，而当用户退出系统能后，该字段的内容又改为N。这个方案看起来是可行的，但是，其在实际应用中，有一个非常大的漏洞。若用户登陆到系统后，终端因为各种原因，如病毒、断电等突然状况，发生死机的话，此时，用户虽然没有登陆到系统中去，但是，因为其退出系统的时候，没有正常退出，这就导致在财务管理系统中的用户信息表中，显示该用户的登陆状态仍然为Y。此时，用户尝试登陆到财务管理系统中去的话，就会被系统拒绝，系统会认为该用户已经登陆了系统，不能重复登陆。

所以说，利用实体表来记录用户登陆的信息，存在着管理上的漏洞。

利用临时表实现用户重新登陆的限制：

后来，数据库设计师们想，能否把该用户登陆信息记录在一张临时性的表中呢?当用户结束会话，无论是正常的退出还是因为意外情况的退出，只要用户结束一个会话后，那么该临时表中的内容就会清空。

若跟这个需求结合的话，数据库设计师就希望能够实现如下功能。

当用户登陆系统开始一个会话后，数据库系统就建立一张临时表，该表中至少有一个内容，就是用户的帐号(或者该帐号对应的ID)。当有其他用户登陆到系统的时候，系统会先从这张临时表中查询，是否有相同的用户记录。若有的话，就会拒绝用户的登陆，警告用户已经有相同的用户登陆了。当用户正常退出系统或者因为以外情况退出系统结束当前会话的时候，那么数据库系统就会清除这张表的内容。如此，当用户下次登陆系统的话，即使是在意外情况下登陆系统的，也可以正常的登陆，

可见，数据库的临时表在企业实际应用中有着举足轻重的作用。

在ORACLE数据库与SQL SERVER数据库中，都实现了临时表的功能。不过两者实现的方式有差异。而不同的实现方式又赋予了其不同的特点，这是我们在数据库选型中不得不重视的一方面内容。

两个数据库临时表实现方式的异同：

SQL SERVER临时表跟ORACLE数据库临时表的差异，可以利用一句话来概括。SQL SERVER 临时表是在需要用到的时候创建;而ORACLE 数据库的临时表，则是在数据库初始化中就开始创建，在具体的会话或者事务开始后进行操作，结束一个会话或者结束一个事务后该数据库的内容就会被清空。

1、在创建时的异同。

SQL SERVER 数据库的临时表，是在实际需要时创建的。具体的来说，可以利用SELECT语句与CREAT语句创建临时表。如可以利用SELECT * INTO #USER_TEMP FROM USER;通过这条语句就可以在需要的时间创建一张临时表。除此之外，还可以利用CREATE语句，在需要的时候创建临时表。

而ORACLE数据库，是在数据库系统初始化的过程中，就需要建立临时表。也就是在用户安装财务管理软件系统时，初始化数据库系统时，系统就会创建临时表。而不是在临时表需要用到的时候，才被创建。故，ORACLE数据库的临时表创建方式只有一种，在数据库初始化的时候，利用CREATE创建数据库临时表。所以，ORACLE数据库临时表，又有另一种说法。我们一般称ORACLE数据库的临时表是永久性的，只是临时表的内容是临时的，在需要用到临时表时，只要直接调用即可，而不用临时创建。这不像SQL SERVER数据库那样，只有在用到时，才创建该临时表;当结束会话时，不仅表中的数据被清空了，而且该表也被删除了。

笔者评论：

oracle创建临时表 第6篇

CREATE TABLE “方案名”.“表名”(字段1 数据类型（长度），字段2 数据类型（长度），……., 字段n 数据类型（长度），CONSTRAINT 约束名1 约束类型(约束参数)，CONSTRAINT 约束名2 约束类型(约束参数)………

CONSTRAINT 约束名n 约束类型(约束参数))TABLESPACE “表空间名”

STORAGE(INITIAL 1M*第一个区的大小*

NEXT 1.5M*第二个区的大小*

MINIEXTENTS 2 *创建时已分配的区数*

MAXEXTENTS 5 *可分配最大区数*

PCTINCREASE 10 *每个区相对于上个区增长的百分比*

BUFFER_POOL DEFAULT *其参数有DEFAUT,RECYCLE和KEEP*)PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255;

要点释义

PCTFREE:指定表的每一个数据块为将来更新现有表行所保留的空间百分比。其值必须在0和99之间，默认值为10。

PCTUSED:指定维持表的每个数据块已用空间的最小百分比。当块中已用空间低于PCTUSED的时候，该块就成为新行插入对象。默认值40。

注意:PCTFREE和PCTUSED之和要小于100，两者结合可以确定是把新行插入现有数据块还是新块中。

INITRANS:分配给表的每一个数据块的并发事务条目的初值。范围1-255，默认1。通常不需要改变INITRANS值。

每个更新块的事务需要该块中的一个事务条目，事务条目大小取决于操作系统，该参数保证最小数目的并发事务可以更快，有助避免动态分配事务条目的开销。

MAXTRANS:可更新分配给表的数据块的最大并发事务数。范围1-255。默认255。一般不要更改MAXTRANS的值。

如果一个块中的并发事务超过了INITRANS的值，那么ORACLE在块中动态分配事务条目，直到超过MAXTRANS值。例题

创建学生信息表，字段 学号 姓名 年龄 电话 籍贯，约束：学号创建primary key约束并且不能为空，电话创建unique约束，年龄创建check约束（15-25岁）。

存储参数：初始区为1m大小，最大可分配区数4，区增量 10。PCTFREE为15，PCTUSED为45。INITRANS和MAXTRANS分别是2和244。

CREATE TABLE “SYSMAN”.“学生信息表”

(学号 VARCHAR2(12)NOT NULL, 姓名 VARCHAR2(8), 年龄 NUMBER(2), 电话 VARCHAR2(2), 籍贯 VARCHAR2(2), CONSTRAINT 年龄约束 CHECK(年龄>=15 and 年龄<=25), CONSTRAINT 学号约束 PRIMARY KEY(学号)，CONSTRAINT 电话检查 UNIQUE(电话)）TABLESPACE “SYSTEM” STORAGE(INITIAL 1M

MAXEXTENTS 4 PCTINCREASE 10 BUFFER_POOL DEFAULT)PCTFREE 15 PCTUSED 45 INITRANS 2 MAXTRANS 244;扩展知识

可使用PCTFREE和PCTUSED参数控制数据块何时进入或离开表、索引或簇的可用清单。使用PCTFREE参数设置最大的阈值，以控制对将来的行的更新操作保留多少数据块空间。当某个数据块使PCTFREE装满时，ORACLE从相应段的可用清单中删除该块。如果你预计只有很少的更新操作会增加行的大小，则可将PCTFREE设置为较低的值（如5或者10），使得ORACLE填满每个块的更多的空间。但是，如果你预计更新操作将会经常增加行的大小，则将PCTFREE设置为较高的值（如20或30），使得ORACLE为已有行的更新操作保留更多的块空间；否则，将出现行链。

恢复Oracle表空间的方法 第7篇

Oracle表空间恢复方案

一。用户表空间

错误：

在启动数据库时出现ora-01157,ora-01110或操作系统级错误例如ora-07360, 在关闭数据库(使用shutdown normal或shutdown immediate) 时将导致错误ora -01116,ora-01110以及操作系统级错误ora-07368

以下有两种解决方案：

1.用户的表空间可以被轻易地重建 即最近导出的对象是可用的或表空间中的对象可以被轻易地重建等。在 这种情况下，最简单的方法是offline并删除该数据文件，删除表空间并 重建表空间以及所有的对象。

svrmgrl> startup mount

svrmgrl> alter database datafile filename offline drop;

svrmgrl> alter database open;

svrmgrl> drop tablespace tablespace_name including contents;

重建表空间及所有对象。

2.用户的表空间不能够被轻易地重建在大多数情况下，重建表空间是不可能及太辛苦的工作。方法是倒备份及做介质恢复。如果您的系统运行在noarchivelog模式下，则只有丢失的数据 在online redo log中方可被恢复。

步骤如下：

1)restore the lost datafile from a backup

2)svrmgrl> startup mount

3)svrmgrl> select v1.group#,member,sequence#,first_change# > from v$log v1,v$logfile v2 > where v1.group#=v2.group#;

4)如果数据库运行在noarchivelog模式下则： svrmgrl> select file#,change# from v$recover_file;

如果 change# 大于最小的first_change#则数据文件可以被恢复。 如果 change# 小于最小的first_change#则数据文件不可恢复。 恢复最近一次的全备份或采用方案一。

5)svrmgrl> recover datafile filename;

6)确认Oracle表空间恢复成功

7)svrmgrl> alter database open resetlogs;

3.只读表空间无需做介质恢复，只要将备份恢复即可。唯一的例外是：

表空间在最后一次备份后被改为read-write 模式

表空间在最后一次备份后被改为read-only 模式

在这种情况下，均需进行介质恢复

二。临时表空间

临时表空间并不包含真正的数据，恢复的方法是删除临时表空间并重建即可，

三。系统表空间

如果备份不可用，则只能采用重建数据库的方法

四。回滚表空间

有两种情况：

1、数据库已经完全关闭(使用shutdown immediate或shutdown命令)

1) 确认数据库完全关闭

2) 修改init.ora文件，注释“rollback-segment”

3) svrmgrl> startup restrict mount

4) svrmgrl> alter database datafile filename offline drop;

5) svrmgrl> alter database open;

基于出现的结果：

“statement processed”

“ora-00604,ora-00376,ora-01110”

6) svrmgrl> shutdown immediate 修改init.ora文件，增加如下一行： _corrupted_rollback_segments = (，…) svrmgrl> startup restrict

7) svrmgrl> drop tablespace tablespace_name including contents;

8) 重建表空间及回滚段

9) svrmgrl> alter system disable restricted session;

10) 修改init.ora文件

2、数据库未完全关闭(数据库崩溃或使用shutdown abort命令关闭数据库)

1) Oracle表空间恢复备份

2) svrmgrl> startup mount

3) svrmgrl> select file#,name,status from v$datafile; svrmgrl> alter database datafile filename online;

4) svrmgrl> select v1.group#,member,sequence#,first_change# > from v$log v1,v$logfile v2 > where v1.group#=v2.group#;

5) svrmgrl> select file#,change# from v$recover_file;

6) svrmgrl> recover datafile filename;

7) svrmgrl> alter database open;

3、数据库处于打开状态

1) 删除回滚段和表空间

2) 重建表空间和回滚段

五。控制文件恢复

1.所有的控制文件均被破坏

将备份的控制文件拷贝至原目录下 对于raw device,则：

dd if=con.bak f=/dev/rdrd/drd1 seek=128

2.并非所有的控制文件均被破坏

Oracle删除用户和表空间 第8篇

racle 使用时间长了， 新增了许多user 和tablespace. 需要清理一下

对于单个user和tablespace 来说， 可以使用如下命令来完成，

步骤一：删除user

drop user ×× cascade

说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。

步骤二： 删除tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

但是，因为是供开发环境来使用的db, 需要清理的user 和 table space 很多。

思路：

Export出DB中所有的user和tablespace, 筛选出系统的和有用的tablespace,把有用的信息load到一张表中去。

然后写例程循环，把不在有用表的tablespace删掉

1. select username,default_tablespace from dba_users;

2.

create table MTUSEFULSPACE

(

ID Number(4) NOT NULL PRIMARY KEY,

USERNAME varchar2(30),

TABLESPACENAME varchar2(60),

OWNERNAME varchar2(30)

);

3.

declare icount number(2);

tempspace varchar2(60);

begin

for curTable in (select username as allusr,default_tablespace as alltblspace from dba_users)

loop

tempspace :=curTable.alltblspace;

dbms_output.put_line(tempspace);

select count(TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace;

if icount=0 then

DROP TABLESPACE tempspace INCLUDING CONTENTS AND DATAFILES;

end if;

commit;

end loop;

end;

执行后会报如下错误

ORA-06550: 第 10 行, 第 5 列:

PLS-00103: 出现符号 “DROP”在需要下列之一时：

begin case declare exit

for goto if loop mod null pragma raise return select update

while with

<<

close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe

06550. 00000 -“line %s, column %s:n%s”

*Cause:Usually a PL/SQL compilation error.

*Action:

好像是被锁了，

。

没办法，例程不能写，就只能组出语句执行了。

把需要删除的user, tablespace 导出到Excel. 使用CONCATENATE 组出SQL.

贴到SQLdevelop 批量执行。

整个删除会比较耗时间， 100多个user.用了12个小时左右。

如要找datafile的具体位置，可以使用

select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;

SQL code

--删除空的表空间，但是不包含物理文件

drop tablespace tablespace_name;

--删除非空表空间，但是不包含物理文件

drop tablespace tablespace_name including contents;

--删除空表空间，包含物理文件

drop tablespace tablespace_name including datafiles;

--删除非空表空间，包含物理文件

drop tablespace tablespace_name including contents and datafiles;

--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS

oracle创建临时表 第9篇

试验目的：全面掌握表空间和数据文件的创建、管理过程 试验内容：

1、创建表空间、增加数据文件

2、管理表空间和数据文件的的各种操作 试验步骤：

1、掌握表空间和数据文件相关的概念

1)oracle默认的表空间

系统默认的表空间:SYSTEM、SYSAUX、UNDOTBS1、EMP、EXAMPLE、USERS 2)系统表空间

SYSTEM、SYSAUX是系统表空间，SYSTEM用于存放oracle数据字典的结构和内部元数据。

SYSAUX是SYSTEM的辅助表空间用于存放个模式（用户）的对象元数据。

3)非系统表空间

由DBA根据需要创建，存放用户自己的数据，也可以包含临时数据以及UNDO数据。

2、创建表空间

1）创建表空间的语法

CREATE [smallfile|bigfile] TABLESPACE tablespace_name DATAFILE ‘/path/filename’ SIZE [k|m] REUSE

[‘/path/filename’ SIZE [k|m] REUSE]

[AUTOEXTEND [ON|OFF] NEXT [k|m] MAXSIZE [UNLIMITED|[K|M]]] [ONLINE|OFFLINE] [LOGGING|NOLOGGING] [PERMANENT|TEMPORARY] [DICTIONARY|LOCAL]

2）定义表空间名字及属性

创建两个表空间ts_test1、ts_test2，本地化管理(Local,默认)、永久表空间（permantent，默认）

3）定义数据文件名字、路径、初始大小、自动扩展等属性

数据文件data_test1、data_test2 路径：c:disk1data_test1.dbf c:disk2data_test2

4)创建脚本：

a、创建本地永久表空间

//创建本地永久表空间ts_test1和ts_test2，默认为永久和本地

SQL>create tablespace ts_test1 datafile ‘c:disk1data_file1.dbf’ size=50m reuse autoextend on next 10m;

SQL>create tablespace ts_test2 datafile ‘c:disk2data_file2.dbf’ size=50m reuse autoextend on next 10m maxsize unlimited;查看一下创建完成的结果

//查看dba_tablespaces结构

SQL>desc dba_tablespaces;

//查看字典中的所有表空间情况,extent_management值为local时为本地表空间 SQL>select tablespace_name,status,extent_management,content from dba_tablespaces;//查看创建表空间时创建的数据文件，首先查看dba_data_files字典结构 SQL>desc dba_data_files;//查看数据文件情况 SQL>set linesize 120 SQL>col file_name for a20 SQL>col file_id for a20 SQL>col tablespace_name a30 SQL>col online_status for a20 SQL>select file_name,file_id,tablespace_name,online_status from dba_data_files;

b、创建还原表空间和临时表空间

//创建还原表空间

SQL>create undo tablespace test_undo datafile ‘c:disk3test_undo’ size 20m;

//查看创建结果,contents为表空间内容类型，如undo

SQL>select tablespace_name,contents from dba_tablespaces;

c、创建临时表空间

//创建临时表空间

SQL>create temporary tablespace test_temp

tempfile ‘c:disk4test_temp.dbf’ size 20m extent management local;

//查询创建结果

SQL>select tablespace_name,extent_management,contents from dba_tablespaces;d、增加数据文件

//增加数据文件

SQL>alter tablespace ts_test1 add datafile ‘c:disk1ts_test11.dbf’ size 10m;

//查看增加数据文件的结果

SQL>col file_name for a30

SQL>col tablespace_name for a30 SQL>select file_name,tablespace_name from dba_data_files;

3、管理表空间的各种操作

1)改变表空间的读写状态

//将表空间设为只读

//这时表空间应为online状态，表空间不能包含回滚段，不能是归档模式

//查看数据库日志模式

SQL>archive log list;//查看表空间contents

SQL>select tablespace_name,contents from dba_tablespaces where tablespace_name like‘TS_%’;

//修改为只读

SQL>alter tablespace ts_test1 read only;2）表空间脱机离线、在线连接

SQL>alter tablespace ts_test1 offline;

SQL>alter tablespace ts_test1 online；

//查看表空间状态

SQL>select tablespace_name,status from dba_tablespaces;3)修改表空间名称 //查询当前表空间名称和所包含的数据文件

SQL>select file_name,tablespace_name from dba_data_files;//确认表空间处在online状态

SQL>select tablespace_name,status from dba_tablespaces;//表空间改名

SQL>alter tablespace ts_test2 rename to ts_test22;//查询修改结果

SQL>Col file_name for a30;SQL>col tablespace_name for a30;SQL>select file_name,tablespace_name from dba_data_files;4)设置默认表空间

SQL>alter database default tablespace users //设置默认临时表空间

SQL>alter database default temporary tablespace temp;5)删除表空间

SQL>drop tablespace ts_test2;//仅删除表空间信息不删除表空间数据文件 SQL>drop tablespace ts_test2 includng contents;//包括数据文件全部删除 查看删除后的结果情况

SQL>select file_name,tablespace_name from dba_data_files;6)切换undo表空间

SQL>alter system set undo_tablespace =undotbs02;7)删除undo表空间

要删除正在使用的undo表空间，首先切换undo表空间后在进行删除 例如删除undost01 SQL>alter system set undo_tablespace=undost02;SQL>drop tablespace undost01;8)查询undo表空间信息

SQL>show parameter undo_tablespace;

4、管理数据文件的各种操作 1)在表空间中新增一个数据文件

SQL>alter tablespace ts_test1 add datafile ‘c:dsk1ta_test111.dbf’ size 50m;2）删除表空间中无数据的数据文件 第一种语法

SQL>alter database datafile ‘c:disk1ts_test1.dbf’ offline;//数据文件脱机 SQL>alter tablespace ts_test1 drop datafile ‘c:disk1ts_test111.dbf’;第二种语法：

SQL>alter database datafile ‘mytb.dbf’ offline drop;3）数据文件自动扩展设置

SQL>alter tablespace ts_test1 datafile ‘c:disk1ts_test1.dbf’ autoextend on next 10m maxsize 500m;4）重置数据文件大小，收回或扩数据文件空间

SQL>alter tablespace ts_test1 datafile ‘c:disk1ts_test1.dbf’ resize 45m;

查询重置数据文件大小后的结果

SQL>col file_name for a30;

SQL>col tablespace_name for a30;

SQL>select file_name,tablespace_name,bytes/(1024*1024)from dba_data_files;5）移动数据文件

有两条语句可以实现对数据文件的移动：

a、alter tablespace tablespace_name rename datafile ‘/path/file_name1’

to ‘/path/file_name_new’;

a这条语句主要适合用户表空间，在执行这条语句时表空间必须脱机才能执行。SQL>alter tablesapace ts_test2 offline;//使表空间ts_test2脱机

SQL>host copy c:disk2ts_test2.dbf c:disk1 //将数据文件拷贝到新的位置

SQL>alter tablespace rename datafile ‘c:disk2ts_test2.dbf’ to ‘c:disk1ts_test1.dbf’;b、alter database db_name rename file ‘/path/file_name1’ to ‘/path/file_name_new’;

这条语句使用于系统表空间及不能置为脱机的表空间中的数据文件。在使用该语句时，数据库必须在加载(mount)的状态且目标数据文件必须存在，因为该语句只是修改文件中指向数据文件的指针(地址)。SQL>shutdown immediate;SQL>host copy c:oracleproduct10.2.0oradatasystem01.dbf c:disk1 SQL>startup mount;SQL>alter database rename file ‘c:oracleproduct10.2.0oradataorclsystem01.dbf’ to ‘c:disk1system01.dbf’;SQL>alter database open;查询移动后的结果