http://VietEnglish.com

http://Edgar411.com

http://ForumGrouper.com

http://hpricot.com

eventExportScript.html
10-19-2001 Bikle
-----------------

This page describes some of the thinking which went into the building
of the export script:

expEventB4July.sh


When we describe the EVENT table, we see the columns below:

18:26:04 SQL> desc event
 Name                                    Null?    Type
 --------------------------------------- -------- ---------------------------
 EVENT_TIME                                       DATE
 EVENT_TYPE                                       VARCHAR2(16)
 VDATA1                                           VARCHAR2(250)
 VDATA2                                           VARCHAR2(250)
 NDATA1                                           NUMBER
 NDATA2                                           NUMBER
 MEMBER_ID                                        NUMBER
 MEMBER_ID_REAL                                   NUMBER
 TAG                                              VARCHAR2(32)

18:26:07 SQL> 

According to the BillB e-mail we want to backup rows from EVENT where
EVENT_TIME < 01-JUL-01.

When we query export help we see the following:

bikle01-oracle-% exp help=y

Export: Release 8.1.6.0.0 - Production on Fri Oct 19 18:29:18 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.
Keyword  Description (Default)        Keyword      Description (Default)
--------------------------------------------------------------------------
USERID   username/password            FULL         export entire file (N)
BUFFER   size of data buffer          OWNER        list of owner usernames
FILE     output files (EXPDAT.DMP)    TABLES       list of table names
COMPRESS import into one extent (Y)   RECORDLENGTH length of IO record
GRANTS   export grants (Y)            INCTYPE      incremental export type
INDEXES  export indexes (Y)           RECORD       track incr. export (Y)
ROWS     export data rows (Y)         PARFILE      parameter filename
CONSTRAINTS export constraints (Y)    CONSISTENT   cross-table consistency
LOG      log file of screen output    STATISTICS   analyze objects (ESTIMATE)
DIRECT   direct path (N)              TRIGGERS     export triggers (Y)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
QUERY    select clause used to export a subset of a table
VOLSIZE  number of bytes to write to each tape volume

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TABLESPACES list of tablespaces to transport  

Export terminated successfully without warnings.
bikle01-oracle-%

The keyword we need to master in order to gain the functionality we
want is: QUERY.

The main idea behind the QUERY keyword is simple: just pretend the
export is a select statement and attach the WHERE clause to the
QUERY keyword on the exp command line.

What makes QUERY cumbersome to use is that exp requires the DBA to
quote many of the characters in the where clause with a "\".

So a simple WHERE clause which looks like this:

WHERE ename='MILLER'
OR    hiredate < 01-JAN-98
OR    empno >= 3344

Needs to look like this:

QUERY=\
\"\
WHERE ename\=\'MILLER\' \
OR    hiredate \< 01\-JAN\-98 \
OR    empno \>\= 3344 \
\"

Based on the above discussion we decide to make the QUERY clause of the
export script looke like this:

QUERY=\
\"\
 WHERE EVENT_TIME \< \'01\-JUL\-01\' \
\"