Partitioned table details

It is difficult to display all the details of a given partitioned table without repetition of column values. This PL/SQL block is useful because it saves you from searching for the tablenames where the datails are available and how to join the tables. It is embedded with unique coding features such as parametric cursors and is useful for beginners who want to learn coding techniques.

set echo off
set termout on
set feed off
set linesize 150
set pages 3000
set ver off

CREATE TABLE TPR(OWNER VARCHAR2(100),TS VARCHAR2(100),TNAME VARCHAR2(100),
PARTNAME VARCHAR2(100),HVALUE VARCHAR2(100),KEYNAME VARCHAR2(100),COLNAME VARCHAR2(100))
storage (initial 200k next 200k maxextents 1024)
/
DECLARE
 CURSOR C1 IS select TABLE_OWNER,tablespace_name,TABLE_NAME,PARTITION_NAME,HIGH_VALUE 
 from dba_tab_partitions where table_owner=UPPER('&OWNER') and table_name=UPPER('&TABLENAME');

 CURSOR C2(P_owner VARCHAR2,p_name varchar2) IS SELECT owner,name,column_name 
 FROM DBA_part_key_columns where owner=p_owner and name=p_name;

 cursor c3(P_owner VARCHAR2,p_name varchar2) IS SELECT owner,table_name,column_name 
 from dba_tab_columns where owner=p_owner and table_name=p_name;

 v_owner  varchar2(50);
 v_ts  varchar2(100);
 v_tname  varchar2(100);
 v_partname varchar2(100);
 v_hvalue varchar2(100);
 v_keyname varchar2(100);
 v_colname varchar2(100);
BEGIN
 OPEN C1;
 LOOP
 FETCH C1 INTO v_owner,v_ts,v_tname,v_partname,v_hvalue;
 EXIT WHEN C1%NOTFOUND;
 INSERT INTO TPR (OWNER,TS,TNAME,PARTNAME,HVALUE) VALUES(v_owner,v_ts,v_tname,v_partname,v_hvalue);
 COMMIT;
      OPEN C2(v_owner,v_tname);
      LOOP
   FETCH C2 INTO v_owner,v_tname,v_keyname;
   EXIT WHEN C2%NOTFOUND;
   INSERT INTO TPR(owner,tname,keyname) VALUES(v_owner,v_tname,v_keyname);
   COMMIT;
     OPEN C3(v_owner,v_tname);
           LOOP
     FETCH C3 INTO v_owner,v_tname,v_colname;
     EXIT WHEN C3%NOTFOUND;
     INSERT INTO TPR(owner,tname,colname) VALUES(v_owner,v_tname,v_colname);
     COMMIT;
        END LOOP;
     CLOSE C3;
   END LOOP;
   CLOSE C2;
 END LOOP;
 CLOSE C1;
END;
/
PROMPT
PROMPT *********************************************
PROMPT ***** PARTITIONED TABLE DETAILS *************
PROMPT *********************************************
PROMPT

col c1 format a8 heading "Owner"
col c2 format a10 heading "Table Name"
col c3 like c2 heading "Tablespace Name"
col c4 format a15 heading "Partition Name"
col c5 like c4 heading "Range Values" 
col c6 like c2 heading "Key Name"
col c7 like c4 heading "Column Name"

BREAK ON C1 ON C2 ON C3

SELECT OWNER c1,TNAME c2,TS c3,PARTNAME c4,
HVALUE c5,COLNAME c7,KEYNAME c6
FROM TPR GROUP BY OWNER,TNAME,TS,PARTNAME,HVALUE,KEYNAME,COLNAME
order BY c6 desc,c7 desc
/
DROP TABLE TPR
/
set echo on
set termout on
set feed on