Structure - Partitions

List partitioned tables

set pages 999 lines 100
col table_name format a40
select table_name
, partitioning_type type
, partition_count partitions
from dba_part_tables
where owner = '&owner'
order by 1
/

List a tables partitions

set pages 999 lines 100
col high_value format a20
col tablespace_name format a20
select partition_name
, tablespace_name
, high_value
from dba_tab_partitions
where table_owner = '&owner'
and table_name = '&table_name'
order by partition_position
/

Show partition sizes for the specified table

set pages 999 lines 100
col tablespace_name format a20
col num_rows format 999,999,999
select p.partition_name
, p.tablespace_name
, p.num_rows
, ceil(s.bytes / 1024 / 1204) mb
from dba_tab_partitions p
, dba_segments s
where p.table_owner = s.owner
and p.partition_name = s.partition_name
and  p.table_name = s.segment_name
and p.table_owner = '&owner'
and p.table_name = '&table_name'
order by partition_position
/

Move a partition to a new tablespace

alter table <table_name>
move partition <partition_name>
tablespace <tablespace_name>
nologging
/

Add a partition

alter table <table_name>
add partition <partition_name> values less than (<value>)
tablespace <tablespace_name>
/

or...

alter table <table_name>
add partition <partition_name> values (<value>)
tablespace <tablespace_name>
/

Split a partition

alter table <table_name>
split partition <partition_name> at (<value>)
into (partition <partition_name>, partition <partition_name>)
update global indexes
/

Drop a partition

alter table <table_name> drop partition <partition_name>
/

Truncate a partition

alter table <table_name> truncate partition <partition_name>
/