ORACLE -- partition table data cleaning, oracle -- Partition Table cleaning

ORACLE -- partition table data cleaning, oracle -- Partition Table cleaning

The tablespace is in a hurry because the partition table data is increased and no cleanup is performed. It takes a long time to clear data and release space. The procedure is as follows:

I. Check which table occupies the space

SELECT t.segment_name, SUM(t.bytes / 1024 / 1024)  FROM user_segments t GROUP BY t.segment_name ORDER BY SUM(t.bytes / 1024 / 1024) DESC

2. view records of each subpartition

SELECT a.table_name,a.partition_name FROM user_tab_partitions  a WHERE a.table_name = 'TBL_SMSMT_AUTOACTIV_BILL_HIS';

3. Check whether the index of the partition table is of the LOCAL type.
-- First view the index of the table

SELECT * FROM User_Indexes a WHERE a.table_name  = 'TBL_SMSMT_AUTOACTIV_BILL_HIS';

-- Check whether the index is of the local type. If the index does not exist, it is a global index.

select table_name,index_name,LOCALITY from user_part_indexes where table_name='TBL_SMSMT_AUTOACTIV_BILL_HIS' ;

4. If both are LOCAL indexes, directly clear the table partition data (if it is a global index, it needs to be rebuilt after clearing)

The local index will be deleted when you delete the sub-partition table.

Alter table partition drop partition P201305; alter table partition drop partition P201306; alter table partition drop partition P201307; alter table partition drop partition P201308; alter table partition drop partition P201309; alter table TBL_SMSMT_AUTOACTIV_BILL_HIS drop partition P201310; alter index SYS_C00105590 REBUILD; alter index implements REBUILD; -- SYS_C00105590 and I _TBL_SMSMT_AUTOACTIV_BILL_HIS are global indexes.

5. Verify whether space is released. (ignore)

Perform the first step;

How to delete a partition table in oracle

You can use alter table drop partition to delete partitions. The metadata and data are also deleted.
Delete all
Alter table yourTable drop partition partionName1;
Clear Data
Alter table yourTable truncate partition partionName1;
The statement is simple and must be operated with caution.

Oracle Database Partition Table backup

Create table par_tab (
Data_date varchar2 (8 ),
Col_n varchar2 (20)
)
Partition by range (data_date )(
Partition part_201000 values less than ('20140901') tablespace tab_201000,
Partition part_201001 values less than ('20140901') tablespace tab_201003,
Partition part_201002 values less than ('20140901') tablespace tab_201003,
Partition part_201003 values less than ('20140901') tablespace tab_201003,
Partition part_201004 values less than ('20140901') tablespace tab_201006,
Partition part_201005 values less than ('20140901') tablespace tab_201006,
Partition part_201006 values less than ('20140901') tablespace tab_201006,
Partition part_201007 values less than ('20140901') tablespace tab_201009,
Partition part_201008 values less than ('20140901') tablespace tab_201009,
Partition part_201009 values less than ('20140901') tablespace tab_201009,
Partition part_201010 values less than ('20140901') tablespace tab_201012,
Partition part_201011 values less than ('20140901') tablespace tab_201012,
Partition part_201012 values less than ('20140901') tablespace tab_201012,
Partition part_201099 values less than ('20140901') tablespace tab_201099,
Partition part_201100 values less than ('20140901') tablespace tab_201000,
Partition part_201101 values less than ('20140901') tablespace tab_201003,
Partition part_201102 values less than ('20140901') tablespace tab_201003,
Partition part_201103 valu ...... remaining full text>