Re: Truncate not working

In article <31B5A8E9.3A51_at_cam.ac.uk>, Charles Jardine <cj10_at_cam.ac.uk> wrote:
>bwskiles_at_adpc.purdue.edu wrote:
>>
>> There's a table, COMPLETE.PAYROLL, and a user, OPERATIONS, that needs to truncate
>> the table. OPERATIONS has been granted the DELETE ANY TABLE system privilege
>> by SYS, and also has insert, select, update, delete rights to the table.
>>
>> When OPERATIONS issues the "truncate table complete.payroll;" command, it gets the
>> insufficient privileges error (ORA-01031). I also granted the COMPLETE user the
>
>Despite the documentation, your need DROP ANY TABLE to truncate others' tables.
>
>I don't know if this is a bug in the doucmentation or a bug in the implementation!
>
>Charles Jardine - U. of Cambridge Computing Service.

It's a bug in both. A "truncate" is effectively a "drop", with a re-create after it. You'll notice a similar problem if you have ANY foreign keys depending on the primary key of your COMPLETE.PAYROLL table. You can only truncate a table which could otherwise be dropped. Oracle returns an erroneous message in such cases, saying that no "ENABLED" foreign keys can be present, if you want to truncate. In fact, no FK constraints at all can exist.

-- 
-------------------------------------------------------------
Kevin Fries                         kelfink_at_ecst.csuchico.edu
CPD/PB, C Developer/DBA  http://www.ecst.csuchico.edu/~kelfink