How to check if fine-grained auditing (FGA) is enabled in your database

SELECT  POLICY_NAME,ENABLED,AUDIT_TRAIL
FROM  DBA_AUDIT_POLICIES;

Result:

POLICY_NAME	ENABLED	AUDIT_TRAIL
MY_FGAPOLICY_1	YES	DB+EXTENDED
MY_FGAPOLICY_2	YES	DB+EXTENDED
MY_FGAPOLICY_3	YES	DB+EXTENDED
MY_FGAPOLICY_4	YES	DB+EXTENDED

Find audit information about dropped users

If you have created your database with dbca, or have manually run the secconf.sql script after your database was created, the "DROP USER" is audited by default in Oracle 12c.
This is a part of what Oracle calls default auditing which I have written about in another post.

Here is how you can find information about dropped users:

select os_username,username,userhost,timestamp,obj_name,action_name,priv_used
from dba_audit_trail 
where action_name='DROP USER'
and to_char(cast ( timestamp as date),'dd.mm.yyyy') > '08.02.2019'
AND OBJ_NAME IN ('JIM','DWIGHT')
order by timestamp desc;

Result:

OS_USERNAME	USERNAME	USERHOST	TIMESTAMP	OBJ_NAME	ACTION_NAME	PRIV_USED
root	SYSTEM	hostname1.mydomain.com	12.02.2019 11:31:05	JIM	DROP USER	DROP USER
root	SYSTEM	hostname1.mydomain.com	12.02.2019 11:28:10	DWIGHT	DROP USER	DROP USER

What is the 100046 trace event anyway?

The 100046 event is a Process Trace event. These trace certain system operations continuously until the tracing is canceled. The results are written to a trace files.

When do I start running 10046 events, then?

* When a process is having problems that can be identified, and you can control which session (or sessions) need to be traced without impacting other things.
* When users can clearly point to processes that need help, and you cannot clearly see what it is other means (statspack, v$sqlarea, etc)


Source: Burlesson Consulting

Syntax for using the dbms_stats.gather_index_stats procedure

How to analyze an index using dbms_stats:

BEGIN
  SYS.DBMS_STATS.GATHER_INDEX_STATS (
     OwnName           => 'SCOTT'
    ,IndName           => 'STATUS_IDX_1'
    ,Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Degree            => DBMS_STATS.DEFAULT_DEGREE 
    ,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE);
END;
/

Official documentation here

How to solve ORA-02017: integer value required when trying to adjust parameters

I was trying to rectify an issue where the alert log of the database kept throwing errors like this:

ORA-01013: user requested cancel of current operation
ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded], [0x114890D28], [6], [1263979496], [], [], [], [], [], [], [], []

Reading Doc ID 330239.1 "Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded]", I realized that these errors are not causing any failure, as they are simply warnings that Oracle implemented to alert DBAs about potential large occupants of database memory.

There are two hidden parameters which control these messages:

set lines 200
col name format a40
col description format a80
col KSPPSTVL format a20
 select
 nam.ksppinm NAME,
 nam.ksppdesc DESCRIPTION,
 val.KSPPSTVL
 from
 x$ksppi nam,
 x$ksppsv val
 where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

NAME                                 DESCRIPTION                                                        KSPPSTVL
-----------------------------------  ------------------------------------------------------------------ --------------
_kgl_large_heap_warning_threshold    maximum heap size before KGL writes warnings to the alert log      524288000
_kgl_large_heap_assert_threshold     maximum heap size before KGL raises an internal error              524288000

These are the default values, 50M. Not very much. Instead, I want a warning to be written to the alert log at 1500M, and an ORA-00600 error to be thrown at 2G.

The first instruction works just fine:

alter system set "_kgl_large_heap_warning_threshold"=1572864000 scope=spfile;

System altered.

The secoond fails with:

alter system set "_kgl_large_heap_assert_threshold"=2147483648 scope=spfile;

ERROR at line 1:
ORA-02017: integer value required

Why?

The answer was found in the document Doc ID 2049098.1 Setting Parameter SORT_AREA_SIZE Throws error ORA-2017: "Integer Value Required", which states

"The parameter SORT_AREA_SIZE has a 2G limit"

and points out that instead of setting the value to 2147483648 bytes, it should be lowered to a value below 2G.

So the solution was to reduce the value by one single byte, from

alter system set "_kgl_large_heap_assert_threshold"=2147483648 scope=spfile;

to

alter system set "_kgl_large_heap_assert_threshold"=2147483647 scope=spfile;

and Oracle happily accepted the new value.

