Search My Oracle Blog

Custom Search

24 November, 2016

12.2 New Features -- 2 : Partitioning an Existing Table

A non-partitioned table can be Partitioned (without having to use DBMS_REDEFINITION) online.

SQL> connect hr/Oracle_4U@PDB1
Connected.
SQL> select count(*) from employees;

  COUNT(*)
----------
       107

SQL> create table employees_part as select * from employees;

Table created.

SQL> select table_name from user_part_tables;

no rows selected

SQL> alter table employees_part
  2  modify
  3  partition by range (last_name)
  4  (partition p_N values less than ('O'),
  5   partition p_Q values less than ('R'),
  6   partition p_LAST values less than (MAXVALUE))
  7  online;

Table altered.

SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'EMPLOYEES_PART'
  4  order by partition_position
  5  /

PARTITION_NAME
--------------------------------------------------------------------------------
HIGH_VALUE
------------
P_N
'O'

P_Q
'R'

P_LAST
MAXVALUE


SQL>
SQL> select table_name, partitioning_type, partition_count
  2  from user_part_tables
  3  where table_name = 'EMPLOYEES_PART'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
PARTITION PARTITION_COUNT
--------- ---------------
EMPLOYEES_PART
RANGE                   3


SQL>
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'EMPLOYEES_PART'
  4  order by partition_position
  5  /

PARTITION_NAME
--------------------------------------------------------------------------------
  NUM_ROWS
----------
P_N
        71

P_Q
        10

P_LAST
        26


SQL>


I was able to convert a Non-Partitioned Table to a Range-Partitioned Table online.
.
.
.

1 comment:

Unknown said...

No more exchange partition headeches. That's cool!

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com