An analysis and treatment of abnormal growth of Oracle database table space

Preface ：

the other day , During my daily inspection of the database, I found , There is a database with little business volume at ordinary times （ Through archiving frequency 、 Memory footprint 、 The number of conversations, etc ）, Its table space is still in 700MB/ The speed of days is increasing , It doesn't make sense . So I analyzed it by the following means , Found out " Behind the scenes ".

One 、 View tablespace growth

Two 、 Find out which tablespaces are growing

3、 ... and 、 Find out which tables in the corresponding table space caused the abnormal growth of space

I wanted to dba_hist_seg_stat View to get useful information , As a result, I found that the information in it was not complete , And the data obtained are not accurate , In the end, I didn't find any useful clues through this view .

So-called “ All roads lead to Beijing ”, The road above is blocked , I'll take a slightly detour to get the information I want . Now that we know which table space is growing abnormally , Then I just need to write a stored procedure , Regularly record the data changes of all tables in this table space .

– First create a table to record data
create table TB_USAGE_RECORD
(
ctime DATE,
segment_name VARCHAR2(200),
partition_name VARCHAR2(200)
segment_type VARCHAR2(30),
header_file NUMBER,
header_block NUMBER,
size_gb NUMBER
)

create table TB_USAGE_RECORD_TOTAL
(
ctime DATE,
total_gb NUMBER
);

– Create stored procedure （ The sensitive information in this article has been rewritten and processed ）
create or replace procedure gather_tb_size_increase authid current_user
is
begin
insert into TB_USAGE_RECORD select sysdate ctime,segment_name,partition_name,segment_type,header_file,header_block,round(bytes/1024/1024/1024,3) size_gb
from dba_segments where tablespace_name=‘THE_EXCEPTION_TBS’;
insert into TB_USAGE_RECORD_TOTAL select sysdate ctime,round(sum(bytes)/1024/1024/1024,3) total_gb from dba_segments where tablespace_name=‘THE_EXCEPTION_TBS’;
commit;
end;

ok, Now I can execute the stored procedure once , Then set a timing job, Let it be executed again at this time tomorrow . When tomorrow's data is available , You can analyze the problem ！

Four 、 Find out the root cause of the problem
Write the following sql And implement , Find the table where the data changes ：

See how much data is in the table ：

Only found 102 Row data , Looking at the table structure, it is found that there are no large fields , Should not occupy 100 many M What a space ！ Then analyze ：

-- Collect statistics ：analyze table LIST_20220415 compute statistics; Check the actual occupied space ：select segment_name,partition_name,round(bytes/1024/1024,2) size_mb from dba_segments where segment_name='LIST_20220415'   	SEGMENT_NAME	PARTITION_NAME	SIZE_MB1	LIST_20220415		             152 Check how many blocks are actually occupied , Is there an empty block ：select table_name,num_rows,blocks,empty_blocks from dba_tables where table_name='LIST_20220415'   	TABLE_NAME	NUM_ROWS	BLOCKS	EMPTY_BLOCKS1	LIST_20220415	102	          222	  19234

In fact, the data only uses 222 Block , There are still... Below the high water mark 19234 Empty blocks , That's why 102 Row data takes up (222+19234)*8/1024=152 MB, And dba_segments The values found in are the same .

therefore , The business side should write a lot of data to this table , And then I did delete operation , A large amount of data was deleted , Cause high water mark .

5、 ... and 、 How to deal with the problem

According to the several tables that led to data growth found before , I found that they are all named after specific dates . And I also found the method to create these historical tables in the relevant stored procedures of the database sql. As for the creation of daily history table , It is initiated by the application side directly calling the stored procedure . During this period, the application performs a series of operations on the history table , This creates a high water level . To save storage space , On the one hand, after communicating with the business , The history table should be backed up and cleaned up in time , On the other hand, we should clean up the high water mark of these historical tables every day .

The following is after clearing the high water mark , Table space usage ：

   	SEGMENT_NAME	PARTITION_NAME	SIZE_MB1	LIST_20220415		             0.31

It can be seen that the effect is still very significant , The space occupied is directly occupied by 152MB Down to 0.31MB.

( The end of this paper )