How to perform partition on existing table which is a non partition table using hash partition.Table has a huge rows.
We can DBMS_REDEFINITION to do with it with minimal downtime. Here's an example
SQL>
SQL> --
SQL> -- our original table
SQL> --
SQL> create table T as
2 select rownum pk, d.*
3 from dba_objects d,
4 ( select 1 from dual connect by level <= 20 )
5 /
Table created.
SQL>
SQL> alter table T add constraint PK primary key ( pk );
Table altered.
SQL>
SQL> --
SQL> -- a template for how we want the table to look (ie, hash partitioned)
SQL> --
SQL> create table T_PAR (
2 pk number not null
3 ,owner varchar2(128)
4 ,object_name varchar2(128)
5 ,subobject_name varchar2(128)
6 ,object_id number
7 ,data_object_id number
8 ,object_type varchar2(23)
9 ,created date
10 ,last_ddl_time date
11 ,timestamp varchar2(19)
12 ,status varchar2(7)
13 ,temporary varchar2(1)
14 ,generated varchar2(1)
15 ,secondary varchar2(1)
16 ,namespace number
17 ,edition_name varchar2(128)
18 ,sharing varchar2(13)
19 ,editionable varchar2(1)
20 ,oracle_maintained varchar2(1)
21 )
22 partition by hash(pk)
23 partitions 8
24 /
Table created.
SQL>
SQL> --
SQL> -- check we'll be ok
SQL> --
SQL> exec dbms_redefinition.can_redef_table(user, 'T');
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- start the copy of all data
SQL> --
SQL> begin
2 dbms_redefinition.start_redef_table(
3 uname => user,
4 orig_table => 'T',
5 int_table => 'T_PAR');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- copy privileges, triggers etc.
SQL> --
SQL> -- I am NOT copying indexes/constraints, because if we are hash partitioning
SQL> -- the table, we probably want to hash partition the primary key as well
SQL> -- so we'll do that separately
SQL> --
SQL> set serverout on
SQL> declare
2 l_errors number;
3 begin
4 dbms_redefinition.copy_table_dependents(
5 uname => user,
6 orig_table => 'T',
7 int_table => 'T_PAR',
8 copy_indexes => 0,
9 copy_triggers => true,
10 copy_constraints => false,
11 copy_privileges => true,
12 ignore_errors => false,
13 num_errors => l_errors,
14 copy_statistics => false,
15 copy_mvlog => false);
16
17 dbms_output.put_line('errors=' || l_errors);
18 end;
19 /
errors=0
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- our new PK
SQL> --
SQL> alter table T_PAR add constraint T_PAR_PK primary key (pk )
2 using index local
3 /
Table altered.
SQL>
SQL> --
SQL> -- and this is the moment where we convert over
SQL> --
SQL> begin
2 dbms_redefinition.finish_redef_table(
3 uname => user,
4 orig_table => 'T',
5 int_table => 'T_PAR');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> @pt "select * from user_part_tables where table_name = 'T'"
old 2: p_query varchar2(32767) := q'{&1}';
new 2: p_query varchar2(32767) := q'{select * from user_part_tables where table_name = 'T'}';
TABLE_NAME : T
PARTITIONING_TYPE : HASH
SUBPARTITIONING_TYPE : NONE
PARTITION_COUNT : 8
DEF_SUBPARTITION_COUNT : 0
PARTITIONING_KEY_COUNT : 1
SUBPARTITIONING_KEY_COUNT : 0
STATUS : VALID
DEF_TABLESPACE_NAME : USERS
DEF_PCT_FREE : 10
DEF_PCT_USED :
DEF_INI_TRANS : 1
DEF_MAX_TRANS : 255
DEF_INITIAL_EXTENT : DEFAULT
DEF_NEXT_EXTENT : DEFAULT
DEF_MIN_EXTENTS : DEFAULT
DEF_MAX_EXTENTS : DEFAULT
DEF_MAX_SIZE : DEFAULT
DEF_PCT_INCREASE : DEFAULT
DEF_FREELISTS : 0
DEF_FREELIST_GROUPS : 0
DEF_LOGGING : NONE
DEF_COMPRESSION : NONE
DEF_COMPRESS_FOR :
DEF_BUFFER_POOL : DEFAULT
DEF_FLASH_CACHE : DEFAULT
DEF_CELL_FLASH_CACHE : DEFAULT
REF_PTN_CONSTRAINT_NAME :
INTERVAL :
IS_NESTED : NO
DEF_SEGMENT_CREATION : NONE
DEF_INDEXING : ON
DEF_INMEMORY : NONE
DEF_INMEMORY_PRIORITY :
DEF_INMEMORY_DISTRIBUTE :
DEF_INMEMORY_COMPRESSION :
DEF_INMEMORY_DUPLICATE :
-----------------
PL/SQL procedure successfully completed.
SQL>
SQL>