[Tip] 如何管理table 使用的空间(置顶一周)

iHero

使用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

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

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. **

musicalzhu

OCP学无止境，看到大家如此热烈的讨论。。。。。
我真恨不得插上一嘴，可太菜：（

yannanqi

大家水平都挺高啊