How to use dbms_application_info to create session details

The package dbms_application_info lets you add supplemental information in your session, which can be picked up in the V$SESSION view.

A very useful package, which I have used many times myself, but for some reason it is rarely used by Developers. It should typically be called before beginning a transaction in order to register and name a transaction/client_info/module for later use when measuring performance across an application.

exec dbms_application_info.set_client_info('Step one.');
exec dbms_application_info.set_module('module1','gettrades');

Here is an example where I was troubleshooting a "hanging" delete statement for a customer.
Turned out the problem was concurrency locks:

select SID,SERIAL#,SQL_ID,STATUS,osuser,machine,schemaname,MODULE,ACTION,CLIENT_INFO,LOGON_TIME,EVENT,STATE,FINAL_BLOCKING_SESSION "blocking"
from v$session 
WHERE schemaname='SALES'
AND STATUS='ACTIVE'
UNION
select SID,SERIAL#,SQL_ID,STATUS,osuser,machine,schemaname,MODULE,ACTION,CLIENT_INFO,LOGON_TIME,EVENT,STATE,FINAL_BLOCKING_SESSION "blocking"
from v$session 
where SID = (SELECT FINAL_BLOCKING_SESSION FROM V$SESSION WHERE schemaname='SALES' AND STATUS='ACTIVE');

In the resulting two rows, the first one is mine, the second is the blocking session:

SID	SERIAL#	SQL_ID	STATUS	OSUSER	MACHINE	SCHEMANAME	MODULE	ACTION	CLIENT_INFO	LOGON_TIME	EVENT	STATE	blocking
32	4759	5jg1839cyxzgh	ACTIVE	oracle	mytestserver.com	SALES	DELETE MODULE	DELETE_SCHEMA	Delete XSD.	15.02.2018 12:04:43	library cache lock	WAITING	2285
2285	48625	g3bc37vx8fy3u	INACTIVE	JIM	COMPANY\PC-157	SALES	SQL*Plus			14.02.2018 14:08:57	SQL*Net message from client	WAITING	NULL

For more information, check the documentation
Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above) (Doc ID 293661.1) at My Oracle Support.

How to view default settings for dbms_stats

Thanks to the author of the blog "Thinking out loud" for writing this piece of code. It makes it easy to view the default settings that applies when gathering schema statistics:

SELECT 
  username,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'INCREMENTAL') incr,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'GRANULARITY') grty,
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'STALE_PERCENT') "stale%",
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'ESTIMATE_PERCENT') "estimate%",
  DBMS_STATS.get_prefs(ownname=>USER,pname=>'CASCADE') cascade,
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt,
  DBMS_STATS.get_prefs(pname=>'DEGREE') degree,
  DBMS_STATS.get_prefs(pname=>'CONCURRENT') CONC
FROM dba_users
WHERE username IN ('BILL','BOB','SCOTT','JANE')
ORDER BY username;

USERNAME	INCR	GRTY	STALE%	ESTIMATE%	CASCADE	METHOD_OPT	DEGREE	CONC
BILL	TRUE	AUTO	10	DBMS_STATS.AUTO_SAMPLE_SIZE	DBMS_STATS.AUTO_CASCADE	FOR ALL COLUMNS SIZE AUTO	DBMS_STATS.DEFAULT_DEGREE	OFF
BOB	TRUE	AUTO	10	DBMS_STATS.AUTO_SAMPLE_SIZE	DBMS_STATS.AUTO_CASCADE	FOR ALL COLUMNS SIZE AUTO	DBMS_STATS.DEFAULT_DEGREE	OFF
SCOTT	TRUE	AUTO	10	DBMS_STATS.AUTO_SAMPLE_SIZE	DBMS_STATS.AUTO_CASCADE	FOR ALL COLUMNS SIZE AUTO	DBMS_STATS.DEFAULT_DEGREE	OFF
JANE	TRUE	AUTO	10	DBMS_STATS.AUTO_SAMPLE_SIZE	DBMS_STATS.AUTO_CASCADE	FOR ALL COLUMNS SIZE AUTO	DBMS_STATS.DEFAULT_DEGREE	OFF

Why doesn't dbms_stats.set_schema_prefs seem to work?

When trying to alter the default settings for statistics gathering on partitioned tables, I had troubles setting schema-level preferences using dbms_stats.set_schema_prefs:

exec dbms_stats.set_schema_prefs('SCOTT','INCREMENTAL','TRUE');

PL/SQL procedure successfully completed.

But when you check if the setting has been accepted using dbms_stats.get_prefs, it still seem to be set to FALSE:

SELECT  DBMS_STATS.get_prefs(ownname=>'SCOTT',pname=>'INCREMENTAL') "INCREMENTAL" FROM DUAL;

INCREMENTAL
--------------------
FALSE

I found the following note by Maria Colgan:

http://www.nocoug.org/download/2011-11/Maria_Colgan_Optimizer_Statistics.pdf