How to solve ORA-01031: insufficient privileges when creating a cross-schema fk constraint

Scenario:
you want to create a cross-schema Foreign key constraint:

 ALTER TABLE JIM.TRANSACTION
 ADD ( CONSTRAINT TRANSACTION_FK 
       FOREIGN KEY (TRANS_ID) REFERENCES 
       DWIGHT.TRANSACTION_HISTORY(TRANS_ID)
     );

In order to accomplish this, user JIM needs the REFERENCES system privilege on DWIGHT's table:

GRANT REFERENCES ON DWIGHT.TRANSACTION_HISTORY TO JIM;

How to remove a job from the job queue

Today I saw the following error message in the alert log of my database:

2019-02-12T13:27:30.506714+01:00
Errors in file /u01/oracle/diag/rdbms/proddb01/proddb01/trace/proddb01_j000_20661.trc:
ORA-12012: error on auto execute of job 98
ORA-30967: operation directly on the Path Table is disallowed

This error was connected to a procedure I had made earlier, in this post.

In short, the procedure creates a job using dbms_jobs, and executes it thereafter. Because an error occured during job execution, the job appeared to be jamming connections to the database. The job needed to be dropped immediately.

To remove the job, use either the dbms_job.remove procedure, like this:

sqlplus username/password

begin
  sys.dbms_job(98);
  commit;
end;
/

If you do not have the password for schema owning the job, Use the undocumented procedure sys.dbms_ijob, like this:

sqlplus / as sysdba
begin
  sys.dbms_ijob(98);
  commit;
end;
/

A workaround for RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the file after a clone from active database

My clone from active database failed at the very end of the procedure with

RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the file

This happened after media recovery was complete, and after the oracle instance was started, as can be seen from the log file below:

RMAN-08181: media recovery complete, elapsed time: 00:00:21
RMAN-03091: Finished recover at 06-FEB-19
RMAN-08031: released channel: c1
RMAN-08031: released channel: c2
RMAN-08031: released channel: aux1
RMAN-08031: released channel: aux2
RMAN-06196: Oracle instance started

Total System Global Area   22749904896 bytes

Fixed Size                     8632928 bytes
Variable Size               3758097824 bytes
Database Buffers           18924699648 bytes
Redo Buffers                  58474496 bytes

RMAN-08161: contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TESTDB01'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
RMAN-08162: executing Memory Script

RMAN-06162: sql statement: alter system set  db_name =  ''TESTDB01'' comment= ''Reset to original value by RMAN'' scope=spfile

RMAN-06162: sql statement: alter system reset  db_unique_name scope=spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/06/2019 04:39:51
RMAN-05501: aborting duplication of target database
RMAN-04014: startup failed: ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 51
Additional information: 1
Additional information: 48

This is a bummer, espescially if you've cloned a very large database and have waited for a long time for it to finish.
There is very little information about this error, but a workaround to save the freshly cloned auxiliary database, is to recreate the controlfile from script

The workaround is outlined in one of my older posts, but basically it boils down to the following steps:

1. Go from the source database, generate a "create controlfile" script
2. Transfer it to the auxiliary server
3. Edit it to suit the auxiliary database data files
4. Remove the controlfile files from the auxiliary server
5. Startup the instance in nomount mode and recreate the controlfile
6. Open the database with the resetlog option

How to create a trigger that will add newly created tables to a role

A commonly asked for functionality in my day-to-day work is READ-ONLY access for users to various tables in the database. This is easy to solve by creating a role, and granting SELECT on the tables to that role.

Every time a new table is created, the role must be updated. This will for most of the time be impossible to administer, and should be automated by creating a trigger on the schema owning the tables/views.

Here is how:

create a new role:

create role my_ro_role;

Grant execute permissions on dbms_jobs to scott:

grant create job to scott;

Note that if you do not have the password for the schema that should own the trigger, you cannot use the SYS user to create it for them. You need SYSTEM or another DBA user to avoid the error

ORA-30510: system triggers cannot be defined on the schema of SYS user

Create the trigger. In this example, I am using the system user to accomplish this:

conn system/password
create or replace trigger scott.add_to_ro_role
after CREATE on schema
declare
 l_str varchar2(255);
 l_job number;
begin
 if ( ora_dict_obj_type = 'TABLE' ) then
  l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to my_ro_role";';
  dbms_job.submit( l_job, replace(l_str,'"','''') );
 end if;
end;
/

Every time a new table is created in the SCOTT schema, SELECT on the same table will be granted to the role.
The role can then be granted to individual users.

Thanks to Tron Malmø-Lund for the idea and the code to implement it! ;-)