删除分区提示ORA-00942

逃离我推掉我的手 2023-06-25 04:50 19阅读 0赞

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

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

ORA-00942: table or view does not exist

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

这问题能解决么？

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

![format_png][]

这就能解决了？我们通过实验，进行验证，首先，在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，

再确认下，很明显，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的每个错误号，其实都蕴含着丰富的知识，有时看着一个简单的提示，其实背后的原理，超乎我们想象，或者绝对是精妙的设计，这才是我们需要体会和汲取的。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9hMXE2NGljODZQYWw5RW9pYjlsc3VWRmZleG1SaFJrOXh0ZjVuSHpPeU01QllhTmJpY21xbzhDaWJ1RDNuNDhpYWlja0xzUnFiNFFpYU9DR0c1bmRqNkdEaHhFeUEvNjQw?x-oss-process=image/format,png