What does it mean to "evolve" a SQL baseline?

Simply put, "evolving" an SQL baseline means testing whether or not an unaccepted plan performs better than any current plan in the plan baseline.

You can use Cloud Control or the package dbms_spm to evolve a plan.


Read more in the Oracle Documentation

How to purge a cursor from the shared pool using dbms_shared_pool

Find the address and hash value for the particular SQL you're looking for:

select address, hash_value from v$sqlarea where sql_id like 'bjkd7pcyfns86';

ADDRESS	HASH_VALUE
0000000085CD2838	1021993222

Execute the purge procedure:

SQL> exec dbms_shared_pool.purge('0000000085CD2838, 1021993222','C');

Note that the name of the object is the combined values of address and hash value from v$sqlarea.
The value C is what you want to use for cursors.

Note that even if an SQL Plan Baseline is dropped from the SMB, it doesn't mean it won't get used if it is still in the cache.

If you have dropped a plan from your SMB that you would like the optimizer to ignore, you must clear out the cursor that previously using the now dropped SQL Plan baseline from the shared pool using the command above. 

sources:

Oracle Documentation

How to use the find command on Unix/Linux systems

Here is how to list the regular files (indicated by -type f) files generated the last 30 minutes in a particular directory on a Linux/Unix server:

find . -mmin -30 -type f -exec ls -l {} +

Another example, listing files in my audit_file_dest older than 10 days:

find /u01/admin/proddb01/adump/ -name '*.aud' -mtime +10 -exec ls -latrh {} \;

Count all files with extension ".trm" generated one day or less ago:

find . -name '*.trm' -mtime -1 -type f -exec ls -l {} + | wc -l

Count all files with extension ".trm" generated more than 30 days ago:

find . -name '*.trm' -mtime +30 -type f -exec ls -l {} + | wc -l

Find all files with timestamp new than April 15th, 16:14 in the afternoon:

export TIMENOW=`date +"%a %b %e %H:%M"`
echo $TIMENOW
Thu Apr 15 16:14
find . -type f -newermt "$TIMENOW" -exec ls -la {} \;

Find and delete can be done in different ways, the recommended way is to use the -delete option. In the command below, I want to find and delete all *.trc and *.trm files older than 10 days:

find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +10 -delete

Delete all *.trm files in the current directory generated earlier than 24 hrs ago:

find *.trm -mmin +1440 -type f -delete

If the -delete option is not available, you can use a different method:

find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +10 -exec rm -f {} \;

Or, using xargs, finding files older than 2 days:

find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +2 -print0 | xargs -I {} -0 rm -v "{}"

Pipe to wc to get the number of files deleted:

find /u01/oracle/diag/rdbms/mydb/mydb/trace -type f -name 'mydb*.tr*' -mtime +2 -print0 | xargs -I {} -0 rm -v "{}" | wc -l

Some good external sources:

How To Format Date For Display or Use In a Shell Script
Linux / Unix: Find And Remove Files With One Command On Fly
Linux Find Command with Practical Examples

How to to use the script-command to log everything a script does

The utility "script" will make a typescript of a terminal session.
Very useful if you have a script which produces lots of output, and you need to search for errors after the script has run to completion:

script -c /tmp/install_oracle.sh

Unless you add the name of the output file, the resulting file will be called typescript.

How to monitor rollback activity after killing long-running session

Found this brilliant script here: http://www.oracle-wiki.net/startsqlshowrollinfo

prompt This query will monitor rollbacks of transactions after killing a job or session. 
prompt If the used undo records/used undo blocks count is going down, then the transaction is rolling back. 
prompt If they are going up, then the transaction is still running.
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 99999
 
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND   rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC
/

In my environment, the output looked as follows:

This query will monitor rollbacks of transactions after killing a job or session.
If the used undo records/used undo blocks count is going down, then the transaction is rolling back.
If they are going up, then the transaction is still running.

USERNAME               SID SERIAL#  USED_UBLK  USED_UREC SEGMENT_NAME                       RSSIZE STATUS         
-------------------- ----- ------- ---------- ---------- ------------------------------ ---------- ---------------
MYUSERNAME_XX_XXX_  1194   10616     670875   14088690 _SYSSMU22_1415974968$          1189502976 ONLINE

The USED_UBLK was dropping, so the ongoing rollback could be confirmed to the users.

How to delete a specific line from a file, or all characters following a specific character using sed

If you want to search a file for a particular string, and then delete that line, use sed, like this:

sed -i "/_use_column_stats_for_function/d" init.ora

In bash, using the double quotation marks was the only way to make this work when using a variable instead of a fixed string.

To delete a specific line number, in this case line number 7:

 sed -i '7d' myfile.txt
 

To delete lines 7 and 8:

 sed -i '7,8d' insert_data.sql

