I have a CDB user called "MYADMIN" and I'm trying to make it connect to log miner.
-- enable calling admin username on CDB
ALTER SESSION set "_ORACLE_SCRIPT"=true
/
-- create unique table space for admin
CREATE TABLESPACE myadmints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON
/
-- create admin user on CDB
CREATE USER myadmin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE myadmints QUOTA UNLIMITED ON myadmints ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/
-- grant needed permissions
GRANT DBA to myadmin
GRANT CREATE SESSION TO myadmin
GRANT CREATE TABLE TO myadmin
GRANT EXECUTE_CATALOG_ROLE TO myadmin
GRANT EXECUTE ON DBMS_LOGMNR TO myadmin
GRANT SELECT ON V_$DATABASE TO myadmin
GRANT SELECT ON V_$LOGMNR_CONTENTS TO myadmin
GRANT SELECT ON V_$ARCHIVED_LOG TO myadmin
GRANT SELECT ON V_$LOG TO myadmin
GRANT SELECT ON V_$LOGFILE TO myadmin
GRANT RESOURCE, CONNECT TO myadmin
I've selected one line from 'v$archived_log' and trying to load the file.
BEGIN
DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/ARC000011_1061542581',Options=>DBMS_LOGMNR.new);
DBMS_LOGMNR.START_LOGMNR(StartScn=>3083464, EndScn=>3388245, Options=>DBMS_LOGMNR.DICT_FROM_ONLINECATALOG+DBMS_LOGMNR.NO_ROW_ID_IN_STMT);
END;
I can run it from the sys as sysdba
user, but when I run it from my "myadmin" user I'm getting:
Error report -
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 3
01435. 00000 - "user does not exist"
*Cause:
*Action:
The error is about the START_LOGMNR
line when I remove it there is no error.
Which privilege I'm missing?
Best Answer
That is what you get when you break and ignore the basic concepts of the CDB architecture and common users and use unsupported "workarounds" (
ALTER SESSION set "_ORACLE_SCRIPT"=true
).Above code works flawlessly when you create the user in a supported way.
Then:
Whenever you use
ALTER SESSION set "_ORACLE_SCRIPT"=true
to create your own users and objects, they have theirORACLE_MAINTAINED
property set toY
. This is not supported and can break functionality of features.