any chance of upgrading to 12c?
if yes, then you could use two new features ("online Conversion of a Non-Partitioned Table to a Partitioned Table" and "Filtering Maintenance Operations" ) introduced in 12.2 to get this done easily.
Sometime like this.
demo@ORA12C> create table t as
2 select a.*, to_date('01-jan-2013','dd-mon-yyyy')
3 + mod(rownum,1825) created_dt
4 from all_objects a;
Table created.
demo@ORA12C> select trunc(created_dt,'Y'),count(*)
2 from t
3 group by trunc(created_dt,'Y')
4 order by trunc(created_dt,'Y');
TRUNC(CREAT COUNT(*)
----------- ----------
01-JAN-2013 12409
01-JAN-2014 12241
01-JAN-2015 12045
01-JAN-2016 12078
01-JAN-2017 12012
demo@ORA12C> alter table t modify
2 partition by range( created_dt )
3 ( partition p_2017 values less than
4 (to_date('01-jan-2018','dd-mon-yyyy')) ,
5 partition p_2018 values less than
6 (to_date('01-jan-2019','dd-mon-yyyy')) )
7 including rows where created_dt >=
8 to_date('01-jan-2017','dd-mon-yyyy')
9 online;
Table altered.
demo@ORA12C> select trunc(created_dt,'Y'),count(*)
2 from t
3 group by trunc(created_dt,'Y')
4 order by trunc(created_dt,'Y');
TRUNC(CREAT COUNT(*)
----------- ----------
01-JAN-2017 12012
demo@ORA12C> select dbms_metadata.get_ddl('TABLE','T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE "DEMO"."T"
( "OWNER" VARCHAR2(128) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER NOT NULL ENABLE,
"EDITION_NAME" VARCHAR2(128),
"SHARING" VARCHAR2(18),
"EDITIONABLE" VARCHAR2(1),
"ORACLE_MAINTAINED" VARCHAR2(1),
"APPLICATION" VARCHAR2(1),
"DEFAULT_COLLATION" VARCHAR2(100),
"DUPLICATED" VARCHAR2(1),
"SHARDED" VARCHAR2(1),
"CREATED_APPID" NUMBER,
"CREATED_VSNID" NUMBER,
"MODIFIED_APPID" NUMBER,
"MODIFIED_VSNID" NUMBER,
"CREATED_DT" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DATA"
PARTITION BY RANGE ("CREATED_DT")
(PARTITION "P_2017" VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DATA" ,
PARTITION "P_2018" VALUES LESS THAN (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM
-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_DATA" )
demo@ORA12C>