Work notes-tablespace cleanup

-- View the detailed usage of tablespaces in the database

SELECT A.TABLESPACE_NAME Tablespace name

A.TOTAL_SPACE "Total Space (M)"

NVL (B.FREE_SPACE, 0) "remaining space (M)"

A.TOTAL_SPACE-NVL (B.FREE_SPACE, 0) "use space (M)"

Trunc (NVL (B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100,2) "remaining%"

FROM (SELECT TABLESPACE_NAME

Trunc (SUM (BYTES) / 1024 / 1024, 2) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) A

(SELECT TABLESPACE_NAME

Trunc (SUM (BYTES / 1024 / 1024) 2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) B

WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)

ORDER BY 5

-- View the usage of tables and partitioned tables in a tablespace: take the BOSSNM_PERF_DATA tablespace as an example

Select segment_name,segment_type,round (sum (bytes) / 1024Universe 1024Universe 4) from dba_segments

Where tablespace_name = upper ('BOSSNM_PERF_DATA')

Group by segment_name,segment_type order by sum (bytes) desc

-- View the usage of tables and partitioned tables in a tablespace: take the BOSSNM_INFO tablespace as an example

Select segment_name,segment_type,round (sum (bytes) / 1024Universe 1024Universe 4) from dba_segments

Where tablespace_name = upper ('BOSSNM_INFO')

Group by segment_name,segment_type order by sum (bytes) desc

Using pkp_tools_orz package to clean up partition table

-- query the usage of tables in the BOSSNM_INFO tablespace

Begin

Pkp_tools_orz.AUTO_CLEAR_PARTITIONTABLES ('BOSSNM_INFO',0)

End

-- query results are stored in CLEAR_SPACE_TEMP temporary table in table usage results.

Select * from CLEAR_SPACE_TEMP

The statement to clean up the table is copied in the clear_sql field of the CLEAR_SPACE_TEMP table, and the cleanup is performed under the command window.

For example, the following three different cleanup formats are: partitioned table with global index, partitioned table with local index, and table without index.

Alter table NE_ALARM_MSG truncate partition PART_1311 update global indexes

Alter table CTNBC_TRAN_DATA_008 truncate partition PART_201610

Truncate table INFO_JT_SR_40200023

-- View the index status of the table. In the status field in user_indexes, NCMA represents local, and VALID represents global.

For example, query the index whose partition is CTNBC_TRAN_DATA_008_NEW, and status.

Select * from user_indexes where table_name='CTNBC_TRAN_DATA_008_NEW'

-- Clean up and query whether the index is normal

For example, check the index of the index name INFO_JT_MID_MSG_ALL_DTL_01. The status field in the table shows that usable is normal.

Select * from Dba_ind_partitions where index_name='INFO_JT_MID_MSG_ALL_DTL_01'