SQL Script Partition Details

Updated: 28-Feb-2001
Version: 1.0

Description

This script lists details of table and index partitions.

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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 a3 heading "L/G"
col alignment form a3 heading "Pre"

set pages 400

spool part.lst

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  
, DECODE(upi.locality,'LOCAL','L','GLOBAL','G',upi.locality) locality
, DECODE(upi.alignment,'NON_PREFIXED','NP','PREFIXED','P',upi.alignment)  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;

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links