topic Re: Export partial data in Operating System - HP-UX

Export partial data

jilpangs — Thu, 10 Feb 2005 12:16:39 GMT

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

Re: Export partial data

Rick Garland — Thu, 10 Feb 2005 12:36:04 GMT

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

We are just lowly Sys Admins here.

Re: Export partial data

Jean-Luc Oudart — Thu, 10 Feb 2005 12:38:33 GMT

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 ?

Re: Export partial data

Brian Crabtree — Thu, 10 Feb 2005 12:46:04 GMT

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

Re: Export partial data

Indira Aramandla — Thu, 10 Feb 2005 20:22:59 GMT

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

Re: Export partial data

Yogeeraj_1 — Fri, 11 Feb 2005 01:00:41 GMT

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

Re: Export partial data

Thierry Poels_1 — Fri, 11 Feb 2005 02:09:25 GMT

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.

Re: Export partial data

B. Hulst — Sun, 13 Feb 2005 05:56:32 GMT

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