Tip

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)

    Requires Free Membership to View

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

This was first published in December 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.