how i can export subsets of data in oracle?

[isonlyme]

Hi,

I'm trying to export a subset of data using a date field
using this:

exp user/pass file=flename.dmp log=filename.log tables=MY_TABLE query=\"where as_of_dt='08-31-2007'\"

and this is the error it gave me:

EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes
Export terminated successfully with warnings.

I was using the to_date function but it gave me errors also using the following syntax:
query=\"where as_of_dt=to_date('08-31-2007','mm\dd\yyyy')\"

error: invalid character '('

Any suggestions?

Thanks in advanced

 

[dbtoo2001]

I think you have to escape the ''s.

query=\"where as_of_dt=to_date(\'08-31-2007\',\'mm\dd\yyyy\')\

 

[SantaMufasa]

I'm curious, isonlyme, what is the target of your "exported subset of data"? Since you are performing an "export", the only place you could use the resulting dump file is in an Oracle "import". And if that is the case, I'm betting that there are better/faster/easier/kinder/gentler ways to achieve your objectives than export/import.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dbtoo2001]

You probably need to escape the banana's too.

query=\"where as_of_dt=to_date\(\'08-31-2007\',\'mm\dd\yyyy\'\)\

 

[isonlyme]

thank you for the response,

Im trying to backup just the End of month data of 8/31 instead of the whole table and then imported to oracle again. I lost some data and i need to do a restore that is old and doesnt have the 8/31 data, so i wanted to export out the data i need so after restoring the old data i just import the new data (the one i'm trying to export using the where clause 8/31/2007).

ill run your suggestion dbtoo2001 and let you know
Thanks in advanced

 

[isonlyme]

also, this table is partitioned if i extract a subset of data and imported back again will it be located in the correct partition?

 

[SantaMufasa]

isonlyme,

If you continue to have trouble with the export, then an alternative (which is probably faster, as well) is the following:

1) CREATE TABLE MY_TABLE_2 as
   SELECT * from MY_TABLE
    WHERE as_of_dt=to_date('08-31-2007','mm-dd-yyyy');

2) restore your old data.

3) INSERT INTO MY_TABLE SELECT * FROM MY_TABLE_2;

This will also ensure that correct partitioning occurs.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[isonlyme]

Thank you santamufasa, I tought of doing that but

This is a datawarehouse, where we don't backup using the export utility and then passing it on to tape.

this is done every once a week:

1- We shutdown the database
2- Backup the physical files of the database
3- startup the database

now my plan:

1- I'm restoring old data from July 6 from tape (I will have the database as it was after the EOM)

2- Export 1 table (wich has the complete data)

3- Restore from tape Friday's production backup (this will put my database as it was last friday with the table with missing data)

4- Import the dmp file from my previous restore that has the complete data. (this will correct the missing records.

5- update witht he currect month the table using the DMP file i did wich is only a subset of data (8/31/2007) to the table that I corrected.

Now my question is

This table is partitioned so running the imp with only 8/31/2007 data will put it back on their correct partitions and will not affect the rest of the data, correct?

what do you think?

 

[SantaMufasa]

what do you think?

Having never done it myself, I can only presume that Oracle will put data into correct partitions. My reasoning is, where else would Oracle put it?

One test is worth 100 expert opinions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[isonlyme]

well i let you know my results

 

[isonlyme]

well my plan worked the import with 8/31 was inserted inteir correct partitions and I also used the insert into solution for data that was in other tables.

thanks guys