which states that set_schema_prefs only applies to current objects in the schema and that new objects will pick up global preferences instead.

So according to the note, I was doing the right thing, after all I was trying to alter the default behaviour for the optimizer when working on existing objects.

How come the new setting didn't seem to stick?

In my case, a global change was acceptable, so I could try to set the parameter globally instead, using set_global_prefs:

BEGIN
  dbms_stats.set_global_prefs('INCREMENTAL','TRUE');
END;
/

PL/SQL procedure successfully completed.

After that, the setting certainly seem to stick:

SELECT  DBMS_STATS.get_prefs(ownname=>'SCOTT',pname=>'INCREMENTAL') "INCREMENTAL" FROM DUAL;

INCREMENTAL
--------------------
TRUE

Maria points out that a global change like the one above will apply to all existing objects as well as any new objects.

My conlution is that dbms_stats.get_prefs will report the settings that applies for future objects only, not existing ones. If that is the case, the output of the report is ambiguous, as it will give the DBA the impression that he/she has not made the change permanent.

To view the default settings for a particular schema, see this post.

Oracle Documentation on the subject is here

How to display processes in Linux in a tree-like fashion

The simplest way to see operating system processes and their sub-processes is to use

ps aufx

Output will look something like the following, where I have used the Oracle agent and its child proceesses to illustrate the formatted process tree:

oracle   33037  0.0  0.0 162892 15600 ?        S    Jan29   0:06 /u01/oracle/product/agent13c/agent_13.2.0.0.0/perl/bin/perl /u01/oracle/product/agent13c/agent_13.2.0.
oracle   33127  0.3  0.9 2714968 313308 ?      Sl   Jan29   5:01  \_ /u01/oracle/product/agent13c/agent_13.2.0.0.0/oracle_common/jdk/bin/java -Xmx128M -XX:MaxPermSize=
oracle   48285  0.0  0.0 331652 23220 ?        S    15:41   0:00      \_ /u01/oracle/product/agent13c/agent_13.2.0.0.0/perl/bin/perl /u01/oracle/product/agent13c/agent

Alternatively, use pstree:

pstree -p
pstree -p 12345

where 12345 is the process id.

For example, the Oracle agent is started via a perl script, and spawns multiple java child processes:

 ps -ef |grep agent | grep perl
oracle    12345      1  0 08:53 ?        00:00:00 /sw/oracle/product/agent13c/GoldImage/agent_13.4.0.0.0/perl/bin/perl /sw/oracle/product/agent13c/GoldImage/agent_13.4.0.0.0/bin/emwd.pl agent /sw/oracle/product/agent13c/agent_inst/sysman/log/emagent.nohup

Use the process id as an argument to pstree to see all the child processes process 12345 has spawned (output abbreviated):

pstree -p 12345
perl(12345)───java(32690)─┬─{java}(32691)
                          ├─{java}(32692)

How to work around ORA-01017 in a migrated 12c database

You may see some users in your 12c database that have the password versions set to 10G:

select username, password_versions from dba_users where username='SCOTT';

USERNAME             PASSWORD_VERSIONS
-------------------- -----------------
SCOTT                10G

At the same time, most other users have their password_versions set to the value 10G 11G 12C.

Oracle uses different password versions in all these three versions:

* Oracle 11g it uses SHA1 password based version
* Oracle 10g uses DES based version.
* Oracle 12c uses SHA-2-based SHA-512 password version

Since the Oracle 12c database runs in exclusive mode by default, users with passwords generated in previous versions
will not be able to login (exclusive mode means that the SQLNET.ALLOWED_LOGON_VERSION_SERVER is set either to 12 or 12a).

Workaround is to force a password reset so that the password is generated for the current version.
But before you do that, you need to change the database's minimum allowed authentication protocol.
This is done by editing the file $TNS_ADMIN/sqlnet.ora.
The parameter controlling the sqlnet authentication protocol is SQLNET.ALLOWED_LOGON_VERSION_SERVER.

Here is how I did it:

1. Find the user(s) with password versions of 10G

select username, password_versions 
from dba_users 
where password_versions = '10G';

I have found cases where there is a space after the string '10G' so that you need to actually search for the string '10G '.

2. Edit the $TNS_ADMIN/sqlnet.ora file so that the database doesn't run in exclusive mode. Add

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11

3. Restart database

4. Expire the user(s) that you want to force a password reset for:

alter user SCOTT password expire;

5. Try to connect as the user:

connect SCOTT
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for SCOTT
New password:
Retype new password:
Password changed
Connected.

Check that the users now has the correct password version:

select username, password_versions,account_status from dba_users where username='SCOTT';

USERNAME             PASSWORD_VERSIONS ACCOUNT_STATUS
-------------------- ----------------- --------------------------------
SCOTT                10G 11G 12C       OPEN

6. When all the affected users have been changed, set the database to run in exclusive mode.
Change the SQLNET.ALLOWED_LOGON_VERSION_SERVER from 11 to 12 in $TNS_ADMIN/sqlnet.ora, and restart once more.

