SQL*Plus

Syntax

sqlplus system/manager@ServeurDB:1521/SIDBASE

Customization : edit $ORACLE_HOME/sqlplus/admin/glogin

SET SQLPROMPT _user"@"_connect_identifier>
SET LINESIZE 132
SET PAGESIZE 1000

Request execution plan

explain plan for select machin from table where truc='chose'
select * from table(dbms_xplan.display()) ;

Affichage alternatif

col operation format a20
col options format a20
col object_name for a20
col object_type for a20
select id,parent_id,operation,options,object_name,object_type from plan_table order by id;

You might draw links between tables in a request using this site in case you miss a join.

explain plan allow to estimate index's size passing down command create index

Track a session

Get session's info

select sid, serial#, username,osuser,program, machine from v$session where username is not null;

Then you can track it using dbms_monitor using sid and serial#

exec dbms_monitor.session_trace_enable(val_sid,val_serial#, TRUE, TRUE);

Then run whatever application you wanna track, then stop tracking

exec dbms_monitor.session_trace_disable(val_sid,val_serial#);

The trace's gonna be in this directory

select value from vdiag_info where name = 'Diag Trace';

Name will be instancename_ora_numSPID.trc. To get SPID, get it using session_id

select spid from vprocess p, vsession s where p.addr = s.paddr and sid = session_id ;

Depending on activity, ls -rtl might do the job if that's the last trace there is in the folder.

tkprof may format this for you. SYS=NO will prevent it from including internal oracle stuff. SORT allow to sort depending on elapsed time, FCHELA or EXEELA

tkprof orcl_ora_1234.trc resu.txt sys=no sort=fchela

Otherwise SQL Trace Analyzer, grab it through MOS 215187.1. Once installed there's multiples options :

  • HTML formating using sqltrcanlzr.sql passing down trace name. You'll get a zip inclusing way more details than tkprof with a better readibility. Including additional data like waiting between order (GAP), objects' stats...

  • You might also generate a report with sqlxtract.sql giving the sql_id or request hash value.

Datafiles

List

select * from dba_data_files;
select * from all_data_files;

Add a datafile to a tablespace

ALTER TABLESPACE ts_data ADD DATAFILE '/production/donnees/dbs1/ts_data2.ora' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Modify a datafile

ALTER DATABASE DATAFILE '/production/donnees/dbs1/ts_data1.ora' AUTOEXTEND ON NEXT 100M MAXSIZE 6000M;

Tempfiles

Same than datafile with s/DATA/TEMP

Archive logs

Enable

set log_archive_dest is optional since you should have it in initPRODUCTION.ora or scope=SPFILE

su - oracle11
export ORACLE_SID=PRODUCTION
sqlplus / as sysdba
shutdown immediate
startup mount
alter database archivelog;
alter system set log_archive_dest='/backup/production/data/archives/arch';
alter database open;
alter system archive log current;
exit

Disable

su - oracle11
export ORACLE_SID=PRODUCTION
sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
exit

Switchs list

set lines 120;
set pages 999;
SELECT
  to_char(first_time,'YYYY-MON-DD') day,
  to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
  to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
  to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
  to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
  to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
  to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
  to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
  to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
  to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
  to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
  to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
  to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
  to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
  to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
  to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
  to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
  to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
  to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
  to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
  to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
  to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
  to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
  to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
  to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
FROM vlog_history
GROUP BY to_char(first_time,'YYYY-MON-DD')
ORDER BY to_char(first_time,'YYYY-MON-DD');

Min max average for redo logs by day

SELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM (
  SELECT To_Char(First_Time,'YYYY-MM-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max#
  FROM vlog_history
  GROUP BY To_Char(First_Time,'YYYY-MM-DD')
  ORDER BY 1 DESC
) A, 
(
  SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes
  FROM vlog
) B
ORDER BY 1;

ADRCI

Reducing logs retention

for HOME in `adrci exec="show homes" | grep -v "ADR Homes"`; do adrci exec="set homepath HOME; set control \\\\(SHORTP_POLICY=168\\\\); set control \\\\(LONGP_POLICY=168\\\\);"; done;

Oracle cleanup

#!/bin/sh
# Cleaning Oracle

# Warning
# Edit awk column number on line LSN_LIST=... ($9 linux, $10 AIX?)
# Listener should be started for at least one day, otherwise date format fuck the awk up
# Require up-to-date /etc/oratab

DATE=$(date +%Y%m%d_%H%M%S)
export ORACLE_HOME=/oracle/11.2
SAVED_PATH=$PATH
export PATH=$ORACLE_HOME/bin:$PATH

# listener.log (oracle 11 only)
# AIX
# LSN_LIST=$(ps -ef | grep "11.*tnslsnr" | grep -v grep | awk '{print $10}' | tr '\
' ' ')
# Linux
LSN_LIST=$(ps -ef | grep "11.*tnslsnr" | grep -v grep | awk '{print $9}' | tr '\
' ' ')
for listener in $LSN_LIST
do
  echo "________________________________________________________________________"
  echo "Working on listener '$listener''s traces..."
  lsnrctl set current_listener $listener > /dev/null
  LOG_STATUS=$(lsnrctl show log_status | egrep "ON|OFF" | awk '{print $7}')
  lsnrctl set log_status off > /dev/null
  export LSN_TRACE=$(lsnrctl show trc_directory | grep trc_ | sed "s;.* \\(.*\\);\\1;")
  export LSN_LOG=$(lsnrctl show log_directory | grep log_ | sed "s;.* \\(.*\\);\\1;")
  if [ -d $LSN_TRACE ]
  then
    cp $LSN_TRACE/listener.log $LSN_TRACE/listener_$DATE.log
    touch $LSN_TRACE/listener.log
    lsnrctl set log_status $LOG_STATUS > /dev/null
    # Cleaning old traces
    find $LSN_TRACE -name "listener_*.log" -mtime +10 -exec rm -f {} \\; #-print
  fi
  if [ -d $LSN_LOG ]
  then
    find $LSN_LOG -name "log_*.xml" -mtime +2 -exec rm -f {} \\; #-print
  fi
done

# alert_SID.log
ORATAB=/etc/oratab
SID_LIST="$(sed -n "s;^\\([^#]*\\):.*:.*$;\\1;gp" $ORATAB | tr '\
' ' ')"
for sid in $SID_LIST
do
  echo "________________________________________________________________________"
  echo "Working on alert_$sid.log..."
  if [ `ps -ef | grep -v grep |  grep -c smon_$sid` -eq 1 ]
  then
    export ORACLE_SID=$sid
    export ORACLE_HOME=$(grep "^$sid:" $ORATAB |  cut -d':' -f2)
    export PATH=$ORACLE_HOME/bin:$SAVED_PATH
    DIAG=$(echo "select value from v\\$parameter where name='diagnostic_dest';" | sqlplus -s system/manager | grep /)/diag
    if [ -d $DIAG ]
    then
      SID_LOWER=$(echo $sid | tr '[:upper:]' '[:lower:]')
      RDBMS_TRACE=$DIAG/rdbms/$SID_LOWER/$sid/trace/
      echo
      echo Rotating daily alert_$sid.log
      echo 'alter system set "_diag_backward_compat"=false;' | sqlplus -S system/manager > /dev/null
      cp $RDBMS_TRACE/alert_$sid.log $RDBMS_TRACE/alert_${sid}_$DATE.log
      touch $RDBMS_TRACE/alert_$sid.log
      echo 'alter system set "_diag_backward_compat"=true;' | sqlplus -S system/manager > /dev/null
      echo
      RETENTION=10
      echo Cleaning traces older than  $RETENTION days
      find $RDBMS_TRACE -name "alert_$sid_*.log" -mtime +$RETENTION -exec rm -f {} \\; #-print
      find $RDBMS_TRACE -name "$sid_*.trm" -mtime $RETENTION -exec rm -f {} \\; #-print
      find $RDBMS_TRACE -name "$sid_*.trc" -mtime $RETENTION -exec rm -f {} \\; #-print
      else
      echo
      echo "No alert_$sid.log rotation, $diag isn't a folder"
    fi
  else
    echo "Database not running, no cleaning done"
  fi    
done      
export PATH=$SAVED_PATH