Dev's Weblog

Reading time: < 1 minute

There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:

SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

ANALYZE TABLE owner.table ESTIMATE STATISTICS;

SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);

Thus, the tables' HWM = (query result 1) - (query result 2) - 1

NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
and if the index rebuild...

http://www.quest-pipelines.com/newsletter-v2/rebuild.htm

Reference:

http://forums.oracle.com/forums/thread.jspa?messageID=2655499&tstart=0

Reading time: 2 - 3 minutes

#!/usr/bin/ksh
# =============================================================================
# File Name: aoe
#
# Function: Configures the oracle environment
#
# Usage: . aoe [SID] [-]
#
# Remarks: aoe is a simple front end to the oracle oraenv utility.
# If a SID is not specified at the command line, a menu will be
# displayed, listing all available entries in the oratab.
# Once a selection has been made oraenv is called and if one exists
# an optional config script will also be executed.
# The optional config script must be located in
# $optional_script_dir and be named as follows: aoe_
# If - is specified as the last parameter, aoe will
# launch sqlplus "/ as sysdba".
#
# Notes: It is worth adding the following to you .profile (or .bash_profile):
# alias aoe=". aoe"
# Doing so removes the need to prefix the command with ". "
#

#

oratab=/etc/oratab
oraenv=/usr/local/bin/oraenv
optional_script_dir=/home/oracle/bin

# work out which echo options need to be used
c=`echo "\c"`; [ "$c" = "" ] && { c="\c"; n=""; } || { c=""; n="-n"; }