How to toggle between "Mixed mode" Auditing, Traditional Auditing and Unified Auditing

This article is applicable to Oracle database versions 12.1 and onwards.

For newly created databases, mixed mode auditing is enabled by default through the predefined policy ORA_SECURECONFIG. 

 

Verify that the database is using "Mixed Mode" auditing

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	FALSE

Check for any enabled unified audit policies:

select policy_name, enabled_option
from audit_unified_enabled_policies;

POLICY_NAME	ENABLED_OPTION
ORA_SECURECONFIG	BY USER

If v$option shows FALSE for unified auditing AND the database have at least one enabled unified auditing policy, we are running in "Mixed Mode" auditing. 

In Mixed Mode Auditing, all of the existing auditing startup parameters for the database are still valid: AUDIT_TRAIL, AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS, and AUDIT_SYSLOG_LEVEL. So if your AUDIT_TRAIL is set to "DB", you can still use all the expected data dictionary views to obtain your audit information. If AUDIT_TRAIL is set to "OS", your auditing information will be sent to the location specified by the parameter AUDIT_FILE_DEST.

To enable "pure" Unified Auditing

1. Shutdown the database:

shutdown immediate

2. Relink the Oracle database binaries:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

3. Enable at least one unified audit policy. By default, two unified auditing policies are created when you create your a 12.2 database: ORA_SECURECONFIG and ORA_LOGON_FAILURES. The first one is enabled by the default, the last one is not. Let's enable the ORA_LOGIN_FAILURES, too:

audit policy ORA_LOGON_FAILURES;

Verify:

select parameter, value from v$option where parameter='Unified Auditing';

PARAMETER	VALUE
Unified Auditing	TRUE

select *
from audit_unified_enabled_policies;

POLICY_NAME	ENABLED_OPTION	ENTITY_NAME	ENTITY_TYPE	SUCCESS	FAILURE
ORA_LOGON_FAILURES	BY USERS	ALL USERS	USER	YES	YES
ORA_SECURECONFIG	BY USERS	ALL USERS	USER	YES	YES

If v$option shows TRUE for Unified Auditing AND we have at least one enabled unified auditing policy, we are using "Pure" Unified Auditing. 

It doesn't matter what all the Traditional Auditing parameters are set to at this point; they will not have any effect. 

Your audit information will from now on be written to the table AUDSYS.AUD$UNIFIED.

The SYS.AUD$ and SYS.FGA_LOG$ tables will still be accessible, but not used by the Oracle instance. They will only contain auditing records from before unified auditing was enabled. Consequently, your previously used queries based on familiar data dictionary views such as dba_audit_trail will only return information from before Unified Auditing was enabled.

The Oracle documentation provides a table which is very helpfull in determining the pros and cons of migrating to Unified Auditing. 

In my opinion, the most important drawback with Unfied Auditing is that it doesn't allow the auditing data to be written to the operating system.

To enable traditional Auditing

1. First, disable any unified audit policies that are currently enabled. Find the currently enabled policies:

select user_name, policy_name, enabled_opt, enabled_option
from audit_unified_enabled_policies;

USER_NAME	POLICY_NAME	ENABLED_OPT	ENABLED_OPTION
ALL USERS	ORA_LOGON_FAILURES	BY	BY USER
ALL USERS	ORA_SECURECONFIG	BY	BY USER

2. Take them out of audit. This step prevents the database from going into mixed mode auditing after you complete this procedure:

noaudit policy ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

3. Shutdown the database:

shutdown immediate

4. Relink the Oracle database binaries:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle ORACLE_HOME=$ORACLE_HOME

5. Start the database

sqlplus / as sysdba
startup

The database should now be in Traditional Auditing mode. There will be no more entries logged to the unified_audit_trail. Your audit records will go to the SYS.AUD$ and SYS.FGA_LOG$ tables, or to the operating system, depending on your value for the parameter AUDIT_TRAIL.

More about disabling unified auditing policies can be found here

Another good source for more information about Unified Auditing is this article found at oracle-base.com

How to bulk-grant privileges to a selection of users using PL/SQL

This anonymous PL/SQL script will select all the desired users, and then grant SELECT on a number of performance views to these.

set serveroutput on

DECLARE

 CURSOR c1 IS
  select username
  from dba_users
  where username like 'IT%'
  or username like 'MAITD%';

BEGIN
 FOR x IN c1 LOOP
    dbms_output.put_line('user ' || x.username || ' processed.' );
    execute immediate('GRANT SELECT ON SYS.IND$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.OBJ$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.TAB$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.USER$ TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$DATABASE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$INSTANCE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LATCH TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$LIBRARYCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$MYSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$PROCESS TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$ROWCACHE TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSION TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SESS_IO TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SGASTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$STATNAME TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSSTAT TO ' || x.username);
    execute immediate('GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO ' || x.username);
 END LOOP;
END;
/