SQL错误:ORA-14006:无效的分区名称 [英] SQL Error: ORA-14006: invalid partition name

问题描述

我正在尝试使用下面的SQL语句在Oracle 12C R1中对现有表进行分区.

I am trying to partition an existing table in Oracle 12C R1 using below SQL statement.

ALTER TABLE TABLE_NAME MODIFY
PARTITION BY RANGE (DATE_COLUMN_NAME)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION part_01  VALUES LESS THAN (TO_DATE('01-SEP-2017', 'DD-MON-RRRR'))
) ONLINE;

获取错误:

Error report -
SQL Error: ORA-14006: invalid partition name
14006. 00000 -  "invalid partition name"
*Cause:    a partition name of the form <identifier> is
           expected but not present.
*Action:   enter an appropriate partition name.

需要根据data datatype列进行分区，间隔为1个月.

Partition needs to be done on the basis of data datatype column with the interval of one month.

表中日期时间"列的最小值为2017年9月1日.

Min value of Date time column in the Table is 01-SEP-2017.

推荐答案

您不能像这样对现有表进行分区.该语句正在修改尚未创建的分区.我不知道执行此操作的自动方式，我不确定您可以执行此操作.

You can't partition an existing table like that. That statement is modifying the partition that hasn't been created yet. I don't know the automatic way to do this operation and I am not sure that you can do it.

尽管我已经做过很多次了，但是需要手动操作.如果找不到自动解决方案，请执行以下操作:

Although I have done this thing many times but with manual steps. Do the following if you can't find an automated solution:

1. 使用子句和所有首选项创建一个名为table_name_part的分区表.
2. 将原始表中的所有行插入此分区表中.注意压缩.如果对表有一些压缩(基本或HCC)，则必须使用 + APPEND 提示.
3. 在分区表上创建原始表的约束和索引.
4. 重命名表并删除原始表.在对它们进行计数之前，请勿将其丢弃.

1. Create a partitioned table named table_name_part with your clauses and all your preferences.
2. Insert into this partitioned table all rows from original table. Pay attention to compression. If you have some compression on table (Basic or HCC) you have to use + APPEND hint.
3. Create on partitioned table your constrains and indexes from the original table.
4. Rename the tables and drop the original table. Do not drop it until you make some counts on them.

我看到您的表可以选择自动创建分区(如果不存在). (NUMTOYMINTERVAL(1，'MONTH'))因此，您必须仅使用第一个分区来创建表.我假设您这里有很多只读数据，因此与上个月相比，您在一致性方面不会有任何问题.可能存在一些读写数据，因此当您要在新表和切换表中插入数据时，必须格外小心.

I saw that your table has the option to auto-create partition if it does not exists. (NUMTOYMINTERVAL(1,'MONTH')) So you have to create your table with first partition only. I assume that you have here a lot of read-only data, so you won't have any problem with consistency instead of last month. Probably there is some read-write data so there you have to be more careful with the moment when you want to insert data in new table and switch tables.

希望能为您提供帮助.据我所知，可能会有一个名为DBMS_REDEFINITION的软件包，可以帮助您自动完成我的步骤.如果您需要更多详细信息或需要有关我的方法的帮助，请不要犹豫.

Hope to help you. As far as I know there might be a package named DBMS_REDEFINITION that can help you with an automated version of my steps. If you need more details or need some help on my method, please don't hesitate.

更新: 在Oracle 12c R2中，您可以使用方法将表从未分区表转换为分区表.在下面找到一个链接.现在这对我来说是一个挑战，我正在尝试进行转换，但是我认为无法在12c R1中在线进行此转换.

UPDATE: From Oracle 12c R2 you can convert a table from an unpartitioned to a partitioned one with your method. Find a link below. Now this is a challenge for me and I am trying to convert, but I think there is no way to make this conversion online in 12c R1.

在以前的版本中，您可以使用以下命令对未分区的表进行分区 以几乎在线"的方式交换分区或DBMS_REDEFINITION， 但两种方法都需要多个步骤. Oracle数据库12c第2版 使从未分区表转换为 分区表，仅需一个命令，无需停机.

In previous releases you could partition a non-partitioned table using EXCHANGE PARTITION or DBMS_REDEFINITION in an "almost online" manner, but both methods require multiple steps. Oracle Database 12c Release 2 makes it easier than ever to convert a non-partitioned table to a partitioned table, requiring only a single command and no downtime.

解决方案

我为您找到了解决方案.在这里，您将拥有我运行的所有所有步骤以在线转换表. :)

I found a solution for you. Here you will have all of my steps that I run to convert table online. :)

1. Create regular table and populate it.

CREATE TABLE SCOTT.tab_unpartitioned
(
    id              NUMBER,
    description     VARCHAR2 ( 50 ),
    created_date    DATE
);
INSERT INTO tab_unpartitioned
        SELECT LEVEL,
               'Description for ' || LEVEL,
               ADD_MONTHS ( TO_DATE ( '01-JAN-2017', 'DD-MON-YYYY' ),
                            -TRUNC ( DBMS_RANDOM.VALUE ( 1, 4 ) - 1 ) * 12 )
          FROM DUAL
    CONNECT BY LEVEL <= 10000;
COMMIT;

2. Create partitioned table with same structure.

--If you are on 11g create table with CREATE TABLE command but with different name. ex: tab_partitioned

CREATE TABLE SCOTT.tab_partitioned
(
    id              NUMBER,
    description     VARCHAR2 ( 50 ),
    created_date    DATE
)
PARTITION BY RANGE (created_date)
INTERVAL( NUMTOYMINTERVAL(1,'YEAR'))
(PARTITION part_2015 VALUES LESS THAN (TO_DATE ( '01-JAN-2016', 'DD-MON-YYYY' )),
 PARTITION part_2016 VALUES LESS THAN (TO_DATE ( '01-JAN-2017', 'DD-MON-YYYY' )),
 PARTITION part_2017 VALUES LESS THAN (TO_DATE ( '01-JAN-2018', 'DD-MON-YYYY' )));

--this is an alter command that works only in 12c.
ALTER TABLE tab_partitioned
    MODIFY
        PARTITION BY RANGE (created_date)
        (PARTITION part_2015 VALUES LESS THAN (TO_DATE ( '01-JAN-2016', 'DD-MON-YYYY' )),
         PARTITION part_2016 VALUES LESS THAN (TO_DATE ( '01-JAN-2017', 'DD-MON-YYYY' )),
         PARTITION part_2017 VALUES LESS THAN (TO_DATE ( '01-JAN-2018', 'DD-MON-YYYY' )));

3. Check if the table can be converted. This procedure should run without any error. 
Prerequisites: table should have an UNIQUE INDEX and a Primary Key constraint.

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','TAB_UNPARTITIONED');

4. Run the following steps like I have done.

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED'); 
var num_errors varchar2(2000);
EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED', 1,TRUE,TRUE,TRUE,FALSE,:NUM_ERRORS,FALSE);
SQL> PRINT NUM_ERRORS -- Should return 0
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED');
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','TAB_UNPARTITIONED','TAB_PARTITIONED');

在脚本末尾，您将看到原始表已分区.

At the end of the script you will see that the original table is partitioned.

这篇关于SQL错误:ORA-14006:无效的分区名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！