-
ORA-00902 with table partition.
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.
-
You cannot add partitions to a non-partitioned table
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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.
Last edited by PAVB; 05-22-2007 at 01:41 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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).
Assistance is Futile...
-
...am I good or what? lol
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|