DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-00902 with table partition.

  1. #1
    Join Date
    Jan 2006
    Posts
    39

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    39
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  5. #5
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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...

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ...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
  •  


Click Here to Expand Forum to Full Width