Oracle partition table check-with max or default partitions

/ * query the partition table with 'default','MAXVALUE' *, and the partition table contains default or max partition /

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.tableowned ownerships 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.tableroomowner.tableroomname.partitionals position) allparttab

/ * query all partition tables * /

(

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.tableowned ownerships 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.tableroomowner.tableroomname.partitionals position) tabname

Where tabname.datestr in ('default','MAXVALUE')

) parttab

/ * query partitioned tables containing only 'default','MAXVALUE'' * /

Where parttab.table_name=allparttab.table_name

- -

/ * query tables without maxvalue and default partitions *, partitioned tables do not contain maxvalue and default partitions /

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.tableowned ownerships 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.tableroomowner.tableroomname.partitionals 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.tableowned ownerships 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.tableroomowner.tableroomname.partitionals position) allparttab

/ * query all partition tables * /

(

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.tableowned ownerships 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.tableroomowner.tableroomname.partitionals position) tabname

Where tabname.datestr in ('default','MAXVALUE')

) parttab

/ * query partitioned tables containing only 'default','MAXVALUE'' * /

Where parttab.table_name=allparttab.table_name) c

Where a.table_name=c.table_name

)