溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
/*查詢含有'default','MAXVALUE'的分區(qū)表*，分區(qū)表含有default或max分區(qū)/
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,
/*查詢所有的分區(qū)表*/
(
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'的分區(qū)表*/
where parttab.table_name=allparttab.table_name
----------------------------------------------------------------------------------------------------------------------------------------------------------
/*查詢不帶有maxvalue和default分區(qū)的表*，分區(qū)表不含有maxvalue和default分區(qū)/
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,
/*查詢所有的分區(qū)表*/
(
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'的分區(qū)表*/
where parttab.table_name=allparttab.table_name ) c
where a.table_name=c.table_name
)
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
