Export/Import/SQL Loader :: Converting Date Format Using RTRIM
Apr 22, 2013
I need loading date formats using sqlldr. I am trying to load some data from another source into our Oracle database, however, the "date" field includes extra characters that i'm trying to deal with. For reference, I'm running Oracle 11.2 on Linux 5.7.
First, the actual date input is:
April 18, 2013 8:50:44 AM EDTI tried to input it a few ways but was unable to load it until I physically removed the "AM EDT" from the input string.
My first question is if there is a way to RTRIM and get rid of those extra characters. Either that, or I need a way to convert the "AM EDT" into our standard date format.
My control file has the following:
LASTCHANGEDAT date "Month dd, YYYY HH MI SS",This will load the data, but only if I strip out the "AM EDT" from the actual data input which is not feasible.
Also, how would I handle date that comes in as: April 18, 2013 3:50:44 PM EDT
Also, I tried the following, but it gives me a different error:
LASTCHANGEDAT "to_date(:LASTCHANGEDAT,'DD-MON-YYYY HH24:MI:SS')",
gives me error:
Record 1: Rejected - Error on table NTWKREP.INTERFACE, column LASTCHANGEDAT.
ORA-01858: a non-numeric character was found where a numeric was expected
View 5 Replies
ADVERTISEMENT
Jan 28, 2013
I'm studying abt SQL*Loader. All I've learn it needs to have:
1. One text input file
2.Control file
3.Bad file...
But I'm confused where to put the input file...where to put the control file in which format and in control file what should I write...
My oracle version is:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
View 3 Replies
View Related
Aug 7, 2012
1) Is there a way to skip database jobs while exporting (EXPDP) ?
2) Is there a way to skip database jobs while importing (IMPDP) ?
View 3 Replies
View Related
Aug 9, 2012
Why do export-import require temporary tablespace? Since export-import do behave like DMLs, when does temporary tablespace be needed by datapump utility?
View 2 Replies
View Related
Apr 4, 2012
select to_date('31-12-2012','DD-MM-RRRR') from dual;
OUT PUT : '12/31/2012'
is't possible to show the date format like
'31-12-2012'
without converting to char
View 8 Replies
View Related
Mar 18, 2013
I have a flat file (student.dat delimiter %~| ) using control file (student.ctl) through sql loader. Here are the details.
student.dat
student_id, student_firstname, gender, student_lastName, student_newId
101%~|abc%~|F %~|xyz%~|110%~|
Corresponding table
Student (
Student_ID,
Student_FN,
Gender,
Student_LN
)
How do i map student_newId field to student_id field in STUDENT DB table so that new id should be inserted in student_id column. How do i specify the mapping in control file. I dont want to create a new column in student table. In control file i will specify the below, Is this a best approach?. Do we have any othe way?
STUDENT_ID *(:STUDENT_NEWID)*,
STUDENT_FN,
GENDER,
STUDENT_LNAME,
STUDENT_NEWID BOUNDFILLER
View 1 Replies
View Related
Jun 29, 2011
I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>
View 11 Replies
View Related
Jun 22, 2012
MY DB Version: 10.2.o
OS: Windows Server 2003
I am trying to import on table which i have the export dump file which i take using expdp previously when i load that table on the same host
by using below command:
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
after that i zip that dump and move it to external usb and now i need that table i copy that table and unzip that that dump
Command i am using to do the import is :
impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
But the query of import is still runing even not showing any amount of rows to be imported.
i already make the tablespace in which the table was previosuly before dropping but when i check the sapce of tablespace that is also not consuming one error i got preiviously while performing this task is:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "CDR"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
Starting "CDR"."SYS_IMPORT_TABLE_03": cdr/********@tsiindia directory=TEST_DIR dumpfile=CAT_IN_DATA_042012.DMP tables=CAT_IN_DATA_042012 logfile=impdpCAT_IN_DATA_042012.log
[code]....
i check streams_pool_size it will show zero and then i make it to 48M and after that
SQL> show parameter streams_pool_size;
NAME TYPE VALUE
-----------
streams_pool_size big integer 48M
But still it takes time
View 13 Replies
View Related
Sep 29, 2012
My database version
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionOS version:
Windows 7 64bit I have schema(scott) export with schema level option and imported with different name as (scott1).At regular period of time i need to import the scott to scott1 without affecting existing records.such as
*1. Need to append new created records.*
*2. Need to append updated records.*
for the above requirement I did in the following way
expdp xxxx/******** schemas=SCOTT directory=dumpdir dumpfile=SCOTT_28-SEP-2012.dmp logfile=exp_SCOTT_28-SEP-2012.log imported in the following way impdp xxxx/******** AS SYSDBA REMAP_SCHEMA=SCOTT:SCOTT1 directory=DUMPDIR dumpfile=SCOTT_28-SEP-2012.dmp logfile=imp_SCOTT2_28-09-2012.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=APPEND.
The problem is i couldn'table to append the records to existing tables the log error show such ways.
ORA-31684: Object type USER:"SCOTT1" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
[code].....
View 5 Replies
View Related
May 10, 2013
I am trying to run impdp over network to import tables only but i am getting an error saying not able to create metadata and the import fails
Here are the steps below,
1. Source database created a user and granted select on certain tables to the user.
2. Created a user in the Target database.
3. Created a public link as sys user in the target database.
4. granted imp and exp full database to both users and all the other privs.
5. Started the impdp from the target server.
The import fails with
$impdp abc/xyz directory=DATA_PUMP_DIR network_link=TESTAR logfile=net_import_proddev.log TABLES=impdb.abc parallel=12 REMAP_SCHEMA=IMPDB:ABC
Import: Release 11.2.0.3.0 - Production on Tue Apr 23 13:10:51 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "IMPDB"."SYS_IMPORT_TABLE_01": abc/******** directory=DATA_PUMP_DIR network_link=TESTAR logfile=net_import_proddev.log TABLES=impdb.abc parallel=12 REMAP_SCHEMA=IMPDB:ABC
[code]....
View 14 Replies
View Related
Jul 9, 2013
aix 6.111.2.0.3 I have an expdp dump from prod to be imported to our test database.I have imported it using impdp, but to my surprise the tables were imported but lots of indexes were not created? even If I have used TRANSFORM=SEGMENT_ATTRIBUTES:N just to use the default USERS tablespace. How do I import the indexes separately, skipping the tables and other objects?
View 14 Replies
View Related
Jul 2, 2012
How to import data from excel(.xls) file to data base table
I have excel sheet(.xls) data details, I neet to upload details to data base table using procedure
excel sheet is not CSV file, so SQL Loader is not using
any alternative solution for this issue
View 3 Replies
View Related
Aug 16, 2013
I am using expdp command to export the table by specifying Query parameter. But i am unable to export the table based on the condition.
Ex:EXPDP username/password dumpfile=employee.dmp logfile=emp.log directory=DATADIR_EXP TABLES=EMPLOYEE query=EMPLOYEE:"UPDATED_TIME >= '04-JUN-13' AND UPDATED_TIME >= '05-AUG-13'" Estimate in progress using BLOCKS method...Processing object type TABLE_ EXPORT/ TABLE/ TABLE_ DATATotal estimation using BLOCKS method: 3 GBORA-31693: Table data object "<username>"."EMPLOYEE" failed to load/unload and is being skipped due to error:ORA-00933: SQL command not properly endedMaster table "<username>"."EMPLOYEE" successfully loaded/unloaded...Dump file set for <username>.SYS_EXPORT_TABLE_01 is: E:IMPDPemployee.dmpJob "<username>"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 12:34:45 Oracle 11g,
View 6 Replies
View Related
May 11, 2013
I am trying to export selective data from one of my prod database tables. But not succeeding. I was keep on trying for the past 2 hours.
OS : SOLARIS SPARC
ORACLE - 10G
Query --> WHERE E3RECV_DT LIKE '201305%' (I need to export this query data)
Below Script i am using
===============
exp E3USER@SGEBAPU2 statistics=none consistent=n buffer=100000000 file=exp_pipe_file TABLES=IFDATA query="WHERE E3RECV_DT LIKE '201305\%'" log=PGTB_IFDATA_conditional.log
View 1 Replies
View Related
Nov 23, 2012
I had just successfully finished a full importing from Oracle 9i DB to Oracle 11gR2 DB. My export was a full db export.
Prior to this importing, my 11g was a newly created DB with the default SYS, System etc.. schema. Their passwords is different from those in 9i.
However, i realised that after importing... their passwords in 11g was replaced by those passwords in 9i, including SYS and SYSTEM user...
View 5 Replies
View Related
May 24, 2013
I want to import data in a csv file by SQL Loader.
but , I don't want to import some illegal rows when the column 'name' is null
how can I modify the SQL Loader ctrl file?
View 1 Replies
View Related
Sep 17, 2012
I try to transfer data from one database to another one through data pump via SQL Developer (data amount is quite important) exporting several tables. Tables export is doing fine, but I encounter the following error when I import the file (I try data only and data + DDL).
"Exception: ORA-39001: argument value invalid dbms_datapump.get_status(64...=
ORA-39001: argument value invalid
ORA-39000: ....
ORA-31619: ...
The file is in the right place, data pump folder of the new database. User is the same on both base, database version are similar.
View 4 Replies
View Related
Jul 11, 2012
I have schema level export for user SAMPLE1(Default tablespace USERS) on oracle 9.2.0.1 production database. I want to import into another 9i database on another server, so do i nneed to Create SAMPLE1 user and USERS tablespace in new database again.
View 5 Replies
View Related
Apr 9, 2013
let us consider mytest schema is having 6 tables
tname tabtype
myt table
myaxpertlog table
abb table
ccc table
ddd table
xxx table
now from this schema i want full dump and also from myaxpertlog table i required metadata only not records.
c:> export mytest/log file=20130409mytest0904pm.dmp tables=(myaxpertlog) rows=n
if i tried i am get only one table but it does have records.
View 6 Replies
View Related
Oct 11, 2012
I have one prod server (11.1.0.6.0) servers on Windows 2003 R2 64 bit.
Server Name (PRODDB)
I do not have access to that prod server , i want to take one export data pump from my client machine and due to space issue in prod server , i want to keep dump file in my client machine itself. i can take traditional export and keep the dump file in my client machine but i do not know how to achieve the same via data pump ...
How to generate dump file in client machine itself via data pump ?
View 18 Replies
View Related
Aug 2, 2012
I am using Oracle 10g Data Pump Export utility expdp. What I am trying to do is to export a single schema, except for a certain partition P in table T.
I have tried:
expdp user/pass@db dumpfile=... logfile=... exclude=table:" = 'T:P' "
It doesn't work. The whole table T gets exported.
Is there a way to exclude partitions from schema mode export?
If not, is there a way I can achieve the same with DBMS_DATAPUMP API?
View 2 Replies
View Related
Jun 29, 2012
export data program "ociuldr" can not run in 64bits win2008 environment. Where can i download 64 bit version of "ociuldr" program. I have read some article . The article mention that the export data program ociuldr.exe need to recompile into 64 bits version. Finally I also want to ask the import data program sql loader "sqlldr.exe". Can it run in 64 bits environment. Where can i downlaod 64 bits version of "sqlldr" program.
View 1 Replies
View Related
Dec 22, 2012
I am trying to migrate a table to a new table that has the field sequence changed and also has a new field added. My main question is if it is possible to have datapump add values to the new field in the target table.For example:
-original table has fields a, b, d, c
-new table has fields b, c, d, a, e
I want to load the new table and also include adding values for field e. In this case, field e is a year field, so it should be loaded with '2012'..Does datapump have the ability to do this? Is reorganizing the fields going to cause me any problems? We are on oracle version 11.2.0.3
View 7 Replies
View Related
Feb 15, 2013
When I do the import the of succeeding dump, I drop the existing schema "SQL> drop user username cascade;" and import dump by " impdp system .... ". I would like to import a dump to an existing instance but only data import and will leave the current packages and other metadata untouched and unchanged on the said existing instance.
1. Do i need to drop user before the import if my requirements are the above?
2. If i need to drop user, what should be script.
3. For the import itself, what parameter should i use?
4. What are the necessaries I need to consider before doing the import.
View 12 Replies
View Related
Dec 12, 2012
version:11.2.0.3
Platform : RHEL 5.4
Imagine you have 100 schemas backed up (expdp) in a dumpfile and you want to import just one schema from that dumpfile in a DB. You can specify just that one schema you want using SCHEMAS parameter in the impdp. But things are not straightforward when you want use REMAP_SCHEMA.
Here is my scenario:
===================
I took the expdp dump of schemas A and B in one go. So, dumpfile has objects from both A and B.The dumpfile name is : schemas_AandB.dmpNow , I want to create schema C from A using REMAP_SCHEMA parameter
-- Putting each parameter in a separate line for readability
impdp PSTREF/PSTREF_123
DIRECTORY=ADET_EFX_DIR
DUMPFILE=schemas_AandB.dmp
LOGFILE=CreatingCfromA-Impdp.log
REMAP_SCHEMA=A:CEverything goes fine. Schema C is created from Schema A in the dumpfile.
But impdp is trying to create schema B as well because schema B was present in the dumpfile. Since the schema B and its objects are already in the DB , I get the following errors.
ORA-31684: Object type USER:"B" already exists
ORA-31684: Object type PROCEDURE:"B"."SP_CLEAREXPIREDSESSIONDATA" already exists
ORA-31684: Object type PROCEDURE:"B"."SP_DELETESESSIONDATA" already exists
ORA-31684: Object type PROCEDURE:"B"."SP_DELETESTATECONTEXTINFO" already exists
[code]...
Trying to avoid schema B in the dumpfile from being imported by specifying SCHEMASBut I got the following error
ORA-39065: unexpected master process exception in MAIN
ORA-12801: error signaled in parallel query server PZ99, instance oracth214:HEWRAC1 (1)
ORA-01460: unimplemented or unreasonable conversion requestedMaybe REMAP_SCHEMA and SCHEMAS parameters won't work together.
Is there any way to prevent the impdp from importing user B and its objects ?
View 1 Replies
View Related
Jan 8, 2013
I want to export a table ( using exp or expdp ) from client machine. Dump file should be created to client machine.
Is this possible ? How to do this ?
View 3 Replies
View Related
Aug 2, 2012
I have a question related to Oracle Data Pump.
So, I want to export two schemas from database with condition:
1. I want to export scheme_1 with all metadata objects + data.
2. I want to export scheme_2 with only metadata objects.
Oracle version is Oracle EE 10.2.0.4.0, OS - Microsoft Server 2003R2.
As far as I know I can not use parameter EXCLUDE like:
EXCLUDE =TABLES:" IN ('SCHEMA_NAME.TABLE')"
(-but this parameter will give me no tables at all) or I can not use CONTENT=SCHEMA_NAME.METADATA_ONLY, maybe I can use QUERY=where table in (select tablename where schema is .... - but I have tables with same name in both schemas).
View 7 Replies
View Related
Mar 17, 2013
We need to export a big table into a text file with Column delimiter '&|' and row delimiter '$#'.
DB Version is 10.2.0.2, the table size is around 4 GB.
View 2 Replies
View Related
Feb 26, 2013
when i run this query i am facing date format error.
select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');
ORA-01830: date format picture ends before converting entire input string
View 1 Replies
View Related
Feb 21, 2010
I am using
SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;
its givinig right result for
1023411->One Million Twenty-Three Thousand Four Hundred Eleven
but if i will change it to 10234111 then this query giving an error
ORA-01830: date format picture ends before converting entire input string.
Is there any limitation or is there any other built in function to get the number to a word.
View 2 Replies
View Related