cancel
Showing results for 
Search instead for 
Did you mean: 

Export partial data

jilpangs
Occasional Advisor

Export partial data

Hi,

How to export partial data of a table using EXP80.
I need to export only data between '01-DEC-2004'
and '31-DEC-2004'.

Seems EXP80 doesnt support "query" option

exp80 query=\"where as_of_date \>\=\'01-DEC-2004\' and as_of_date\<\=\'31-DEC-2004\' \" tables=MYTABLE admin/manager@emps

I DONT want to do the following for official constraints/policies..
1)
Create table abc as Select * from mytable where
date between <1-dec-2004> and <31-dec-2004>....

2) Create a view

Please help.

Thanks
Raj
7 REPLIES
Rick Garland
Honored Contributor

Re: Export partial data

Don't you want to direct this question to DBAs?

We are just lowly Sys Admins here.
Jean-Luc Oudart
Honored Contributor

Re: Export partial data

Hi

export cannot be used to export a subset of a table, unless you're talking exporting a partition.

To solve this you could run a query to dump the data into a flat file and then upload into a different database with SQlloader.

Regards
Jean-Luc

PS : by the way where did you see the "query" option ?
fiat lux
Brian Crabtree
Honored Contributor

Re: Export partial data

As far as I know, EXP80 was the 8.0 version of Oracle for Windows. The 'query' option wasn't put in until 8i. The only things that I can suggest would be a view or a snapshot.

Brian
Indira Aramandla
Honored Contributor

Re: Export partial data

Hi Raj,

As brian mentioned, the "QUERY" option to the export utility is an Oracle8i feature. It's introduced form 8.1.5 and higher release version.

If you do not have these higher version, then there are few ways around.

1. To select into a temporary table or view which you do not want to do.
2. You could use a database link if you were intending to insert this data into another table.
3. You can select the data for the condition and output into a flat file
4. Use TOAD a free trial version and export the data with the condition and this will create a file with insert statements.


Indira A
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: Export partial data

hi raj,

as mentioned above you cannot use the QUERY option to export tables unless you are using 8i and above.

type: exp80 help=yes

to get a list of supported option.

bad luck.

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Thierry Poels_1
Honored Contributor

Re: Export partial data

hi,

you can export partitions! So if you (have) put every year in a separate partition you can:

exp80 tables=schemaname.tablename:partitionname ...

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
B. Hulst
Trusted Contributor

Re: Export partial data

Hi,

Create a temporary table and insert the data you want (1.1.2004 - 31.12.2004) into that table.

Then you can use export to export that temporary table.

Then you clean up the temp table and optionally the data in the source table with date 1.1.2004-31.12.2004.

Regards,
Bob