删除分区提示ORA-00942

为了限制开发人员在测试库随意变更结构，我们在测试库中为开发人员创建了专用账号，这个账号内只包含系统真正属主数据库用户表的同义词，授予了select/insert/update/delete权限，无法执行create table/alter table等操作。

但是，最近有个需求，需要用这个专用账号删除分区表的某个分区，执行的时候，提示这个错误，

ORA-00942: table or view does not exist

ORA-00942这个错误，包含了两层含义，一个是这张表/视图真的不存在，另一个就是为了保护，这张表/视图存在，但是你没权限。很显然，在上面的例子中，是后者的含义。

这问题能解决么？

官方文档说，如果要具有删除分区的权限，就需要授予DROP ANY TABLE的权限，

这就能解决了？我们通过实验，进行验证，首先，在TEST1下创建分区表，

SQL> show user
USER is "TEST1"

SQL> CREATE TABLE interval_sale
  2      ( prod_id        NUMBER(6)
  3      , cust_id        NUMBER
  4      , time_id        DATE
  5      , channel_id     CHAR(1)
  6      , promo_id       NUMBER(6)
  7      , quantity_sold  NUMBER(3)
  8      , amount_sold    NUMBER(10,2)
  9      ) 
 10    PARTITION BY RANGE (time_id)
 11    INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
 12      ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')),
 13        PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')),
 14        PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),
 15        PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')));
Table created.

在TEST2下创建同义词，

SQL> show user
USER is "TEST2"

SQL> create synonym interval_sales for test1.interval_sales;
Synonym created.

在TEST2下，删除分区，提示ORA-00942，

SQL> show user
USER is "TEST2"

SQL> alter table interval_sales drop partition for (to_date('1-1-2004', 'DD-MM-YYYY'));
alter table interval_sales drop partition for (to_date('1-1-2004', 'DD-MM-YYYY'))
*
ERROR at line 1:
ORA-00942: table or view does not exist

给TEST2授予DROP ANY TABLE权限，

SQL> show user
USER is "SYS"

SQL> grant drop any table to test2;
Grant succeeded.

在TEST2下删除分区(注意，此处是用同义词的删除)，还是报了ORA-00942，

SQL> show user
USER is "TEST2"

SQL> alter table interval_sales drop partition for (to_date('1-1-2004', 'DD-MM-YYYY'));
alter table interval_sales drop partition for (to_date('1-1-2004', 'DD-MM-YYYY'))
*
ERROR at line 1:
ORA-00942: table or view does not exist

再确认下，很明显，drop或者truncate分区，要求存在DROP ANY TABLE权限，

Additional Prerequisites for Partitioning Operations If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause.

You cannot grant privileges directly to a single partition of a partitioned table.

难道DROP ANY TABLE没起作用？别着急，我们改用“schema.table_name”的形式，删除分区，执行成功了，

SQL> show user
USER is "TEST2"

SQL> alter table test1.interval_sales drop partition for (to_date('1-1-2004', 'DD-MM-YYYY'));
Table altered.

用“同义词”删除分区提示错误，用“schema.table_name”删除分区成功，这两者是何区别？

我们看下同义词的定义，同义词只是schema对象的别名，他除了数据字典定义，不占用任何存储空间，

A synonym is an alias for a schema object. For example, you can create a synonym for a table or view, sequence, PL/SQL program unit, user-defined object type, or another synonym. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

分区表的同义词，代表的是分区表对象，但是分区表的每个分区其实也是个对象，我们通过user_objects视图就可以看出来，每个分区名称，都是实实在在的对象，但是每个分区，并没有对应的同义词，

SQL> select object_name, subobject_name, object_id
     from user_objects; 
OBJECT_NAME               SUBOBJECT_NAME                  OBJECT_ID
------------------------- ------------------------------ ----------
INTERVAL_SALES            SYS_P161                           104893
INTERVAL_SALES                                               104888
INTERVAL_SALES            P0                                 104889
INTERVAL_SALES            P1                                 104890
INTERVAL_SALES            P3                                 104892

因此，用“同义词”删除分区，确实因为没有对应分区的同义词，提示ORA-00942，并不是为了保护，用“schema.table_name”删除分区，则是具备DROP ANY TABLE权限删除真实对象的操作，成功是正常的。

从这个问题，可以看到，Oracle的每个错误号，其实都蕴含着丰富的知识，有时看着一个简单的提示，其实背后的原理，超乎我们想象，或者绝对是精妙的设计，这才是我们需要体会和汲取的。