Oracle truncate synonym 不允许 ORA-00942 drop any table

truncate其他用户的表，需要的是DROP ANY TABLE

alter  table不行的，
grant all on sales  to XXX 也是alter table的权限，所以不能truncate

1、在功能上，truncate是清空一个表的内容，它相当于delete from table_name。
2、delete是dml操作，truncate是ddl操作；因此，用delete删除整个表的数据时，会产生大量的roolback，占用很多的rollback segments， 而truncate不会。
3、在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，便于以后的使用，另外它是“假相”的删除，相当于windows中用delete删除数据是把数据放到回收站中，还可以恢复，当然如果这个时候重新启动系统（OS或者RDBMS），它也就不能恢复了！
而用truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被立即释放，相当于windows中用shift+delete删除数据，不能够恢复！
4、truncate 调整high water mark 而delete不；truncate之后，TABLE的HWM退回到 INITIAL和NEXT的位置（默认）delete 则不可以。
5、truncate 只能对TABLE，delete 可以是table,view,synonym。
6、TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限。
7、在外层中，truncate或者delete后，其占用的空间都将释放。
8、truncate和delete只删除数据，而drop则删除整个表（结构和数据）。
 

或者在其他用户下建立一个store-proc, 这个store-proc使用 AUTHID DEFINER

3、权限问题导致

平时所做的对其它用户下资源的操作权限有些是通过角色授权的，但在存储过程、函数、触发器不能直接使用，需要单独授权。授权语句举例：

grant select on sys.XXX TO XXX;
4、需要在存储过程中使用role权限（这个我没太看懂，以下代码部分为粘贴，来自http://www.cnblogs.com/jzbml/p/5991918.html）

复制代码
SQL> select * from dba_role_privs where grantee='SUK';
 
  GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
  ------------ ------------ ------------ ------------
  SUK DBA NO YES
  SUK CONNECT NO YES
  SUK RESOURCE NO YES
 
  --用户SUK拥有DBA这个role
 
  --再创建一个测试存储过程：
  create or replace procedure p_create_table  
    is
  begin
  Execute Immediate 'create table create_table(id int)';
  end p_create_table;
 
  --然后测试
  SQL>  begin  p_create_table end;
 
  begin p_create_table; end;
 
  ORA-01031: 权限不足
  ORA-06512: 在"SUK.P_CREATE_TABLE", line 3
  ORA-06512: 在line 1
 
  --可以看到，即使拥有DBA role，也不能创建表。role在存储过程中不可用。
  --遇到这种情况，我们一般需要显式进行系统权限，如grant create table to suk;
  --但这种方法太麻烦，有时候可能需要进行非常多的授权才能执行存储过程
  --实际上，oracle给我们提供了在存储过程中使用role权限的方法：
  --修改存储过程，加入Authid Current_User时存储过程可以使用role权限。
create or replace procedure p_create_table  
Authid Current_User is
begin
Execute Immediate 'create table create_table(id int)';
end p_create_table
;
 
  --再尝试执行：
begin  p_create_table end;
 
  PL/SQL procedure successfully completed
 
drop table create_table

对同义词synonym是不能用truncate的。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

SQL> SELECT COUNT(1) FROM T_EMP;

  COUNT(1)
----------
         3

SQL> TRUNCATE TABLE T_EMP;S

TRUNCATE TABLE T_EMP

ORA-00942: 表或视图不存在 因为它确实不是table。。

SQL> truncate synonym sales; 
truncate synonym sales

ORA-03290: 无效的截断命令 - 缺失 CLUSTER 或 TABLE 关键字

SQL> 

SQL> SELECT S.OBJECT_NAME,S.OBJECT_TYPE,s.status FROM user_objects s WHERE s.object_name='T_EMP';

OBJECT_NAME          OBJECT_TYPE           STATUS
--------------------       -------------------         -------
T_EMP                     SYNONYM                 VALID

SQL>  SELECT dbms_metadata.get_ddl('SYNONYM','T_EMP')  TEXT FROM dual;

TEXT
--------------------------------------------------------------------------------

  CREATE OR REPLACE SYNONYM "SCOTT"."T_EMP" FOR "SCOTT"."TEST2"

SQL> TRUNCATE TABLE TEST2;

Table truncated

SQL> SELECT COUNT(1) FROM T_EMP;

  COUNT(1)
----------
         0

SQL>
 

------------------------------

oracle文档中对truncate权限的要求是需要某表在当前登录的用户下，或者当前登录的用户有drop any table的权限。

但是如果不满足第一个条件的情况下，要让某用户满足第二个条件就导致权限过大了。

参考网上的讨论，可以使用存储过程来时间。

例如有两个用户 u1,u2，u1下有表 test1a

现在想要实现u2能 truncate u1下的表 test1a。

可以使用用户u1创建存储过程，

create procedure u1.stgtruncate(table_name in varchar2) as
begin
  execute immediate 'truncate table '||table_name;
end;

然后将该存储过程的权限赋予u2，

grant execute on u1.stgtruncate to u2;

现在登录u2，通过执行如下sql即可truncate u1下的表test1a

call u1.stgtruncate('test1a');

在Oracle中， 如何让普通用户可以TRUNCATE其他用户的表 ？

用户1若要删除用户2的索引，则用户1需要有DROP ANY INDEX的权限。

