Relationship between ORA-01031 and DBMS_COMPRESSION package
(Doc ID 2382266.1)
Last updated on AUGUST 04, 2018
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.2]
Information in this document applies to any platform.
In case an user executes get_compression_type procedure in dbms_compresssion package
it shows ORA-01031 depending on an user; test_user1, test_user2
The following is the test case to be reproducible.
--- scenario ---
drop user test_user1 cascade;
drop user test_user2 cascade;
create user test_user1 identified by test_user1;
grant connect, resource to test_user1;
create table test_user1.test_tab (id number);
insert into test_user1.test_tab values(100);
create user test_user2 identified by test_user2;
grant connect, resource to test_user2;
grant select on test_user1.test_tab to test_user2;
grant select on seg$ to test_user1;
grant select on DBA_OBJECTS to test_user1;
grant select on seg$ to test_user2;
grant select on DBA_OBJECTS to test_user2;
Then the below SQL can be executed successfully or not depending on an user.
SELECT rowid, id,
CASE DBMS_COMPRESSION.get_compression_type ('TEST_USER1', 'TEST_TAB', rowid)
WHEN 1 THEN 'NOCOMPRESS'
WHEN 2 THEN 'ADVANCED'
WHEN 4 THEN 'QUERY_HIGH'
WHEN 8 THEN 'QUERY_LOW'
WHEN 16 THEN 'ARCHIVE_HIGH'
WHEN 32 THEN 'ARCHIVE_LOW'
END AS compression_type
WHERE rownum <= 5;
- case I : in case of executing the above SQL as an user, test_user1, it works successfully
- case II : in case of executing the above SQL as an user, test_user2, it does not work.
Instead, we can get the following errors.
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_ROWID", line 142
ORA-06512: at "SYS.DBMS_COMPRESSION", line 246
1. If test_user2 has dba privilege, it works.
2. With 12.1 and 12.2 as well, it works fine from both version.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!