Relationship between ORA-01031 and DBMS_COMPRESSION package
Last updated on APRIL 16, 2018
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms