Relationship between ORA-01031 and DBMS_COMPRESSION package
(Doc ID 2382266.1)
Last updated on JANUARY 22, 2020
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
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);
commit;
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
FROM test_user1.test_tab
WHERE rownum <= 5;
That is,
- 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
Changes
1. If test_user2 has dba privilege, it works.
2. With 12.1 and 12.2 as well, it works fine from both version.
Reproduced ?
-----------------
11.2.0.4.0 yes
12.1.0.2.0 no
12.2.0.1.0 no
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |