Quickly write and operate the tablespace cleanup scheme

1. Query database table space usage

select total.tablespace_name,

round(total.gb, 2) total_gb,

round(total.gb, 2) - round(nvl(free.gb, 0), 2) used_gb,

round(nvl(free.gb, 0), 2) free_gb,

round( 100 ( 1 - nvl( free.gb, 0 ) / total.gb ), 2 ) "USED RATE(%)",

round(nvl(free.gb, 0) / total.gb 100, 2) "FREE RATE(%)"

from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 GB

from dba_free_space

group by tablespace_name) free,

(select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 GB

from dba_data_files

group by tablespace_name) total

where total.tablespace_name = free.tablespace_name(+)

order by 5 desc;

Note: The asterisk in the article is not displayed. If there is an error, the right bracket is missing. Add these two positions by yourself.

II. Query objects with large space occupied by table space T_SPACES

select *

from (select segment_name,

segment_type,

tablespace_name,

sum(bytes / 1024 / 1024 / 1024) used_gb

from dba_segments

where tablespace_name = 'T_SPACES'

group by segment_name, segment_type, tablespace_name )

order by 4 desc;

After analysis: occupy table space T_SPACES utilization rate as shown in the figure above.

Note: Write for a long time, the figure disappeared, according to the above statement can be found out that the largest space is the front, because the use of ORDER BY DESC reverse order.

Conclusion: We need to clean up the data in the chart above by reducing the use of the table space T_SPACES.

III. It is suggested to delete the data of T_TAB1 table (because this table has duplicate data with historical table T_TAB1_his)

data distribution

1. The distribution of T_TAB1 is as follows:

select substr(tran_dt,1,6),count(*) from T_TAB1 group by substr(tran_dt,1,6) order by 1;

backup

exp test@test file=/aas/database_bak/T_TAB1.dmp tables=T_TAB1 log=/aas/database_bak/T_TAB1.log

Clean up:

truncate table T_TAB1;

Partition table:

Backup:

exp test@test file=/aas/database_bak/test1.dmp tables=test1 query=\"where tran_dt>='20190101'\" log=/aas/database_bak/test1.log STATISTICS=NONE DIRECT=Y compress=N INDEXES=N CONSTRAINTS=N

Clean up:

alter table test1 truncate partition M20181201;

alter table test1 truncate partition M20190101 update global indexes;

Check whether the index is invalid:

select index_name,table_name,tablespace_name,status from dba_indexes where table_name='test1';

Backup and clean up according to conditions:

Backup:

exp test@test file=/aas/database_bak/test2.dmp tables=test2 query=\"where tran_dt