Expdp parameters Include and Query

1) Expdp  with Include.

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log include=TABLE:\"LIKE \'%2013%\'\"

or

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log
include=table:"in(select table_name from user_tables where table_name like 'ITEM_'||to_char(sysdate-1,'mmddyy')||'%')"

Export: Release 11.2.0.1.0 - Production on Tue Dec 31 05:42:19 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "RESEARCH"."SYS_EXPORT_SCHEMA_01":  research/******** directory=dp_dir dumpfile=tabs_research_31dec13.dmp log tabs_research_31dec13.log include=TABLE:"LIKE '%2013%'"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 30.57 GB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

2)  Expdp with Include and query

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log include=TABLE:\"LIKE \'%2013%\'\"  query="where bus_date between to_date('01-DEC-13 00:00:00','DD-MON-YY HH24:MI:SS') and to_date('31-DEC-13 00:00:00','DD-MON-YY HH24:MI:SS')"

Enabling Explain Plan at Session level

SQL>  set autotrace traceonly explain;
SQL>
SQL> select count(0) from tab;

Execution Plan
----------------------------------------------------------
Plan hash value: 1728992512

-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |     1 |    44 |   106   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE         |         |     1 |    44 |            |          |
|*  2 |   FILTER                |         |       |       |            |          |
|   3 |    NESTED LOOPS OUTER   |         |  1270 | 55880 |   106   (1)| 00:00:02 |
|*  4 |     HASH JOIN           |         |  1270 | 49530 |    31   (4)| 00:00:01 |
|   5 |      INDEX FULL SCAN    | I_USER2 |    93 |  2046 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN   | I_OBJ5  |  1270 | 21590 |    29   (0)| 00:00:01 |
|   7 |     TABLE ACCESS CLUSTER| TAB$    |     1 |     5 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN  | I_OBJ#  |     1 |       |     0   (0)| 00:00:01 |
|   9 |    NESTED LOOPS         |         |     1 |    29 |     2   (0)| 00:00:01 |
|* 10 |     INDEX SKIP SCAN     | I_USER2 |     1 |    20 |     1   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN    | I_OBJ4  |     1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
              "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11
              AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
              "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5) AND
              ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE
              ' OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curren
              t_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2"
              WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND
              "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."OWNER#"="U"."USER#")
   6 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."TYPE#">=2 AND
              "O"."LINKNAME" IS NULL AND "O"."TYPE#"<=5)
       filter("O"."TYPE#"<=5 AND "O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL)
   8 - access("O"."OBJ#"="T"."OBJ#"(+))
  10 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere
              nv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere
              nv','current_edition_id')))
  11 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
              "O2"."OWNER#"="U2"."USER#")

SQL>

Shell script to send alert for database status check

#! /bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export HOST_NAME=`hostname`
export MAIL_FILE=/home/oracle/dba_scripts/dbstatcheck.log
export MAIL_ID_LIST="database-team-vsoft@vsoftcorp.com"
export ALERT_LOG=/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
dbchk=`sqlplus -s / as sysdba <set feed off lines 1000 pages 200 tab off space 1 head off FLUSH OFF TRIMSPOOL ON echo off verify off termout off
select status from v\\$instance;
exit
EOF`
dbchk1=`echo $dbchk|sed s/^$//`
echo $dbchk1
if [ "$dbchk1" != "OPEN" ]
then
echo "database is not open"
echo "Sending recent errors in alert log : ${ALERT_LOG} " > $MAIL_FILE
echo "############################################################" >> $MAIL_FILE
grep -A 3 -B 3 "ORA-" ${ALERT_LOG}|tail -n 20 >> $MAIL_FILE

cat $MAIL_FILE|mail -s "### Severity High : DATABASE IS DOWN : ${HOST_NAME}:${ORACLE_SID} ###" "${MAIL_ID_LIST}"
fi