Partition details getting

declare
  l_high_value VARCHAR2(32767);
  l_date DATE;
BEGIN
  SELECT high_value
  INTO   l_high_value
  FROM   all_tab_partitions
  WHERE  lower(table_owner) = 'imsdwh_lpin'
  AND    lower(table_name)  = 'es_dwh_pck_his'
  AND    lower(partition_name) = 'p201505';

  EXECUTE IMMEDIATE 'SELECT ' || l_high_value || ' FROM dual' INTO l_date;
  dbms_output.put_line(l_date);
END;

select partition_name from ALL_TAB_PARTITIONS
where lower(table_name)='es_dwh_pck_his' and lower(table_owner) = 'imsdwh_lpin';

select max(count(*))
from (select table_name,
       partition_name,
       to_date (
          trim (
          '''' from regexp_substr (
                     extractvalue (
                       dbms_xmlgen.getxmltype (
                       'select high_value from all_tab_partitions where table_name='''
                                || table_name
                                || ''' and table_owner = '''
                                || table_owner
                                || ''' and partition_name = '''
                                || partition_name
                                || ''''),
                             '//text()'),
                          '''.*?''')),
          'syyyy-mm-dd hh24:mi:ss')
          high_value_in_date_format
  FROM all_tab_partitions
 WHERE lower(table_name) = 'es_dwh_pck_his' AND lower(table_owner) = 'imsdwh_lpin'
 )a
 group by to_char(high_value_in_date_format,'yyyy');

------------------------------------------------------------------
select owner as schema_name,table_name
FROM all_tables t
WHERE lower(t.OWNER) IN ('imsdwh_lpin','imsdwh_lids','es_lids','prdb')
and partitioned = 'YES'
and table_name not in (SELECT table_name
                        FROM all_tables t
                        WHERE lower(t.OWNER) IN ('imsdwh_lpin','imsdwh_lids','es_lids','prdb')
                        and regexp_like(lower(table_name),'$_bck|_s2e|aa|xx|old'))
ORDER BY OWNER, table_name;

------------------------------------------------------
set serveroutput on;

declare
db_status varchar(100);
cursor p_table_name is
select table_name
FROM all_tables t
WHERE lower(t.OWNER) IN ('imsdwh_lpin','imsdwh_lids','es_lids','prdb')
and partitioned = 'YES'
and table_name not in (SELECT table_name
                        FROM all_tables t
                        WHERE lower(t.OWNER) IN ('imsdwh_lpin','imsdwh_lids','es_lids','prdb')
                        and regexp_like(lower(table_name),'$_bck|_s2e|aa|xx|old'))
ORDER BY OWNER, table_name;
begin
for r in p_table_name
loop
begin
Execute immediate 'select count(*) from '|| r.table_name ||'@es_pp' into db_status;
db_status := 'Valid';
exception
when others then
db_status := 'InValid';
end;
dbms_output.put_line(db_status);
end loop;
end;