oracle-databasedatabase-partitioning

Oracle adding a partition by year to timestamp column

I have one Oracle table (my_table) with one timestamp column (my_column) I´d like to add some partitions to the table by year

ALTER TABLE my_table
ADD PARTITION BY RANGE(EXTRACT(YEAR FROM my_column))
(
  PARTITION p1 VALUES LESS THAN (2019),
  PARTITION p2 VALUES LESS THAN (2020),
  PARTITION p_max VALUES LESS THAN (2100)
);

It says: "ORA-00902:tipo de dato no válido"

Any idea how to solve this?

thanks very much

Solution

  • You can use:

    ALTER TABLE my_table
  MODIFY PARTITION BY
  RANGE(my_column)
  INTERVAL(INTERVAL '1' YEAR)
  (
    PARTITION p1 VALUES LESS THAN (TIMESTAMP '2019-01-01 00:00:00'),
    PARTITION p2 VALUES LESS THAN (TIMESTAMP '2020-01-01 00:00:00'),
    PARTITION p_max VALUES LESS THAN (TIMESTAMP '2100-01-01 00:00:00')
  );