Differences

This shows you the differences between two versions of the page.

--- handy_scripts [2018/12/08 12:49] – created 0.0.0.0
+++ handy_scripts [2024/07/15 20:30] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Handy_scripts ======
+==== Download java grants / permissions so they can be reissued after a database duplication ====
+Ref [[https://www.thegeekdiary.com/how-to-backup-and-restore-java-classes-and-privileges-only-in-oracle-database/|Backup & Restore Java Privileges]]
+<code>
+TS=$(date '+%Y%m%d')
-======SQL======
+rm -f "${DATA_PUMP_DIR}/export_${TO_SID}_JAVA_${TS}.sql"
-  * [[Database Overview - from idevelopment]] [[http://www.idevelopment.info/data/Oracle/DBA_scripts/Database_Administration/dba_snapshot_database_10g.sql|online version]]
-  * [[Extract ddl with "dbms metadata.get ddl"]]
+sqlplus -s / as sysdba<<'EOSQL' >"${DATA_PUMP_DIR}/export_${TO_SID}_JAVA_${TS}.sql" 2>"/tmp/results.synchro.$$"
-  * [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:83012348058|Connecting as another user via proxy in SQL*Plus]]
+set echo off feed off newpa none head off lines 1000 pages 0 trims on
-  * [[http://www.petefinnigan.com/tools.htm|Password cracker and role and priv tools from Pete Finnigan]]
+column stmt format a500 word_wrapped
-  * [[http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html|Using pivot tables in SQL - XML and XLS (Excel) also)]]
+select 'exec '||stmt||''');'
-=====List all instances running on a server=====
+from   (
-Sounds simple enough and indeed pre 12.1, is was as easy as either
+       select seq
-<code>0@@</code>
+       ,      'dbms_java.grant_permission('''||grantee||''','''|| type_schema||':'||type_name||''','''||name||''','''||action stmt
-or this one is safer in case you have an underscore character in your SID name!!
+       from   dba_java_policy
-<code>1@@</code>
+       where  grantee not in ('JAVADEBUGPRIV','JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY','SYS','PUBLIC','EJBCLIENT','SYSTEM','ORDSYS','MDSYS','JMXSERVER','DBJAVASCRIPT')
+       and    type_name != 'oracle.aurora.rdbms.security.PolicyTablePermission'
+       union  all
+       select seq
+       ,      'dbms_java.grant_policy_permission('''||a.grantee||''','''|| u.name||''','''||permission||''','''||action       stmt
+       from   sys.user$ u
+       ,      (
+              select seq                                                                seq
+              ,      grantee                                                            grantee
+              ,      to_number(substr(name,1,instr(name,':')-1))                        userid
+              ,      substr(name, instr(name,':')+1, instr(name,'#')-instr(name,':')-1) permission
+              ,      substr(name,instr(name,'#')+1)                                     action
+              from   dba_java_policy
+              where  grantee not in ('JAVADEBUGPRIV','JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY','SYS','PUBLIC','EJBCLIENT','SYSTEM','ORDSYS','MDSYS','JMXSERVER','DBJAVASCRIPT')
+              and    type_name = 'oracle.aurora.rdbms.security.PolicyTablePermission'
+              ) a
+       where  u.user# = userid
+       )
+order  by seq
+/
+EOSQL
+</code>
+==== Use wget to download patches etc. from Oracle ====
+Using MOS / Metalink credentials, wget can be used from the command line to retrieve files from Oracle rather than use a browser and then ftp them
+<code>
+#!/bin/sh
+#
+# Generated 6/21/20 4:57 PM
+# Start of user configurable variables
+#
+LANG=C
+export LANG
+# Trap to cleanup cookie file in case of unexpected exits.
+trap 'rm -f $COOKIE_FILE; exit 1' 1 2 3 6
+# SSO username
+printf 'SSO UserName:'
+read SSO_USERNAME
+printf 'SSO Password:'
+read SSO_PASSWORD
+# Path to wget command
+WGET=/usr/local/bin/wget
+# Log directory and file
+LOGDIR=.
+LOGFILE=$LOGDIR/wgetlog-$(date +%m-%d-%y-%H:%M).log
+# Print wget version info
+echo "Wget version info:
+------------------------------
+$($WGET -V)
+------------------------------" > "$LOGFILE" 2>&1
+# Location of cookie file
+COOKIE_FILE=$(mktemp -t wget_sh_XXXXXX) >> "$LOGFILE" 2>&1
+if [ $? -ne 0 ] || [ -z "$COOKIE_FILE" ]
+then
+ echo "Temporary cookie file creation failed. See $LOGFILE for more details." |  tee -a "$LOGFILE"
+ exit 1
+fi
+echo "Created temporary cookie file $COOKIE_FILE" >> "$LOGFILE"
+# Output directory and file
+OUTPUT_DIR=.
+#
+# End of user configurable variable
+#
+# The following command to authenticate uses HTTPS. This will work only if the wget in the environment
+# where this script will be executed was compiled with OpenSSL.
+#
+# $WGET  --secure-protocol=auto --save-cookies="$COOKIE_FILE" --keep-session-cookies  --http-user "$SSO_USERNAME" --ask-password  "https://updates.oracle.com/Orion/Services/download" -O /dev/null 2>> "$LOGFILE"
+ $WGET  --save-cookies="$COOKIE_FILE" --keep-session-cookies  --http-user "$SSO_USERNAME" --http-password="$SSO_PASSWORD" "http://updates.oracle.com/Orion/Services/download" -O /dev/null 2>> "$LOGFILE"
+# Verify if authentication is successful
+if [ $? -ne 0 ]
+then
+ echo "Authentication failed with the given credentials." | tee -a "$LOGFILE"
+ echo "Please check logfile: $LOGFILE for more details."
+else
+ echo "Authentication is successful. Proceeding with downloads..." >> "$LOGFILE"
+ $WGET  --load-cookies="$COOKIE_FILE" --save-cookies="$COOKIE_FILE" --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p29202461_195000DBRU_AIX64-5L.zip?aru=23226670&patch_file=p29202461_195000DBRU_AIX64-5L.zip" -O "$OUTPUT_DIR/p29202461_195000DBRU_AIX64-5L.zip"   >> "$LOGFILE" 2>&1
+ $WGET  --load-cookies="$COOKIE_FILE" "https://updates.oracle.com/Orion/Services/download/p30125133_190000_AIX64-5L.zip?aru=23151934&patch_file=p30125133_190000_AIX64-5L.zip" -O "$OUTPUT_DIR/p30125133_190000_AIX64-5L.zip"   >> "$LOGFILE" 2>&1
+fi
+# Cleanup
+rm -f "$COOKIE_FILE"
+echo "Removed temporary cookie file $COOKIE_FILE" >> "$LOGFILE"
+</code>
+===== SQL =====
+  *  [[Database Overview - from idevelopment]] [[http://www.idevelopment.info/data/Oracle/DBA_scripts/Database_Administration/dba_snapshot_database_10g.sql|online version]]
+  *  [[Extract ddl with dbms metadata.get ddl]]
+  *  [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:83012348058|Connecting as another user via proxy in SQL*Plus]]
+  *  [[http://www.petefinnigan.com/tools.htm|Password cracker and role and priv tools from Pete Finnigan]]
+  *  [[http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html|Using pivot tables in SQL - XML and XLS (Excel) also)]]
+==== List all instances running on a server ====
+Sounds simple enough and indeed pre 12.1, is was as easy as
+<code>
+RUNNING_SIDS="$(ps -efo args|grep '[p]mon'|awk -F'ora_pmon_' '! /\+ASM|^$/ {print $NF}'|sort -fu)"
+</code>
 but now we have the added complication of CDB/PDB (container / pluggable databases) so a fair bit more work is needed!
-<code>2@@</code>
+<code>
+#!/bin/ksh
+# ==============================================================================
+# Name         : list_server_db
+# Description  : List all running databases found on a server
+#
+# Parameters   : none
+#
+# Notes        : none
+#
+# Modification History
+# ====================
+# When      Who               What
+# ========= ================= ==================================================
+# 21-DEC-16 Stuart Barkley    Created
+# ==============================================================================
+for db in $(ps -ef | grep [[p]]mon|awk -F_ '{print $NF}')
+do
+    export ORACLE_SID=${db}
+    ORAENV_ASK=NO
+    . oraenv >/dev/null 2>&1
+    sqlplus -s / as sysdba<<EOSQL
+set serveroutput on feed off pages 0 head off newpa none
+declare
+    e_col_not_found exception;
+    e_not_mounted   exception;
+    pragma          exception_init(e_not_mounted, -01507);
+    pragma          exception_init(e_col_not_found, -904);
+    l_db_name       varchar2(20);
+    l_database_role varchar2(20);
+    l_cdb           varchar2(20);
+    l_status        varchar2(20);
+    type varchar2_tab is table of varchar2(240) index by binary_integer;
+    t_pdbs          varchar2_tab;
+begin
+    begin
+        dbms_output.enable(null);
+        execute immediate 'select name, database_role, cdb from v\$database' into l_db_name, l_database_role, l_cdb;
+        if l_cdb = 'YES' then
+            l_status := 'CDB';
+            execute immediate 'select name from v\$pdbs' bulk collect into t_pdbs;
+            for i in nvl(t_pdbs.first,1) .. nvl(t_pdbs.last,0)
+            loop
+                dbms_output.put_line (t_pdbs(i)||':PDB');
+            end loop;
+        else
+            l_status := 'Independent';  -- col CDB exists but this is not one
+        end if;
+        if l_database_role like '%STANDBY%' then
+            l_status := 'Standby';
+        end if;
+    exception
+    when e_not_mounted then
+        l_status := 'Not Mounted';
+    when e_col_not_found then
+        l_status := 'pre12';
+    end;
+    dbms_output.put_line ('${db}'||':'||l_status);
+end;
+/
+EOSQL
+done
+</code>
-=====Run a command or script on all databases in tnsnames.ora=====
+==== Run a command or script on all databases in tnsnames.ora ====
-Very handy if you have a global tnsnames.ora accessible to all clients.<br />
+Very handy if you have a global tnsnames.ora accessible to all clients.
-So from a client install, this has access to all databases.<br />
+So from a client install, this has access to all databases.
-Any files created can be created on the client so no need to scp to all the machines to recover spool files etc.<br />
+Any files created can be created on the client so no need to scp to all the machines to recover spool files etc.
 Downside: Cannot connect as sysdba across tns so password will need to be known for whatever user is used.
-<code>3@@</code>
+<code>
+#!/bin/ksh
+TNSNAMES="./tnsnames.ora"
+[[ ! -r "${TNSNAMES}" ]] && echo "${TNSNAMES} is not accessible" && exit 1
-=====Run a command or script on all databases in oratab=====
+SIDLIST=$(perl -ne 'print "$1\n" if /(?:SID|SERVICE_NAME)\s*=\s*(\S+?)\)/' ${TNSNAMES}|sort|uniq|xargs)
-<code>4@@</code>
+for SID in ${SIDLIST}
-<code>5@@</code>
+do
+    echo "Processing $SID"
+    ls -al | grep "^d" | grep "\_${SID}" >/dev/null
+    if [[ $? -ne 0 ]]; then
+        echo 'exit' | sqlplus system/xxxxxx@${SID} @ReviewLite17.1.sql >/dev/null 2>&1
+    else
+        echo "directory already exists so must have done this SID"
+    fi
+done
+</code>
-=====Run an SQL command on behalf of another user=====
+==== Run a command or script on all databases in oratab ====
-It can be used to execute any sql command as a specified user provided this procedure is created in sys schema.<br />
+<code>
-  * not got this working properly yet *
+#!/usr/bin/ksh
-<code>6@@</code>
+# ==============================================================================
+# Name         : all_db_do
+# Description  : Loops over all the databases in oratab and executes the
+#                specified SQL command - be careful!
+#
+# Parameters   : -v(erbose)
+#                -q(uiet) - just the results
+#                -i(nstance list) - if provided, only run on these instances
+#                -f(ilename) | <quoted SQL command to be executed>]
+#
+# Examples     :
+#        all_db_do -v 'alter system switch logfile;'
+#        all_db_do -f sessions.sql
+#        all_db_do -q -i "orgt rpad reportt" 'select open_mode from v$database;'
+#        all_db_do -v "alter system set local_listener='LISTENER_&SID';"
+#        all_db_do "create directory data_pump_dir as '/oracle/export/&SID';"
+#
+# Notes        : If -b is specified to run on all databases concurently, a
+#                logfile is created in the form /tmp/results_all_db_do_<SID>.log
+#
+# Modification History
+# ====================
+# When      Who               What
+# ========= ================= ==================================================
+# 14-JAN-13 Stuart Barkley    Created
+# 25-FEB-13 Stuart Barkley    Adapt for Solaris
+# 21-MAR-13 Stuart Barkley    Check db is running first
+# 29-APR-13 Stuart Barkley    Added verbose flag processing
+# 24-MAY-13 Stuart Barkley    Wrap / as sysdba in quotes for older versions
+# 23-JUN-16 Stuart Barkley    Add -f to run a file containing sql
+# 23-SEP-21 Stuart Barkley    Add -b to run stuff in the background
+# ==============================================================================
-=====Run a script on all databases listed in tnsnames.ora=====
+PROGNAME=$(basename $0)
-Can run an SQL command file on all SID/SERVICE found in a tnsnames.ora file. I use this from a client laptop having a global tns file on it.<br />
+OS=$(uname -s)
+AWK=$(which awk)
+GREP=$(which grep)
+if [[ "${OS}" == "SunOS" ]]; then
+    AWK=/usr/xpg4/bin/awk
+    GREP=/usr/xpg4/bin/grep
+fi
+if [[ ! -r /etc/oratab ]]; then
+    if [[ "${OS}" == "SunOS" ]]; then
+        echo "oratab is not where we want it. Please run 'ln -s /var/opt/oracle/oratab /etc/oratab' as root and retry" && exit 1
+    else
+        echo "Either Oracle is not installed or database is running without an oratab" && exit 1
+    fi
+fi
+# -------------------------
+# get the arguments, if any
+# -------------------------
+unset BACKGROUND VERBOSE SIDLIST
+BACKGROUND=false
+while getopts "btvqf:i:" flag
+do
+    case "${flag}" in
+    b) BACKGROUND=true;;
+    t) TNS='@${ORACLE_SID}';;
+    v) VERBOSE=1;;
+    q) QUIET="feed off verif off head off newpa none";;
+    f) FILE2RUN=${OPTARG};;
+    i) SIDLIST=${OPTARG};;
+    *) echo "ERROR: Unrecognised flag, ${flag}" && exit 1;;
+    esac
+done
+shift $((OPTIND-1))
+# ----------------------------------------------------------------------------------
+# if no instances supplied, take all those where the restart parameter is set to "Y"
+# ----------------------------------------------------------------------------------
+if [[ "${SIDLIST}" == "" ]]; then
+    SIDLIST="$(awk -F':' 'NF>3 && $3=="Y" && $1 !~/[#*]/ {print $1}' /etc/oratab)"
+fi
+if [[ -z ${FILE2RUN} ]]; then
+    # no file, need an SQL supplied
+    if [[ -z $1 ]]; then
+        echo "Usage: $0 [-v] [-q] [-f <filename> | <quoted SQL command to be executed>]"
+        echo "eg: $0 -f sessions.sql"
+        echo "eg: $0 -v 'alter system switch logfile;'"
+        exit 1
+    fi
+    SQL=$1
+elif [[ -s ${FILE2RUN} ]]; then
+    # file supplied and exists
+    SQL="@${FILE2RUN}"
+else
+    # file supplied, does it exist
+    echo "File ${FILE2RUN} is empty or does not exist"
+    echo "Usage: $0 [-v] [-q] [-f <filename> | <quoted SQL command to be executed>]"
+    echo "eg: $0 -f sessions.sql"
+    echo "eg: $0 -v 'alter system switch logfile;'"
+    exit 1
+fi
+echo "${PATH}"|grep '/usr/local/bin' >/dev/null 2>&1
+[[ $? -ne 0 ]] && PATH="/usr/local/bin:${PATH}"
+ORAENV_ASK=NO
+for db in ${SIDLIST}
+do
+    if ( [[ $1 != "startup" ]] && [[ $(ps -ef | ${GREP} -c -E "[o]ra_pmon_${db}$") -eq 0 ]] ); then
+        [[ ! -z "$VERBOSE" ]] && echo "$db is not running, skipping..."
+        continue
+    fi
+    echo
+    [[ ! -z "$VERBOSE" ]] && printf "\n\n%s %s %s\n" "================ Start on ${db} at" "$(date '+%d-%b-%Y %H:%M:%S')" "================"
+    export ORACLE_SID=$db
+    . oraenv >/dev/null
+    [[ $? -ne 0 ]] && echo "ERROR: Unable to set environment for ${ORACLE_SID}, skipping" && continue
+    case "${BACKGROUND}" in
+    (false)
+        "${ORACLE_HOME}/bin/sqlplus" -s "/${TNS} as sysdba" <<EOSQL
+        define SID="${ORACLE_SID}"
+        col comp_name format a50
+        col value for a40
+        col filepath for a200
+        set lines 1000 pages 100 $QUIET
+        $SQL
+EOSQL
+        true
+        ;;
+    (true)
+        "${ORACLE_HOME}/bin/sqlplus" -s "/${TNS} as sysdba" <<EOSQL >>/tmp/results_${PROGNAME}_${db}.log &
+        define SID="${ORACLE_SID}"
+        col comp_name format a50
+        col value for a40
+        col filepath for a200
+        set lines 1000 pages 100 $QUIET
+        $SQL
+EOSQL
+        true
+        ;;
+    (*)
+        echo "ERROR: Unexpected value for BACKGROUND [${BACKGROUND}]. Please report this, it is a scripting error."
+        false
+        ;;
+    esac
+    [[ ! -z "$VERBOSE" ]] && printf "%s %s %s\n\n" "================   End on ${db} at" "$(date '+%d-%b-%Y %H:%M:%S')" "================"
+done
+wait
+echo "INFO: All done"
+</code>
+<code>
+all_db_do "create or replace directory data_pump_dir as '/oracle/export/&SID';"
+all_db_do "archive log list"
+all_db_do "shutdown immediate"
+all_db_do 'alter system set log_archive_dest_1="location=/oracle/arch/&SID" scope=both;'
+</code>
+==== Run an SQL command on behalf of another user ====
+It can be used to execute any sql command as a specified user provided this procedure is created in sys schema.
+  *  not got this working properly yet *
+<code>
+set serveroutput on
+create or replace procedure run_sql_as_user ( p_schema_name in varchar2
+                                            , p_sql         in varchar2
+                                            ) is
+    l_user_id dba_users.user_id%type;
+    l_cursor  number;
+    l_rc      number;
+begin
+    dbms_output.enable(null);
+    begin
+        select u.user_id
+        into   l_user_id
+        from   dba_users u
+        where  u.username = p_schema_name;
+    exception
+    when no_data_found then
+        raise_application_error (-20001, 'Cannot find user '||p_schema_name);
+    end;
+    l_cursor := dbms_sys_sql.open_cursor;
+    dbms_sys_sql.parse_as_user ( c             => l_cursor
+                               , statement     => p_sql
+                               , language_flag => dbms_sql.native
+                               , userid        => l_user_id
+    );
+    l_rc := dbms_sys_sql.execute(l_cursor);
+    dbms_sys_sql.close_cursor(l_cursor);
+exception
+when others then
+    raise_application_error (-20001, 'ERROR: '||sqlerrm);
+end;
+/
+</code>
+==== Run a script on all databases listed in tnsnames.ora ====
+Can run an SQL command file on all SID/SERVICE found in a tnsnames.ora file. I use this from a client laptop having a global tns file on it.
 This way it's not necessary to copy the file onto every host before executing it. Any files created by the script will be created on the client.