To delete everything after a specific character, for example #:

sed -i "s/#.*//g" /tmp/autoupgrade.ora

How to find the currently enabled unified auditing policies in an Oracle 12c database

Oracle 12c comes with two auditing policies enabled by default: ORA_SECURECONFIG and ORA_LOGON_FAILURES.
You can find the currently enabled policies in the AUDIT_UNIFIED_ENABLED_POLICIES view.

The query below will reveal what the currently enabled policies will actually audit

select policy_name,audit_option,condition_eval_opt
from audit_unified_policies
where policy_name in (  select policy_name 
                        from audit_unified_enabled_policies);
                        

The result shows that the default Unfied Auditing Setup covers many of the actions you would certainly want to audit, like DROP USER, ALTER SYSTEM etc:

POLICY_NAME	AUDIT_OPTION	CONDITION_EVAL_OPT
ORA_SECURECONFIG	LOGMINING	NONE
ORA_SECURECONFIG	TRANSLATE ANY SQL	NONE
ORA_SECURECONFIG	EXEMPT REDACTION POLICY	NONE
ORA_SECURECONFIG	PURGE DBA_RECYCLEBIN	NONE
ORA_SECURECONFIG	ADMINISTER KEY MANAGEMENT	NONE
ORA_SECURECONFIG	DROP ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	ALTER ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE ANY SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE SQL TRANSLATION PROFILE	NONE
ORA_SECURECONFIG	CREATE EXTERNAL JOB	NONE
ORA_SECURECONFIG	CREATE ANY JOB	NONE
ORA_SECURECONFIG	GRANT ANY OBJECT PRIVILEGE	NONE
ORA_SECURECONFIG	EXEMPT ACCESS POLICY	NONE
ORA_SECURECONFIG	CREATE ANY LIBRARY	NONE
ORA_SECURECONFIG	GRANT ANY PRIVILEGE	NONE
ORA_SECURECONFIG	DROP ANY PROCEDURE	NONE
ORA_SECURECONFIG	ALTER ANY PROCEDURE	NONE
ORA_SECURECONFIG	CREATE ANY PROCEDURE	NONE
ORA_SECURECONFIG	ALTER DATABASE	NONE
ORA_SECURECONFIG	GRANT ANY ROLE	NONE
ORA_SECURECONFIG	DROP PUBLIC SYNONYM	NONE
ORA_SECURECONFIG	CREATE PUBLIC SYNONYM	NONE
ORA_SECURECONFIG	DROP ANY TABLE	NONE
ORA_SECURECONFIG	ALTER ANY TABLE	NONE
ORA_SECURECONFIG	CREATE ANY TABLE	NONE
ORA_SECURECONFIG	DROP USER	NONE
ORA_SECURECONFIG	CREATE USER	NONE
ORA_SECURECONFIG	AUDIT SYSTEM	NONE
ORA_SECURECONFIG	ALTER SYSTEM	NONE
ORA_LOGON_FAILURES	LOGON	NONE
ORA_SECURECONFIG	CREATE DATABASE LINK	NONE
ORA_SECURECONFIG	DROP DATABASE LINK	NONE
ORA_SECURECONFIG	ALTER USER	NONE
ORA_SECURECONFIG	CREATE ROLE	NONE
ORA_SECURECONFIG	DROP ROLE	NONE
ORA_SECURECONFIG	SET ROLE	NONE
ORA_SECURECONFIG	CREATE PROFILE	NONE
ORA_SECURECONFIG	DROP PROFILE	NONE
ORA_SECURECONFIG	ALTER PROFILE	NONE
ORA_SECURECONFIG	ALTER ROLE	NONE
ORA_SECURECONFIG	CREATE DIRECTORY	NONE
ORA_SECURECONFIG	DROP DIRECTORY	NONE
ORA_SECURECONFIG	ALTER DATABASE LINK	NONE
ORA_SECURECONFIG	CREATE PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	ALTER PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	DROP PLUGGABLE DATABASE	NONE
ORA_SECURECONFIG	EXECUTE	NONE

If the $ORACLE_HOME/rdbms/admin/secconf.sql script was created during database creation, you will have some other policies in your database, too, but they won't be enabled by default. These can be found by executing the following statement:

select policy_name,count(audit_option) "number of audits"
from AUDIT_UNIFIED_POLICIES
where policy_name not in (select unique policy_name from audit_unified_enabled_policies )
group by policy_name
order by 2 desc;

In my database, the following collection shows up as being created, but not yet enabled:

POLICY_NAME	number of audits
ORA_RAS_POLICY_MGMT	33
ORA_CIS_RECOMMENDATIONS	26
ORA_RAS_SESSION_MGMT	14
ORA_ACCOUNT_MGMT	9
ORA_DATABASE_PARAMETER	3