oracle根据表空间，分区大小构建自动删分区语句

select t1.tablespace_name
	  ,t1.owner
	  ,t1.segment_name
	  ,t1.part_num
	  ,sum(t1.table_part_size_gb) over (partition by t1.tablespace_name,t1.owner,t1.segment_name,t1.part_num)  table_size_gb
	  ,t1.partition_name
	  ,t2.partition_position
	  ,t1.table_part_size_gb
	  ,'ALTER TABLE ' || t1.owner || '.' || t2.drop_partition_sql 
from ( select tablespace_name
			 ,owner
			 ,segment_name
			 ,count(1) over (partition by tablespace_name,owner,segment_name)as part_num
			 ,round((sum(bytes)over(partition by tablespace_name,owner,segment_name))/1024/1024/1024) table_size_gb
			 ,partition_name
			 ,round(sum(bytes)/1024/1024/1024) table_part_size_gb
		from dba_segments
		where tablespace_name = 'DB_TBS'
		and segment_type = 'TABLE PARTITION'
		group  by tablespace_name,owner,segment_name,partition_name
	 ) t1
inner join (
		select table_name || ' DROP PARTITION(' || partition_name || ');' drop_partition_sql
			   ,tablespace_name
			   ,table_name
			   ,partition_name
			   ,partition_position
		from (
			select tablespace_name
				,table_name
				,partition_name
				,partition_position
				,max(partition_position) over (partition by table_name) as max_part
			from user_tab_partitions --这个视图只查当前登入用户相关的
			WHERE tablespace_name = 'DB_TBS'
			) tmp
		where tmp.partition_position > 1 --不是第一个分区
		and tmp.partition_position <= max_part - 2 --不是最后两个分区
) T2
ON T1.tablespace_name = T2.tablespace_name
AND T2.segment_name = T2.table_name
AND T1.partition_name = T2.partition_name
where t1.part_num > 3 --分区数量要大于3
ORDER BY t1.table_part_size_gb DESC;