Data export/import

Toad for Oracle
1

I’m older and somewhat wiser now, fortunately at no great cost this time.

I needed to copy the data from a table in one db to a similarly structured table
in another db. A dblink would have been lovely, but the DBA does not allow them.
L So I exported the data to a pipe-delimited txt file. I believe the delimiter
is the only thing I changed from the default settings. Dates exported as
mm/dd/yyyy hh:mi:ss pm. The import wizard default date delimiter is –
(hyphen). With all my imports from Excel spreadsheets I had never touched it.
Seems it’s a bit more significant in the case of txt files. My 3/5/2010
4:02:01 PM dates loaded as 5/3/2020, no timestamp. March 5 vs. May 3, and no
typo, 2010 vs. 2020.

So in the future I shall be paying closer attention.

Is it too late to request that the defaults for txt exports match the defaults
for txt imports?

Thanks much,

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

image001.jpeg

2

Dan, would “copy data to another schema” have worked for you? Right click option
in Tables on the schema browser. It uses two open connections and doesn’t need a
DB Link. I find it a lot easier than exporting and importing for many tasks.

– jim
image001.jpeg

3

Umm… perhaps the obvious was overlooked?

Dates exported as mm/dd/yyyy hh:mi:ss pm

Curious question: what is the NLS Date Format in each of the databases, are they
the same or a different format?

The reason I ask is that your data file is text based, not Oracle internal date
value based… which, of course, is why you can read the dates in the file.

When Oracle is doing an import of text data into a date column and a format is
not explicitly identified, the format is implicitly applied from what Oracle
knows… such as NLS Date Format.

Is this perhaps responsible for the issue you ran into?

Roger S.
image001.jpeg

4

For now I must assume that they are the same. Built by the same DBA. Built for
the same purpose; one is supposed to be a test copy of the other.

How might I confirm that they are the same?

Thanks much,

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg

5

How might I confirm that they are the same?

Well… the simplest way I can think of would be to run the following
queries on both databases and observe the results.

Select sysdate from dual;

Select systimestamp from dual;

In my case, the results are:

XAD>select sysdate from dual;

SYSDATE

2010-03-10

XAD>select systimestamp from dual;

SYSTIMESTAMP

6

Our format differs from yours, but I get the same results in both DB’s.
Thanks.

3/10/2010 4:50:46.872820 PM -06:00

Next time I’ll try Brother Hudson’s Schema Copy idea. Always more to
learn.

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg