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
)