SQL Script Partition Details

Updated : 28-Feb-2001
Version : 1.0

Description

This script lists details of table and index partitions. Note: Version 8 only.

Parameters

None.

SQL Source

col name form a15
col table_name form a15
col index_name form a15
col column_name form a20
col partition_name form a10 heading "Partition"
col high_value form a8 heading "High Value"
col partition_position form 99 heading "Po"
col tablespace_name form a10 heading "Tspace"
col locality form a6 heading "L/G"

select
 atp.TABLE_NAME
,atp.PARTITION_NAME
,apt.PARTITIONING_TYPE
,atp.HIGH_VALUE
,atp.PARTITION_POSITION
,atp.TABLESPACE_NAME
FROM all_tab_partitions atp, all_part_tables apt
WHERE atp.table_owner = apt.owner
AND atp.table_name = apt.table_name
ORDER BY atp.table_name, atp.partition_position;

select 
NAME                   
,OBJECT_TYPE           
,COLUMN_NAME          
,COLUMN_POSITION 
from all_part_key_columns
ORDER BY name,column_position;

select  upi.INDEX_NAME  
, upi.locality
, upi.alignment
, uip.PARTITION_NAME       
, uip.HIGH_VALUE            
, uip.PARTITION_POSITION      
, uip.STATUS   
, uip.TABLESPACE_NAME                      
FROM all_ind_partitions uip, all_part_indexes upi
WHERE uip.index_name = upi.index_name 
AND uip.index_owner = upi.owner
ORDER BY uip.index_name, uip.partition_position;

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal