alter tablespace temp coalesce; 清理临时表空间问题

zlovelan 2003-10-09 11:05:49

临时表空间随着使用排序字段等的使用，占用的比例越来越大，该如何不重起数据库下，释放临时表空间。看到有文章介绍：
SELECT username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr
然后可以用alter system kill session杀掉那些session
最后,coalesce一下就可以了
eg:alter tablespace temp coalesce;

但是执行后提示：
ORA-03217: 变更 TEMPORARY TABLESPACE 无效的选项
请指教。

...全文

3195 16 打赏收藏转发到动态举报

写回复

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

zlovelan 2003-10-14

打赏
举报

回复

我还是重起数据库来解决吧。

yangqingdelphi 2003-10-13

打赏
举报

回复

我查了一本书.机械工业出版社的Oracle8i的数据库管理员手册中的361页中讲到使用LMT管理方式不需要合并盘区.
因为使用局部管理的表空间，不更新字典，不产生回滚活动。LMT的表空间自动跟踪相邻的自由空间，因此不需要合并盘区.
但我和你有一样的困惑，我的表空间也是一样，全部占用了.

zlovelan 2003-10-12

打赏
举报

回复

请教：yangqingdelphi() ，如果方便请教你,lmt管理方式 & dict方式.或者推荐一本书。

to: zzzsssccc(籍秋风) ,我去试试。把标记kill的会话杀掉。对吧。

再次感谢2位，可能我这问题另类。但很困扰我，因为temp中的空间不够的时候，用编写的程序往数据库中添加文件（blob）时候，很慢。

yangqingdelphi 2003-10-11

打赏
举报

回复

我想你可能管理临时表空间时用的LMT技术.
可以看一下你的临时表创建的sql语句。
贴出来::

zlovelan 2003-10-11

打赏
举报

回复

期待。。。

三杯倒 2003-10-11

打赏
举报

回复

那可能还是session没有杀掉，只是标记kill了

在pl/sql dev中tools-->sessions可以看到

yangqingdelphi 2003-10-11

打赏
举报

回复

to:zlovelan
你的是lmt管理方式，非dict方式.

zlovelan 2003-10-11

打赏
举报

回复

to:zzzsssccc(籍秋风) 我把temp表空间脱机，oracle企业管理器不让脱机，提示：ora-03217 TempOrary TableSpace 无效的选项。

zlovelan 2003-10-11

打赏
举报

回复

好，我去看看

三杯倒 2003-10-11

打赏
举报

回复

是把数据文件或表空间（记不清了）OFFLINE，不是关闭数据库

zlovelan 2003-10-11

打赏
举报

回复

to: yangqingdelphi()
CREATE
TEMPORARY TABLESPACE "TEMP" TEMPFILE
'D:\ORACLE\ORADATA\PRODUCT\TEMP01.DBF' SIZE 176M REUSE
AUTOEXTEND
ON NEXT 10240K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1024K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"

to :zzzsssccc(籍秋风) 如果我把数据库关闭，启动，temp表空间就自动清空了

三杯倒 2003-10-11

打赏
举报

回复

好像offline,然后online就可以

coalesce试过，有时候不行

zlovelan 2003-10-10

打赏
举报

回复

不行，使用 alter tablespace temp coalesce; 命令问题如故。请帮忙。

bluesky888 2003-10-10

打赏
举报

回复

你用SYS用户进去试试。

zlovelan 2003-10-10

打赏
举报

回复

up

yangjuanli 2003-10-09

打赏
举报

回复

友情UP！

影响数据库性能的一大因素 -- 数据库碎片

