Major Update on Search Audit Data Script

 Many years back I've shared a script in the DBA Bundle to easily fetch audit data, recently I thought to add the capability of searching the transactions using LogMiner feature which is a license free feature in the Enterprise Edition, and considered the best option to use for searching audit data if the Standard Auditing is not enabled.

There are some prerequisites of using LogMiner:
- The database Edition should be Enterprise.
- The database should be in ARCHIVE LOG mode.
- Minimal supplemental logging should be enabled during all the time period you're mining the data within.

The script will do check all these prerequisites before using the LogMiner feature.

As usual, the script is very easy to use, just read the prompted messages carefully ...

Download the script from this link:

https://www.dropbox.com/s/xq26ogdyb4ws7lu/search_audit_data.sh?dl=0

And here is the GitHub version:

https://gist.github.com/haballan/4c0668e60e271df2433fa96e8249c8fc

	# ####################################################################################################
	# This script shows AUDIT records for DB User.
	# To be run by ORACLE user
	# Ver 2.2
	# # # #
	# Author: Mahmmoud ADEL # # # # ###
	# Created: 25-04-2013 # # # # #
	#
	# Modified: 07-03-2019 Allow the user to control the display of LOGIN/LOGOFF data.
	# 10-03-2019 Added the option of excluding specific audit action from the report.
	# 28-09-2021 Added the LogMiner feature to be used if statndard auditing is disabled.
	# 01-10-2021 Added the pretty option for better output readability.
	# 01-10-2021 Added the option to show the COMMITED transactions only.
	# ####################################################################################################
	# ###########################
	# Listing Available Instances:
	# ###########################
	echo
	echo "==========================================="
	echo "This Script Retreives AUDIT data for a user"
	echo "==========================================="
	echo
	sleep 1
	# #######################################
	# Excluded INSTANCES:
	# #######################################
	# Here you can mention the instances the script will IGNORE and will NOT run against:
	# Use pipe "|" as a separator between each instance name.
	# e.g. Excluding: -MGMTDB, ASM instances:
	EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline].
	# Count Instance Numbers:
	INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
	# Exit if No DBs are running:
	if [ $INS_COUNT -eq 0 ]
	then
	echo No Database Running !
	exit
	fi
	# If there is ONLY one DB set it as default without prompt for selection:
	if [ $INS_COUNT -eq 1 ]
	then
	export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	# If there is more than one DB ASK the user to select:
	elif [ $INS_COUNT -gt 1 ]
	then
	echo
	echo "Select the Instance You Want To Run this script Against:[Enter the number]"
	echo "-------------------------------------------------------"
	select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
	do
	integ='^[1-9]+$'
	if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ]
	then
	echo
	echo "Error: Not a valid number!"
	echo
	echo "Enter a valid NUMBER from the displayed list !: i.e. Enter a number from [1 to ${INS_COUNT}]"
	echo "-----------------------------------------------"
	else
	export ORACLE_SID=$DB_ID
	echo
	printf "`echo "Selected Instance: ["` `echo -e "\033[33;5m${DB_ID}\033[0m"` `echo "]"`\n"
	echo
	break
	fi
	done
	fi
	# Exit if the user selected a Non Listed Number:
	if [ -z "${ORACLE_SID}" ]
	then
	echo "You've Entered An INVALID ORACLE_SID"
	exit
	fi
	# #########################
	# Getting ORACLE_HOME
	# #########################
	ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
	USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
	# SETTING ORATAB:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	export ORATAB
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	export ORATAB
	fi
	# ATTEMPT1: Get ORACLE_HOME using pwdx command:
	export PGREP=`which pgrep`
	export PWDX=`which pwdx`
	if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]]
	then
	PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
	export PMON_PID
	ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
	export ORACLE_HOME
	fi
	# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	## If OS is Linux:
	if [ -f /etc/oratab ]
	then
	ORATAB=/etc/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	## If OS is Solaris:
	elif [ -f /var/opt/oracle/oratab ]
	then
	ORATAB=/var/opt/oracle/oratab
	ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
	export ORACLE_HOME
	fi
	#echo "ORACLE_HOME from oratab is ${ORACLE_HOME}"
	fi
	# ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from environment is ${ORACLE_HOME}"
	fi
	# ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
	export ORACLE_HOME
	#echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}"
	fi
	# ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
	export ORACLE_HOME
	#echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}"
	fi
	# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
	if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
	then
	echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
	echo "e.g."
	echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
	exit
	fi
	export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
	# ########################################
	# Exit if the user is not the Oracle Owner:
	# ########################################
	CURR_USER=`whoami`
	if [ ${ORA_USER} != ${CURR_USER} ]; then
	echo ""
	echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
	echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
	echo "Script Terminated!"
	exit
	fi
	# #########################
	# SQLPLUS Section:
	# #########################
	# PROMPT FOR VARIABLES:
	# ####################
	echo
	echo "Do you want to SPOOL the OUTPUT?: [Y|N Default [N]]"
	echo "================================"
	while read SPOOLANS
	do
	case ${SPOOLANS} in
	#""|N|n|NO|no|No) export SPOOLING="--";echo; echo "Spooling Disabled."; echo; break ;;
	Y|y|YES|yes|Yes) export SPOOLING=""; export LOGDATE=`date +%d-%b-%y-%T`; echo; echo "Spooling Enabled under the current working directory.";echo;break ;;
	*) export SPOOLING="--";echo; echo "Spooling Disabled."; echo; break ;;
	esac
	done
	echo "Select the DB Feature to use for retreiving data: [Enter a number 1|2]"
	echo "================================================"
	echo "1. Standard Auditing [Recommended if ENABLED]"
	echo "2. Log Miner [Use when Auditing is Disabled to Search transactions]"
	while read FEATURE
	do
	case ${FEATURE} in
	""|1|Stand*|stand*) echo; echo "Using Standard Auditing feature ...";echo
	echo "Enter The DB_USERNAME you want to retrieve its Audit Data: [Leave it Blank to list ALL Users]"
	echo "=========================================================="
	while read DB_USERNAME_INCL
	do
	case $DB_USERNAME_INCL in
	"") export USERNAME_INCL=""
	echo
	echo "Enter the DB_USERNAME you want to EXCLUDE from the list: [Leave it Blank to NOT EXCLUDE any Users]"
	echo "--------------------------------------------------------"
	while read DB_USERNAME_EXCL
	do
	case $DB_USERNAME_EXCL in
	"") export USERNAME_EXCL=""; break;;
	*) export USERNAME_EXCL="USERNAME <> upper('${DB_USERNAME_EXCL}') AND "; break;;
	esac
	done
	break ;;
	*) export USERNAME_INCL="USERNAME=upper('${DB_USERNAME_INCL}') AND ";break ;;
	esac
	done
	#echo "Enter The USERNAME you want to retrieve its Audit Data: [Blank Value means ALL Users]"
	#echo "======================================================"
	#while read DB_USERNAME
	# do
	# case $DB_USERNAME in
	# # NO VALUE PROVIDED:
	# "") USERNAME_COND="";break ;;
	# #*) USERNAME_COND="USERNAME=upper('${DB_USERNAME}') or OS_USERNAME='${DB_USERNAME}' AND";break ;;
	# *) USERNAME_COND="USERNAME=upper('${DB_USERNAME}') AND";break ;;
	# esac
	# done
	echo
	echo "Do you want to include LOGIN/LOGOFF information: [Y|N Default [N]]"
	echo "==============================================="
	while read LOGININFO
	do
	case ${LOGININFO} in
	# NO VALUE PROVIDED:
	""|N|n|NO|no|No) export EXCLUDELOGINDATA="AND ACTION_NAME not like 'LOGO%' AND";break ;;
	Y|y|YES|yes|Yes) export EXCLUDELOGINDATA="";break ;;
	*) echo "Please enter a VALID answer [Y|N]" ;;
	esac
	done
	echo
	echo "Do you want to EXCLUDE a specific Action from the list:"
	echo "======================================================"
	echo "[Blank means INCLUDE ALL Actions Or Provide One of These Action to exclude: SELECT, ALTER, DROP, CREATE, TRUNCATE, GRANT or REVOKE]"
	while read EXCLUDEDACTION
	do
	case ${EXCLUDEDACTION} in
	# NO VALUE PROVIDED:
	"") export EXCLUDEDACTION="null";break ;;
	*) export EXCLUDEDACTION;break ;;
	esac
	done
	echo
	echo "How [MANY DAYS BACK] you want to retrieve AUDIT data? [Default 1]"
	echo "====================================================="
	echo "OR: Enter A Specific DATE in this FORMAT [DD-MM-YYYY] e.g. 25-01-2011"
	echo "== ================================================================="
	while read NUM_DAYS
	do
	case $NUM_DAYS in
	# User PROVIDED a NON NUMERIC value:
	*[!0-9]*) echo;echo "Retreiving AUDIT data for User [${DB_USERNAME}] on [${NUM_DAYS}] ..."
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set linesize 173 pages 1000
	set feedback off pages 0
	EXEC DBMS_SESSION.set_identifier('FETCHING_AUDIT_DATA_DBA_BUNDLE');
	PROMPT
	select /*+RULE*/ '[SESSION Details]: SID,SERIAL#: '||s.sid||','||s.serial#||' | OSPID: '||p.spid||' | SESSION IDENTIFIER: FETCHING_AUDIT_DATA_DBA_BUNDLE'
	from v\$session s, v\$process p
	where s.sid = (select sid from v\$mystat where rownum = 1) and s.paddr=p.addr;
	set feedback on pages 1000 timing on
	PROMPT
	col DATE for a20
	col "DB_USER | OS_USER | MACHINE" for a50
	col ACTION_OWNER_OBJECT for a55
	col SQL_TEXT for a49
	col "ACTION_OWNER_OBJECT|SQL_TEXT" for a100
	${SPOOLING}spool Audit_Extract_Report_${LOGDATE}.log
	select to_char(extended_timestamp,'DD-Mon-YYYY HH24:MI:SS')"DATE",USERNAME||'|'||OS_USERNAME||'|'||USERHOST "DB_USER | OS_USER | MACHINE",ACTION_NAME||' '||OWNER||'.'||OBJ_NAME||'|'||SQL_TEXT "ACTION_OWNER_OBJECT|SQL_TEXT"
	from dba_audit_trail
	where ${USERNAME_INCL} ${USERNAME_EXCL}
	timestamp > SYSDATE-${NUM_DAYS} ${EXCLUDELOGINDATA}
	ACTION_NAME not like upper ('%${EXCLUDEDACTION}%')
	--AND TRUNC(extended_timestamp) = TO_DATE('${NUM_DAYS}','DD-MM-YYYY')
	order by EXTENDED_TIMESTAMP;
	${SPOOLING} spool off
	PROMPT
	EOF
	exit
	break ;;
	# NO VALUE PROVIDED:
	"") export NUM_DAYS=1;echo;echo "Retreiving AUDIT data in the last 24 Hours ... [Please Wait]";break ;;
	# A NUMERIC VALUE PROVIDED:
	*) export NUM_DAYS;echo;echo "Retreiving AUDIT data in the last ${NUM_DAYS} Days ... [Please Wait]";break ;;
	esac
	done
	# Execution of SQL Statement:
	# ##########################
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set linesize 173 pages 1000
	set feedback off pages 0
	EXEC DBMS_SESSION.set_identifier('FETCHING_AUDIT_DATA_DBA_BUNDLE');
	PROMPT
	select /*+RULE*/ '[SESSION Details]: SID,SERIAL#: '||s.sid||','||s.serial#||' | OSPID: '||p.spid||' | SESSION IDENTIFIER: FETCHING_AUDIT_DATA_DBA_BUNDLE'
	from v\$session s, v\$process p
	where s.sid = (select sid from v\$mystat where rownum = 1) and s.paddr=p.addr;
	set feedback on pages 1000 timing on
	PROMPT
	col DATE for a20
	col "DB_USER | OS_USER | MACHINE" for a50
	col ACTION_OWNER_OBJECT for a55
	col SQL_TEXT for a50
	col "ACTION_OWNER_OBJECT|SQL_TEXT" for a100
	${SPOOLING}spool Audit_Extract_Report_${LOGDATE}.log
	select to_char(extended_timestamp,'DD-Mon-YYYY HH24:MI:SS')"DATE",USERNAME||'|'||OS_USERNAME||'|'||USERHOST "DB_USER | OS_USER | MACHINE",ACTION_NAME||' '||OWNER||'.'||OBJ_NAME||'|'||SQL_TEXT "ACTION_OWNER_OBJECT|SQL_TEXT"
	from dba_audit_trail
	where ${USERNAME_INCL} ${USERNAME_EXCL}
	timestamp > SYSDATE-${NUM_DAYS} ${EXCLUDELOGINDATA}
	ACTION_NAME not like upper ('%${EXCLUDEDACTION}%')
	order by EXTENDED_TIMESTAMP;
	${SPOOLING}spool off
	PROMPT
	EOF
	break;;
	2|Log*|log*) echo; echo "Using LogMiner feature ..."; echo
	# LogMiner Feature:
	# ################
	# Checking if database is in ARCHIVELOG mode:
	ARCHIVELOG_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 feedback off echo off;
	select count(*) from v\$database where LOG_MODE='ARCHIVELOG';
	exit;
	EOF
	)
	ARCHIVELOG_OPTION=`echo ${ARCHIVELOG_OPTION_RAW} | awk '{print $NF}'`
	if [ ${ARCHIVELOG_OPTION} -eq 0 ]
	then
	echo "ARCHIVELOG mode is not ENABLED. LogMiner Feature Cannot be used! "
	echo ""
	echo "SCRIPT TERMINATED! "
	echo ""
	exit
	fi
	# Checking if Data Mining feature is enabled:
	DATAMINING_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 feedback off echo off;
	select count(*) from v\$option where parameter='Data Mining' and value='TRUE';
	exit;
	EOF
	)
	DATAMINING_OPTION=`echo ${DATAMINING_OPTION_RAW} | awk '{print $NF}'`
	if [ ${DATAMINING_OPTION} -eq 0 ]
	then
	echo "DATA MINING Feature is DISABLED in this Database Edition!"
	echo ""
	echo "SCRIPT TERMINATED! "
	echo ""
	exit
	else
	echo "[DATA MINING Feature is Available]"
	echo
	fi
	# Checking supplemental_log_data_min is enabled:
	SUPPL_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
	set pages 0 feedback off echo off;
	select count(*) from v\$database where supplemental_log_data_min='YES';
	exit;
	EOF
	)
	SUPPL_OPTION=`echo ${SUPPL_OPTION_RAW} | awk '{print $NF}'`
	if [ ${DATAMINING_OPTION} -eq 0 ]
	then
	echo "SUPPLEMENTAL LOG Feature is DISABLED, LogMiner may fail to find the required data, but let's try!"
	echo ""
	fi
	# Setting the number of Hours for LogMiner to search within:
	echo "How many HOURS back you want to mine for transactions?: [Default 1 Hour]"
	echo "------------------------------------------------------"
	while read HOURS
	do
	case ${HOURS} in
	"") export NUM_HOURS=1; echo "LogMiner will search the last 1 hours transactions";echo; break;;
	*[!0-9]*) echo "Please enter a valid NUMBER:"
	echo "---------------------------";;
	*) echo; export NUM_HOURS=${HOURS}; break;;
	esac
	done
	# Allow the user to specify the DB Username to narrow down the output:
	echo "Enter The DB_USERNAME you want to retrieve its Audit Data: [Leave it Blank to list ALL Users]"
	echo "=========================================================="
	while read DB_USERNAME_INCL
	do
	case $DB_USERNAME_INCL in
	"") export USERNAME_INCL=""
	echo
	echo "Enter the DB_USERNAME you want to EXCLUDE from the list: [Leave it Blank to NOT EXCLUDE any Users]"
	echo "--------------------------------------------------------"
	while read DB_USERNAME_EXCL
	do
	case $DB_USERNAME_EXCL in
	"") export USERNAME_EXCL=""; break;;
	*) export USERNAME_EXCL="USERNAME <> upper('${DB_USERNAME_EXCL}') AND "; break;;
	esac
	done
	break ;;
	*) export USERNAME_INCL="USERNAME=upper('${DB_USERNAME_INCL}') AND ";break ;;
	esac
	done
	# Allow the user to specify the ACTION to narrow down the output:
	echo
	echo "Do you want to EXCLUDE a specific Action from the list:"
	echo "======================================================"
	echo "[Leave it Blank to include ALL Actions Or Provide One of These Action to exclude: INSERT | DELETE | UPDATE | DDL]"
	while read EXCLUDEDACTION
	do
	case ${EXCLUDEDACTION} in
	"") export EXCLUDEDACTION="null";break ;;
	*) export EXCLUDEDACTION;break ;;
	esac
	done
	# SHOW COMMITED TRANSACTIONS ONLY:
	echo
	echo "Do you want to Show COMMITED Transactions ONLY: [Y|n]"
	echo "=============================================="
	while read COMMITED
	do
	case ${COMMITED} in
	N|n|NO|No|no) export COMMITTED_DATA_ONLY="";break ;;
	""|Y|y|YES|Yes|yes) export COMMITTED_DATA_ONLY="DBMS_LOGMNR.COMMITTED_DATA_ONLY + ";break ;;
	*) echo "Please Enter a Valid answer: [Y|N]"
	echo "---------------------------";;
	esac
	done
	# Include the ROLLBACK STATEMENT:
	echo
	echo "Do you want to Include the ROLLBACK statement in the output: [y|N]"
	echo "============================================================"
	while read ROLLBACK
	do
	case ${ROLLBACK} in
	""|N|n|NO|No|no) export SQL_UNDO="";break ;;
	Y|y|YES|Yes|yes) export SQL_UNDO=",SQL_UNDO ROLLBACK_SQL";break ;;
	*) echo "Please Enter a Valid answer: [Y|N]"
	echo "---------------------------";;
	esac
	done
	export SPOOL_DIR=/tmp
	export SPOOL_FILE='Build_LogMiner_Catalog.sql'
	# Building the LogMiner Catalog of Archive Logs:
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	prompt
	prompt Archiving the current REDOLOG file ...
	alter system archive log current;
	prompt
	prompt Adding Archive Logs to LogMiner Catalog:
	set pages 0 lines 300 feedback off echo off termout off
	spool ${SPOOL_DIR}/${SPOOL_FILE}
	select distinct 'EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '''||NAME||''',OPTIONS => DBMS_LOGMNR.ADDFILE);' from gv\$archived_log where completion_time > sysdate-${NUM_HOURS}/24 and DEST_ID=1 group by name order by 1;
	spool off
	EOF
	# Executing LogMiner:
	# Removing un-needed lines from the generated spool file:
	sed -i 's/completion_time//g' ${SPOOL_DIR}/${SPOOL_FILE}
	sed -i 's/spool//g' ${SPOOL_DIR}/${SPOOL_FILE}
	sed -i '1s/.ADDFILE/.NEW/' ${SPOOL_DIR}/${SPOOL_FILE}
	${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
	set feedback off
	prompt
	prompt Building the LogMiner catalog using ${SPOOL_FILE} ...
	start ${SPOOL_DIR}/${SPOOL_FILE}
	prompt
	prompt Activating the LogMiner catalog ...
	EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + ${COMMITTED_DATA_ONLY} DBMS_LOGMNR.PRINT_PRETTY_SQL);
	prompt
	prompt Searching for Activities ...
	prompt
	set pages 1000 lines 173 long 2000000000 timing on feedback on
	col TIMESTAMP for a20
	col OBJ_OWNER for a15
	col OS_USERNAME for a15
	col MACHINE_NAME for a20
	col "DB_USER | OS_USER | MACHINE" for a45
	col sql_redo for a76
	col ROLLBACK_SQL for a83
	${SPOOLING}spool Transactions_output_LogMiner_${LOGDATE}.log
	select to_char(timestamp,'DD-MON-YY HH24:MI:SS') TIMESTAMP,username||'|'||OS_USERNAME||'|'||MACHINE_NAME "DB_USER | OS_USER | MACHINE",sql_redo ${SQL_UNDO}
	from v\$logmnr_contents
	where ${USERNAME_INCL} ${USERNAME_EXCL}
	OPERATION not in ('INTERNAL')
	and OPERATION not like upper ('%${EXCLUDEDACTION}%')
	and SEG_OWNER not in ('SYS','SYSTEM')
	and sql_redo <> 'set transaction read write;';
	${SPOOLING}spool off
	-- Cleanup the LogMiner Session:
	set feedback off timing off
	execute dbms_logmnr.end_logmnr();
	EOF
	break;;
	esac
	done
	# #############
	# END OF SCRIPT
	# #############
	# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
	# Do not live under a rock :-) Every month a new version of DBA_BUNDLE get released, download it by visiting:
	# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
	# REPORT BUGs to: mahmmoudadel@hotmail.com

view raw search_audit_data.sh hosted with ❤ by GitHub