Oracle analysis table operations

Perform the following operations on the oracle analysis table: 1) Verify the storage status of the table 2) view the statistical information of the table 3) Search for the link record and migration record in the table 1) verify the storage status of the table. The table exists in the table space and the table space exists in the data file. Therefore, the table may have bad logical blocks due to software bugs, in this case, you can use analyze table table_name validate structure; to verify the Logical Block of the table. If a logical error exists, you need to restore the table. When we need to verify whether the storage structure of the table is damaged by logical blocks, we need the invalid_rows table to collect logical block information, this table does not exist in the Database by default. You can use utlvalid. create an SQL script by yourself. The script is in the $ ORACLE_HOME/rdbms/admin directory. The permission for this table belongs to the dba role: 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    analyze 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 analyze_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,    analyze_timestamp  date  );  

 

The test is as follows:

[sql] SQL> @./utlvalid.sql    Table created.    SQL> conne rhys/root  Connected.  SQL> analyze table emp validate structure;    Table analyzed.    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) view the statistical information of the table. You can view the total number of data blocks, number of idle data blocks, and average free space of the table with user_tables, all_tables, and dba_tables: the statistical information of a table is divided into the statistical information for full scanning of the table and an estimation information for some table data. For fully scanned table statistics, we can use analyze 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 analyzed.    SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_SPACE FROM USER_TABLES WHERE TABLE_NAME='EMP';        BLOCKS EMPTY_BLOCKS  AVG_SPACE  ---------- ------------ ----------           1            6       7586    SQL>   

 

For table Analysis statistics performed by partial table scans, you can use: analyze table table_name estimate statistics samp 20 [percent or rows]; command for statistical collection: 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> analyze table emp estimate statistics sample 20 rows;    Table analyzed.    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>   

 

Because the table has two statistics, the information changes of the statistical table are displayed two times after the table changes; eg:

[sql] SQL> analyze table emp estimate statistics sample 20 percent;    Table analyzed.    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>

 

Note: When the statistical sample for a table is greater than 50%, full table data statistics are performed. 3) what are table link records and migration records? What are table link records? Each table is composed of records. When the records are stored every day, if the record data is smaller than the free space of a data block, the record is stored in a data block. However, when a long record data is stored, this record will be split into many segments and stored in each data block separately. The rowid address of the record will be stored in the header information of each data block, when querying this record, it may make up a record from each data block. It can be seen that this will consume a lot of resources. What is a table migration record? When a data block stores a record, but update is used to update the value of a field in the record, if the information exceeds the free space of the data block, the data block will be migrated and the record will be migrated to other data blocks, save a pointer to another data block in this data block. This is the migration record. To view the migration records and link records of a table, a chained_row table is required. All records are stored in the table. Similarly, this table does not exist. You need to manually execute $ ORACLE_HOME/rdbms/admin/utlchain. create a table using SQL. If you want to collect statistics on the Link record and migration record of the table, use: analyze table table_name list chained rows into chained_rows;

Utlchained. SQL: remrem $ Header: utlchain. SQL 07-may-96.19: 40: 01 sbasu Exp $ remRem Copyright (c) 1990,199 5, 1996,199 8 by Oracle configurationrem NAMEREM UTLCHAIN. SQLRem FUNCTIONRem Creates the default table for storing the output of theRem analyze list chained rows commandRem NOTESRem MODIFIEDRem syeung 06/17/98-add Comment mmonajje 05/21/96-Replace timestamp col name with analyze_ti MestamRem sbasu 05/07/96-Remove echo settingRem ssamu 08/14/95-merge PTI with ObjectsRem ssamu 07/24/95-add field for partition nameRem glumpkin 10/19/92-Renamed from CHAINROW. SQLRem ggatlin 03/09/92-add set echo onRem rlim 04/29/91-change char to varchar2Rem Klein 01/10/91-add owner name for chained rowsRem Klein 12/04/90-CreationRemcreate 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); eg: [SQL] SQL> @. /utlchain. SQL Table created. SQL> desc chained_rows Name Null? Type direction -------- primary 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 SQL> eg: [SQL] SQL> @. /utlchain. SQL Table created. SQL> desc chained_rows Name Null? Type reserved -------- ---------------------------- 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 SQL> SYS @ orcl # analyze table emp list chained rows into chained_rows; the table has been analyzed. SYS @ orcl # select * from chained_rows; the row SYS @ orcl # is not selected #

 

Note: When the table has a large number of link records and migration records, we should consider whether the method of the table we created is correct. When we manage the tablespace locally, when the segment management mode is segment space management manual, we need to consider the setting of pctfree and pctused parameters.