/*
 * Title  : tab_part_col.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Shows table partition columns.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 120
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

col "Owner" 		format a30
col "Table Name" 	format a30
col "Column Name" 	format a30
col "Part Type" 	format a9
col "Part Count" 	format 99999999
col "Column Pos" 	format 9999999
col "Comments"		format a11

accept p_owner char prompt "Enter Owner "
accept p_order_col char prompt "Enter order by column {1|2|3|4|5} [asc|desc] "

spool &&rpt_dir/tab_part_col.rpt

select
	x.tab_name					"Table Name",
	x.col_name					"Column Name",
	x.prt_type					"Part Type",
	x.prt_cnt					"Part Cnt",
	x.col_pos					"Col Pos",
	decode(y.con_type,'P','Primary Key')		"Comments"
from
	(
		select
			distinct
			a.table_name		tab_name,
			c.column_name		col_name,
			b.partitioning_type	prt_type,
			b.partition_count	prt_cnt,
			c.column_position	col_pos
		from
			dba_tab_partitions a,
			dba_part_tables b,
			dba_part_key_columns c
		where
			a.table_owner=b.owner
		and	b.owner=c.owner
		and	a.table_name=b.table_name
		and	b.table_name=c.name
		and	a.table_owner=upper('&&p_owner')
	) x,
	(
		select 
			distinct
			d.table_name		tab_name,
			d.constraint_type	con_type,
			e.column_name		col_name
		from
			dba_constraints d,
			dba_cons_columns e
		where
			d.constraint_name=e.constraint_name
		and	d.constraint_type='P'
		and	d.owner=upper('&&p_owner')
	) y
where
	x.col_name=y.col_name(+)
and	x.tab_name=y.tab_name(+)
order by
	&&p_order_col;

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
