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

/*查询含有'default','MAXVALUE'的分区表*，分区表含有default或max分区/
select                                                                                                                                                                                             
allparttab.datestr,                                                                                                                                                                                
allparttab.table_owner,                                                                                                                                                                            
allparttab.partition_name,                                                                                                                                                                         
allparttab.table_name,                                                                                                                                                                             
allparttab.num_rows                                                                                                                                                                                
from                                                                                                                                                                                               
(select  *  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  ) )  allparttab,                                                                                                                        
 /*查询所有的分区表*/                                                                                                                                                                              
                                                                                                                                                                                                   
 (                                                                                                                                                                                                 
 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                                                                                                                                                                                        
 /*查询只含有'default','MAXVALUE'的分区表*/                                                                                                                                                        
 where parttab.table_name=allparttab.table_name     
 
---------------------------------------------------------------------------------------------------------------------------------------------------------- 

 /*查询不带有maxvalue和default分区的表*，分区表不含有maxvalue和default分区/

select  * from 

(select  *  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  ) ) a
 
where  not  exists

(
select  *  from
(
select                                                                                                                                                                                             
allparttab.datestr,                                                                                                                                                                                
allparttab.table_owner,                                                                                                                                                                            
allparttab.partition_name,                                                                                                                                                                         
allparttab.table_name                                                                                                                                                                                                                                                                                                                                                      
from                                                                                                                                                                                               
(select  *  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  ) )  allparttab,                                                                                                                        
 /*查询所有的分区表*/                                                                                                                                                                                                                                                                                                                                                                              
 (                                                                                                                                                                                                 
 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                                                                                                                                                                                        
 /*查询只含有'default','MAXVALUE'的分区表*/                                                                                                                                                        
 where parttab.table_name=allparttab.table_name  ) c
 where  a.table_name=c.table_name
 )