oracle 查询default,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

)