Shulou(Shulou.com)06/01 Report--

A preface

Recently, a performance machine with 3T of disk space has been found to be running out of disk space, which mainly deploys ORACLE databases, so it is likely to be caused by changes in tablespace data files. Next, we need to troubleshoot the problem step by step, and finally confirm that it is the fault of ORACLE BLOCK_ID.

Second, locate the occupation of disk space

First of all, you need to determine which files take up space and use du-sh *. Sure enough, there is a tablespace increased by 20 data files, and each data file is set to 30G word days, who is so hard, can not find the culprit, well, then I will dispose of at will, can not affect the later performance testing.

3. Delete data files

Since I found so many data files, of course I wanted to drop them directly, so I disagreed to execute alter tablespace TEST drop datafile'/ oradata/dat20.dbf'; to kill the last one first. As a result, I reported an error "ORA-03262: THE FILE IS NON-EMPTY". Oh, there is data, but I can't delete it directly. Therefore, I want to query the table in this tablespace and TRUNCATE the data, but considering that there are thousands of TABLE in the tablespace, and I am not sure which table can be deleted, it can not be too reckless. It turns out that it has nothing to do with the data.

Fourth, second best-free space for RESIZE data files

Since you can't drop the data file, then resize it, and you can't get back the space if you don't believe it. So, first find out how much space can be released, and first execute the following command:

Select d. Filekeeper name as free_byte d. Filekeeper name as free_byte d.bytesUniverse 1024 as

From dba_data_files d,dba_free_space f

Where d.file_id=f.file_id and d.file_id=67

Group by d.file_name,d.file_id,d.bytes/1024/1024

The output shows that the available space of data file No. 67 is 29.9G. Seeing this, I secretly scolded in my heart who was so unreliable and randomly set up data files. However, they are all minor problems. 1G of resize will be fine. So, excitedly, I hastened to implement ALTER DATABASE DATAFILE'/ oradata/hisdat20.dbf' RESIZE 1G; unexpectedly, I made a mistake again.

"ORA-03297:file contains used data beyond requested RESIZE value", seeing this error report, began to realize that the problem may not be that simple.

Fifth shrink space lowers the high water level

Since there is very little actual data, but resize is not successful, it means that some data blocks are located at the end of the data file. First, lower the high water level of HWM and manipulate the table in the tablespace. The main commands are as follows:

Alter table test_table enable row movement

Alter table test_table shrink space;-lowering the high water level and freeing up space

Alter table test_table disable row movement

Of course, such individual execution is not displayed, but needs to be executed in batches, as follows:

SELECT DISTINCT 'alter table' | | segment_name | | 'enable row movement;' | |

'alter table' | | segment_name | | 'shrink space;' | |

'alter table' | | segment_name | | 'disable row movement;''

FROM dba_extents

WHERE tablespace_name = 'TEST'

AND segment_type = 'TABLE'

After reducing the HWM, execute the RESIZE operation again, and the error is still the same. Well, since this has not been done, it needs to be carefully studied.

6. Find the real killer and the solution

Through the above attempts, it is found that there is enough free space for the data file, but for ORACLE, the data file uses 30G, so RESIZE to 1G will report an error and fail, and it is useless to lower the high water level or TRUNCATE as soon as possible. Therefore, the idea of investigation and solution is as follows:

1) query the maximum BLOCK_ID of the data file

Select max (block_id) from dba_extents where file_id=67

2) determine which table or index the BLOCK_ID is related to

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME FROM DBA_EXTENTS A WHERE FILE_ID = 67 AND block_id = 3839929

After querying, it is found that the largest BLOCK_ID is related to partitioned tables or partitioned indexes.

3) the processing method for the maximum BLOCK_ID appearing in the partition table

For the case where the maximum BLOCK_ID occurs in the partition table, first lower the high water level of the partition table, and then MOVE the tablespace. The command is as follows:

Alter table TEST_TABLE MODIFY PARTITION P101101 shrink space;--- note that lowering the high water level does not reduce the block_id size in the data file

Alter table TEST_TABLE move partition P101101 tablespace TEST;---move operates data to move the free block at the front of the tablespace. Note that the index needs to be rebuilt.

4) the processing method for the occurrence of the maximum BLOCK_ID in the index partition

If the maximum BLOCK_ID occurs in the index partition, you can rebuild the partition index. The command is as follows:

ALTER INDEX IDX_TEST_TABLE REBUILD PARTITION P201201

5) after processing, the RESIZE operation is performed again, and the size of the data file is modified successfully.

Finally, because there may be more than one partition table on the same file number, the partition index needs to be dealt with, so it is recommended to write it in batch as step 5 to improve efficiency.

About shrink space lowering the high water level, you can refer to the blog post Oracle delete operation to hide secrets that you may not know.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.