Export/Import/SQL Loader :: ORA-39127 - Unexpected Error From Call To Export_string
Nov 3, 2013
I am trying to export a Schema from 11.2.0.2 and getting the below error on export ORA-39127: unexpected error from call to export_string
:=SYS.DBMS_RMGR_GROUP_EXPORT.GRANT_EXP(12425,1,...) ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 154 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049 ORA-39127: unexpected error from call to export_string :=SYS.DBMS_RMGR_GROUP_EXPORT.GRANT_EXP(12424,1,...) ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 154 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049 ORA-39127: unexpected error from call to export_string :=SYS.DBMS_RMGR_GROUP_EXPORT.GRANT_EXP(12423,1,...) ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "SYS.DBMS_RMGR_GROUP_EXPORT", line 154 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049 ORA-39127: unexpected error from call to export_string := SYS.DBMS_SCHED_JOB_EXPORT.GRANT_EXP(2003412,1,...) ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2296 ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 52 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_METADATA", line 7049.
I have searched the forum and found similar errors, but I am struggling to understand the cause and solution.Forums seem to indicate it may be related to bug 4358907, Re: Datapump export/import object grants given by another user but not sure it is relevant as I can see a ORA-01031: insufficient privileges in this log.
View 2 Replies
ADVERTISEMENT
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
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
Mar 5, 2013
I have a data base oracle 10g (10.2.0.3). I am exporting the Schema using Grid Control 10g.
Servers:
Source:
Oracle 10.2.0.3.0
To:
Oracle RAC 11.2.0.3
I am trying to import the Schema for the server Oracle RAC 11gR2 11.2.0.3 using the Cloud Control 12c and is presenting the following error:
Error:
O job IMPORTA_EGESTAO_ESQUEMA_NOVO foi reaberto em Terca-Feira, 05 Marco, 2013 11:02
Reiniciando "SYSTEM"."IMPORTA_EGESTAO_ESQUEMA_NOVO":
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLESPACE_QUOTA
Execute Failed: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 18
ORA-06512: at line 44 (DBD ERROR: OCIStmtExecute)
View 1 Replies
View Related
Sep 4, 2012
We are getting the below errors while migrating partitioned tables using expdp.
The source and target databases are both running on 10.2.0.5 and the main thing is source database doesn't have any active sessions. This is a clone of a Prod Database and no one is accessing it.
ORA-31693: Table data object "DPMMGR"."WHSE_CTNR_EVNT_W":"MSG_PRCS_N"."MSG_PRCS_N_DC556" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 31 with name "_SYSSMU31$" too small
ORA-31693: Table data object "DPMMGR"."RLTM_PRDCT_LOG":"RPL_20120814" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14$" too small
Undo Tablespace has enough space but still the expdp is failing.
SQL>/
TABLESPACE Totalspace(MB) Used Space(MB) Freespace(MB) % Used % Free
--------------- --------------- -------------- ------------- ---------- ----------
UNDO01 145096 115338 29758 79.49 20.51
SQL> show parameter undo
[code]....
View 2 Replies
View Related
Oct 25, 2012
Im trying to import DMP file through Toad but below error while importing. My DMP file from 11G and importing into 10g server.
ORA-39000: bad dump file specification
ORA-39143: dump file "D:oracleproduct10.2.0adminorcl1dpdumpdumpfile1.dmp" may be an original export dump file
View 21 Replies
View Related
May 30, 2013
I've been using datapump for a long time now but I have not come across this problem before.
Importing just two tables: Table1 data=100Mb=11 million rows
Table2 data=4.2Gb =19.6 million rows
Table1 ran for approx. 5 hours
Table2 ran for approx. 15 hours
If I run the impdp importing both tables in the same par file the default tablespace of the users the import is running as runs out of space due to ORA-01691: unable to extend lob segment <owner>.SYS_LOG0001175799C00045$$ by 512 in tablespace USERS. I do not understand why it is creating objects in order to import tables into someone elses schema.
The environment is Red Hat LINUX 4.1.2-51 running Oracle 11.2.0.1 of Oracle11gR2. This is a 9 node RAC using ASM.
View 4 Replies
View Related
Aug 22, 2013
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Solaris: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production I'm trying to load a table, small in size (110 rows, 6 columns). One of the columns, called NOTES is erroring when I run the load. It is saying that the column size exceeds max limit. As you can see here, the table column is set to 4000 Bytes)
CREATE TABLE NRIS.NRN_REPORT_NOTES
(
NOTES_CN VARCHAR2(40 BYTE) DEFAULT sys_guid() NOT NULL,
REPORT_GROUP VARCHAR2(100 BYTE) NOT NULL,
AREACODE VARCHAR2(50 BYTE) NOT NULL,
ROUND NUMBER(3) NOT NULL,
NOTES VARCHAR2(4000 BYTE),
[code]....
View 2 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
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 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
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
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