-<code>7@@</code>
+<code>
+#!/bin/ksh
-=====List invalid objects=====
+TNSNAMES="./tnsnames.ora"
+[[ ! -r "${TNSNAMES}" ]] && echo "${TNSNAMES} is not accessible" && exit 1
+SIDLIST=$(perl -ne 'print "$1\n" if /(?:SID|SERVICE_NAME)\s*=\s*(\S+?)\)/' ${TNSNAMES}|sort|uniq|xargs)
+for SID in ${SIDLIST}
+do
+    echo "Processing $SID"
+    ls -al | grep "^d" | grep "\_${SID}" >/dev/null
+    if [[ $? -ne 0 ]]; then
+        echo 'exit' | sqlplus system/password@${SID} @ReviewLite17.1.sql >/dev/null 2>&1
+    else
+        echo "directory already exists so must have done this SID"
+    fi
+done
+</code>
+==== List invalid objects ====
+<code>
  set lines 200
  set pages 200
@@ Line 45: / Line 445: @@
  where  1=1
  and    status = 'INVALID';
-=====Recompile all invalid objects=====
+</code>
+==== Recompile all invalid objects ====
 @?/rdbms/admin/utlrp.sql
-=====See how much space is left in the flash recovery area (FRA)=====
+==== See how much space is left in the flash recovery area (FRA) ====
-<code>8@@</code>
+<code>
+select name
+,      to_char (space_limit, '999,999,999,999') as space_limit
+,      to_char (space_limit - space_used + space_reclaimable
+,               '999,999,999,999')
+           as space_available
+,      round ( (space_used - space_reclaimable) / space_limit * 100, 1)
+           as pct_full
+from   v$recovery_file_dest;
+</code>
 or
-<code>9@@</code>
+<code>
-=====How far back can we flashback?=====
+# ==============================================
-<code>10@@</code>
+# Check FRA to see how much space is reclaimable
+# ==============================================
+fra_reclaim_check() {
+LOGFILE=${RMAN_LogDir}/${ORACLE_SID}_`date '+%Y%m'`_${V_SCRIPT}.log
+sqlplus "/ as sysdba" <<EOSQL > /tmp/results.$$
+set vefify off echo off heading off newpage none linesize 1000 echo off
+select 'Filesystem:'||name||
+       '; Total(Gb):'||round((space_limit/1073741824),2) ||
+       '; Reclaimable(Gb):'||round((space_reclaimable/1073741824),2) ||
+       '; Reclaimable(%):'||round( ((space_reclaimable/1073741824)/(space_limit/1073741824)*100),2 )
+from   v$recovery_file_dest
+/
+EOSQL
+cat /tmp/results.$$ >> $LOGFILE
+rm /tmp/results.$$
+}
+</code>
+==== How far back can we flashback? ====
+<code>
+col time_now              for a25
+col time_oldest_flashback for a25
+select to_char(sysdate,'DD-MON-YY HH24:MI')                    time_now
+,      to_char(fdl.oldest_flashback_time, 'DD-MON-YY HH24:MI') time_oldest_flashback
+,      round((sysdate-fdl.oldest_flashback_time)*24,1)         hours_flashback_possible
+from   v$database d
+,      v$flashback_database_log fdl
+/
+</code>
-=====DBA privs tables=====
+==== DBA privs tables ====
+<code>
  DBA_AQ_AGENT_PRIVS
  DBA_COL_PRIVS
@@ Line 66: / Line 506: @@
  DBA_WM_SYS_PRIVS
  DBA_WORKSPACE_PRIVS
