Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Work notes-tablespace cleanup

2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

-- 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'

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report