Add partition to table

Question

I have created a table and trying to add a partition on it but it is failing with the error invalid datatype.

drop table test;
create table test (id number , h_date date);

ALTER TABLE TEST ADD  PARTITION BY RANGE ("h_date") INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(PARTITION "P20120101"  VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))                                                                                                            
PARALLEL 4                                                                                                              
PCTFREE 10;    

error:

Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Is something wrong here? Thanks in advance.

Answer 1

As @Wernfried Domscheit pointed out you need to redefine your table. For an existing and data-populated table the following block might be referenced as a method :

SQL> create table test(id number , h_date date);     
Table created

SQL> insert all
  2         into test values(1,date'2011-12-31')
  3         into test values(1,date'2012-01-01')
  4         into test values(1,date'2012-01-02')
  5  select * from dual;     
3 rows inserted

SQL> select *
  2    from test;     
        ID H_DATE
---------- -----------
         1 31.12.2011
         1 01.01.2012
         1 02.01.2012

SQL> create table test2
  2    partition by range(h_date)
  3    interval
  4   (numtodsinterval(1,'day'))
  5   (partition "P20120101" values less than(to_date('2012-01-01 00:00:00', 
                             'yyyy-mm-dd hh24:mi:ss', 'NLS_CALENDAR=GREGORIAN')))
  6  as
  7  select * from test;     
Table created

SQL> select *
  2    from test2
  3     partition("P20120101");     
        ID H_DATE
---------- -----------
         1 31.12.2011

SQL> drop table test;
Table dropped

SQL> rename test2 to test;
Table renamed