管理table使用的空间的方法
管理table使用的空间的方法
作者：snowhite2000时间：02-03-13 20:21
如何管理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:
我在一个有问题的database 下面的运行结果：代码:
------------------------------------------------ ttitle - center'Segment Storage Summary'skip 2 col ownr format a8heading 'Owner'justify c col name format a28heading 'Segment Name' justify c col type format a8heading 'Type'justify c trunc col hfil format9,990 heading 'Header|File'justify c col hblk format99,990 heading 'Header|Block' justify c col exts format9,990 heading 'Extents'justify c col blks format 999,990 heading 'Blocks'justify c break on ownr skip 1 select ownerownr, segment_namename, segment_typetype, header_filehfil, header_blockhblk, extentsexts, blocksblks from dba_segments where owner like upper('&ownr') and segment_name like upper('&segt') / undef ownr undef segt set verify on ----------------------------------------------------
用下面 statement 检查：代码:
Segment Storage Summary Header Header OwnerSegment NameTypeFileBlockExtentsBlocks -------- ---------------------------- -------- ------ ------- ------- -------- CLINICAL ACCOUNT_INFOTABLE1822364,720 ADDRESSTABLE1921,35927,465 ALLERGY_INFOTABLE272120 AUDIT_EVENTTABLE202180 CONTACT_INFOTABLE2121643,280 CONTACT_TYPE_DESCTABLE2722120 DOCTOR_HOSPITAL_ASSOCIATTABLE2742150 DOCTOR_INFOTABLE2792135 DOCTOR_PATIENT_RELNTABLE2221583,160 DOC_PAT_RELN_CODETABLE27127120 DRUG_INFOTABLE27147180 ERROR_PROCEDURESTABLE222262412,620 ERROR_TXNTABLE821,691108,480 ERROR_TXN_DESCTABLE27227120 GUARANTORTABLE221821543,080 HOSPITAL_INFOTABLE27247120 INSURANCETABLE23252310,460 LAB_RAD_ORDERTABLE2422,08742,145 LAB_RAD_ORDER_RESULTTABLE25256011,200 LAB_RAD_ORDER_STATUSTABLE27267120 LAB_RAD_RESULT_DESCTABLE2629,013169,442 MEDICAL_RECORDTABLE242249
注意到了： 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. 请感兴趣的朋友贴出您的建议，谢谢。代码:
SQL> select segment_name, next_extent/1024 from user_segments; SEGMENT_NAMENEXT_EXTENT/1024 ----------------------------------- ---------------- ACCOUNT_INFO128 ADDRESS128 ALLERGY_INFO128 AUDIT_EVENT128 CONTACT_INFO128 CONTACT_TYPE_DESC128 DOCTOR_HOSPITAL_ASSOCIAT128 DOCTOR_INFO128 DOCTOR_PATIENT_RELN128 DOC_PAT_RELN_CODE128 DRUG_INFO128 ERROR_PROCEDURES128 ERROR_TXN512 ERROR_TXN_DESC128 GUARANTOR128 HOSPITAL_INFO128 INSURANCE128 LAB_RAD_ORDER512 LAB_RAD_ORDER_RESULT128 LAB_RAD_ORDER_STATUS128 LAB_RAD_RESULT_DESC128 MEDICAL_RECORD
作者：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. **