Thanks for the question, Rahul.
Asked: December 05, 2004 - 8:48 pm UTC
Last updated: December 07, 2004 - 10:02 am UTC
Version: 9.2
Viewed 10K+ times! This question is 
You Asked
Dear Tom,
I have a huge database of some terabytes. I am using oracle version 9.2 I need to export the data of this DB and make a dmp file out of it so that I can move the data from one machine to another.
In the table design, each and every table in the database has two
columns "updatedate" and "updatetime". I would like to get the dump file for all the tables and the records in the table should be selected as per the update date. For eg I want the dump of data which was updated since last one month.
How can I do this with the exp command? Or is there any other way out for this?
Thanks in advance.
Regards
Rahul
and Tom said...
Well, I don't get the "updatedate" and 'updatetime" concept. I hope you didn't create your own brand new "datatype" using two number fields (given that the oracle date holds both date and time)
I'll demonstrate using a single column -- but since 8i, export has supported a "query=" parameter. consider:
ops$tkyte@ORA10GR1> create table t1 ( x int, updatedate date );
Table created.
ops$tkyte@ORA10GR1> create table t2 ( x int, updatedate date );
Table created.
ops$tkyte@ORA10GR1> create table t3 ( x int, updatedate date );
Table created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t1 select rownum, sysdate-rownum from all_objects where rownum < 1000;
999 rows created.
ops$tkyte@ORA10GR1> insert into t2 select rownum, sysdate-rownum from all_objects where rownum < 1000;
999 rows created.
ops$tkyte@ORA10GR1> insert into t3 select rownum, sysdate-rownum from all_objects where rownum < 1000;
999 rows created.
ops$tkyte@ORA10GR1> commit;
Commit complete.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> host echo 'query="where updatedate >= add_months(sysdate,-1)"' > exp.par
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select count(*), count(case when updatedate >= add_months(sysdate,-1) then 1 end) cnt2
2 from t1;
COUNT(*) CNT2
---------- ----------
999 29
so, of the 999 records in there, 29 are "interesting", running export:
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> host exp userid=/ 'tables=(t1,t2,t3)' parfile=exp.par
Export: Release 10.1.0.3.0 - Production on Mon Dec 6 10:24:02 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T1 29 rows exported
. . exporting table T2 29 rows exported
. . exporting table T3 29 rows exported
Export terminated successfully without warnings.
we get just those rows..
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment