如何管理table 使用的空间
1. 如何发现你的table 有fragmentation:
(a) analyzed table tablename compute statistics;
---- you may analyze all the tables under that user schema)
(b) select table_name, round(avg_row_len*num_rows/1024/1024, 0) data_size from user_tables;
---- 你可以知道表中大约的data size (单位: M)
(c) select segment_name, round(blocks*8/1024, 0) table_size from user_segments where segment_type='TABLE';
---- 你可以知道你的表的实际size (单位: M)
如果你比较同一个表的(c) table_size 远远大于(b) data_size，你的表可能有fragmentation。
也许有没有用的extents 在high water mark 上面，如果是这种情况，可以直接deallocate unused space:
alter table tablename deallocate keep 0;
2. 如果你确定是fragmentation存于表中，有下面的方法解决。
(a) create a temp table, copy all the data from fragmented table to temp table, truncate the fragmented table, copy data back, then drop temp table:
---- this way, you really don't need to recrate any indexes, constrains etc. but if there are columns in the table are other tables' reference foreign key, and the table has some procedures/tiggers, you have to disable them all before truncate the table. And, you also need enable those foreign keys, procedures and triggers.
(b) alter table tablename move tablespace B; (tablespace B is different than original tablespace, you may move back from tablespace B to original one, run table move commend again)
----you need rebuild all indexes on the table
(c) export/import
---- 这个方法不用多说了吧。
请有好方法的朋友跟贴。谢谢。
作者：笨笨熊 时间：02-03-13 22:16
如何发现你的table space有fragmentation:
ttitle 'Tablespace Fragmentation Information'
SELECT SUBSTR(ts.name,1,10) TSPACE,
tf.blocks BLOCKS,
SUM(f.length) FREE,
COUNT(*) PIECES,
MAX(f.length) BIGGEST,
MIN(f.length) SMALLEST,
ROUND(AVG(f.length)) AVERAGE
FROM sys.fet$ F,
sys.file$ TF,
sys.ts$ TS
WHERE ts.ts# = f.ts#
AND ts.ts# = tf.ts#
GROUP BY ts.name, tf.blocks;
解决的方法我同意你的。
作者：ArthurXu 时间：02-03-13 23:36
try this one
column table_name format a40
column degree heading " DEGREE"
column density heading " DATA|DENSITY"
column new_free format 99 heading "SUGGEST|PCTFREE"
column new_used format 99 heading "SUGGEST|PCTUSED"
column reads_wasted format 999999 heading "MBREADS|TO SAVE"
select /*+ ordered */
u.name ||'.'|| o.name table_name,
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree, 1)), 7) degree,
substr(
to_char(
100 * t.rowcnt / (
floor((p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11))
* t.blkcnt
),
'999.00'
),
2
) ||
'%' density,
1 new_free,
99 - ceil(
( 100 * ( p.value - 66 - t.initrans * 24 -
greatest(
floor(
(p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11)
) - 1,
1
) * greatest(t.avgrln + 2, 11)
)
/
(p.value - 66 - t.initrans * 24)
)
) new_used,
ceil(
( t.blkcnt - t.rowcnt /
floor((p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11))
) / m.value
) reads_wasted
from
sys.tab$ t,
( select
value
from
sys.v_$parameter
where
name = 'db_file_multiblock_read_count'
) m,
sys.obj$ o,
sys.user$ u,
(select value from sys.v_$parameter where name = 'db_block_size') p
where
t.tab# is null and
t.blkcnt > m.value and
t.chncnt = 0 and
t.avgspc > t.avgrln and
ceil(
( t.blkcnt - t.rowcnt /
floor((p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11))
) / m.value
) > 0 and
o.obj# = t.obj# and
o.owner# != 0 and
u.user# = o.owner#
order by
5 desc, 2
/
作者：Yong Huang 时间：02-03-14 15:08
Re: 如何管理table 使用的空间
Comments on the following suggestion.
1. It's only available in Oracle8i.
2. Tablespace B can be the same as the original.
3. In fact, the tablespace clause can be omitted entirely.
4. Do remember to rebuild indexes as snowwhite2000 suggested. Indexes become unusable after alter table move, even if the indexes are on columns with no data.
Here's my screen:
SQL> alter table t move;
Table altered.
SQL> select tablespace_name from user_tables where table_name = 'T';
TABLESPACE_NAME
------------------------------
SYSTEM
SQL> alter table t move tablespace system;
Table altered.
Note: don't create a table in SYSTEM like I do here.
Yong Huang
quote:
最初由 snowhite2000 发布
(b) alter table tablename move tablespace B; (tablespace B is different than original tablespace, you may move back from tablespace B to original one, run table move commend again)
----you need rebuild all indexes on the table
作者：Yong Huang 时间：02-03-14 15:22
This query may fail to find anything if the tablespace is locally managed.
Yong Huang
quote:
最初由 笨笨熊 发布
SELECT SUBSTR(ts.name,1,10) TSPACE,
tf.blocks BLOCKS,
SUM(f.length) FREE,
COUNT(*) PIECES,
MAX(f.length) BIGGEST,
MIN(f.length) SMALLEST,
ROUND(AVG(f.length)) AVERAGE
FROM sys.fet$ F,
sys.file$ TF,
sys.ts$ TS
WHERE ts.ts# = f.ts#
AND ts.ts# = tf.ts#
GROUP BY ts.name, tf.blocks;
作者：flyingknife 时间：02-03-14 22:02
这种情况有没有办法？
如果ORACLE是8I以下的版本，而数据库需要运行在7*24的模式下，有什么办法可以清理碎片呢？
作者：chao_ping 时间：02-03-15 03:59
quest的产品live-reorg可以满足你的需求。
不过应该会非常的昂贵的
作者：oracledba 时间：02-03-16 06:19
如果发现有碎片。。。
alter tablespace temp coalesce;
这样做可以嘛？
作者：chao_ping 时间：02-03-16 06:40
这个语句帮助不大。只能够融合本来就是相邻的碎片。
原来yong huang讲过这个问题，就是表空间的碎片分成两种，一个是碎片都是相邻的，一个是被某个extent分离的。
第二种情况才是真正的碎片。
而你的语句子能够处理第一仲情况。而第一仲情况实际上是你把pctincrease=1也可以做到的。或者在oracle allocate new extent的时候也能自动合并的。
表空间的碎片重组还是比较麻烦的，如果大家有兴趣，可以专题讨论一下。
作者：snowhite2000 时间：02-03-17 01:43
如何发现现有的table storage parameter 不合适
不合适的storage parameter 会导致 fragmentation。如何发现不合适的parameter?
这是一种方法：
运行这个 script:
代码:
------------------------------------------------
ttitle -
center 'Segment Storage Summary' skip 2
col ownr format a8 heading 'Owner' justify c
col name format a28 heading 'Segment Name' justify c
col type format a8 heading 'Type' justify c trunc
col hfil format 9,990 heading 'Header|File' justify c
col hblk format 99,990 heading 'Header|Block' justify c
col exts format 9,990 heading 'Extents' justify c
col blks format 999,990 heading 'Blocks' justify c
break on ownr skip 1
select
owner ownr,
segment_name name,
segment_type type,
header_file hfil,
header_block hblk,
extents exts,
blocks blks
from
dba_segments
where
owner like upper('&ownr')
and
segment_name like upper('&segt')
/
undef ownr
undef segt
set verify on
----------------------------------------------------
我在一个有问题的database 下面的运行结果：
代码:
Segment Storage Summary
Header Header
Owner Segment Name Type File Block Extents Blocks
-------- ---------------------------- -------- ------ ------- ------- --------
CLINICAL ACCOUNT_INFO TABLE 18 2 236 4,720
ADDRESS TABLE 19 2 1,359 27,465
ALLERGY_INFO TABLE 27 2 1 20
AUDIT_EVENT TABLE 20 2 1 80
CONTACT_INFO TABLE 21 2 164 3,280
CONTACT_TYPE_DESC TABLE 27 22 1 20
DOCTOR_HOSPITAL_ASSOCIAT TABLE 27 42 1 50
DOCTOR_INFO TABLE 27 92 1 35
DOCTOR_PATIENT_RELN TABLE 22 2 158 3,160
DOC_PAT_RELN_CODE TABLE 27 127 1 20
DRUG_INFO TABLE 27 147 1 80
ERROR_PROCEDURES TABLE 22 22 624 12,620
ERROR_TXN TABLE 8 2 1,691 108,480
ERROR_TXN_DESC TABLE 27 227 1 20
GUARANTOR TABLE 22 182 154 3,080
HOSPITAL_INFO TABLE 27 247 1 20
INSURANCE TABLE 23 2 523 10,460
LAB_RAD_ORDER TABLE 24 2 2,087 42,145
LAB_RAD_ORDER_RESULT TABLE 25 2 560 11,200
LAB_RAD_ORDER_STATUS TABLE 27 267 1 20
LAB_RAD_RESULT_DESC TABLE 26 2 9,013 169,442
MEDICAL_RECORD TABLE 24 22 49
用下面 statement 检查：
代码:
SQL> select segment_name, next_extent/1024 from user_segments;
SEGMENT_NAME NEXT_EXTENT/1024
----------------------------------- ----------------
ACCOUNT_INFO 128
ADDRESS 128
ALLERGY_INFO 128
AUDIT_EVENT 128
CONTACT_INFO 128
CONTACT_TYPE_DESC 128
DOCTOR_HOSPITAL_ASSOCIAT 128
DOCTOR_INFO 128
DOCTOR_PATIENT_RELN 128
DOC_PAT_RELN_CODE 128
DRUG_INFO 128
ERROR_PROCEDURES 128
ERROR_TXN 512
ERROR_TXN_DESC 128
GUARANTOR 128
HOSPITAL_INFO 128
INSURANCE 128
LAB_RAD_ORDER 512
LAB_RAD_ORDER_RESULT 128
LAB_RAD_ORDER_STATUS 128
LAB_RAD_RESULT_DESC 128
MEDICAL_RECORD
注意到了：
LAB_RAD_RESULT_DESC TABLE 26 2 9,013 169,442
这行的结果，169442个blocks 来自9013个extents，产生了overextended segments 的问题。因此解决办法就是将 next_extent 的size 加大，我将原来的128K 加大至 512K。等待下一次 database downtime 时， reorginize 这些 tables。再查看结果。
同样的问题，可以使用OEM diagnostics Pack 中的 tablespace map 工具，analyze tablespace 之后 report 有显示有问题的 tables/indexes.
请感兴趣的朋友贴出您的建议，谢谢。
作者：iHero 时间：02-03-18 00:13
使用OEM.会使你分析更加的方便:
以下是我分析结果和例子:
使用OEM 管理数据库是很方便的:
由TABLEASPCE MAP ->选则表空间的分析
2002-1-22 表空间的分析报告结果
TABLESPACE ANALYSIS REPORT
类型 段名 预警状态
--------------------------------------------------------------------------------
表 iHero.iHero_RESUME_BAS ALERT
ALERT: 过分扩展的段。 已分配的区数: 2350。
WARNING: 过多的链式行。 7.01% 的行被链接/移植。平均行长度 (字节): 311.
表 iHero.iHero_RESUME_EDU ALERT
ALERT: 过分扩展的段。 已分配的区数: 1091
EXCESSIVE ROW CHAINING
在两种情形下，表或表分区中某一行的数据可能太多而无法装入单个数据块。因此产生了行碎片。
在第一种情形中，第一次插入该行时，由于行过大而无法装入一个数据块。Oracle Server 使用为该段保留的一连串数据块存储行数据。链式行常常在使用较大的行时出现，如包含一列长数据类型的行。在这种情形下，如果不使用由 DB_BLOCK_SIZE 初始化参数定义的较大的数据库块大小，行延续是不可避免的。
但在第二种情形下，起初能够装入一个数据块的某行被更新，使得整行长度增加，并且数据块的空闲空间已经完全被填满。在这种情形下，Oracle Server 将整行数据移植到一个新的数据块 (假设这一整行可以装入新块)。Oracle Server 会保留移植行的原始行，使其引用包含移植行的新块。
在续行或移植行时，与该行相关联的输入/输出性能降低，因为 Oracle Server 必须扫描一个以上的数据块以检索该行的信息。
有两种解决行移植问题的方法。重建表或表分区可以避免在重建过程中，随着行被紧密地装入各数据块而产生行碎片。但是，如果未对表或表分区进行其它更改 (只是修复移植行)，将集中修复有问题的行，而不是完全重建。
注：如果行在更新中可能增大，请考虑增加段的 PCTFREE 值.
我的分析了.自己的数据库db_block=8K 足够大. 而且行的size不是很大, 造成行移植的主要原因是数据更新很多, 原始欲留的不是多.
所以重新建立表更该表的参数加大PCTFREE值.导入数据.
1. creat table temp as select * from tablename
2. DROP TABLE NAME .
3. 重新建表(更改表的参数)
4. insert into tablename select * from temp . 导入数据.
可以用INSERT /*APPEND*/ INTO TALBENAME SELECT * FORM TEMP. 避免LOGGING. Direct load insert .
已分配的区数太多.
分析原因可能是表空间的原始参数太小. NEXT 45k .
1.调整TABLESPACE NEXT 大小. ALTER TABLESPACE TABLESPACENAME MINIMUM EXTENT 54k
2.exp 导出数据.
3.执行imp命令时使用indexfile参数：
imp userid=scott/tiger file=emp.dmp indexfile=emp.sql Oracle把表和索引的创建信息写到指定的文件，而不是把数据写回。
对它进行编辑，去除"REM"等信息，找到Initial参数，根据需要改变它。
4.在SQL*plus中执行emp.sql。
5.IMP 装入数据：Imp userid=scott/tiger ignore=y file=emp.dmp
同样也可以不用SQL 文件建立表. 直接IMP. 在修改表的存储参数. ALTER TABLE TABLENAME STORAGE ( NEXT 540k )
作者：ligengocp 时间：02-03-20 23:44
1 什么是Migrated and Chained Rows
If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block. This is called chaining a row. Rows can also be chained when they are inserted.
Dynamic space management, especially migration and chaining, is detrimental to performance:
UPDATE statements that cause migration and chaining perform poorly.
Queries that select migrated or chained rows must perform more I/O.
2 如何查看Migrated and Chained Rows
2。1 Use the ANALYZE statement to collect information about migrated and chained rows. For example:
ANALYZE TABLE order_hist LIST CHAINED ROWS;
Query the output table:
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96
The output lists all rows that are either migrated or chained.
If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows with the following steps:
Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:
CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist
SELECT *
FROM int_order_hist;
Drop the intermediate table:
DROP TABLE int_order_history;
Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
2.2 在V$sysstat中查看TABlE FETCH CONTINUED ROW记录
3. 如何查避免Migrated and Chained Rows
3.1
加大表参数PCTFREE，让块有更多的空间去容纳记录的增长
3.2
加大DB_BLOCK_SIZE,可以容纳较大的字录
**Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns. **
作者：ligengocp 时间：02-03-21 00:04
1 什么是Migrated and Chained Rows
If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block. This is called chaining a row. Rows can also be chained when they are inserted.
Dynamic space management, especially migration and chaining, is detrimental to performance:
UPDATE statements that cause migration and chaining perform poorly.
Queries that select migrated or chained rows must perform more I/O.
2 如何查看Migrated and Chained Rows
2。1 使用Analyze with LIST CHAINED ROWS,分析有哪些Migrated and Chained Rows，其结果可以输入到表CHAINED_ROWS中，脚本/ORACLE_HOME/RDBMS/ADMIN/UTLCHAIN.SQL如下：
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
Use the ANALYZE statement to collect information about migrated and chained rows. For example:
ANALYZE TABLE order_hist LIST CHAINED ROWS;
Query the output table:
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96
The output lists all rows that are either migrated or chained.
If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows with the following steps:
Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:
CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist
SELECT *
FROM int_order_hist;
Drop the intermediate table:
DROP TABLE int_order_history;
Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
2.2 在V$sysstat中查看TABlE FETCH CONTINUED ROW记录
3. 如何查避免Migrated and Chained Rows
3.1
加大表参数PCTFREE，让块有更多的空间去容纳记录的增长
3.2
加大DB_BLOCK_SIZE,可以容纳较大的字录
**Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns. **
2537
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
