Abnormal growth of the temporary segment

Happened that the overnight, the TEMP temporary segment of the Oracle database, when running a loading process, the TEMP temporary segment grew to its maximum size without apparent reason.

This problem was solved by running the ANALYZE tables, it appears that the the execution plan (2 days before the ANALYZE was run)  depure and data load the some tables was the result of the execution plan was not updated.

EXPORT by Pipe file

#!/bin/ksh
######################################################################
# PROGRAM NAME: xport_ux
# PURPOSE: Performs export of the database
# Compresses the export file on the fly while gziped.
#
# USAGE: xport_ux SID OWNER
######################################################################
CURRENT_DATE=`date "+%d%m%y-%H:%M"`
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_verify(): Verify that database is online
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_verify(){
STATUS=`ps -fu ${ORA_OWNER} |grep -v grep| grep ora_pmon_${ORA_SID}`
funct_chk_unix_command_status "Database is down for given SID($ORA_SID),Owner($ORA_OWNER). Can't perform export "
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_cleanup(): Cleanup interim files
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_cleanup() {
rm -f $PIPE_DEVICE
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_splitcompress_pipe(): Creates pipe for compressing of file
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_splitcompress_pipe() {
# Creates pipe for compressing
if [ ! -r ${PIPE_DEVICE} ]; then
/etc/mknod ${PIPE_DEVICE} p
fi

nohup gzip < ${PIPE_DEVICE} > ${ZFILE} &
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_build_parfile(): Creates parameter file
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_build_parfile() {
# This line makes sure that it always creates a new parameter file
echo " " >${PARFILE}
echo "userid="\"/ as sysdba\""">>${PARFILE}
echo "Full=Y">>${PARFILE}
#echo "tables=scott.t1">>${PARFILE}
#echo "owner=scott">>${PARFILE}
#echo "Grants=Y">>${PARFILE}
#echo "Indexes=Y">>${PARFILE}
#echo "Rows=Y">>${PARFILE}
#echo "Constraints=Y">>${PARFILE}
#echo "Compress=N">>${PARFILE}
echo "Consistent=Y">>${PARFILE}
echo "File=${PIPE_DEVICE}">>${PARFILE}
echo "Direct=Y">>${PARFILE}
echo "Statistics=none">>${PARFILE}
echo "Log=${EXPORT_DIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log">>${PARFILE}
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_export(): Export the database
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_export() {
# Remove old export file
rm -f ${ZFILE}

${ORACLE_HOME}/bin/exp parfile=${PARFILE}
if [ $? != 0 ]; then
echo `date` >> $LOGDIR/fullexp_${ORA_SID}_${CURRENT_DATE}.log
echo "EXPORT_FAIL: ${ORA_SID}, Export Failed" >> $LOGDIR/fullexp_${ORA_SID}_${CURRENT_DATE}.log
funct_cleanup
exit 1
fi
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_parm(): Check for input parameters
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_chk_parm() {
if [ ${NARG} -ne 2 ]; then
echo "EXPORT_FAIL: ${ORA_SID}, Not enough arguments passed"
exit 1
fi
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_bkup_dir(): Create backup directories if not already existing
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_chk_bkup_dir() {
EXPORT_DIR=${BACKUPDIR}
if [ ! -d ${EXPORT_DIR} ]; then mkdir -p ${EXPORT_DIR}; fi
if [ ! -d ${LOGDIR} ]; then mkdir -p ${LOGDIR}; fi
ZFILE="${EXPORT_DIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.dmp.gz"
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_get_vars(): Get environment variables
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_get_vars(){
ORA_HOME=`sed /#/d ${ORATABDIR}|grep -i ${ORA_SID}|nawk -F ":" '{print $2}'`
ORA_BASE=`echo ${ORA_HOME}|nawk -F "/" '{for (i=2; i<=NF-2; i++) print "/"$i}'` ORACLE_BASE=`echo $ORA_BASE|tr -d " "` ORACLE_HOME=${ORA_HOME}; export ORACLE_HOME ORACLE_SID=${ORA_SID}; export ORACLE_SID } #::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: # funct_chk_unix_command_status(): Check exit status of Unix command #:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: funct_chk_unix_command_status() { if [ $? != 0 ]; then echo "`date`" >> ${LOGDIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log
echo "EXPORT_FAIL: ${1} " >> ${LOGDIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log
exit 1
fi
}

#######################################
# MAIN
#######################################
NARG=$#
ORA_SID=$1
ORA_OWNER=$2

# Set up environment
BACKUPDIR="/backups"
ORATABDIR=/var/opt/oracle/oratab
TOOLS="/u01/oracomn/admin/my_dba"

DYN_DIR="${TOOLS}/DYN_FILES"
PARFILE="/oracle/oracle9i/admin/scripts/export_${ORA_SID}.par"
LOGDIR="/backups/"

PIPE_DEVICE="/backups/${ORA_SID}_pipe"

echo "... Now exporting .... ${ORA_SID}"

funct_chk_parm
funct_get_vars
funct_verify
funct_chk_bkup_dir
funct_splitcompress_pipe
funct_build_parfile
funct_export
funct_cleanup

echo `date` >> $LOGDIR/${ORA_SID}_${CURRENT_DATE}.log
echo "${ORA_SID}, export completed successfully" >> $LOGDIR/${ORA_SID}_${CURRENT_DATE}.log

####################### END MAIN ###############################

Check dmp file generated

To check if a backup made ​​with the export utility is reliable we can use the
INDEXFILE parameter, which generates a file with the definitions of tables and indexes in a file. Do not worry, this parameter is not "load" information to the database:
Once done the backup with the export utility, we can test it with the import command.

imp system/passwd file=expfull.dmp log=testdmp.log fromuser=fulano touser=fulano tables=tablita indexfile=archivodat.txt








This way we can verify that the file expfull.dmp is in good condition.

The file contains the definitions archivodat.txt creating Tables and indexes.

The INDEXFILE parameter can only be used with FULL = Y, FROMUSER,TOUSER, TABLES parameters.

Reorganizing tables in Oracle

I show the details to move tables to other tablespaces.
The steps are as follows:

1 .- We need to know who is the object you want to move, so that we make the following script:

SELECT
a.owner "OWNER",
a.segment_name "TABLE",
b.tablespace_name "TABLESPACE",
round(NVL(SUM(a.bytes),0)/(1024*1024),2) "SIZE_MB",
SUM(a.extents) "EXTENTS"
FROM dba_segments a, dba_tables b
WHERE a.segment_name = b.table_name
AND a.segment_type = 'TABLE'
AND a.owner='FULANO'
GROUP BY a.owner, a.segment_name, b.tablespace_name ORDER BY 4 DESC

2 .- Once we have the objects, we know the size and that tablespace is hosted.

3 .- We do the same for those indexes with following script:
SELECT
a.owner "OWNER",
a.segment_name "INDEX",
b.tablespace_name "TABLESPACE",
round(NVL(SUM(a.bytes),0)/(1024*1024),2) "SIZE_MB",
SUM(a.extents) "EXTENTS"
FROM dba_segments a, dba_indexes b
WHERE a.segment_name = b.index_name
AND a.segment_type = 'INDEX'
AND b.owner='FULANO'
GROUP BY a.owner, a.segment_name, b.tablespace_name ORDER BY 4 DESC

4 .- Move the tables to another tablespace

SELECT
'alter table '|| a.owner || '.' || a.segment_name ||' move tablespace NWTBS;'
FROM dba_segments a, dba_tables b
WHERE a.segment_name = b.table_name
AND a.segment_type = 'TABLE'
AND a.owner IN ('FULANO','SUTANO')
group by a.owner,a.segment_name;




5 .- Move indexes

SELECT
'alter index '|| a.owner || '.' || a.segment_name ||' rebuild tablespace INDICE;'
FROM dba_segments a, dba_indexes b
WHERE a.segment_name = b.index_name
AND a.segment_type = 'INDEX'
AND a.owner IN ('FULANO','SUTANO')
group by a.owner,a.segment_name,b.tablespace_name