+</code>
-=====Schedule a shell job=====
+==== Schedule a shell job ====
+<code>
  begin
      dbms_scheduler.create_program ( program_name   => 'myshelljob'
@@ Line 77: / Line 519: @@
  end;
  /
-=====Start the job=====
+</code>
+==== Start the job ====
+<code>
  begin
      dbms_sheduler.create_job ( job_name     => 'myshelljob'
@@ Line 84: / Line 528: @@
                               );
  /
+</code>
-=====What statement is a user running?=====
+==== What SQL statement is a user running? ====
-<code>11@@</code>
+<code>
-=====See what statements all users are running=====
+select a.sid
+,      a.serial#
+,      b.sql_text
+from   v$session a
+,      v$sqlarea b
+where  a.sql_address = b.address
+and    a.username    = '&username'
+/
+</code>
+==== See what SQL statements all users are running ====
 Useful to see what is happening when process limit is reached
-<code>12@@</code>
+<code>
+set lines 300 pages 1000
-=====A logminer session=====
+col username for a10
+col command for 999 head "CMD"
+col lockwait for a10
+col status for a8
+col schemaname for a12
+col osuser for a10
+col process for a10
+col program for a20
+col machine for a10
+col action for a10
+col module for a10
+col identifier for a10
+col event for a27
+col state for a8
+col service_name for a10
+col serial# for 999999
+select a.username
+,      a.command
+,      a.lockwait
+,      a.status
+,      a.schemaname
+,      a.osuser
+,      a.process
+,      a.machine
+,      a.program
+,      a.type
+--,      a.sql_exec_start
+--,      a.plsql_object_id
+--,      a.plsql_subprogram_id
+--,      a.module
+--,      a.action
+,      a.logon_time
+,      a.event
+,      a.wait_time
+,      a.seconds_in_wait
+,      a.state
+--,      a.service_name
+,      a.serial#
+,      b.sql_text
+from   v$session a
+,      v$sqlarea b
+where  a.sql_address = b.address
+order  by a.logon_time desc
+/
+set lines 80
+</code>
+==== A logminer session ====
 Mine all SQL statements in a 10 minute period
-<code>13@@</code>
+<code>
-=====Huge Pages=====
+sqlplus / as sysdba
-<code>14@@</code>
+alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
+execute dbms_logmnr.start_logmnr ( starttime => '27-OCT-2009 11:05:00'
+                                 , endtime   => '27-OCT-2009 11:15:00'
+                                 , options   => dbms_logmnr.dict_from_online_catalog+dbms_logmnr.continuous_mine
+                                 );
+create table mycontents as select * from v$logmnr_contents;
+execute dbms_logmnr.end_logmnr();
+select * from mycontents;
+drop table mycontents;
+</code>
+==== Huge Pages ====
+<code>
+#!/bin/bash
+#
+# hugepages_settings.sh
+#
+# Linux bash script to compute values for the
+# recommended HugePages/HugeTLB configuration
+#
+# Note: This script does calculation for all shared memory
+# segments available when the script is run, no matter it
+# is an Oracle RDBMS shared memory segment or not.
+# Check for the kernel version
+KERN=`uname -r | awk -F. '{ printf("%d.%d\
+",$1,$2); }'`
+# Find out the HugePage size
+HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
+# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
+NUM_PG=1
+# Cumulative number of pages required to handle the running shared memory segments
+for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[[0-9]][[0-9]]*"`
+do
+   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
+   if [[ $MIN_PG -gt 0 ]]; then
+      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
+   fi
+done
+# Finish with results
+case $KERN in
+   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
+          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
+   '2.6' | '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
+    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
+esac
+# End
+</code>
-=====Pretty longops listing=====
+==== Pretty longops listing ====
-<code>15@@</code>
+<code>
-<code>16@@</code>
+set lines 2000 pages 1000
+col opname for a34 head "Job"
+col message for a60
+col perc_done for a10 head "Done"
+col started for a18
+col killer for a15
+col mins_busy head "Mins busy"
+col mins_left head "Mins left"
+select sid||','||serial#                       killer
+,      opname
+,      message
+,      round ((sofar/totalwork),4)*100||'%'    perc_done
+,      to_char(start_time,'DD-MON-YY HH24:MI') started
+,      floor(elapsed_seconds/60)               mins_busy
+,      ceil(time_remaining/60)                 mins_left
+from   v$session_longops
+where  1=1
+and    sofar     != totalwork
+and    totalwork != 0
+and    opname    like 'RMAN%'
+/
+</code>
+<code>
+select * from v$session where module='Data Pump Worker'
+</code>
 or attach to the impdp job and it shows percentage done.
-<code>17@@</code>
+<code>
-=====Apply the database patch after the ORACLE_HOME has been patched=====
+Get the SID from v$session_longops and plug it into v$session to check the SQL command details.
-<code>18@@</code>
-=====Show how much archivelog data is generated per day=====
+Current Running SQLs
+--------------------
+set pages 1000 lines 2000
+col program  format a40
+col sql_text format a130
+select s.sid
+,      s.status
+,      s.last_call_et
+,      s.program
+,      sa.sql_id
+,      sa.sql_text
+from   v$session s
+,      v$sqlarea sa
+where  s.sql_id = sa.sql_id
+and    s.sid = '&sid'
+/
+set pages 1000 lines 2000
+col USERNAME for a10
+col OSUSER   for a10
+col MACHINE  for a10
+select s.sid
+,      s.serial#
+,      p.spid
+,      s.username
+,      s.osuser
+,      s.status
+,      s.process fg_pid
+,      s.longon_time
+,      s.machine
+,      p.spid bg_pid
+from   gv$session s
+,      gv$process p
+where  s.addr = p.addr
+and    s.sid  = '&sid'
+/
+$ps -ef | grep <spid>
+set pages 1000 lines 2000
+SELECT INST_ID
+,      SID
+,      SERIAL#
+,      SQL_ID
+,      USERNAME
+,      PROGRAM
+,      MACHINE
+,      SERVICE_NAME
+FROM   GV$SESSION
+WHERE  SID IN ('<SID_NO.1>','<SID_NO.2>')
+/
+Active Running SQLs
+--------------------
+set pages 1000 lines 2000
+col SPID     for a10
+col PROGRAM  for a15
+col OSUSER   for a10
+col ACTION   for a10
+col EVENT    for a25
+col SQL_TEXT for a25
+col MACHINE  for a10
+col P1TEXT   for a10
+col P2TEXT   for a10
+col P3TEXT   for a10
+SELECT b.sid
+,      b.serial#
+,      a.spid
+,      b.sql_id
+,      b.program
+,      b.osuser
+,      b.machine
+,      b.type
+,      b.event
+,      b.action
+,      b.p1text
+,      b.p2text
+,      b.p3text
+,      b.state
+,      c.sql_text
+,      b.logon_time
+FROM   v$process a
+,      v$session b
+,      v$sqltext c
+WHERE  a.addr           = b.paddr
+AND    b.sql_hash_value = c.hash_value
+AND    b.STATUS         = 'ACTIVE'
+AND    b.sid            = '&sid'
+ORDER  BY a.spid
+,      c.piece
+/
+OR - Use the below Query
+Longops.sql
+-----------
+set pages 50000 lines 32767
+col OPNAME for a10
+col SID form 9999
+col SERIAL form 9999999
+col PROGRAM for a10
+col USERNAME for a10
+col SQL_TEXT for a40
+col START_TIME for a10
+col LAST_UPDATE_TIME for a10
+col TARGET for a25
+col MESSAGE for a25
+alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
+SELECT l.inst_id,l.sid, l.serial#, l.sql_id, l.opname, l.username, l.target, l.sofar, l.totalwork, l.start_time,l.last_update_time,round(l.time_remaining/60,2) "REMAIN MINS", round(l.elapsed_seconds/60,2) "ELAPSED MINS", round((l.time_remaining+l.elapsed_seconds)/60,2) "TOTAL MINS", ROUND(l.SOFAR/l.TOTALWORK*100,2) "%_COMPLETE", l.message,s.sql_text
+FROM gv$session_longops l
+LEFT OUTER JOIN v$sql s on s.hash_value=l.sql_hash_value and s.address=l.sql_address and s.child_number=0
+WHERE l.OPNAME NOT LIKE 'RMAN%' AND l.OPNAME NOT LIKE '%aggregate%' AND l.TOTALWORK != 0 AND l.sofar<>l.totalwork AND l.time_remaining > 0
+/
+</code>
+==== Apply the database patch after the ORACLE_HOME has been patched ====
+<code>
+ORAENV_ASK=NO
+export ORACLE_SID=$1
+. oraenv
+sqlplus /nolog <<EOSQL
+connect / as sysdba
+startup
+@?/rdbms/admin/catbundle.sql psu apply
+@?/rdbms/admin/utlrp.sql
+purge dba_recyclebin;
+prompt check patch apply...
+set lines 150
+col action_time for a50
+col comp_name for a50
+col comments for a50
+select action_time,action,comments from sys.registry\$history where trunc(action_time) = trunc(sysdate);
+select comp_name,version,status,modified from dba_registry;
+exit
+EOSQL
+</code>
+==== Show how much archivelog data is generated per day ====
 A companion script for 'how many log switches in a day'
-<code>19@@</code>
+<code>
+col orderby noprint
+select trunc(first_time) orderby
+,      to_char(first_time,'DD-MON-YYYY') "Date"
+,      round(sum(bytes/1024/1024/1024),2) arch_logs_gigs
+from (
+    select unique (blocks * block_size) bytes,
+    thread#,
+    sequence#,
+    resetlogs_change#,
+    first_time
+    from v$archived_log
+)
+group by trunc(first_time)
+,      to_char(first_time, 'DD-MON-YYYY')
+order by trunc(first_time) desc
+/
+</code>
-=====Abort a hanging database even when sysdba cannot connect=====
+==== Abort a hanging database even when sysdba cannot connect ====
 Unable to connect to database with sqlplus / as sysdba because audit file cannot be created?
-  * [[http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so|tech.e2sn.com]]
+  *  [[http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so|tech.e2sn.com]]
-  * [[http://blog.dbi-services.com/oracle-is-hanging-dont-forget-hanganalyze-and-systemstate/|blog.dbi-services.com]]
+  *  [[http://blog.dbi-services.com/oracle-is-hanging-dont-forget-hanganalyze-and-systemstate/|blog.dbi-services.com]]
-Instead of just killing the pmon process, there is a kinder (and more useful) way to kill the database.<br />
+Instead of just killing the pmon process, there is a kinder (and more useful) way to kill the database.
-Use the hidden, undocumented 'prelim' switch parameter to sqlplus to make a connection to the database without creating SGA session data structures.<br />
+Use the hidden, undocumented 'prelim' switch parameter to sqlplus to make a connection to the database without creating SGA session data structures.
 It can be very useful for hang analysis so the possible cause can be found after the database has been restarted.
-<code>20@@</code>
+<code>
+sqlplus -prelim / as sysdba
+oradebug unlimit
+oradebug hanganalyze 3
+oradebug setmypid              -- or oradebug setorapname diag
+oradebug dump ashdumpseconds 30
+oradebug dump systemstate 10   -- or oradebug dump systemstate 266
+oradebug tracefile_name
+shutdown abort
+</code>
-=====Lists Oracle session with kill session statement all ready to go=====
+==== Lists Oracle session with kill session statement all ready to go ====
 disconnect attempts to kill the o/s processes too. kill doesn't.
-<code>21@@</code>
+<code>
+col killer for a60
+col osuser for a12
+col logon  for a23
+col spid   for a8
+col username for a15
+col program for a50
+set lines 2000
+select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' killer
+,      s.inst_id
+,      to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') logon
+,      sci.osuser
+,      p.spid
+,      s.username
+,      s.program
+from   gv$session s
+,      gv$session_connect_info sci
+,      gv$process p
+where  1=1
+and    p.addr     = s.paddr
+and    p.inst_id  = s.inst_id
+and    s.sid      = sci.sid
+and    s.serial#  = sci.serial#
+and    s.type    != 'BACKGROUND'
+and    s.status   = 'INACTIVE'
+order  by s.logon_time desc
+/
-=====How much space is being used by a guaranteed restore point?=====
-<code>22@@</code>
-=====Get statistics for LOB columns in a table=====
+KILLER                                                          INST_ID LOGON                   OSUSER       SPID     USERNAME        PROGRAM
-<code>23@@</code>
+------------------------------------------------------------ ---------- ----------------------- ------------ -------- --------------- --------------------------------------------------
+alter system disconnect session '490,33947' immediate;                1 23-JUN-16 13:30:53      oraibm       23205    SYS             sqlplus@benouerp07 (TNS V1-V3)
+alter system disconnect session '490,33947' immediate;                1 23-JUN-16 13:30:53      oraibm       23205    SYS             sqlplus@benouerp07 (TNS V1-V3)
+alter system disconnect session '490,33947' immediate;                1 23-JUN-16 13:30:53      oraibm       23205    SYS             sqlplus@benouerp07 (TNS V1-V3)
+alter system disconnect session '490,33947' immediate;                1 23-JUN-16 13:30:53      oraibm       23205    SYS             sqlplus@benouerp07 (TNS V1-V3)
+</code>
-=====badprivs.sql=====
+==== Create a guaranteed restore point ====
+<code>
+create restore point beginning guarantee flashback database;
+</code>
+==== How much space is being used by a guaranteed restore point? ====
+<code>
+show parameter recovery
+set numwi 15
+set lines 200
+col name for a40
+col time for a23
+col gigs_limit head "Max (but check df -g also!)"
+select name
+,      time
+,      round(storage_size/1024/1024/1024,2) gigs_used
+from   v$restore_point
+where  1=1
+and    guarantee_flashback_database = 'YES'
+/
+select * from v$flash_recovery_area_usage
+/
+select name
+,      round(space_used/1024/1024/1024,2) gigs_used
+,      round(space_limit/1024/1024/1024,2) gigs_limit
+,      space_reclaimable
+,      number_of_files
+from v$recovery_file_dest
+/
+</code>
+==== Get statistics for LOB columns in a table ====
+<code>
+select
+  max(dbms_lob.getlength(lob_content)) max_len
+, avg(dbms_lob.getlength(lob_content)) avg_len
+, min(dbms_lob.getlength(lob_content)) min_len
+, sum(case when dbms_lob.getlength(lob_content) <= 4000 then 1
+else 0 end) "<4k"
+, sum(case when dbms_lob.getlength(lob_content) > 4000 then 1
+else 0 end) ">4k"
+, sum(case when dbms_lob.getlength(lob_content) is null then 1 else
+end) "is null"
+from &tablename
+/
+</code>
+==== badprivs.sql ====
 Check to see if any ordinary users have privileges / access / grants / rights they should not have
-<code>24@@</code>
+<code>
+select grantee, privilege, admin_option
+from   sys.dba_sys_privs
+where  (       privilege like '% ANY %'
+        or     privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
+        or     admin_option = 'YES'
+       )
+and    grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
+                      'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
+                      'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
+                      'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
+                      'TIMESERIES_DBA')
+</code>
-=====Generate a list of tablespaces ready for a migration=====
+==== Generate a list of tablespaces ready for a migration ====
-<code>25@@</code>
+<code>
-=====List the privileges assigned to users on directories=====
+set echo off
+set lines 300
+set pages 0
+Set headi off
+set feedb off
+set long 32000
+spool migration_create_target_tablespaces.sql
+exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
+select DBMS_METADATA.GET_DDL('TABLESPACE',tablespace_name) from dba_tablespaces
+/
+spool off
+</code>
+==== List the privileges assigned to users on directories ====
 table_name is the name of the directory...
-<code>26@@</code>
+<code>
+select grantor
+,      grantee
+,      table_schema
+,      table_name
+,      privilege
+from   all_tab_privs
+where  table_name = 'DATA_PUMP_DIR'
+/
+</code>
-=====List the privileges assigned to users on packages/procedures=====
+==== List the privileges assigned to users on packages/procedures ====
 table_name is the name of the package...
-<code>27@@</code>
+<code>
+select grantor
+,      grantee
+,      table_schema
+,      table_name
+,      privilege
+from   all_tab_privs
+where  upper(table_name) like upper('%UTL_FILE%')
+/
+</code>
+==== List users with DBA privilege (role) ====
+<code>
+all_db_do -q "select vi.instance_name,drp.grantee,drp.admin_option from v\$instance vi,dba_role_privs drp where drp.granted_role='DBA';"
+</code>
-=====List the system privileges assigned to a user (used to copy user as or clone user as)=====
+==== List the system privileges assigned to a user (used to copy user as or clone user as) ====
 Maybe this one is better [[Extract ddl with "dbms metadata.get ddl"]]
+<code>
  SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
  FROM (
@@ Line 158: / Line 1032: @@
  START WITH grantee IS NULL
  CONNECT BY grantee = prior granted_role;
+</code>
 or
+<code>
  SELECT path
  FROM (
@@ Line 180: / Line 1056: @@
     AND username != 'SYS')
  OR grantee='PUBLIC'
+</code>
 or
 for a migration from 10g...
-<code>28@@</code>
+<code>
+set headi off
+set feedb off
+set long  9999999
+set pages 0
+set lines 300
+spool migration_create_target_users_grants.sql
+select dbms_metadata.get_ddl('USER', username) || ';' ddl
+from dba_users
+where  1=1
+and    username like upper('%&&USER_NAME%')
+/
+select 'grant '||privilege|| ' to ' || grantee || ';'
+from   dba_sys_privs
+where  1=1
+and    grantee like upper('%&&USER_NAME%')
+/
+select 'grant '||granted_role|| ' to ' || grantee || ';'
+from   dba_role_privs
+where  1=1
+and    grantee like upper('%&&USER_NAME%')
+/
+spool off
+</code>
 or for a migration on 11g...!
-<code>29@@</code>
+<code>
+set head off
+set pages 0
+set long 9999999
+col ddl for a1000
-=====Move datafiles between filesystems=====
-  * [[https://blogs.oracle.com/alejandrovargas/moving-a-datafile-from-file-system-to-asm|Moving a datafile from File System to ASM (does not have to be ASM!) - oracle.com]]
-  * [[https://docs.oracle.com/database/121/OSTMG/GUID-3B8D0956-0888-452D-A9E4-9FB8D98577E0.htm#OSTMG89997|Moving Data Files Between Oracle ASM (does not have to be ASM!) Using RMAN - oracle.com]]
-  * [[https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files|Moving a data file online between filesystems - oracle-base.com]]
-====Set the relevant datafile offline====
-<code>30@@</code>
-====Rename the files on the filesystem using o/s commands====
-<code>31@@</code>
-====Run the relevant alter commands to inform the control files of the new locations====
-<code>32@@</code>
-====Recover the datafiles====
+SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || ';' DDL
-<code>33@@</code>
+FROM DBA_USERS
-====Set the relevant datafile online====
+where  1=1
-<code>34@@</code>
+and    default_tablespace not in ('SYSTEM','SYSAUX')
-====Check all is well with the datafiles by using an appropriate statement====
+and    upper(username) like '%'||upper('&&username')||'%'
-<code>35@@</code>
+UNION ALL
-====Rename a datafile on 12c====
+SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || ';' DDL
-If you're lucky enough to be on 12c, it's one command - no need to offline the datafile!
+FROM DBA_USERS
-<code>36@@</code>
+where  1=1
+and    default_tablespace not in ('SYSTEM','SYSAUX')
+and    upper(username) like '%'||upper('&&username')||'%'
+UNION ALL
+SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || ';' DDL
+FROM DBA_USERS
+where  1=1
+and    default_tablespace not in ('SYSTEM','SYSAUX')
+and    upper(username) like '%'||upper('&&username')||'%'
+UNION ALL
+SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || ';' DDL
+FROM DBA_USERS
+where  1=1
+and    default_tablespace not in ('SYSTEM','SYSAUX')
+and    upper(username) like '%'||upper('&&username')||'%'
-=====Pass parameter/argument into perl one-liner script from shell=====
+spool migration_create_users_grants.sql
+/
+spool off
+</code>
+==== Move datafiles between filesystems ====
+Also used to rename a datafile or tempfile using the traditional method
+  *  [[https://blogs.oracle.com/alejandrovargas/moving-a-datafile-from-file-system-to-asm|Moving a datafile from File System to ASM (does not have to be ASM!) - oracle.com]]
+  *  [[https://docs.oracle.com/database/121/OSTMG/GUID-3B8D0956-0888-452D-A9E4-9FB8D98577E0.htm#OSTMG89997|Moving Data Files Between Oracle ASM (does not have to be ASM!) Using RMAN - oracle.com]]
+  *  [[https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files|Moving a datafile online between filesystems / Renaming or Moving Oracle Datafiles - oracle-base.com]]
+=== Check the status of the datafiles ===
+<code>
+set lines 1000 pages 100
+col file_name for a70
+select file_id, file_name, status, online_status from dba_data_files where tablespace_name = 'TS_THALER_DATA' order by 1;
+</code>
+or
+=== Check the status of the tempfiles ===
+<code>
+set lines 1000 pages 100
+col file_name for a70
+select v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v where t.file_id = v.file# order by 1;
+</code>
+=== Set the relevant datafile offline ===
+<code>
+alter database datafile 125 offline;
+alter database datafile 126 offline;
+alter database datafile 127 offline;
+</code>
+=== Rename the files on the filesystem using o/s commands ===
+This has to be done before the alter otherwise the alter will fail (it checks that the destination file exists)
+<code>
+alter tablespace ts_thaler_data_offline;
+host mv '/cln/tst/ora_data1/clnt/data4/ts_thaler_data_113.dbf' '/cln/tst/ora_data1/clnt/data1/ts_thaler_data_113.dbf'
+host mv '/cln/tst/ora_data1/clnt/data4/ts_thaler_data_114.dbf' '/cln/tst/ora_data1/clnt/data1/ts_thaler_data_114.dbf'
+host mv '/cln/tst/ora_data1/clnt/data4/ts_thaler_data_115.dbf' '/cln/tst/ora_data1/clnt/data1/ts_thaler_data_115.dbf'
+</code>
+=== Run the relevant alter commands to inform the control files of the new locations ===
+<code>
+alter database move datafile '/cln/tst/ora_data1/clnt/data4/ts_thaler_data_113.dbf' to '/cln/tst/ora_data1/clnt/data1/ts_thaler_data_113.dbf'
+alter database move datafile '/cln/tst/ora_data1/clnt/data4/ts_thaler_data_114.dbf' to '/cln/tst/ora_data1/clnt/data1/ts_thaler_data_114.dbf'
+alter database move datafile '/cln/tst/ora_data1/clnt/data4/ts_thaler_data_115.dbf' to '/cln/tst/ora_data1/clnt/data1/ts_thaler_data_115.dbf'
+</code>
+=== Recover the datafiles ===
+This step is skipped when renaming tempfiles
+<code>
+recover datafile 125;
+recover datafile 126;
+recover datafile 127;
+</code>
+=== Set the relevant datafile online ===
+<code>
+alter database datafile 125 online;
+alter database datafile 126 online;
+alter database datafile 127 online;
+</code>
+=== Rename a datafile on 12c and higher ===
+If you're lucky enough to be on 12c or higher it's one command - no need to offline the datafile!
+<code>
+SQL> alter database move datafile '/oracle/ora_data2/prod/data4/ts_thaler_data_113.dbf' to '/oracle/ora_data2/prod/data3/ts_thaler_data_113.dbf';
+Database altered.
+</code>
+It seems this method cannot be used for tempfiles - even though the error message suggests the database is aware of it! Have to use the traditional method above.
+<code>
+SQL> alter database move tempfile '/cln/acc/ora_data3/amla/temp_system/temp_system01.dbf' to '/cln/acc/ora_data3/amla/temp_system/temp_system_01.dbf';
+alter database move tempfile '/cln/acc/ora_data3/amla/temp_system/temp_system01.dbf' to '/cln/acc/ora_data3/amla/temp_system/temp_system_01.dbf'
+                    *
+ERROR at line 1:
+ORA-00905: missing keyword
+SQL> alter database move datafile '/cln/acc/ora_data3/amla/temp_system/temp_system01.dbf' to '/cln/acc/ora_data3/amla/temp_system/temp_system_01.dbf';
+alter database move datafile '/cln/acc/ora_data3/amla/temp_system/temp_system01.dbf' to '/cln/acc/ora_data3/amla/temp_system/temp_system_01.dbf'
+*
+ERROR at line 1:
+ORA-01516: nonexistent log file, data file, or temporary file
+"/cln/acc/ora_data3/amla/temp_system/temp_system01.dbf"
+</code>
+==== Pass parameter/argument into perl one-liner script from shell ====
 Trick? Just use ARGV to pass them in...
-<code>37@@</code>
+<code>
+FREQ=$(perl -e 'my @CAL=split(";",$ARGV[0]);shift @CAL;print $CAL[$ARGV[1]];' -- "$LINE" $offset)
+</code>
 or
-<code>38@@</code>
+<code>
+DUMP_DATE=`perl -MTime::Local -e 'print(timelocal(0,$ARGV[0],$ARGV[1],$ARGV[2],$ARGV[3],$ARGV[4]))' -- $MI $HH $DD $MM $YYYY`
+</code>
 or export the variable from shell and access it via the ENV hash
-<code>39@@</code>
+<code>
+export db=ENDP1
+perl -p -i -e 's!DSMI_LOG.+$!DSMI_LOG           /oracle/$ENV{db}/admin/tdpoerror_$ENV{db}!' ${db}/admin/tdpo.opt
+</code>
-=====Return epoch seconds in Perl=====
+==== Return epoch seconds in Perl ====
-<code>40@@</code>
+<code>
+perl -e 'print time();'
+</code>
 or to convert a specific day of the year to epoch seconds
-<code>41@@</code>
+<code>
-or for a specific date (careful though - month is 0-11 not 1-12!...<br />
+use POSIX;
-<code>42@@</code>
+my $epochsecs = mktime(0,0,0, $jday, 0, $year-1900);
-or more long-winded but delimited by anything...<br />
+</code>
+or for a specific date (careful though - month is 0-11 not 1-12!...
+<code>
+use Time::Local;
+#                          SS MM HH DD MM YYYY
+my $epochsecs = timelocal (30,10,19,03,04,2014);
+</code>
+or more long-winded but delimited by anything...
 Supply the date/time in any delimited format. Eg. YYYY-MM-DD:HH:MI
-<code>43@@</code>
+<code>
+#!/usr/bin/perl -w
+use Time::Local;
-=====Return epoch seconds in DOS/VBS/Windows=====
+sub date2epoch {
+  my($s) = @_;
+  my($year, $month, $day, $hour, $minute, $second);
+  if($s =~ m{^\s*(\d{1,4})\W*0*(\d{1,2})\W*0*(\d{1,2})\W*0*
+                 (\d{0,2})\W*0*(\d{0,2})\W*0*(\d{0,2})}x) {
+    $year = $1;  $month = $2;   $day = $3;
+    $hour = $4;  $minute = $5;  $second = $6;
+    $hour |= 0;  $minute |= 0;  $second |= 0;  # defaults.
+    $year = ($year<100 ? ($year<70 ? 2000+$year : 1900+$year) : $year);
+    return timelocal($second,$minute,$hour,$day,$month-1,$year);
+  }
+  return -1;
+}</code>
+==== Return epoch seconds in DOS/VBS/Windows ====
 Paste this code into epoch.vbs
-<code>44@@</code>
+<code>
+function date2epoch(p_date)
+    date2epoch = DateDiff("s", "01/01/1970 00:00:00", p_date)
+end function
+Wscript.Echo date2epoch(Now())
+</code>
 and call from a DOS box like this
-<code>45@@</code>
+<code>
+cscript //nologo epoch.vbs
+</code>
-=====Return date from epoch seconds in Perl=====
+==== Return date from epoch seconds in Perl ====
-<code>46@@</code>
+<code>
-=====Return epoch seconds from julian day number in Perl=====
+perl -e 'print scalar (localtime (1243269270))'
-<code>47@@</code>
+</code>
-=====Extract specific pieces of a date from an epoch timestamp in Perl=====
+==== Return epoch seconds from julian day number in Perl ====
-<code>48@@</code>
+<code>
+use POSIX;
+my ($year, $jday) = (2012,60);
+my $epochsecs = mktime(0,0,0, $jday, 0, $year-1900);
+</code>
+==== Extract specific pieces of a date from an epoch timestamp in Perl ====
+<code>
+use POSIX;
+# return localtime into an array
+my @tm = localtime $epochsecs;
-=====Return epoch seconds in Shell (linux)=====
+my $yyyymmdd = strftime "%Y%m%d", @tm;
-<code>49@@</code>
+print $yyyymmdd."\
+";
+</code>
-=====Return date from epoch seconds in Shell (linux)=====
+==== Return epoch seconds in Shell (linux) ====
-<code>50@@</code>
+<code>
+date -d "2014/04/03 18:34:30" "+%s"
+</code>
-=====Return epoch seconds in PL/SQL=====
+==== Return date from epoch seconds in Shell (linux) ====
+<code>
+date -d @1396542870
+</code>
+==== Return epoch seconds in PL/SQL ====
 You cannot just use sysdate as that is timezone sensitive... (epoch seconds are in UTC/GMT)
-<code>51@@</code>
+<code>
+select round((cast(sys_extract_utc(systimestamp) as date) - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS'))*24*60*60) from dual;
+or
+select round((vd.created - to_date('01-JAN-1970 00:00:00','DD-MON-YYYY HH24:MI:SS'))*24*60*60)  created from v$database vd;
+</code>
 How long has this instance been up (in minutes)?
-<code>52@@</code>
+<code>
+select round((cast(sys_extract_utc(systimestamp) as date) - startup_time)*24*60) up_mins from v$instance;
+</code>
-=====Return date from epoch seconds in SQL=====
+==== Return date from epoch seconds in SQL ====
-<code>53@@</code>
+<code>
+select to_char(to_date('19700101','YYYYMMDD') + ((&epoch)/24/60/60),'DD-MON-YYYY HH24:MI:SS') utc_date from dual;
+</code>
-=====A database overview using the sys.plato package=====
+==== A database overview using the sys.plato package ====
+<code>
  exec plato.help;
+</code>
 or
+<code>
  exec plato.complete('HTM');
+</code>
+==== List datafiles for a tablespace ====
+<code>
+select *
+from   dba_data_files
+where  1=1
+and    tablespace_name ='&ts_name';
+</code>
+==== Show graph of available and free space in tablespaces ====
+<code>
+-- =============================================================================
+-- File Name    : http://www.oracle-base.com/dba/monitoring/ts_free_space.sql
+-- Author       : Tim Hall
+-- Description  : Displays a list of tablespaces and their used/full status.
+-- Requirements : Access to the DBA views.
+-- Call Syntax  : @ts_free_space.sql
+-- Last Modified: 13-OCT-2012 - Created. Based on ts_full.sql
+--
+-- Modification History
+-- ====================
+-- When      Who               What
+-- ========= ================= =================================================
+-- 13-NOV-13 Stuart Barkley    Added cols free_pct and used_pct
+-- =============================================================================
+SET PAGESIZE 140 lines 180
+COLUMN used_pct FORMAT A11
+COLUMN max_used_pct FORMAT A11
+    SELECT tablespace_name                                                                      tablespace_name
+    ,      size_mb                                                                              size_mb
+    ,      free_mb                                                                              free_mb
+    ,      TRUNC((free_mb/size_mb) * 100)                                                       free_pct
+    ,      RPAD(' '|| RPAD('X',ROUND((size_mb-free_mb)/size_mb*10,0), 'X'),11,'-')              used_pct
+    ,      max_size_mb                                                                          max_size_mb
+    ,      max_free_mb                                                                          max_free_mb
+    ,      TRUNC((max_free_mb/max_size_mb) * 100)                                               max_free_pct
+    ,      RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-')  max_used_pct
+    from   (
+           select a.tablespace_name                                         tablespace_name
+           ,      b.size_mb                                                 size_mb
+           ,      a.free_mb                                                 free_mb
+           ,      b.max_size_mb                                             max_size_mb
+           ,      a.free_mb + (b.max_size_mb - b.size_mb)                   max_free_mb
+           from   (
+                  select tablespace_name                                    tablespace_name
+                  ,      trunc(sum(bytes)/1024/1024)                        free_mb
+                  from   dba_free_space
+                  group  by tablespace_name
+                  ) a
+           ,      (
+                  select tablespace_name                                    tablespace_name
+                  ,        trunc(sum(bytes)/1024/1024)                      size_mb
+                  ,        trunc(sum(greatest(bytes,maxbytes))/1024/1024)   max_size_mb
+                  from   dba_data_files
+                  group by tablespace_name
+                  ) b
+           ,      v$instance vi
+           where  a.tablespace_name = b.tablespace_name
+           )
+    order  by tablespace_name;
-=====List datafiles for a tablespace=====
+set lines 80
-<code>54@@</code>
+</code>
-=====Show graph of available and free space in tablespaces=====
-<code>55@@</code>
-=====Another method for calculating free space in tablespace  using segments=====
+==== Another method for calculating free space in tablespace  using segments ====
-<code>56@@</code>
+<code>
-=====Work out maximum possible size of a datafile=====
+select df.tablespace_name "Tablespace"
-<code>57@@</code>
+,      totalusedspace "Used MB"
+,      (df.totalspace - tu.totalusedspace) "Free MB"
+,      df.totalspace "Total MB"
+,      round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
+from   (
+       select tablespace_name
+,      round(sum(bytes) / 1048576) totalspace
+       from   dba_data_files
+       group  by tablespace_name
+       ) df
+,      (
+       select round(sum(bytes)/(1024*1024)) totalusedspace
+       ,      tablespace_name
+       from   dba_segments
+       group  by tablespace_name
+       ) tu
+where  df.tablespace_name = tu.tablespace_name;
+</code>
+==== Work out maximum possible size of a datafile ====
+<code>
+select round( (4*1024*1024*value) / (1024*1024*1024) ,2) maxfilesizeingigabytes from v$parameter where name='db_block_size';
+</code>
 or, as tablespaces can have block sizes differing from that of the init.ora parameter...
-<code>58@@</code>
+<code>
+select round( (4*1024*1024*block_size) / (1024*1024*1024) ,2) maxfilesizeingigabytes from dba_tablespaces where tablespace_name='&tablespace_name';
+</code>
-=====Show size and maxsize at datafile level=====
+==== Show size and maxsize at datafile level ====
 datafiles
-<code>59@@</code>
+<code>
+set pages 100 lines 200
+col file_name for a60
+select sum(bytes)/1024/1024      mbytes_alloc
+,      sum(maxbytes)/1024/1024   mbytes_max
+,      file_name
+from   sys.dba_data_files
+group  by file_name
+/
+</code>
 tempfiles
-<code>60@@</code>
+<code>
+set pages 100 lines 200
+col file_name for a60
+select sum(bytes)/1024/1024      mbytes_alloc
+,      sum(maxbytes)/1024/1024   mbytes_max
+,      file_name
+from   sys.dba_temp_files
+group  by file_name
+/
+</code>
-=====Resize the online redo logfiles=====
+==== Resize the online redo logfiles ====
-Found a more concise, smarter way of doing it [[http://www.oracle-wiki.net/startdocshowtorecreateredogrps|here]] - oracle-wiki.net<br />
+Found a more concise, smarter way of doing it [[http://www.oracle-wiki.net/startdocshowtorecreateredogrps|here]] - oracle-wiki.net
-Switching logs too often? Redolog files too small?<br />
+Switching logs too often? Redolog files too small?
-  * Show current log groups
+  *  Show current log groups
-<code>61@@</code>
+<code>
-  * Create additional (bigger) groups
+set lines 1000
-<code>62@@</code>
+col group_member for a60
+select l.group#          group_number
+,      (bytes/1024/1024) megs
+,      l.status          group_status
+,      f.member          group_member
+,      l.archived        archived
+,      f.type            file_type
+from   v$log     l
+,      v$logfile f
+where  l.group# = f.group#
+order  by l.group#
+,      f.member
+/
+</code>
+  *  Create additional (bigger) groups
+<code>
+define SID=TIBQFW
+alter database add logfile group 6  '/oracle/&SID/oradata1/redo1.log' size 1G;
+alter database add logfile group 7  '/oracle/&SID/oradata2/redo2.log' size 1G;
+alter database add logfile group 8  '/oracle/&SID/oradata3/redo3.log' size 1G;
+alter database add logfile group 9  '/oracle/&SID/oradata1/redo4.log' size 1G;
+alter database add logfile group 10 '/oracle/&SID/oradata2/redo5.log' size 1G;
+</code>
 or if you use multiple members...
-<code>63@@</code>
+<code>
-  * Switch archivelogs until the new ones are current and the old ones are inactive
+alter database add logfile group 6 ('/oracle/&SID/oradata1/redo/redo01a.log', '/oracle/&SID/oradata1/redo01b') size 50M;
-<code>64@@</code>
+alter database add logfile group 7 ('/oracle/&SID/oradata1/redo/redo02a.log', '/oracle/&SID/oradata1/redo02b') size 50M;
+alter database add logfile group 8 ('/oracle/&SID/oradata1/redo/redo03a.log', '/oracle/&SID/oradata1/redo03b') size 50M;
+</code>
+  *  Switch archivelogs until the new ones are current and the old ones are inactive
+<code>
+alter system switch logfile;
+</code>
 If the old logs are not yet inactive, checkpoint the database
-<code>65@@</code>
+<code>
-  * Drop the old groups
+alter system checkpoint;
-<code>66@@</code>
+</code>
+  *  Drop the old groups
+<code>
+alter database drop logfile group 1;
+alter database drop logfile group 2;
+alter database drop logfile group 3;
+alter database drop logfile group 4;
+alter database drop logfile group 5;
+</code>
-=====Increase the existing size of a datafile=====
+==== Increase the existing size of a datafile ====
-<code>67@@</code>
+<code>
+alter database datafile '&full_path_of_datafile' resize &new_meg_size.M;
+</code>
-=====Increase the max size of a tempfile=====
+==== Increase the max size of a tempfile ====
-<code>68@@</code>
+<code>
-=====Find out what processes are using the temp tablespace=====
+alter database tempfile '/oracle/ENDP1/oradata90/temp01.dbf' autoextend on maxsize unlimited;
+</code>
+==== Find out what processes are using the temp tablespace ====
 Temporary tablespace is used for sorting query results. Find them with this.
-<code>69@@</code>
+<code>
+SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
+a.username,a.osuser, a.status
+FROM v$session a,v$sort_usage b
+WHERE a.saddr = b.session_addr;
+</code>
-=====Increase the maximum size of a datafile=====
+==== Increase the maximum size of a datafile ====
 Although this can be set lower than existing size, try not to. It makes the reports look weird!
-<code>70@@</code>
+<code>
+alter database
+      datafile '&full_path_of_datafile'
+      autoextend on
+      maxsize &new_meg_size.m;
+</code>
-=====Find schemas (users) with objects created outside their default tablespace=====
+==== Find schemas (users) with objects created outside their default tablespace ====
 Indexes could be a notable exception if they are created in an index tablespace
-<code>71@@</code>
+<code>
+set lines 200
+col owner           for a20
+col segment_name    for a45
+col tablespace_name for a32
+select owner
+,      segment_name
+,      tablespace_name
+from   dba_segments
+where  exists (select username from dba_users where username = owner)
+and    tablespace_name != (select default_tablespace from dba_users where username = owner)
+and    owner = '&owner'
+/
+</code>
-=====Add a datafile to a tablespace=====
+==== Add a datafile to a tablespace ====
 'unlimited' will default to the maximum datafile size for the current db_block_size
-<code>72@@</code>
+<code>
+alter tablepace &tablespace_name
+      add datafile '&full_path_of_datafile'
+      size 100M
+      autoextend on
+      next 100M
+      maxsize unlimited
+</code>
-=====List all datafiles with their size=====
+==== List all datafiles with their size ====
-<code>73@@</code>
+<code>
-=====Database size (How big is my database?)=====
+set pages 1000 lines 2000
-There are different interpretations of what constitutes the size of a database...<br />
+col filetype for a15
+col name for a60
+break on report
+compute sum of gig on report
+select filetype
+,      name
+,      gig
+from   (
+       select 'datafile' filetype
+       ,      name
+       ,      bytes/1024/1024 gig
+       from   v$datafile
+       union all
+       select 'tempfile' filetype
+       ,      name
+       ,      bytes/1024/1024
+       from   v$tempfile
+       union all
+       select 'logfile' filetype
+       ,      lf.member "name"
+       ,      l.bytes/1024/1024
+       from   v$logfile lf
+       ,      v$log     l
+       where  1=1
+       and    lf.group# = l.group#
+       ) used
+,      (
+       select sum(bytes)/1024/1024
+       from   dba_free_space
+       ) free
+</code>
+==== Database size (How big is my database?) ====
+There are different interpretations of what constitutes the size of a database...
 Is it the space allocated to the datafiles or only the space taken up by the data? Are tempfiles included? Are redo logs included? etc...
-<code>74@@</code>
+<code>
+set lines 132
+col dbname for a9  heading "Database"
+col dbsize for a15 heading "Size"
+col dbused for a15 heading "Used space"
+col dbfree for a15 heading "Free space"
+with used_space as
+(
+select  bytes
+from    v$datafile
+union   all
+select  bytes
+from    v$tempfile
+union   all
+select  bytes
+from    v$log
+)
+, free_space as
+(
+select sum(bytes) sum_bytes
+from dba_free_space
+)
+select vd.name                                                                                            dbname
+,      round(sum(used_space.bytes)/1024/1024/1024 ) || ' GB'                                              dbsize
+,      round(sum(used_space.bytes)/1024/1024/1024 ) - round(free_space.sum_bytes/1024/1024/1024) || ' GB' dbused
+,      round(free_space.sum_bytes/1024/1024/1024) || ' GB'                                                dbfree
+from   free_space
+,      used_space
+,      v$database vd
+group  by vd.name
+,      free_space.sum_bytes
+/
+</code>
-=====Drop datafile from temp tablespace=====
+==== Drop datafile from temp tablespace ====
-Database is completely stuck because the temporary tablespace has grown to fill all available room.<br />
+Database is completely stuck because the temporary tablespace has grown to fill all available room.
-Look around for at least some log files to delete in order to get connected (audit files, sqlnet logs, listener logs)<br />
+Look around for at least some log files to delete in order to get connected (audit files, sqlnet logs, listener logs)
 Once connected, change the default temporary tablespace to a small one so we can remove the original.
-<code>75@@</code>
+<code>
-<code>76@@</code>
+create temporary tablespace temp2 tempfile '/oracle/RMANV12/oradata2/temp2.dbf' size 5M;
+</code>
+<code>
+alter database default temporary tablespace temp2;
+</code>
 I found trying to drop the datafile like this didn't work, just hung there (probably active tranactions)...
-<code>77@@</code>
+<code>
+drop tablespace temp including contents and datafiles;
+</code>
 So did it like this...
-<code>78@@</code>
+<code>
+alter database tempfile '/oracle/RMANV12/oradata2/temp.dbf' drop including datafiles;
+Database altered.
+drop tablespace temp including contents and datafiles;
+Tablespace dropped.
+</code>
 ... an alternative would be to find the sessions using the temp space and kill them...
-<code>79@@</code>
+<code>
+select sid, serial#, status from v$session where serial# in (select session_num from v$sort_usage);
+</code>
 Then to be neat and tidy, rename the temp2 tablespace back to temp.
-<code>80@@</code>
+<code>
+create temporary tablespace temp tempfile '/oracle/RMANV12/oradata2/temp.dbf' size 256M reuse autoextend on next 128M maxsize 1G;
+alter database default temporary tablespace temp;
+drop tablespace temp2 including contents and datafiles;
+</code>
 Although the last statement will not work until any transactions using it have been rolled back or committed.
-=====List files that are in hot-backup mode=====
+==== List files that are in hot-backup mode ====
+<code>
  set lines 100
  set pages 100
  col name format a60
+</code>
+<code>
  select df.name
  ,      b.status
@@ Line 352: / Line 1678: @@
  and    b.status = 'ACTIVE'
  order  by b.file#;
+</code>
-=====Waiting for snapshot control file enqueue=====
+==== Waiting for snapshot control file enqueue ====
 Script to identify sessions causing RMAN to hang because of 'waiting for snapshot control file enqueue' message
-<code>81@@</code>
+<code>
+set lines 2000
+col killer     for a70
+col program    for a20
+col module     for a20
+col action     for a20
+col logon_time for a20
-=====Info on blocking processes=====
+select 'alter system disconnect session '''||s.sid||','||s.serial#||''' immediate;' killer
-<code>82@@</code>
+,      username
+,      program
+,      module
+,      action
+,      logon_time
+from   v$session s
+,      v$enqueue_lock l
+where  l.sid  = s.sid
+and    l.type = 'CF'
+and    l.id1  = 0
+and    l.id2  = 2
+/
+</code>
+==== Info on blocking processes ====
+<code>
+select s1.username || '@' || s1.machine
+       || ' ( SID=' || s1.sid || ' )  is blocking '
+       || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '  blocking_status
+from   v$lock l1
+,      v$session s1
+,      v$lock l2
+,      v$session s2
+where  s1.sid     = l1.sid
+and    s2.sid     = l2.sid
+and    l1.block   = 1
+and    l2.request > 0
+and    l1.id1     = l2.id1
+and    l2.id2     = l2.id2
+/
+</code>
 session doing the blocking
-<code>83@@</code>
+<code>
+select *
+from  v$lock l1
+where 1=1
+and   block = 1 ;
+</code>
 sessions being blocked
-<code>84@@</code>
+<code>
+select *
+from   v$lock l1
+where  1=1
+and    id2 = 85203
+</code>
 info on session doing the blocking
-<code>85@@</code>
+<code>
+select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
+from   v$session
+where  sid = 234
+select do.object_name
+,      row_wait_obj#
+,      row_wait_file#
+,      row_wait_block#
+,      row_wait_row#
+,      dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) row_id
+from   v$session   s
+,      dba_objects do
+where  sid=234
+and    s.ROW_WAIT_OBJ# = do.OBJECT_ID;
+</code>
-=====Show locked objects=====
+==== Show locked objects ====
+<code>
  set lines 100
  set pages 999
@@ Line 401: / Line 1790: @@
  and    v.session_id = s.sid
  order  by oracle_username
- ,	session_id;
+ ,\tsession_id;
+</code>
-=====Show which row is locked=====
+==== Show which row is locked ====
+<code>
  select do.object_name
  ,      row_wait_obj#
@@ Line 415: / Line 1806: @@
  and    s.ROW_WAIT_OBJ# = do.OBJECT_ID
  and    sid             = &sid;
+</code>
-=====Check what is audited on a database=====
+==== Check what is audited on a database ====
-<code>86@@</code>
+<code>
-=====How old is the oldest audit record?=====
+set pagesize 100
-<code>87@@</code>
+set linesize 256
-=====Check what objects are being audited=====
+select audit_option,success,failure from dba_stmt_audit_opts;
-<code>88@@</code>
+</code>
+==== How old is the oldest audit record? ====
+<code>
+select extract (day from (systimestamp - min(ntimestamp#))) days_old from aud$;
+</code>
+==== Check what objects are being audited ====
+<code>
+tti col 60 'Object Audit Options (dba_obj_audit_opts)' skip col 50 'A=By Access, S=By Session. Left=on success, right=on failure' skip 2
+set lines 1000 pages 100
+col alt for a3 head "Alt|er"
+col aud for a3 head "Aud|it"
+col com for a3 head "Com|ent"
+col del for a3 head "Del|ete"
+col gra for a3 head "Gra|nt"
+col ind for a3 head "Ind|ex"
+col ins for a3 head "Ins|ert"
+col loc for a3 head "Lo|ck"
+col ren for a3 head "Ren|ame"
+col sel for a3 head "Sel|ect"
+col upd for a3 head "Upd|ate"
+col ref for a3 head "Ref|rnc"
+col exe for a3 head "Exe|cut"
+col cre for a3 head "Cre|ate"
+col rea for a3 head "Re|ad"
+col wri for a3 head "Wri|te"
+col fbk for a3 head "Fla|sh"
+\t
+select *
+from   dba_obj_audit_opts
+/
+</code>
-=====Procedure to delete old audit records=====
+==== Procedure to delete old audit records ====
-<code>89@@</code>
+<code>
+select text from all_source where lower(name)='purge_audit_trail';
-=====Job setup to run the audit purge=====
+TEXT
-<code>90@@</code>
+------------------------------------------------------------------------------------------------
-=====A list of all SIDs in oratab that should restart after a server boot=====
+PROCEDURE purge_audit_trail IS
+BEGIN
+-- set the last archive timestamp
+DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
+       audit_trail_type    => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
+       last_archive_time   => systimestamp - interval '365' day,
+       rac_instance_number => null
+                                          );
+-- purge audit trail till last archive timestamp
+DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
+, use_last_arch_timestamp => TRUE);
+END purge_audit_trail;
+</code>
+==== Job setup to run the audit purge ====
+<code>
+BEGIN
+sys.dbms_scheduler.create_job(
+    job_name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"',
+    job_type => 'STORED_PROCEDURE',
+    job_action => '"SYS"."PURGE_AUDIT_TRAIL"',
+    repeat_interval => 'FREQ=DAILY;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
+    start_date => to_timestamp_tz('2012-02-22 Europe/Paris', 'YYYY-MM-DD TZR'),
+    job_class => '"DEFAULT_JOB_CLASS"',
+    comments => 'purge audit trails until last archive timestamp',
+    auto_drop => FALSE,
+    enabled => FALSE
+);
+sys.dbms_scheduler.set_attribute( name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
+sys.dbms_scheduler.set_attribute( name => '"SYS"."PURGE_AUDIT_TRAIL_JOB"', attribute => 'job_weight', value => 1);
+sys.dbms_scheduler.enable( '"SYS"."PURGE_AUDIT_TRAIL_JOB"' );
+END;
+/
+</code>
+==== A list of all SIDs in oratab that should restart after a server boot ====
 Using awk
-<code>91@@</code>
+<code>
+awk -F: '$NF=="Y" && $1 !~/[#]/ {print $1}'  /etc/oratabab
+</code>
 Using perl
-<code>92@@</code>
+<code>
+perl -F: -ne 'print "$1\n" if /(\w+):(.+):Y$/' /etc/oratab
+</code>
+==== Move the admin directories to a new location ====
+<code>
+./all_db_do "alter system set audit_file_dest='/oracle/&SID/admin/adump' scope=spfile;"
+./all_db_do "shutdown immediate"
+for db in `awk -F: 'NF && $1 !~/[#*]/ {print $1}'  /etc/oratab`
+do
+    echo $db
+    mkdir -p /oracle/${db}/admin
+    echo "/oracle/${db}/admin created"
+    mv /oracle/admin/${db}/adump /oracle/${db}/admin/
+    mv /oracle/admin/${db}/dpdump /oracle/${db}/admin/
+    echo "Moved /oracle/admin/${db}/adump to /oracle/${db}/admin/"
+done
+./all_db_do startup
-=====Move the admin directories to a new location=====
+./all_db_do "create pfile from spfile;"
-<code>93@@</code>
+</code>
-=====Check if the Standby database is up-to-date with the primary database=====
+==== Check if the Standby database is up-to-date with the primary database ====
+<code>
  select arch.thread# "Thread"
  ,      arch.sequence# "Last Sequence Received"
@@ Line 467: / Line 1948: @@
  and    arch.thread# = appl.thread#
  order  by 1;
+</code>
-=====Check for gaps on the Standby database=====
+==== Check for gaps on the Standby database ====
 Run this on the standby
-<code>94@@</code>
+<code>
+select thread#, low_sequence#, high_sequence# from v$archive_gap;
+</code>
 or run this on the Primary to see the latest generated archive...
-<code>95@@</code>
+<code>
+select thread#   "Thread"
+,      sequence# "Last sequence generated"
+from   v$archived_log
+where  (thread#, first_time) in ( select thread#, max(first_time)
+                                  from   v$archived_log
+                                  group  by thread#
+                                )
+order  by 1
+/
+</code>
 and run this on the standby to check for received but not yet applied logs (this could be normal if a lag has been set)...
-<code>96@@</code>
+<code>
+select arch.thread# "Thread"
+,      arch.sequence# "Last sequence received"
+,      appl.sequence# "Last sequence applied"
+,      (arch.sequence#-appl.sequence#) "Difference"
+from   (
+       select thread#
+       ,      sequence#
+       from   v$archived_log
+       where  (thread#,first_time ) in (select thread#, max(first_time)
+                                        from   v$archived_log
+                                        group  by thread#
+                                       )
+       ) arch
+,      (
+       select thread#
+       ,      sequence#
+       from   v$log_history
+       where  (thread#,first_time ) in (select thread#, max(first_time)
+                                        from   v$log_history
+                                        group  by thread#
+                                       )
+      ) appl
+where arch.thread# = appl.thread#
+order by 1
+/
+</code>
 Compare the results from Primary and Secondary to detect possible network problems or other errors in shipping.
-=====Ignore case-sensitive passwords in 11g (deprecated in 12c)=====
+==== Ignore case-sensitive passwords in 11g (deprecated in 12c) ====
-<code>97@@</code>
+<code>
+alter system set SEC_CASE_SENSITIVE_LOGON = false scope=both
+</code>
-=====External table for the Alert log (pre 11g)=====
+==== External table for the Alert log (pre 11g) ====
-Modified to use (the better) scripts found on [[http://www.adp-gmbh.ch/ora/admin/scripts/read_alert_log.html|Rene Nyffenegger's site]]<br />
+Modified to use (the better) scripts found on [[http://www.adp-gmbh.ch/ora/admin/scripts/read_alert_log.html|Rene Nyffenegger's site]]
-This script generates the required DDL for the alertlog table
+This script creates the custom alertlog table\\
-<code>98@@</code>
+Ensure the owning schema has the following privileges:\\
+create any directory
+drop any directory
-=====Alert log (11g+)=====
+  *  create_custom_alertlog.sql
-This reads the log.xml file (which contains the same information as alert.log)<br />
+<code>
+-- ================
+-- to be run as sys
+-- ================
+-- ---------------------------------------------------
+-- create an external table based on the text alertlog
+-- ---------------------------------------------------
+column db    new_value _db    noprint;
+column bdump new_value _bdump noprint;
+select instance_name db from v$instance;
+select value bdump
+from   v$parameter
+where  name = 'background_dump_dest';
+drop   directory bdump;
+create directory bdump as '&&_bdump';
+grant read,write on directory bdump to public;
+drop   table monuser.alert_log_disk;
+create table monuser.alert_log_disk ( text varchar2(2000) )
+organization external (
+type oracle_loader
+default directory bdump
+access parameters ( records delimited by newline nologfile nobadfile
+                    fields terminated by "&" ltrim
+                  )
+location('alert_&&_db..log')
+)
+reject limit unlimited;
+-- ------------------------------
+-- create a custom alertlog table
+-- ------------------------------
+drop table monuser.alert_log;
+create table monuser.alert_log (
+    indx         number
+,   message_date date
+,   message_text varchar2(2000)
+)
+storage (initial 512k next 512K pctincrease 0);
+create index monuser.alert_log_u1 on monuser.alert_log(indx)
+storage (initial 512k next 512K pctincrease 0);
+create index monuser.alert_log_u2 on monuser.alert_log(message_date)
+storage (initial 512k next 512K pctincrease 0);
+drop   sequence monuser.alert_log_s1;
+create sequence monuser.alert_log_s1 minvalue 1 increment by 1;
+-- -----------------------------------------------------------------
+-- make this custom table appear the same as the more modern version
+-- -----------------------------------------------------------------
+drop   view monuser.x$dbgalertext;
+create view monuser.x$dbgalertext as
+select indx
+,      message_date
+,      message_text
+from   monuser.alert_log;
+drop public synonym x$dbgalertext;
+create public synonym x$dbgalertext for monuser.x$dbgalertext;
+</code>
+Now, after the two tables are created, the alert_log table can be filled with the following script.\\
+It only loads those records that are greater than the last time it loaded. And it loads the date/time on every line for convienence.\\
+It also helps when the alertlogs get rotated. You still keep the history within an Oracle table.\\
+Finally, it also strips out all the crap that is really not needed to see if you are looking for errors.
+  *  update_custom_alertlog.sql
+<code>
+set serveroutput on
+alter session set current_schema=monuser;
+declare
+  isdate         number := 0;
+  start_updating number := 0;
+  rows_inserted  number := 0;
+  message_date   date;
+  max_date       date;
+  message_text  alert_log_disk.text%type;
+begin
+    dbms_output.enable(null);
+    /* find a starting date */
+    select max(message_date) into max_date from alert_log;
+    if (max_date is null) then
+        max_date := to_date('01-jan-1980', 'dd-mon-yyyy');
+    end if;
+    for r in (
+        select substr(text,1,2000) message_text
+        from   alert_log_disk
+        where  text not like '%offlining%'
+        and    text not like 'ARC_:%'
+        and    text not like '%LOG_ARCHIVE_DEST_1%'
+        and    text not like '%Thread 1 advanced to log sequence%'
+        and    text not like '%Current log#%seq#%mem#%'
+        and    text not like '%Undo Segment%lined%'
+        and    text not like '%alter tablespace%back%'
+        and    text not like '%Log actively being archived by another process%'
+        and    text not like '%alter database backup controlfile to trace%'
+        and    text not like '%Created Undo Segment%'
+        and    text not like '%started with pid%'
+        and    text not like '%ORA-12012%'
+        and    text not like '%ORA-06512%'
+        and    text not like '%ORA-000060:%'
+        and    text not like '%coalesce%'
+        and    text not like '%Beginning log switch checkpoint up to RBA%'
+        and    text not like '%Completed checkpoint up to RBA%'
+        and    text not like '%specifies an obsolete parameter%'
+        and    text not like '%BEGIN BACKUP%'
+        and    text not like '%END BACKUP%'
+    )
+    loop
+        isdate       := 0;
+        message_text := null;
+        select count(*)
+        into   isdate
+        from   dual
+        where  substr(r.message_text, 21) in ('2019','2020','2021','2022','2023')
+        and    r.message_text not like '%cycle_run_year%';
+        if (isdate = 1) then
+            select to_date(substr(r.message_text, 5),'Mon dd hh24:mi:ss rrrr')
+            into message_date
+            from dual;
+            if (message_date > max_date) then
+                start_updating := 1;
+            end if;
+        else
+            message_text := r.message_text;
+        end if;
+        if (message_text is not null) and (start_updating = 1) then
+            insert into alert_log (indx, message_date, message_text) values (alert_log_s1.nextval, message_date, message_text);
+            rows_inserted := rows_inserted+1;
+        end if;
+    end loop;
+    sys.dbms_output.put_line('Inserting after: '||to_char(max_date, 'DD-MON-RR HH24:MI:SS'));
+    sys.dbms_output.put_line('Rows Inserted:   '||rows_inserted);
+    commit;
+end;
+/
+</code>
+Update the alert_log table periodically from crontab with the alerts generated since the last run
+<code>
+#!/usr/bin/ksh
+ORAENV_ASK=NO
+export ORACLE_SID=fin
+export PATH="/usr/local/bin:${PATH}"
+. oraenv
+sqlplus / as sysdba<<EOSQL
+start /oracle/scripts/update_custom_alertlog
+EOSQL
+</code>
+==== Alert log (11g+) ====
+This reads the log.xml file (which contains the same information as alert.log)
 Show the messages put in the alert log in the last 24 hours
-<code>99@@</code>
+<code>
-There is also a fixed table X$DBGDIREXT which returns all file and directory names under [[diagnostic_dest]]/diag directory:
+select substr(MESSAGE_TEXT, 1, 300) message_text
-<code>100@@</code>
+,      count(*) cnt
-=====Directory paths available in v$diag_info=====
+from   X$DBGALERTEXT
-<code>101@@</code>
+where  1=1
-=====Find out the names of the active trace files for the running processes=====
+and    (MESSAGE_TEXT like '%ORA-%' or upper(MESSAGE_TEXT) like '%ERROR%')
-<code>102@@</code>
+and    cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - &days_ago/1440
+group  by substr(MESSAGE_TEXT, 1, 300)
+</code>
+There is also a fixed table X$DBGDIREXT which returns all file and directory names under diagnostic_dest/diag directory:
+<code>
+select lpad(' ',lvl,' ')||logical_file file_name
+from   x$dbgdirext
+where  rownum <=20;
+</code>
+==== Directory paths available in v$diag_info ====
+<code>
+set lines 2000
+col name for a35
+col value for a100 wrap
+select * from v$diag_info;
+   INST_ID NAME                                VALUE                                                                                                    CON_ID
+---------- ----------------------------------- ---------------------------------------------------------------------------------------------------- ----------
+Diag Enabled                        TRUE                                                                                                       0
+ADR Base                            /cln/prg/ora_bin2/app/oracle                                                                               0
+ADR Home                            /cln/prg/ora_bin2/app/oracle/diag/rdbms/bild/bild                                                          0
+Diag Trace                          /cln/prg/ora_bin2/app/oracle/diag/rdbms/bild/bild/trace                                                    0
+Diag Alert                          /cln/prg/ora_bin2/app/oracle/diag/rdbms/bild/bild/alert                                                    0
+Diag Incident                       /cln/prg/ora_bin2/app/oracle/diag/rdbms/bild/bild/incident                                                 0
+Diag Cdump                          /cln/prg/ora_bin2/app/oracle/diag/rdbms/bild/bild/cdump                                                    0
+Health Monitor                      /cln/prg/ora_bin2/app/oracle/diag/rdbms/bild/bild/hm                                                       0
+Default Trace File                  /cln/prg/ora_bin2/app/oracle/diag/rdbms/bild/bild/trace/bild_ora_44696768.trc                              0
+Active Problem Count                0                                                                                                          0
+Active Incident Count               0                                                                                                          0
+rows selected.
+</code>
+==== Find out the names of the active trace files for the running processes ====
+<code>
+select pid, program, tracefile from v$process;
+</code>
+==== Request was to duplicate a large tablespace from production to test environment ====
+...but rather than copy the terabytes of data, create the destination tablespace with just the most recent months data
+=== See what the source data looks like ===
+<code>
+col table_owner    for a20
+col table_name     for a30
+col partition_name for a20
+set pages 100 lines 1000
-=====Request was to duplicate a large tablespace from production to test environment=====
+select table_owner
-...but rather than copy the terabytes of data, create the destination tablespace with just the most recent months data<br />
+,      table_name
-====See what the source data looks like====
+,      partition_name
-<code>103@@</code>
+,      partition_position
+,      tablespace_name
+from   dba_tab_partitions
+where  tablespace_name = 'ADS_ARCHIVE'
+order  by 1,2,3,4
+/
+</code>
 This query produces something like this...
-<code>104@@</code>
+<code>
-====Capture the tablespace metadata====
+TABLE_OWNER          TABLE_NAME                     PARTITION_NAME       PARTITION_POSITION TABLESPACE_NAME
-<code>105@@</code>
+-------------------- ------------------------------ -------------------- ------------------ ------------------------------
+ADS_ARCHIVE          BIL_DAY_INV_CTR_ARC            Y2017_Q1_M01_D01                      1 ADS_ARCHIVE
+ADS_ARCHIVE          BIL_HIS_DETAIL_FAC_ARC         Y2016_Q2_M05_D04                      1 ADS_ARCHIVE
+ADS_ARCHIVE          BIL_HIS_DETAIL_FAC_ARC         Y2016_Q2_M05_D09                      2 ADS_ARCHIVE
+ADS_ARCHIVE          BIL_MTH_INV_CTR_ARC            Y2017_Q1_M01_D01                      1 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_CBA_BGAAP_ARC          Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_CBA_BGAAP_ARC          Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_CBA_IFRS_ARC           Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_CBA_IFRS_ARC           Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC          Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC          Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC          Y2012_Q1_M03_D31                      3 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC          Y2012_Q2_M04_D01                      4 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC          Y2012_Q4_M10_D12                      5 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC          Y2012_Q4_M10_D13                      6 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC          Y2012_Q4_M10_D14                      7 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC_TST      Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          FAH_DAY_MVT_BGAAP_ARC_TST      Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+...
+ADS_ARCHIVE          SBP_HIS_LINES_ARC              P_LS                                  3 ADS_ARCHIVE
+ADS_ARCHIVE          THA_DAY_INV_CTR_ARC            Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          THA_DAY_INV_CTR_ARC            Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_HEAVY_ARC              Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_HEAVY_ARC              Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D12                      2 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D13                      3 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D15                      4 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D16                      5 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D17                      6 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D18                      7 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D19                      8 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D20                      9 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D21                     10 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D22                     11 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D23                     12 ADS_ARCHIVE
+ADS_ARCHIVE          THA_INV_LIGHT_ARC              Y2012_Q4_M10_D24                     13 ADS_ARCHIVE
+ADS_ARCHIVE          THA_MTH_INV_CTR_ARC            Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          THA_MTH_INV_CTR_ARC            Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          THA_MTH_INV_CTR_ARC            Y2013_Q2_M04_D30                      3 ADS_ARCHIVE
+ADS_ARCHIVE          XBD01_TEMP_HIS_ARC             Y2012_Q4_M12_D31                      1 ADS_ARCHIVE
+ADS_ARCHIVE          T_RPT_RECO_THA_INV_ACC_ARC     Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          T_RPT_RECO_THA_INV_ACC_ARC     Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          T_RPT_RECO_THA_INV_MTH_ARC     Y2011_Q4_M12_D30                      1 ADS_ARCHIVE
+ADS_ARCHIVE          T_RPT_RECO_THA_INV_MTH_ARC     Y2011_Q4_M12_D31                      2 ADS_ARCHIVE
+ADS_ARCHIVE          XBD01_TEMP_HIS_ARC             Y2012_Q4_M12_D31                      1 ADS_ARCHIVE
+ADS_DBA              FAH_DAY_CBA_BGAAP              Y2016_Q3_M07_D12                   1657 ADS_ARCHIVE
+ADS_DBA              FAH_DAY_CBA_BGAAP              Y2016_Q4_M12_D13                   1811 ADS_ARCHIVE
+ADS_DBA              FAH_DAY_MVT_IFRS               Y2016_Q4_M10_D10                   1654 ADS_ARCHIVE
+</code>
+=== Capture the tablespace metadata ===
+<code>
+set long 1000000
+select dbms_metadata.get_ddl ( 'TABLESPACE', 'ADS_ARCHIVE') from dual
+/
+</code>
 produces...
-<code>106@@</code>
+<code>
-====Capture the tablespace objects' metadata====
+CREATE TABLESPACE "ADS_ARCHIVE" DATAFILE
-<code>107@@</code>
+  '/oracle/tst/ora_data3/adst/ads_archive01.dbf' SIZE 104857600
-====Find the partitions with data from the most recent month====
+  AUTOEXTEND ON NEXT 104857600 MAXSIZE 32000M,
-<code>108@@</code>
+...
+  '/oracle/tst/ora_data3/adst/ads_archive66.dbf' SIZE 104857600
+  AUTOEXTEND ON NEXT 104857600 MAXSIZE 3200M,
+  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
+  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
+ NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
+</code>
+=== Capture the tablespace objects' metadata ===
+<code>
+cat<<EOCAT >expdp_adst_ads_archive_metadata.par
+userid='/ as sysdba'
+dumpfile=adst_ads_archive_metadata.dmp
+logfile=expdp_adst_ads_archive_metadata.log
+tablespaces=ADS_ARCHIVE
+parallel=20
+content=metadata_only
+exclude=statistics
+EOCAT
+expdp parfile=expdp_adst_ads_archive_metadata.par
+</code>
+=== Find the partitions with data from the most recent month ===
+<code>
+cat <<EOCAT>/tmp/adst_ads_archive_partitions.sql
+set termo off echo off lines 1000 feedb off headi off newpa none trims on
+spool /tmp/adst_ads_archive_partitions.lst
+select table_owner||'.'||table_name||':'||partition_name
+from   dba_tab_partitions
+where  tablespace_name = 'ADS_ARCHIVE'
+and    partition_name like 'Y2017_Q1_M01%'
+/
+spool off
+echo "@/tmp/adst_ads_archive_partitions.sql" | sqlplus / as sysdba
+EOCAT
+</code>
 produces...
-<code>109@@</code>
+<code>
-====Export the data in these partitions====
+ADS_ARCHIVE.FAH_DAY_CBA_BGAAP_ARC:Y2017_Q1_M01_D07
-<code>110@@</code>
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D26
-====Drop the old tablespace====
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D25
-<code>111@@</code>
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D19
-====Recreate the tablepspace using the metadata capture above====
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D18
-<code>112@@</code>
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D12
-====Import the tablespace metadata====
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D11
-<code>113@@</code>
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D05
-====Import the last month partition data====
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D26
-<code>114@@</code>
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D25
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D19
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D18
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D12
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D11
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D05
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M02_D04
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D29
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D28
+...
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D22
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D21
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D15
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D14
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D08
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D07
+ADS_ARCHIVE.FAH_DAY_MVT_BGAAP_ARC:Y2017_Q1_M01_D01
+ADS_ARCHIVE.BIL_MTH_INV_CTR_ARC:Y2017_Q1_M01_D01
+ADS_ARCHIVE.BIL_DAY_INV_CTR_ARC:Y2017_Q1_M01_D01
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M02_D04
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M01_D29
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M01_D28
+ADS_ARCHIVE.FAH_DAY_MVT_IFRS_ARC:Y2017_Q1_M01_D22
+</code>
+=== Export the data in these partitions ===
+<code>
+cat<<EOCAT >expdp_adst_ads_archive_partitions.par
+userid='/ as sysdba'
+dumpfile=adst_ads_archive_Y2017_%U.dmp
+logfile=expdp_adst_ads_archive_Y2017.log
+EOCAT
+cat /tmp/adst_ads_archive_partitions.lst | perl -p -i -e 's/^/tables=/'g >>expdp_adst_ads_archive_partitions.par
+cat<<EOCAT >>expdp_adst_ads_archive_partitions.par
+parallel=20
+exclude=statistics
+EOCAT
+expdp parfile=expdp_adst_ads_archive_partitions.par
+</code>
+=== Drop the old tablespace (make sure you are on the destination database!) ===
+<code>
+drop tablespace ads_archive including contents and datafiles
+/
+</code>
+=== Recreate the tablepspace using the metadata capture above ===
+<code>
+create tablespace "ADS_ARCHIVE"
+datafile '/oracle/tst/ora_data3/adst/ads_archive01.dbf' size 100M autoextend on next 50M maxsize unlimited
+,        '/oracle/tst/ora_data3/adst/ads_archive02.dbf' size 100M autoextend on next 50M maxsize unlimited
+,        '/oracle/tst/ora_data3/adst/ads_archive03.dbf' size 100M autoextend on next 50M maxsize unlimited
+,        '/oracle/tst/ora_data3/adst/ads_archive04.dbf' size 100M autoextend on next 50M maxsize unlimited
+,        '/oracle/tst/ora_data3/adst/ads_archive05.dbf' size 100M autoextend on next 50M maxsize unlimited
+/
+</code>
+=== Import the tablespace metadata ===
+<code>
+cat<<EOCAT >impdp_adst_ads_archive_metadata.par
+userid='/ as sysdba'
+dumpfile=adst_ads_archive_metadata.dmp
+logfile=impdp_adst_ads_archive_metadata.log
+EOCAT
+impdp parfile=impdp_adst_ads_archive_metadata.par
+select 'alter index '|| owner||'.'||index_name||' rebuild online parallel 8;'
+from   dba_indexes
+where  status = 'UNUSABLE'
+/
+</code>
+=== Import the last month partition data ===
+<code>
+cat<<EOCAT >impdp_adst_ads_archive_partitions.par
+userid='/ as sysdba'
+dumpfile=adst_ads_archive_Y2017_%U.dmp
+logfile=impdp_adst_ads_archive_Y2017.log
+table_exists_action=append
+parallel=20
+EOCAT
+impdp parfile=impdp_adst_ads_archive_partitions.par
+</code>
-=====Write to a TCP/IP socket from PL/SQL=====
+==== Write to a TCP/IP socket from PL/SQL ====
-<code>115@@</code>
+Like writing to a pipe to get data out to the shell from PL/SQL
+<code>
+declare
+ bt_conn               utl_tcp.connection;
+ retval                binary_integer;
+ l_sequence            varchar2(30) := 'Sequence Number 101021';
+begin
+ bt_conn := utl_tcp.open_connection(remote_host => '10.10.1.1'
+                                   ,remote_port => 5000
+                                   ,tx_timeout => 1
+                                   )
+ ;
+ retval := utl_tcp.write_line(bt_conn,l_sequence)
+ ;
+ utl_tcp.flush(bt_conn)
+ ;
+ utl_tcp.close_connection(bt_conn)
+ ;
+exception
+ when others then
+  raise_application_error(-20101, sqlerrm)
+  ;
+  utl_tcp.close_connection(bt_conn)
+  ;
+end
+;
+/
+</code>
 and read from it using netcat in another session
-<code>116@@</code>
+<code>
+nc -lk 5000
+</code>
 produces
-<code>117@@</code>
+<code>
-=====Execute a script as a different user=====
+Sequence Number 101021
-Some things have to be done as the user and cannot be done as sys (eg. creating and dropping jobs or create and drop a database link)<br />
+</code>
-It is possible to login as the user by saving, changing and resetting his password (using identified by values) but using dbms_sys_sql is another option<br />
+==== Drop all database links on an instance using the user procedure method ====
+<code>
+set feed off head off lines 1000 pages 0 newpa none termo off trims on
+spool /tmp/drop_db_links_for_sid_&_CONNECT_IDENTIFIER..sql
+select 'drop public database link "'||db_link||'";'
+from   dba_db_links
+where  owner = 'PUBLIC'
+/
+select 'create or replace procedure '||owner||'.drop_'||replace(db_link,'.','')||' as begin execute immediate ''drop database link "'||db_link||'"''; end;'
+from   dba_db_links
+where  owner not in ('SYS','PUBLIC')
+/
+select 'exec '||owner||'.drop_'||replace(db_link,'.','')||';'
+from   dba_db_links
+where  owner not in ('SYS','PUBLIC')
+/
+select 'drop procedure '||owner||'.drop_'||replace(db_link,'.','')||';'
+from   dba_db_links
+where  owner not in ('SYS','PUBLIC')
+/
+spool off
+set termo on lines 80 head on feed on newpa 1
+prompt @/tmp/drop_db_links_for_sid_&_CONNECT_IDENTIFIER
+</code>
+==== Drop all database links on an instance (from shell) using sqldba cursor method ====
+<code>
+#!/usr/bin/ksh
+# ==============================================================================
+# Name         : drop_database_links.ksh
+# Description  : Drop all database links on an instance
+#
+# Parameters   : instance name
+#
+# Example      : ./drop_database_links.ksh orgm
+#
+# Modification History
+# ====================
+# When      Who               What
+# ========= ================= ==================================================
+# 09-OCT-19 Stuart Barkley    Created
+# ==============================================================================
+# --------------------------
+# setup database environment
+# --------------------------
+if [[ $# -ne 1 ]]; then
+    echo "ERROR: SID must be passed as argument"
+    exit 1
+fi
+ORAENV_ASK=NO
+export ORACLE_SID="${1}"
+. oraenv >/dev/null 2>&1
+if [[ $? -ne 0 ]]; then
+    echo "ERROR: Failed to setup environment for ${ORACLE_SID}."
+    exit 1
+fi
+TMPFILE="/tmp/dba_db_links_to_drop_${ORACLE_SID}.log"
+# ----------------------------------------
+# build procedure to remove database links
+# ----------------------------------------
+"${ORACLE_HOME}/bin/sqlplus" -s '/ as sysdba'<<-EOSQL >"${TMPFILE}" 2>&1
+    whenever sqlerror continue
+    set headi off newpa none feedb off verif off trims on
+    create or replace procedure drop_dblink ( p_schema_name in varchar2
+                                            , p_db_link     in varchar2
+                                            ) as
+        plsql   varchar2(4000);
+        cur     number;
+        uid     number;
+        rc      number;
+    begin
+        select u.user_id
+        into   uid
+        from   dba_users u
+        where  u.username = p_schema_name
+        ;
+        plsql := 'drop database link "'||p_db_link||'"';
+        cur := sys.dbms_sys_sql.open_cursor;
+        sys.dbms_sys_sql.parse_as_user ( c             => cur
+                                       , statement     => plsql
+                                       , language_flag => dbms_sql.native
+                                       , userid        => uid
+                                       );
+        rc := sys.dbms_sys_sql.execute (cur);
+        sys.dbms_sys_sql.close_cursor (cur);
+    end;
+    /
+    spool /tmp/dba_db_links_to_drop.$$.sql
+    select 'drop public database link '||db_link||';'
+    from   dba_db_links
+    where  owner = 'PUBLIC'
+    /
+    select 'exec drop_dblink( '''||owner||''''||','||''''||db_link||''''||');'
+    from   dba_db_links
+    where  owner != 'PUBLIC'
+    /
+    spool off
+rem    start /tmp/dba_db_links_to_drop.$$.sql
+EOSQL
+grep 'ORA-' "${TMPFILE}" >/dev/null 2>&1
+if [[ $? -eq 0 ]]; then
+    echo "ERROR: Unable to complete dropping of database links:"
+    cat "${TMPFILE}"
+    rm -rf "${TMPFILE}" /tmp/dba_db_links_to_drop.$$.sql
+    exit 1
+fi
+rm -rf "${TMPFILE}" /tmp/dba_db_links_to_drop.$$.sql
+exit 0
+</code>
+==== From a dba or sysdba account, test database links owned by a user to see if they are valid ====
+ * borrowed from [[https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9531409900346749897|this asktom question]]
+<code>
+set serveroutput on
+begin
+    dbms_output.enable(null);
+    for f in (select *
+              from   dba_db_links
+              order  by owner
+              ,      db_link
+             )
+    loop
+        dbms_scheduler.create_job ( job_name     => f.owner||'.dblink'
+                                  , job_type     => 'PLSQL_BLOCK'
+                                  , job_action   => 'declare '
+                                                  ||'    x char;'
+                                                  ||'begin '
+                                                  ||'    select dummy into x from dual@'||f.db_link||';'
+                                                  ||'    dbms_output.put_line('''||f.owner||' '||f.db_link||' OK'');'
+                                                  ||'end;'
+                                  );
+        begin
+            dbms_scheduler.run_job (f.owner||'.dblink', true);
+        exception
+        when others then
+            dbms_output.put_line (f.owner||' '||f.db_link||' NOK ('||sqlerrm||')');
+        end;
+        dbms_scheduler.drop_job (f.owner||'.dblink');
+    end loop;
+end;
+/
+</code>
+==== Execute a script as a different user ====
+Some things have to be done as the user and cannot be done as sys (eg. creating and dropping jobs or create and drop a database link)
+It is possible to login as the user by saving, changing and resetting his password (using identified by values) but using dbms_sys_sql is another option
 Create a PL/SQL procedure to drop a db link
-<code>118@@</code>
+<code>
+create or replace procedure drop_dblink ( p_schema_name in varchar2
+                                        , p_dblink      in varchar2
+                                        ) is
+    l_plsql   varchar2(1000);
+    l_cur     number;
+    l_uid     number;
+    l_rc      number;
+begin
+    select  du.user_id
+    into    l_uid
+    from    dba_users du
+    where   du.username = p_schema_name
+    ;
+    plsql := 'drop database link "'||p_dblink||'"';
+    l_cur := sys.dbms_sys_sql.open_cursor;
+    sys.dbms_sys_sql.parse_as_user(
+        c             => l_cur,
+        statement     => l_plsql,
+        language_flag => dbms_sql.native,
+        userid        => l_uid
+    );
+    l_rc := sys.dbms_sys_sql.execute(l_cur);
+    sys.dbms_sys_sql.close_cursor(l_cur);
+end;
+/
+</code>
 Generate the calls to the procedure with the required parameters
-<code>119@@</code>
+<code>
+select 'exec drop_dblink ('''|| owner ||''''||', '||''''||db_link||''''||');'
+from   dba_db_links
+where  owner   != 'PUBLIC'
+and    db_link like '%FTST%'
+or     db_link like '%HDEV%'
+/
+</code>
 and this reveals the lines to be executed
-<code>120@@</code>
+<code>
+exec drop_dblink ('USER1', 'DBL_FTST.WORLD');
+exec drop_dblink ('USER1', 'DBL_HDEV.WORLD');
+</code>
 Drop the procedure when finished with it
-<code>121@@</code>
+<code>
+drop procedure drop_dblink;
+</code>
 or do it as an anonymous block in one go...
-<code>122@@</code>
+<code>
-=====Handling single quote marks inside SQL statements=====
+declare
-<code>123@@</code>
+    uid number;
+    sqltext varchar2(1000) := 'drop database link "&DB_LINK"';
+    myint integer;
+begin
+    select user_id into uid from dba_users where username = '&USERNAME';
+    myint:=sys.dbms_sys_sql.open_cursor();
+    sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
+    sys.dbms_sys_sql.close_cursor(myint);
+end;
+/
+</code>
+==== Handling single quote marks inside SQL statements ====
+<code>
+SQL> SELECT 'D''COSTA' name FROM DUAL;
-=====Run a script on all databases in /etc/oratab=====
+NAME
-<code>124@@</code>
+-------
-=====Schedule a cron job to run on the last day of each month=====
+D'COSTA
-<code>125@@</code>
-=====How to tell if a year is a leap year or not - using cal instead of reinventing the wheel=====
+Alternatively, use the 10g+ quoting method:
-Test: Why is Julian day 0, 17th November 1858?<br />
-Pretty comprehensive answer: [[http://h71000.www7.hp.com/wizard/wiz_2315.html|here]] and [[http://mentalfloss.com/article/51370/why-our-calendars-skipped-11-days-1752|here]]<br />
+SQL> SELECT q'$D'COSTA$' NAME FROM DUAL;
+NAME
+-------
+D'COSTA
+</code>
+==== Run a script on all databases in /etc/oratab ====
+<code>
+export ORAENV_ASK=NO
+for db in `awk -F: 'NF && $1 !~/[#*]/ {print $1}'  /etc/oratab`
+do
+    echo $db
+    ORACLE_SID=$db
+    . oraenv
+    sqlplus -V
+done
+</code>
+==== Schedule a cron job to run on the last day of each month ====
+<code>
+23 28-31 * * [ $(date -d +1day +%d) -eq 1 ] && su - oracle -c "dsmc  arch -server=SO_U_`hostname`_ORX_M_SOL '/oracle/export/PV6/expdp_PV6_D_FULL_`date +\%Y\%m\%d`*'"
+</code>
+==== How to tell if a year is a leap year or not - using cal instead of reinventing the wheel ====
+Test: Why is Julian day 0, 17th November 1858?\\
+Pretty comprehensive answer: [[http://h71000.www7.hp.com/wizard/wiz_2315.html|here]] and [[http://mentalfloss.com/article/51370/why-our-calendars-skipped-11-days-1752|here]]\\
 Also attempts to answer the question why typing 'cal 09 1752' on Unix systems looks so odd!
-<code>126@@</code>
+<code>
+function leap {
+# This function tells you if the year passed in as a parameter is a leap year or not...
+if [[ $(cal 2 ${1:-$(date '+%Y')} | egrep '\\<[[:digit:]]{1,2}\\>' | wc -w) -eq 29 ]]; then
+    leap='Y'
+else
+    leap='N'
+fi
+echo ${leap}
+}
+</code>
-=====Reset an expired (and/or locked) user password to the same value=====
+==== Clone an Oracle Home ====
-Can be used to save user passwords for example in a test environment before destroying them with a copy of production.<br />
+Cloning an ORACLE_HOME saves much time if this has to be done several times.\\
-Simple, quick and easy version. Run this then pick and choose the statements you want to run.
-<code>127@@</code>
+On source server
+<code>
+cd $ORACLE_HOME
+tar -cvf - . | gzip -c > clonehome.tar.gz
+</code>
+Copy the archive to the destination server.\\
+On the destination server
+<code>
+mkdir -p <where you want the new ORACLE_HOME to be>
+export ORACLE_HOME=<above directory>
+gunzip -c clonehome.tar.gz | tar -xvf -
+</code>
+  * Check $ORACLE_HOME/dbs is empty of all old files
+  * Check $ORACLE_HOME/network/admin is empty of all old files
+<code>
+${ORACLE_HOME}/perl/bin/perl ${ORACLE_HOME}/clone/bin/clone.pl ORACLE_BASE="<path as far as oracle>" ORACLE_HOME="$ORACLE_HOME" ORACLE_HOME_NAME="OraDB12Home2"
+</code>
+As root\\
+Check logfile for path to oraInventory
+<code>
+<path to oraInventory>/orainstRoot.sh
+<path to ORACLE_HOME>/root.sh
+</code>
+  * Add $ORACLE_HOME/bin to PATH
+  * Run netca to create a listener
+==== Reset an expired (and/or locked) user password to the same value ====
+Can be used to save user passwords for example in a test environment before destroying them with a copy of production.
+<code>
+set lines 200 pages 200 head off
+col user_list for a200 head "Expired User List"
+select 'alter user '||su.name||' identified by values '''||decode( su.spare4
+                                                                 , null, decode( su.password
+                                                                               , null, 'not identified'
+                                                                               , su.password
+                                                                               )
+                                                                 , decode( su.password
+                                                                         , null, su.spare4
+                                                                         , su.spare4||';'||su.password
+                                                                         )
+                                                                 )||'''account unlock;' stmt
+from   sys.user$ su
+,      dba_users du
+where  1=1
+and    su.name              = du.username
+and    du.oracle_maintained = 'N'
+and    (account_status      like '%EXPIRED%' or account_status like '%LOCKED%')
+order  by su.name
+/
+</code>
 More industrialised version. Create a package.
-<code>128@@</code>
+<code>
-Call it
+create or replace package dba_utils
-<code>129@@</code>
+as
+    procedure reset_password ( p_userlist in varchar2 );
+end dba_utils;
+/
-=====Latch, mutex and beyond=====
-A fine example of how to write scripts from SQL*Plus that interact with the shell underneath<br />
-Found [[https://andreynikolaev.wordpress.com/demonstration-scripts/cursor_pin_s_contention-sql/|here at andreynikolaev.wordpress.com]]
-<code>130@@</code>
-======shell======
+create or replace package body dba_utils
+as
+procedure reset_password ( p_userlist in varchar2 )
+is
+    cursor c1 ( p_userlist in varchar2 ) is
+    select username
+    ,      regexp_substr ( dbms_metadata.get_ddl('USER', username), '''[^'']+''' ) pwd
+    from   dba_users
+    where  1=1
+    and    username in upper( p_userlist )
+    ;
+begin
+    dbms_output.enable(null);
+    for r1 in c1 ( p_userlist )
+    loop
+        begin
+            execute immediate 'alter user ' || r1.username || ' account unlock';
+            execute immediate 'alter user ' || r1.username || ' identified by values ' || r1.pwd;
+        exception
+        when others then
+            dbms_output.put_line('Unable to modify user ' || r1.username);
+        end;
+    end loop;
+end reset_password;
+end dba_utils;
+/
+</code>
+Call it
+<code>
+set serveroutput on
+exec dba_utils.reset_password ('STUART');
+</code>
+===== shell =====
 [[http://www.databasejournal.com/features/oracle/article.php/3869076/Alert-Log-Scraping-with-Oracles-ADRCI-Utility.htm|Alert Log scraping with ADRCI]]
-====Count the number of background processes for a particular SID====
+=== Count the number of background processes for a particular SID ===
-<code>131@@</code>
+<code>
+SID=RAVJDE1
+ps -ef|cut -c54-100|awk -v SID=$SID '$0 ~ /'$SID'$/ {print}'|wc -l
+</code>
 or ...this will work with any OS (so long as the process is at the end of the <strong>ps -ef</strong> or <strong>ps -aux</strong> listing
-<code>132@@</code>
+<code>
+SID=RAVJDE1
+ps -ef|awk -v SID=$SID -F_ '{ $NF ~ /'$SID'$/ {print} }'|wc -l
+</code>
 Pick the columns you want in the ps listing
-<code>133@@</code>
+<code>
-====Find Oracle errors in the most recent DataPump log files====
+ps -efo user,pid,ppid=MOM -o args
-<code>134@@</code>
+</code>
+=== Find Oracle errors in the most recent DataPump log files ===
+<code>
+09 * * * /home/tools/scripts/oracle/dosh 'find /oracle/export -name "expdp*log" -mtime -1 -exec grep ORA- {} \; -ls' | mailx -s 'Datapump errors for <customer>last night' stuart@domain.com
+</code>
-====Find the 10 largest directories on a particular filesystem====
+=== Find the 10 largest directories on a particular filesystem ===
-<code>135@@</code>
+<code>
+du -ag /oracle | sort -nr | head -n 10
+</code>
-====Find the 10 largest files on a particular mount point====
+=== Find the 10 largest files on a particular mount point ===
-<code>136@@</code>
+<code>
+find /home -size +1g -ls | sort -nr +6 | head -10
+</code>
 or
-<code>137@@</code>
+<code>
+find /home -xdev -ls | sort -nr +6 | head -10
+</code>
-====Split a file in pieces using sed====
+=== Split a file in pieces using sed ===
 Using fixed line numbers
-<code>138@@</code>
+<code>
+sed -ne '1,12p' file > text.part1
+sed -ne '13,$p' file > text.part2
+</code>
 Using line with a /pattern/ on it
-<code>139@@</code>
+<code>
+sed -e '/hello/q' tt.dat | grep -v hello > text.part1
+sed -n `sed -n '/hello/=' tt.dat`',$p' tt.dat | grep -v hello > text.part2
+</code>
+=== Split a file in pieces using Perl ===
+<code>
+perl -ne 'print if 1 .. /marker line/' text.dat >text.part1
+perl -ne 'print if /marker line/ .. eof()' text.dat >text.part2
+</code>
-====Split a file in pieces using Perl====
+=== Find and display RMAN errors in an RMAN log file ===
-<code>140@@</code>
+<code>
+sed -ne '/^RMAN-/,/^$/p'  RMAN_<SID>_<DATE>.log
+</code>
-====Find and display RMAN errors in an RMAN log file====
+===== cmd =====
-<code>141@@</code>
+  * [[http://www.dba-oracle.com/t_scripts_windows_export.htm|Database Export - from DBA Oracle (Burleson)]]
+===== Perl =====
+==== For all files in a directory, replace a keyword (SSIIDD) in a file with another (a parameter) ====
+<code>
+for i in `ls`
+do
+    perl -p -i -e 's/SSIIDD/'$SID'/g' $i
+done
+</code>
-======cmd======
+==== Parse tnsnames.ora to get just the service names ====
-*[[http://www.dba-oracle.com/t_scripts_windows_export.htm|Database Export - from DBA Oracle (Burleson)]]
+[[http://stackoverflow.com/questions/12605415/parsing-tnsnames-ora-with-regex-to-just-get-name|from here]]
-======Perl======
+<code>
-=====For all files in a directory, replace a keyword (SSIIDD) in a file with another (a parameter)=====
+perl -ne 'print if m/^([^#()\W ][a-zA-Z.]*(?:[.][a-zA-Z]*\s?=)?)/' $TNS_ADMIN/tnsnames.ora
-<code>142@@</code>
+</code>
-=====Parse tnsnames.ora to get just the service names=====
+==== and use that to check the listener status ====
-[[http://stackoverflow.com/questions/12605415/parsing-tnsnames-ora-with-regex-to-just-get-name from here]]
+<code>
-<code>143@@</code>
+for svc in `perl -ne 'print if m/^([^#()\W ][a-zA-Z.]*(?:[.][a-zA-Z]*\s?=)?)/' $TNS_ADMIN/tnsnames.ora | sed -e 's/=//'` ;do
+    tnsping $svc
+done
+</code>
-=====and use that to check the listener status=====
-<code>144@@</code>