ORA-00902 with table partition.

[traveller]

I tried to alter two unpartitioned tables and add partitions on the tables. The DDL code like this:

ALTER TABLE schema.fact_table_name
ADD PARTITION BY RANGE (partition_key_column)
(
PARTITION partition_name_200607 VALUES LESS THAN (TO_DATE(' 20060801, 'YYYYMMDD', 'NLS_DATE_LANGUAGE = American'))
NOLOGGING
NOCOMPRESS
TABLESPACE TABLE_SPACE_NAME
UPDATE INDEXES;

The ORA-00902 invalid datatype was generated by execution this DDL. Is this because partittion_key column datatype doesn't match with range? one partition_key_column datatype is number and other partition_key_column datatype is date. But ORA-00902 were generated for two tables. I also tried different syntex for adding partition clause, it generated different errors like ORA-14020, 14004 and 14501. If the table and indexes are not partitioned, can I use add partition clause as above or I have to drop table and create table with partition? I failed to find resolution on line and on your partition documents. Please advise the reason and resolutions. Thanks a lot.

[PAVB]

You cannot add partitions to a non-partitioned table

[traveller]

PAVB:

Thanks for your input. Based on what you said, add partition clause can not work on unpartitioned table. So I have to create a new table with the partition I need. Am I right? These two tables have contained huge data and I don't want to truncate data and reload it again. This is why I tried to use " alter table add partition" clause. So based on this circumstance, what is the best approach to put partition on existing tables with data? Thanks in advance.

[PAVB]

If you can afford a little downtime for this table, this is what I would do...

1- Rename your current table as fact_table_name_old
2- Script and then drop all indexes, pk, etc from fact_table_name_old
3- Create new fact_table_name as partitioned (*)
4- Load fact_table_name from fact_table_name_old
5- Rebuild PK, indexes (**), etc.
6- Gather fresh stats

(*) I'm assuming this is a factual table in a data warehouse environment therefore, purging is out of the picture. If my assumption is correct, be sure your partitioning strategy helps the most used queries.

(**) You might want to evaluate when indexes have to be partitioned or not.

[waitecj]

Have a look at DBMS_REDEFINITION. We moved a load of huge, non-partitioned tables to partitioned using this. It effectively does what PAVB is suggesting, but the other way round (it creates a YOUR_TAB_TEMP table with data, creates the constraints, then renames to YOUR_TAB).

[PAVB]

...am I good or what? lol