Oracle and AMM /dev/shm

###############################################
#
# What is /dev/shm
# http://blog.oracle48.nl/oracle-11g-amm-memory_target-memory_max_target-and-dev_shm/
#
###############################################
for k in y
do
MV=0
echo -e "\n"
# for x in `ps -ef | grep smon | grep -Ev 'ASM|grep|bin' | cut -f3 -d "_"`
for x in `ps -ef | grep smon | grep -Ev 'grep|bin' | cut -f3 -d "_"`
do
MC=0
for i in `ls -al /dev/shm | grep ${x} | awk '{print $5}'`
do
MC=`expr ${MC} + ${i}`
done
echo "Checking memory for: ${x} - ${MC}"
A=`echo "scale=2;"\`echo ${MC}\`/1048576/1024 | bc -l`
echo -e "\t${A}G"
MV=`expr ${MC} + ${MV}`
done
echo "Total usage: "
B=`echo "scale=2;"\`echo ${MV}\`/1048576/1024 | bc -l`
echo -e "\t${B}G"
# echo "Total usage: ${MV}"
df -h /dev/shm
echo -e "\n"
done

Empty a schema(s) without dropping

#############################################
#
# Empty schema(s) script generation script
#
#
#############################################

# Check listof users on the database
sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select ''''||username||''','
 from dba_users
where username NOT in
('ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBA_TOOLS','DBSNMP','DIP','DMSYS', 'ESMDBA','ESMDBA1','EXFSYS','HGIOSD','JPMCEPV','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','OPS$ORACLE','ORACLE', 'ORACLE_COM','ORDDATA','ORDPLUGINS','ORDSYS','OSDAUD','OUTLN','SCOTT','SECURITY','SI_INFORMTN_SCHEMA','SRDF_DBA', 'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')
order by 1
/
END

# Generate the drop commands
for z in SED
do
echo -e "Enter list of names as in 'TEST','ONE','TWO'  :"
read USERS

for i in 1
do
echo -e "
spool sed_user_drop.log

select object_type,count(*) from dba_objects where owner in (${USERS});

"

echo -e "
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,object_name, object_type
                  FROM   dba_objects
                  WHERE owner in (${USERS}) and  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE','SYNONYM','TYPE','TYPE BODY'))
LOOP
    BEGIN
     IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || ' CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || '';
      END IF;

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"');
    END;
  END LOOP;
END;
/



BEGIN
  FOR cur_rec IN (SELECT owner,object_name, object_type
                  FROM   dba_objects
                  WHERE owner in (${USERS}) and  object_type IN ('MATERIALIZED VIEW'))
LOOP
    BEGIN
     IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || ' CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type ||' ' || cur_rec.owner||'.' || cur_rec.object_name || '';
      END IF;

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' \"' || cur_rec.object_name || '\"');
    END;
  END LOOP;
END;
/


prompt If required purge recycle_bin

purge dba_recycle_bin;

select object_type,count(*) from dba_objects where owner in (${USERS});

prompt confirm that all objects are dropped apart from DB LINK

"
done > sed_drop_user.info
echo -e "\nCheck the details in: sed_drop_user.info
done

Exclude Schema when exporting full database

######################
#
# Full export data pump excluding 'Oracle Schemas'

# To export whole database except one or more schemas
#
######################


for i in 1
do
PWD=`pwd`
DPAREX=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select DIRECTORY_NAME
 from dba_directories
where DIRECTORY_PATH like '${PWD}%'
/
END`
echo -e "The datapump directory is called: = \t${DPARE}\n\n"
SCHEMLIST=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select ''''||username||''','
 from dba_users
where username in
('ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBA_TOOLS','DBSNMP','DIP','DMSYS', \\  'ESMDBA','ESMDBA1','EXFSYS','HGIOSD','JPMCEPV','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','OPS$ORACLE','ORACLE', \\
'ORACLE_COM','ORDDATA','ORDPLUGINS','ORDSYS','OSDAUD','OUTLN','SCOTT','SECURITY','SI_INFORMTN_SCHEMA','SRDF_DBA', \\
'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')
order by 1
/
END`
SL=`echo $SCHEMLIST | sed 's/ //g' | sed 's/,$//g'`
if [ -z ${DPAREX} ]
  then echo -e "Incorrect directory - change\n\tABORTING!!!"
else
  echo "Correct directory"
  DPARE=`echo ${DPAREX}`
  export DPARE
  echo ${DPARE}
  export ORACLE_OWNER=${LOGNAME}
  # DT=`date '+%Y%d%m%H%M'`
  DT=`date '+%Y%m%d_%H%M'`
  # FBDT=`date "+\\"TO_TIMESTAMP\(\'%d/%m/%Y %H:%M:%S\',\'DD-MM-YYYY HH24:MI:SS\'\)\\""`
  FBDT=`date "+\\"TO_TIMESTAMP\(\'%d/%m/%Y %H:%M:%S\',\'DD-MM-YYYY HH24:MI:SS\'\)"`
  #
  # Test Script
  #
  echo "nohup expdp \"/\" \\
  directory=${DPARE} \\
  dumpfile=expdp${SCHEMA}_${DT}_%U.dmp \\
  logfile=expdp${SCHEMA}_${DT}.log \\
  JOB_NAME=EXP_${SCHEMA}_${DT} \\
  COMPRESSION=ALL \\
  FLASHBACK_TIME="\\"${FBDT}"\\"\" \\
  PARALLEL=15 \\
  ESTIMATE_ONLY=Y \\
  FULL=Y \\
  EXCLUDE=SCHEMA:\"IN\(${SL}\)\" &"
  #
  # If schema list too long, then break using backslashes
  echo -e "\n\n"
fi
done