oracle分区表检查--是否带有max或default分区

                                                                                                                                                                              
                                                                                                                                                                                                   
 (                                                                                                                                                                                                 
 select  distinct(tabname.table_name)  from                                                                                                                                                        
(                                                                                                                                                                                                  
with xs as                                                                                                                                                                                         
   (select x.*,                                                                                                                                                                                    
       to_date (substr (high_value, 11, 19), 'YYYY-MM-DD HH24:MI:SS') dates,                                                                                                                       
       replace (high_value, 'TIMESTAMP''') datestr                                                                                                                                                 
  from xmltable('/ROWSET/ROW' passing (select dbms_xmlgen.getxmltype('                                                                                                                             
select t.table_owner, t.table_name, t.partition_name, t.high_value                                                                                                                                 
  from dba_tab_partitions t')                                                                                                                                                                      
                      from dual) columns                                                                                                                                                           
                   table_owner varchar2(30) path 'TABLE_OWNER',                                                                                                                                    
                   table_name varchar2(30) path 'TABLE_NAME',                                                                                                                                      
                   partition_name varchar2(30) path 'PARTITION_NAME',                                                                                                                              
                   high_value varchar2(30) path 'HIGH_VALUE') x )                                                                                                                                  
select                                                                                                                                                                                             
xs.datestr,                                                                                                                                                                                        
p.table_owner,                                                                                                                                                                                     
p.partition_name,                                                                                                                                                                                  
p.table_name,                                                                                                                                                                                      
p.num_rows                                                                                                                                                                                         
from xs,dba_tab_partitions p                                                                                                                                                                       
where xs.table_owner IN (select distinct(p.table_owner) from dba_tab_partitions p where p.table_owner<>'SYS' and substr(table_name,-1,1) <> '$' )                                                  
 and p.table_owner = xs.table_owner                                                                                                                                                                
 and p.table_name = xs.table_name                                                                                                                                                                  
 and p.partition_name = xs.partition_name                                                                                                                                                          
 order by p.table_owner,p.table_name,p.partition_position  ) tabname                                                                                                                               
 where tabname.datestr in('default','MAXVALUE')                                                                                                                                                    
                                                                                                                                                                                                   
 )  parttab                                                                                                                                                                                        
                                                                                                                                                         
 where parttab.table_name=allparttab.table_name     
 
---------------------------------------------------------------------------------------------------------------------------------------------------------- 

                                                                                                                                                                                                                                                                                                                                                                               
 (                                                                                                                                                                                                 
 select  distinct(tabname.table_name)  from                                                                                                                                                        
(                                                                                                                                                                                                  
with xs as                                                                                                                                                                                         
   (select x.*,                                                                                                                                                                                    
       to_date (substr (high_value, 11, 19), 'YYYY-MM-DD HH24:MI:SS') dates,                                                                                                                       
       replace (high_value, 'TIMESTAMP''') datestr                                                                                                                                                 
  from xmltable('/ROWSET/ROW' passing (select dbms_xmlgen.getxmltype('                                                                                                                             
select t.table_owner, t.table_name, t.partition_name, t.high_value                                                                                                                                 
  from dba_tab_partitions t')                                                                                                                                                                      
                      from dual) columns                                                                                                                                                           
                   table_owner varchar2(30) path 'TABLE_OWNER',                                                                                                                                    
                   table_name varchar2(30) path 'TABLE_NAME',                                                                                                                                      
                   partition_name varchar2(30) path 'PARTITION_NAME',                                                                                                                              
                   high_value varchar2(30) path 'HIGH_VALUE') x )                                                                                                                                  
select                                                                                                                                                                                             
xs.datestr,                                                                                                                                                                                        
p.table_owner,                                                                                                                                                                                     
p.partition_name,                                                                                                                                                                                  
p.table_name                                                                                                                                                                                                                                                                                                                                                                           
from xs,dba_tab_partitions p                                                                                                                                                                       
where xs.table_owner IN (select distinct(p.table_owner) from dba_tab_partitions p where p.table_owner<>'SYS' and substr(table_name,-1,1) <> '$' )                                                  
 and p.table_owner = xs.table_owner                                                                                                                                                                
 and p.table_name = xs.table_name                                                                                                                                                                  
 and p.partition_name = xs.partition_name                                                                                                                                                          
 order by p.table_owner,p.table_name,p.partition_position  ) tabname                                                                                                                               
 where tabname.datestr in('default','MAXVALUE')                                                                                                                                                    
                                                                                                                                                                                                   
 )  parttab                                                                                                                                                                                        
                                                                                                                                                         
 where parttab.table_name=allparttab.table_name  ) c
 where  a.table_name=c.table_name
 )