Export partial data

[jilpangs]

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

[Rick Garland]

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

We are just lowly Sys Admins here.

[Jean-Luc Oudart]

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 ?

[Brian Crabtree]

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]

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

[Yogeeraj_1]

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

[Thierry Poels_1]

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.

[B. Hulst]

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