Re: data pump
- From: Ryan January <rjjanuary@xxxxxxxxxxxxxxxx>
- To: jose.soares@xxxxxxxxxxxxxx
- Date: Fri, 05 Apr 2013 10:03:37 -0500
I'm not certain of what platform you're on, however you may be running
into a case sensitivity issue with your environment variables. For
example, on Linux it appears only the upper case NLS_DATE_FORMAT env
variable is utilized.
[rjjanuary@oracle1 log]$ echo $NLS_DATE_FORMAT
[rjjanuary@oracle1 log]$ echo $nls_date_format
[rjjanuary@oracle1 log]$ export nls_date_format='yyyymmdd hh24:mi:ss'
[rjjanuary@oracle1 log]$ echo $nls_date_format
yyyymmdd hh24:mi:ss
[rjjanuary@oracle1 log]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Apr 5 09:55:54 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL> select sysdate from dual;
SYSDATE
------------------
05-APR-13
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.1.0.7.0 - 64bit Production
[rjjanuary@oracle1 log]$ export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss'
[rjjanuary@oracle1 log]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Apr 5 09:56:27 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL> select sysdate from dual;
SYSDATE
-----------------
20130405 09:56:35
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.1.0.7.0 - 64bit Production
[rjjanuary@oracle1 log]$
On 04/05/2013 09:48 AM, jo wrote:
> SQL*Load is great, it uploads an entire database in a few minutes.
> Faster than Postgres.
> But I'm yet fighting with dates style.
> I need to insert dates with iso style yyyy-mm-dd but oracle wants it in
> the format dd-mon-yy
>
> I tried to set environment
> export set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF'
> export set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FFTZD'
> export set nls_date_format='YYYY-MM-DD'
> without success
>
> in sql*plus I can do:
> alter session set nls_timestamp_format = "YYYY-MM-DD HH24:MI:SS.FF";
> alter session set nls_timestamp_tz_format = "YYYY-MM-DD HH24:MI:SS.FFTZD";
> alter session set nls_date_format = "YYYY-MM-DD";
>
> but I don't know how to change date style in sql*load
>
> any hint?
>
> j
>
>
>
>
>
>
> rjamya wrote:
>> Oh yes, as Nigel said, if you create a proper delimited file, you can
>> create independent sqlloader control files, you can fire them off in
>> parallel. Much much better than individual insert statements. Use direct
>> path option and load even faster.
>> If you want to add data transformation while loading raw data? see if you
>> can use external table interface. For large files, split them (i use unix
>> split command), and then use parallel option.
>>
>> If you are loading into an empty db, it might make sense to drop indexes,
>> disable constraints and after load add indexes and re-enable constraints.
>> and dont forget stats :)
>>
>> Raj
>>
>>
>> On Fri, Apr 5, 2013 at 2:42 AM, jo <jose.soares@xxxxxxxxxxxxxx> wrote:
>>
>>
>>> my real problem is to upload a database from postgres into Oracle, because
>>> it takes about 11 hours (using cx_Oracle)
>>>
>>>
>>
>>
>>> It takes about 11 hours to upload into Oracle an INSERTs file format.
>>>
>>> Is there a faster way to upload a db into Oracle from a text file ?
>>> something similar to postgres' COPY...
>>>
>>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
