The Oracle (tm) Users' Co-Operative FAQ

How do I retrieve information about partitioned tables from the data dictionary ?


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 5th Sept 2002

Oracle version(s): 8.1.5 - 9.2

How do I retrieve information about partitioned tables from the data dictionary ?

Back to index of questions


Information about partitioned tables are typically stored in the data dictionary in a number of views with the word 'PART' in them; so a query like the following will list the relevant views (if you select any table privileges):

	select	object_name
	from	dba_objects
	where	object_name like '%PART%'
	order by 
		object_name
	;

You will get the usual feature of lists of views with the three family prefixes, (DBA_, ALL_, USER_), which should include the following:

	DBA_(SUB_)PART_TABLES		Identifies tables that have been (sub) partitioned
	DBA_TAB_(SUB_)PARTITIONS	Lists (sub)partitions of tables
	DBA_(SUB_)PART_INDEXES		Identifies indexes that have been (sub) partitioned
	DBA_IND_(SUB_)PARTITONS		Lists (sub) partitions of indexes
	DBA_(SUB_)PART_KEY_COLUMNS	List (sub) partitioning columns of objects
	DBA_(SUB_)PART_COL_STATISTICS	Holds (sub)partition level statistics on columns
	DBA_(SUB_)PART_HISTOGRAMS	Holds (sub)partition level histograms on columns
	DBA_LOB_(SUB_)PARTITIONS	Lists (sub)partitions of lOBs
	DBA_(SUB_)PART_LOBS		Identifies LOBs in (sub)partitioned tables

Further reading: N/A


Back to top

Back to index of questions