用户1若要TRUNCATE用户2的表(paritition 也是一样)，则用户1需要有DROP ANY TABLE的权限。但是，DROP ANY INDEX和DROP ANY TABLE的权限过大，一般不能赋予普通用户这2个权限，那么可以通过写存储过程来实现该功能，如下所示：

CREATE OR REPLACE PROCEDURE PRO_TRUNC_DROP_LHR(COMMAND IN VARCHAR2,

                                               O_TYPE  IN VARCHAR2,

                                               OWNER   IN VARCHAR2,

                                               O_NAME  IN VARCHAR2) AUTHID DEFINER AS

  V_SQL VARCHAR2(4000);

BEGIN

  IF UPPER(COMMAND) IN ('DROP', 'TRUNCATE') AND

     UPPER(O_TYPE) IN ('TABLE', 'INDEX') THEN  

    V_SQL := COMMAND || ' ' || O_TYPE || ' ' || OWNER || '.' || O_NAME;

    EXECUTE IMMEDIATE V_SQL;

  END IF;

END PRO_TRUNC_DROP_LHR;

使用示例如下所示：

创建用户1和用户2，分别赋予CONNECT和RESOURCE权限：

SQL> SHOW USER

USER is "SYS"

SQL> 

SQL> CREATE USER LHR_U1 IDENTIFIED BY LHR_U1;

User created.

SQL> CREATE USER LHR_U2 IDENTIFIED BY LHR_U2;

User created.

SQL> GRANT CONNECT,RESOURCE TO LHR_U1;

Grant succeeded.

SQL> GRANT CONNECT,RESOURCE TO LHR_U2;

Grant succeeded.

用户2创建表U2_T_LHR：

SQL> CONN LHR_U2/LHR_U2

Connected.

SQL> CREATE TABLE U2_T_LHR AS SELECT * FROM DUAL;

Table created.

SQL> SELECT * FROM U2_T_LHR;

D

-

X

SQL> CREATE INDEX IDX_U2_T_LHR ON U2_T_LHR(DUMMY);

Index created.

SQL> 

SQL> GRANT SELECT,DELETE,UPDATE ON U2_T_LHR TO LHR_U1;

Grant succeeded.

用户2创建存储过程并赋予用户1的执行权限：

SQL> CREATE OR REPLACE PROCEDURE PRO_TRUNC_DROP_LHR(COMMAND IN VARCHAR2,

  2                                                 O_TYPE  IN VARCHAR2,

  3                                                 OWNER   IN VARCHAR2,

  4                                                 O_NAME  IN VARCHAR2) AUTHID DEFINER AS

  5    V_SQL VARCHAR2(4000);

  6  BEGIN

  7    IF UPPER(COMMAND) IN ('DROP', 'TRUNCATE') AND

  8       UPPER(O_TYPE) IN ('TABLE', 'INDEX') THEN  

  9      V_SQL := COMMAND || ' ' || O_TYPE || ' ' || OWNER || '.' || O_NAME;

 10      EXECUTE IMMEDIATE V_SQL;

 11    END IF;

 12  END PRO_TRUNC_DROP_LHR;

 13  /

Procedure created.

SQL> GRANT EXECUTE ON PRO_TRUNC_DROP_LHR TO LHR_U1;

Grant succeeded.

用户1开始查询：

SQL> conn LHR_U1/LHR_U1

Connected.

SQL> 

SQL> SELECT * FROM LHR_U2.U2_T_LHR;

D

-

X

SQL> TRUNCATE TABLE LHR_U2.U2_T_LHR;

TRUNCATE TABLE LHR_U2.U2_T_LHR

                      *

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> DROP INDEX LHR_U2.IDX_U2_T_LHR;

DROP INDEX LHR_U2.IDX_U2_T_LHR

                  *

ERROR at line 1:

ORA-01418: specified index does not exist

SQL> DROP TABLE  LHR_U2.U2_T_LHR;

DROP TABLE  LHR_U2.U2_T_LHR

                   *

ERROR at line 1:

ORA-01031: insufficient privileges

可以看到TRUNCATE、DROP都没有权限，下面采用存储过程删除：

SQL> EXEC LHR_U2.PRO_TRUNC_DROP_LHR('TRUNCATE','TABLE','LHR_U2','U2_T_LHR');

PL/SQL procedure successfully completed.

SQL>  SELECT * FROM LHR_U2.U2_T_LHR;

no rows selected

SQL>  EXEC LHR_U2.PRO_TRUNC_DROP_LHR('DROP','INDEX','LHR_U2','IDX_U2_T_LHR');

PL/SQL procedure successfully completed.

SQL>  EXEC LHR_U2.PRO_TRUNC_DROP_LHR('DROP','TABLE','LHR_U2','U2_T_LHR');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM LHR_U2.U2_T_LHR;

SELECT * FROM LHR_U2.U2_T_LHR

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> CONN LHR_U2/LHR_U2

Connected.

SQL> 

SQL> SELECT * FROM USER_INDEXES;

no rows selected

---

假如想清空某个用户下的多个表或所有表

则可以通过将存储过程的表名写成传参形式。

2.1 创建存储过程

create procedure scott.stgtruncate(table_name in varchar2) as
begin
  execute immediate 'truncate table '||table_name;
end;

2.2 授予目标用户对该存储过程的执行权限

grant execute on scott.stg

truncate to jiao ;

2.3 调用存储过程清空表

call scott.stgtruncate('t1');