分区语句整理 2

-- 查找"分区维护表"中有这个表,但该表不存在或不是分区表

select *

  from lbidw.tb_sys_tab_partitions a

 where not exists (select 'a'

          from dba_tab_partitions b

         where b.table_owner in

               ('LBIDW', 'LBIMK', 'LBIKR')

           and upper(a.table_name) = b.table_name)

 

-- 查看现有的分区情况

select *

  from (select a.table_owner,

               a.table_name,

               a.partition_name,

               b.deal_cycle,

               case when b.deal_cycle = 'M' then substr(a.partition_name, length(a.partition_name) - 5, 6)

                    when b.deal_cycle = 'D' then substr(a.partition_name, length(a.partition_name) - 7, 8)

                end partition_date

          from dba_tab_partitions a, lbidw.tb_sys_tab_partitions b

         where a.table_owner in

               ('LBIDW', 'LBIMK', 'LBIKR', 'LBI_MALI_SOTELMA')

           and a.table_name = b.table_name

           and a.table_owner = upper(b.owner))

 where partition_date < '20100501'

   and deal_cycle = 'D'

--and table_owner <> 'LBIKR'

 order by table_owner asc, table_name asc, partition_date asc