oracle分析表操作

oracle分析表操作

 

oracle 分析表

 

  对于表的分析可以进行如下操作：

 

                                      1）验证表的的存储情况

 

                                      2）查看表的统计信息

 

                                      3）查找表中的链接记录和迁移记录

 

   1）验证表的存储情况，表存在于表空间中，表空间存在于数据文件中，那么可能由于软件bug的问题，导致表存在逻辑坏块，那么这个时候我们可以使用易做图yze table table_name validate structure;进行表逻辑块的验证，如果存在逻辑错误那么需要恢复。

 

    当需要验证表的存储结构是否出现逻辑块损坏的时候，那么我们需要invalid_rows这个表进行逻辑块信息的收集，这个表在数据库默认是没有的那么可以通过utlvalid.sql脚本进行自己创建,该脚本在$ORACLE_HOME/rdbms/admin目录下，该表的权限属于dba角色：

 

utlvalid.sql:

 

[plain] 
rem  
Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation  
Rem NAME  
REM    UTLVALID.SQL  
Rem  FUNCTION  
Rem    Creates the default table for storing the output of the  
Rem    易做图yze validate command on a partitioned table  
Rem  NOTES  
Rem  MODIFIED  
Rem     syeung     06/17/98 - add subpartition_name  
Rem     mmonajje   05/21/96 - Replace timestamp col name with 易做图yze_timestamp  
Rem     sbasu      05/07/96 - Remove echo setting  
Rem     ssamu      01/09/96 - new file utlvalid.sql  
Rem  
  
create table INVALID_ROWS (  
  owner_name         varchar2(30),  
  table_name         varchar2(30),  
  partition_name     varchar2(30),  
  subpartition_name  varchar2(30),  
  head_rowid         rowid,  
  易做图yze_timestamp  date  
);  

 

 

  测试如下：

 

eg：

 

[sql] 
SQL> @./utlvalid.sql  
  
Table created.  
  
SQL> conne rhys/root  
Connected.  
SQL> 易做图yze table emp validate structure;  
  
Table 易做图yzed.  
  
SQL> desc invalid_rows  
ERROR:  
ORA-04043: object invalid_rows does not exist  
  
  
SQL> select * from invalid_rows;  
select * from invalid_rows  
              *  
ERROR at line 1:  
ORA-00942: table or view does not exist  
  
  
SQL> conn sys/root as sysdba  
Connected.  
SQL> desc invalid_rows;  
 Name                                      Null?    Type  
 ----------------------------------------- -------- ----------------------------  
 OWNER_NAME                                         VARCHAR2(30)  
 TABLE_NAME                                         VARCHAR2(30)  
 PARTITION_NAME                                     VARCHAR2(30)  
 SUBPARTITION_NAME                                  VARCHAR2(30)  
 HEAD_ROWID                                         ROWID  
 ANALYZE_TIMESTAMP                                  DATE  
  
SQL> select * from invalid_rows;  
  
no rows selected  
  
SQL>   

 

 

           2）查看表的统计信息

 

  对于表的统计信息可以和user_tables,all_tables，dba_tables进行查看本表的数据块总数、空闲的数据块数目，平均空闲空间等信息：

 

   那么对于表的统计信息又分为对表进行完全扫描的统计信息和对部分表数据一个估算信息。对于完全扫描表统计信息我们可以使用易做图yze table table_name compute statistics;

 

BLOCKS                    NUMBER       Y                The number of used blocks in the table                                                          

EMPTY_BLOCKS              NUMBER       Y                The number of empty (never used) blocks in the table                                            

AVG_SPACE                 NUMBER       Y                The average available free space in the table

 

EG:

 

[sql] 
SQL> ANALYZE TABLE EMP COMPUTE STATISTICS ;  
  
Table 易做图yzed.  
  
SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_SPACE FROM USER_TABLES WHERE TABLE_NAME='EMP';  
  
    BLOCKS EMPTY_BLOCKS  AVG_SPACE  
---------- ------------ ----------  
         1            6       7586  
  
SQL>   

 

    对于表的部分扫描进行的表分析统计可以使用:易做图yze table table_name estimate statistics samp 20 [percent or rows]；命令进行统计收集：

 

eg：

 

[sql] 
SQL> select num_rows,blocks,empty_blocks,avg_space from all_tables where table_name='EMP';  
  
  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  
---------- ---------- ------------ ----------  
        11          1            6       7586  
        11          5            3       7977  
  
SQL>   
[sql] 
SQL> 易做图yze table emp estimate statistics sample 20 rows;  
  
Table 易做图yzed.  
  
SQL> select num_rows,blocks,empty_blocks,avg_space from all_tables where table_name='EMP';  
  
  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  
---------- ---------- ------------ ----------  
        11          1            6       7586  
        11          5            3       7977  
  
SQL>   

 

 

因为对表进行了两此统计所以显示了表变化后两次次统计表的信息变化；

 

eg：

 

[sql] 
SQL> 易做图yze table emp estimate statistics sample 20 percent;  
  
Table 易做图yzed.  
  
SQL> select num_rows,blocks,empty_blocks,avg_space from all_tables where table_name='EMP';  
  
  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  
---------- ---------- ------------ ----------  
        11          1            6       7586  
        11          5            3       7977  
  
SQL>

 

注意：当对表的统计sample大于50%的时候，会进行全表数据统计。

 

         3）表的链接记录和迁移记录

 

什么是表的链接记录：每个表是有记录组成的，那么每天记录进行存储的时候，如果记录数据小于一个数据块的空闲空间大小，那么这条记录就被存储在一个数据块中，但是当存储一条很长的记录数据的时候，那么会把这一条记录分割成很多片段，分别存储在各个数据块中，在每个数据块的头部信息中都会存储该记录的rowid地址，当查询这条记录的时候就会从每个数据块中或许信息组成一条记录，可见这样是很消耗资源的。

 

什么是表的迁移记录：当一个数据块中已经存储了一条记录，但是当使用update更新了记录中的某一个字段的值，使其信息已经超出了该数据块的空闲空间大小，那么这个时候数据块就会进行数据迁移，把这条记录迁移到其他数据块中，在本数据块保存一个指向其他数据块的指针。这就是迁移记录。

     查看表的迁移记录和链接记录信息，需要一个chained_row表，所有的记录信息都存储于该表中。同样该表也是不存在的，需要手动执行$ORACLE_HOME/rdbms/admin/utlchain.sql进行该表的创建，如果想对表进行链接记录和迁移记录进行统计收集信息，那么使用：易做图yze table table_name