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>
/
|