一、重建索引的前提 1、表上频繁发生update,delete操作； 2、表上发生了alter table ..move操作（move操作导致了rowid变化）。二、重建索引的标准 1、索引重建是否有必要，一般看索引是否倾斜的严重，是否浪费了空间，那应该如何才可以判断索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析（如下）： SQL>Analyze index index_name validate structure; 2、在执行步骤1的session中查询index_stats表，不要到别的session去查询。 SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats; 说明：当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合，该索引考虑重建。举例： (t_gl_assistbalance 26 万多条信息 ) SQL> select count(*) from t_gl_assistbalance ; 输出结果： COUNT(*) ---------- 265788 SQL> Analyze index IX_GL_ASSTBAL_1 validate structure; Index analyzed SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; 输出结果： HEIGHT DEL_LF_ROWS/LF_ROWS ---------- ------------------- 4 1 三、重建索引的方式 1、drop 原来的索引，然后再创建索引; 举例：删除索引：drop index IX_PM_USERGROUP; 创建索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid); 说明：此方式耗时间，无法在24*7环境中实现，不建议使用。 2 、直接重建：举例： alter index indexname rebuild; 或alter index indexname rebuild online; 说明：此方式比较快，可以在24*7环境中实现，建议使用此方式。四、alter index rebuild 内部过程和注意点 alter index rebuild 和alter index rebuil online的区别 1、扫描方式不同 1.1、Rebuild以index fast full scan（or table full scan）方式读取原索引中的数据来构建一个新的索引，有排序的操作; 1.2、rebuild online 执行表扫描获取数据，有排序的操作; 说明：Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost) 举例1 SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| | --------------------------------------------------------------------- 举例2 SQL> explain plan for alter index idx_policy_id rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | | | 2 | SORT CREATE INDEX | | 2072K| 9M| | | 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 | 举例3 ( 注意和举例1 比较 ) Rebuil online 方式： SQL> explain plan for alter index idx_policy_id2 rebuild online; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 | 2 、rebuild 会阻塞 dml 操作，rebuild online 不会阻塞 dml 操作； 3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表，所有 rebuild online 时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后 drop 掉旧的索引，rebuild online 就完成了。注意点： 1、执行rebuild操作时，需要检查表空间是否足够； 2、虽然说rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行； Rebuild操作会产生大量redo log ；五、重建分区表上的分区索引重建分区索引方法： Alter index indexname rebuild partition paritionname tablespace tablespacename; Alter index indexname rebuild subpartition partitioname tablespace tablespacename; Partition name 可以从user_ind_partitions查找 Tablepace 参数允许alter index操作更改索引的存储空间；六、索引状态描述在数据字典中查看索引状态，发现有三种： valid:当前索引有效 N/A :分区索引有效 unusable:索引失效七、术语 1、高基数：简单理解就是表中列的不同值多。 2、低基数：建单理解就是表中的列的不同值少。 3、以删除的叶节点数量：指得是数据行的delete操作从逻辑上删除的索引节点的数量，要记住oracle在删除数据行后，将 “ 死 “ 节点保留在索引中，这样做可以加快sql删除操作的速度，因此oracle删除数据行后可以不必重新平衡索引。 4、索引高度：索引高度是指由于数据行的插入操作而产生的索引层数，当表中添加大量数据时，oracle将生成索引的新层次以适应加入的数据行，因此，oracle索引可能有4层，但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目，而具备4层或是更多层的需要重建。 5、每次索引访问的读取数：是指利用索引读取一数据行时所需要的逻辑I/O操作数，逻辑读取不必是物理读取，因为索引的许多内容已经保存在数据缓冲区，然而，任何数据大于10的索引都需要重建。 6、什么时候重建呢？察看 dba_indexes 中的 blevel 。这列是说明索引从根块到叶快的级别，或是深度。如果级别大于等于4。则需要重建，如下：Select index_name,blevel from dba_indexes where blevel>=4. 另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时，也应当重建，如下 SQL>analyze index index_name validate structure SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’ 就能看到是否这个索引被删除的百分比。 7、什么样的重建方式更好？（1）、建索引的办法： 1.1、删除并从头开始建立索引。 1.2 、使用 alter index index_name rebuild 命令重建索引。 1.3 、使用 alter index index_name coalesce 命令重建索引。（2）、下面讨论一下这三种方法的优缺点： 2.1、删除并从头开始建索引：方法是最慢的，最耗时的。一般不建议。 2.2、Alter index index_name rebuild 快速重建索引的一种有效的办法，因为使用现有索引项来重建新索引，如果客户操作时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题，alter index index_name rebuild online。但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。 Alter index index_name rebuild tablespace tablespace_name 。这个命令的执行步骤如下：首先，逐一读取现有索引，以获取索引的关键字。其次，按新的结构填写临时数据段。最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。需要注意的是alter index index_name rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行。 2.3、alter index index_name coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来将一个索引转移到其他表空间。八、其他 1、truncate 分区操作和truncate 普通表的区别？ 1.1、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响； 1.2、Truncate 分区操作不会释放全局索引中的空间，而truncate 普通表会释放索引所占空间； 2、rename 表名操作对索引没有影响，因为rename操作只是更改了数据字典，表中数据行的rowid并没有发生变化总结： 1、判断是否需要重建索引： SQL>analyze index index_name validate structure; SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; （或 Select index_name,blevel from dba_indexes where blevel>=4 ）; 说明：当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合，该索引考虑重建； 2 、重建索引方法：方法一、 Alter index index_name rebuild tablespace tablespace_name; 优点：是快速重建索引的一种有效的办法，可以用来将一个索引移到新的表空间。缺点：重建期间需要额外空间。方法二、 alter index index_name coalesce; 优点：重建期间不需要额外空间。缺点：coalesce选项不能用来将一个索引转移到其他表空间。

Oracle作爲一種大型資料庫，廣泛應用於金融、郵電、電力、民航等資料吞吐量巨大，電腦網路廣泛普及的重要部門。對於系統管理員來講，如何保證網路穩定運行，如何提高資料庫性能，使其更加安全高效，就顯得尤爲重要。作爲影響資料庫性能的一大因素--資料庫碎片，應當引起DBA的足夠重視，及時發現並整理碎片乃是DBA一項基本維護內容。

包含orcale的所有命令集合，每一个步骤都描叙得很详细

从网上找的资源，添加了一些我认为经常会用到的命令

Oracle 高级技术

3,499

社区成员

18,709

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章