Actually, in 8i you can use a query to get only the rows you need in an
export. See this section of the 8i Utilities manual:
QUERY
Default: none
This parameter allows you to select a subset of rows from a set of
tables when doing a table mode export. The value of the query parameter
is a string that contains a WHERE clause for a SQL SELECT statement that
will be applied to all tables (or table partitions) listed in the TABLE
parameter.
For example, if user SCOTT wants to export only those employees whose
job title is SALESMAN and whose salary is greater than 1600, he could do
the following (note that this example is UNIX-based):
exp scott/tiger tables=emp query=\"where job=\'SALESMAN\' and
sal\<1600\"
Note: Because the value of the QUERY parameter contains blanks, most
operating systems require that the entire strings where job=\'SALESMAN\'
and sal\<1600 be placed in double quotation marks or marked as a literal
by some method. Operating system reserved characters also need to be
preceded by an escape character. See your operating system-specific
documentation for information about special and reserved characters on
your system.
When executing this command, Export builds a SQL SELECT statement
similar to this:
SELECT * FROM EMP where job='SALESMAN' and sal <1600;
The QUERY is applied to all tables (or table partitions) listed in the
TABLE parameter. For example, the following statement will unload rows
in both EMP and BONUS that match the query:
exp scott/tiger tables=emp,bonus query=\"where job=\'SALESMAN\' and
sal\<1600\"
Again, the SQL statements that Export executes are similar to these:
SELECT * FROM EMP where where job='SALESMAN' and sal <1600;
SELECT * FROM BONUS where where job='SALESMAN' and sal <1600;
If a table is missing the columns specified in the QUERY clause, an
error message will be produced and no rows will be exported for the
offending table.
Restrictions
The parameter QUERY cannot be specified for full, user, or transportable
tablespace mode exports.
The parameter QUERY must be applicable to all specified tables.
The parameter QUERY cannot be specified in a direct path export
(DIRECT=Y)
The parameter QUERY cannot be specified for tables with inner nested
tables.
You will not be able to determine from the contents of the export file
whether the data is the result of a QUERY export.
-----Original Message-----
From: Stacy Gaddy
[mailto:oracledba-ezmlmshield-x84065004.[Email address protected]
Sent: Wednesday, August 31, 2005 8:31 AM
To: LazyDBA Discussion
Subject: Re: Export Help
Or....just export the entire schema, import into second database, then
delete the rows you dont need.
Stacy
On Wednesday 31 August 2005 6:52 am, Bhat Vikas wrote:
> Hi Pradhan,
>
> AFAIK, you cannot use where filter in individual data level in export.
> Maybe Sql loader is a better option for you. But then for that you
> will need to create 100 control files and that I think wont be a good
idea.
>
> Another option that I can think of is this.
>
> 1. Create views for each of those tables satisfying the condition.
> This can be achieved by using a plsql block.
> 2. Export those views to the target schema and recreate tables based
> on those views alongwith the constraints required.
>
> I know it is a tedious process, but given your requirement I do not
> see any other way out.
>
> Vikas Bhat
> Senior Software Engineer,
> FBSI Pvt Ltd.,
> Embassy Golf Links Business Park,
> Intermediate Ring Road,
> Bangalore - 560071
> Email - vikas.[Email address protected]
> Ph:91-80-56916482
> Mob:91-9845589149
> Those who aspire for the light of sun; need to bear its heat.
>
> Any comments or statements made in this email are not necessarily
> those of Fidelity Business Services India Pvt. Ltd. or any of the
> Fidelity Investments group companies. The information transmitted is
> intended only for the person or entity to which it is addressed and
> may contain confidential and/or privileged material. If you have
> received this in error, please contact the sender and delete the
> material from any computer. All e-mails sent from or to Fidelity
> Business Services India Pvt. Ltd. may be subject to our monitoring
procedures.
>
>
>
> -----Original Message-----
> From: Pradhan Prakash
> [mailto:oracledba-ezmlmshield-x37260149.[Email address protected]
> Sent: Wednesday, August 31, 2005 4:10 PM
> To: LazyDBA Discussion
> Subject: Export Help
>
>
> Hi Folks
>
> Need some help on Oracle export - i am using Oracle 8.1.5 I am trying
> to export about 100 tables out of total 300 tables in a schema,
> depending on some of the values in the tables :
> for example : all the 100 tables have empno as a column and i want to
> export all 100 tables using a where clause saying where empno = '001'.
> Can i export using where clause ? Can i put this condition in a PAR
> file
>
> and then export ?
>
> Please help..
>
> Regards,
> Pradhan
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
> see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com By using this list you agree
> to these terms:http://www.lazydba.com/legal.html
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
> see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com By using this list you agree
> to these terms:http://www.lazydba.com/legal.html
--
Stacy Gaddy
W-(919)321-0855
C-(910)376-1256
stacy.[Email address protected]
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page