if [ $# -eq 0 ] || [[ $# -eq 1 && $1 = "-" ]]; then
# if no sid has been specified, display the menu

echo "Advanced OraEnv"
echo "==============="
item=1
for database in $( grep -v "#" ${oratab}|grep -v "*"|cut -f1 -d:|awk '$0!~/^$/ {print $0}' ); do
echo $n "${item}) $database${c}"
# if the entry matches the current ORALCE_SID setting mark it with a '*'
if [ "${database}" == "${ORACLE_SID}" ]; then
echo " *"
else
echo
fi
item=$((item + 1))
done

# get the users selection
echo
echo $n "Choose: $c"
read selection

# find the correct sid for the selection
item=1
ORACLE_SID=q1w2e3r4t5y6u7i8o9p
for database in $( grep -v "#" ${oratab}|grep -v "*"|cut -f1 -d:|awk '$0!~/^$/ {print $0}' ); do
if [ $item -eq $selection ];then
ORACLE_SID=$database
fi
item=$((item + 1))
done
else
# if a sid was specified make sure it's in the oratab
if [ `grep -v "#" ${oratab}|grep -v "*"|grep "${1}:"|wc -l` -eq 1 ];then
ORACLE_SID=$1
else
ORACLE_SID=q1w2e3r4t5y6u7i8o9p
fi
fi

# set the oracle environment
if [ $ORACLE_SID != "q1w2e3r4t5y6u7i8o9p" ]; then
export ORAENV_ASK=NO
. $oraenv
export ORAENV_ASK=YES
echo "ORACLE_SID : ${ORACLE_SID}"
echo "ORACLE_HOME : ${ORACLE_HOME}"
echo "LD_LIBRARY_PATH : ${LD_LIBRARY_PATH}"
echo "TNS_ADMIN : ${TNS_ADMIN}"

# look for and, if found, run the optional config script
if [ -a "${optional_script_dir}/aoe_${ORACLE_SID}" ]; then
echo "Executing optional config file..."
. ${optional_script_dir}/aoe_${ORACLE_SID}
echo "Completed execution of optional config file."
fi

# optionaly run sqlplus "/ as sysdba"
if [[ $# -eq 1 && $1 = "-" ]] || [[ $# -eq 2 && $2 = "-" ]]; then
sqlplus "/ as sysdba"
fi

else
echo "aoe error: Invalid SID or selection"
unset ORACLE_SID
unset ORACLE_HOME
fi

Reading time: 6 - 10 minutes

A

Patch Number for Linux platform.

A

• You must use the OPatch 10.2 version 10.2.0.4.2 or later. Oracle recommends that you use the latest released OPatch 10.2, which is available for download from My Oracle Support patch 6880880 by selecting the 10.2.0.0.0 release.

A

• Ensure that your Oracle Database installation is the same release for which you are applying this patch. In other words, only apply the Release 10.2.0.4 CPUJan2009 patch to an Oracle Database Release 10.2.0.4

A

• If you are patching an ASM instance, shut down all Oracle Database instances that use this ASM instance. (To see which Oracle Database instances are connected to this ASM instance, query the V$ASM_CLIENT view.

A

• Shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator's Guide

A

• Ensure that the $PATH has the following executables: make, ar, ld, and nm.

A

• The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH as follows:

export PATH=$PATH:/usr/ccs/bin

• Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:

A

unzip p7592346_10204_<platform>.zip
cd 7592346
opatch napply -skip_subset -skip_duplicate

A

• If this is the first CPU installed in this Oracle home since CPUApr2008, then run cpu_root.sh as root to complete the installation of molecule 7155248. To run cpu_root.sh, go to the directory where you downloaded the patch (if you are not already in that directory) and enter the following commands.

sh cpu_root.sh

• If you are unable to run cpu_root.sh during the CPU patch installation because of the additional access rights required, it can be run after the CPU patch installation, and the database does not need to be shut down.

ORA-27369: job of type EXECUTABLE failed with exit code: ...

Post Installation:

Loading Modified .sql Files into the Database

1. If there is a database in the Oracle home that you are patching, start all database instances running from this Oracle home.
2. For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:

   cd $ORACLE_HOME/rdbms/admin
   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   SQL> STARTUP
   SQL> @catbundle.sql cpu apply
   SQL> QUIT
   

3. Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle any errors:

   catbundle_CPU_<database SID>_APPLY_<TIMESTAMP>.log
   catbundle_CPU_<database sid>_GENERATE_<TIMESTAMP>.log

4. If you want to check whether view recompilation has already been performed for the database, execute the following statement.

   SELECT * FROM registry$history where ID = '6452863';

   If the view recompilation has been performed, this statement returns one or more rows. If the view recompilation has not been performed, this statement returns no rows.

   To recompile the views in the database, follow these steps:

   1. Run the pre-check script (so named because it was initially released in CPUJan2008), which reports the maximum number of views and objects that may be recompiled:

      cd $ORACLE_HOME/cpu/view_recompile
      sqlplus /nolog
      SQL> CONNECT / AS SYSDBA
      SQL> @recompile_precheck_jan2008cpu.sql
      SQL> QUIT

      The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.

   2. If the database is not in a RAC environment, perform this step. (If the database is in a RAC environment, go to the next step.)

      Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

      cd $ORACLE_HOME/cpu/view_recompile
      sqlplus /nolog
      SQL> CONNECT / AS SYSDBA
      SQL> SHUTDOWN IMMEDIATE
      SQL> STARTUP UPGRADE
      SQL> @view_recompile_jan2008cpu.sql
      SQL> SHUTDOWN;
      SQL> STARTUP;
      SQL> QUIT

   3. If the database is in a RAC environment, run the view recompilation script as follows. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

      cd $ORACLE_HOME/cpu/view_recompile
      sqlplus /nolog
      SQL> CONNECT / AS SYSDBA
      SQL> STARTUP NOMOUNT
      SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;
      SQL> SHUTDOWN
      SQL> STARTUP UPGRADE
      SQL> @view_recompile_jan2008cpu.sql
      SQL> SHUTDOWN;
      SQL> STARTUP NOMOUINT;
      
      Set the CLUSTER_DATABASE initialization parameter to TRUE:
      
      SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;
      
      Restart the database:
      
      SQL> SHUTDOWN;
      SQL> STARTUP;
      SQL> QUIT

   4. Check the log file for any errors. The log file is in the current directory and is named: vcomp_<sid>_<timestamp>.log
   5. If any invalid objects were reported, run the utlrp.sql script as follows:

      cd $ORACLE_HOME/rdbms/admin
      sqlplus /nolog
      SQL> CONNECT / AS SYSDBA
      SQL> @utlrp.sql

      Then, manually recompile any invalid objects. For example:

      SQL> alter package schemaname.packagename compile;

Reading time: < 1 minute

rem session.sql - displays all connected sessions

set echo off;

set termout on;

set linesize 80;

set pagesize 60;

set newpage 0;

select

AA rpad(c.name||':',11)||rpad(' current logons='||

AA (to_number(b.sessions_current)),20)||'cumulative logons='||

AA rpad(substr(a.value,1,10),10)||'highwater mark='||

AA b.sessions_highwater Information

from

AA v$sysstat a,

AA v$license b,

AA v$database c

where

AA a.name = 'logons cumulative'

;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;

select 'Sessions on database '||substr(name,1,8) from v$database;

set heading on;

select

AAAAAA substr(a.spid,1,9) pid,

AAAAAA substr(b.sid,1,5) sid,

AAAAAA substr(b.serial#,1,5) ser#,

AAAAAA substr(b.machine,1,6) box,

AAAAAA substr(b.username,1,10) username,

--AAAAAA b.server,

AAAAAA substr(b.osuser,1,8) os_user,

AAAAAA substr(b.program,1,30) program

from v$session b, v$process a

where

b.paddr = a.addr

and type='USER'

order by spid;

ttitle off;

spool off;

[ad#ad-1]

Reading time: < 1 minute

Please compile the following program and run it to check whether OS is releasing any shared memory for application or not.

=================================================================

#include <sys/types.h>
#include <sys/ipc.h>
#include <sys/shm.h>
#include <errno.h>
int main()
{
int shmid,size;
void *buf=0;
extern int errno;
/* Change it to size of shared memory you want (in MB) */
size = 200;
if ((shmid=shmget(rand()%10,1048576*size,0666|IPC_CREAT))==-1)
{
printf("Error during shmget, OS Error %d\n",errno);
exit(1);
}
if (((void *)shmat(shmid,(void *)0,0666))==0)
{
printf("Error during shmat, OS Error %d\n",errno);
exit(1);
}
shmctl(shmid,IPC_RMID,buf);
printf("Succesfully got, attached a %dM shared memory segment\n",size);
}

=================================================================

$cc -o <output filename>A <program.c>