How can I use the PL/SQL hierarchical profiler in an Oracle autonomous database? When I try to run the following code, I get the error "PLS-00201: identifier 'DBMS_HPROF' must be declared":
begin
sys.dbms_hprof.create_tables(force_it => true);
end;
/
The package DBMS_HPROF exists but is not granted to any user:
SQL> select owner,object_name,object_type from dba_objects where object_name='DBMS_HPROF' order by 1,2,3;
OWNER OBJECT_NAME OBJECT_TYPE
------- ------------ -------------
PUBLIC DBMS_HPROF SYNONYM
SYS DBMS_HPROF PACKAGE
SYS DBMS_HPROF PACKAGE BODY
SQL> select * from dba_tab_privs where table_name = 'DBMS_HPROF';
no rows selected
If this was a local database, I would logon as SYS and run grant execute on sys.dbms_hprof to my_username;. But as far as I know, there is no way to run a command as SYS on an Oracle autonomous database.
If this was Amazon RDS, I could probably run a command like RDSADMIN.RDSADMIN_UTIL.GRANT_SYS_OBJECT(.... But as far as I know, there is no such package on an Oracle autonomous database. None of the DBMS_CLOUD* packages seem to have what I'm looking for either.
I'm using Oracle 21c Autonomous Data Warehouse on the Always Free tier.
Is there a way to run the PL/SQL hierarchical profiler on the cloud? Or am I stuck using the older profiler as a work around?
Works now fine in ADB!
begin
sys.dbms_hprof.create_tables(force_it => true);
end;
/
PL/SQL procedure successfully completed.