Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
Showing results for 
Search instead for 
Did you mean: 

Export partial data

Occasional Advisor

Export partial data


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..
Create table abc as Select * from mytable where
date between <1-dec-2004> and <31-dec-2004>....

2) Create a view

Please help.

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


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.


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.

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
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.

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


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

exp80 tables=schemaname.tablename:partitionname ...

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

Re: Export partial data


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.