Data Dumpfile Import From 11g To 10g XE?

Sep 6, 2010

Getting below error , when trying to import 11g dumpfile set into 10g XE. I haven't taken care of VERSION parameter while taking export , will try that soon. But as of now getting below error which does not seem to be a version mismatch issue.

IMP-00038: Could not convert to environment character set's handle
IMP-00000: Import terminated unsuccessfully

The below query when ran from both the database returns the same result.

select * from nls_database_parameters where parameter='NLS_CHARACTERSET' or parameter='NLS_NCHAR_CHARACTERSET';
The result is:
PARAMETER VALUE
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

View 8 Replies


ADVERTISEMENT

Export/Import/SQL Loader :: Skip Import Of A Schema In Dumpfile?

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

Server Utilities :: Import 9i Dumpfile Into 10g Database?

Aug 6, 2011

Is it possible to import a 9i dumpfile (taken by tradition export method) into 10g database using datapump import?

9i and 10g are database version

View 7 Replies View Related

Export/Import/SQL Loader :: Dumpfile Creation Directly On Target

Jun 12, 2012

Is there any way that dumpfile can be created directly on the target rather than on the source and copying that dumpfile to target using datapump. I want to do full database export.

View 8 Replies View Related

Export/Import/SQL Loader :: How To Import Data From Excel File To Table Through Procedure

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

Export/Import/SQL Loader :: How To Filter Some Illegal Rows When Import Data

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

Export/Import/SQL Loader :: Error During Data Pump Import With Developer

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

Export/Import/SQL Loader :: Full Dump But Data Only Import

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

Export/Import/SQL Loader :: How To Import Only Data From The Dmp File

Feb 11, 2013

I received dmp file , and i want to import only data from that file ?

How can we achieve that in oracle Oracle 11.2.0.3

View 5 Replies View Related

Export/Import/SQL Loader :: Export And Import Of Data Not Table And Data?

Sep 11, 2012

Export and import of data in oracle forms...i have created 02 boutons one for export his trigger like this:

eclare
alrt number;
v_directory varchar2(200) := 'c:ackup'; --- that if the C Drive not the Drive that the windows had installed in it.
path varchar2(100):='back_up'
||to_char(sysdate,'dd_mm_yyyy-hh24_mi_ss');
v_exp varchar2(200) := 'exp hamada/hamada2013@orcl file = '
||v_directory
||''
||path
||'.dmp';
[code]....

this code is correct he expot not only the data but also the creation of the table ....for exemple i do export and everything is good until now and i find the .dmp in the folder backup .. but when i deleted all data from my app and try to import this .dmp iit show me error it tell me thet the table phone is already created...just export the data of phone not the creation of table and data ???? or how can i import just the data from this .dmp ??

View 3 Replies View Related

Export/Import/SQL Loader :: Import Table Without Messing Up Existing Data In Table

Sep 6, 2012

table already exist & its little data too, may have to imp rest of lost data, is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y CONSTRAINTS=n

scenerio2 (if have to drop & recreate the entire table) is this the right command?

imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y

just for single table imp

View 2 Replies View Related

SQL & PL/SQL :: Import Data From Csv To Temp Then Alter Data As It Goes Into Another Table?

May 30, 2012

I need to import some data from .csv files. There is one file each day, so I want them to be automatically imported into the DB. This is the format it comes in:

DSM,LOD,20120524,01,01,9999AMP02,1.1262240,M,0.6397380,M
DSM,LOD,20120524,01,02,9999AMP02,1.1315700,M,0.6450840,M
DSM,LOD,20120524,01,03,9999AMP02,1.1297880,M,0.6450840,M

I want this data to go into TEMP_TABLE. It then needs to reformatted as it goes from temp_table to my_table:

filename,readingID, field1,field2,date,num1,num2,meterid,read1,m1,read2,m2

so filename is the actual name of the .csv file that this row came from. And reading id is date, num1, num2, meterid combined. And the remaining fields coming from temp_table

This is what I have:

procedure import_data()
begin
TEMPFILENAME CHAR(60)='DSM_2010_Husky_Oil_20120525064122_20120525065011.csv';
create table temp_table
(dsm char(3),lod char(3),usage_date date,he char(2),reading char(2),loc_id char(9),mwh number(15,10),eormmwh char(1),mvar number(15,10),eormmvar char(1));

[code]....

, which does not work at all.

View 2 Replies View Related

Import Data To Tablespace

Jun 29, 2011

my previous topic was locked. I was unable to respond.So I am sending it again. The user referred to in the message has its own tablespace assigned.I am trying to import the data to that tablespace. I have noticed that within the .imp file the USERS tablespace is being referenced.

I am having an issue importing. We are currently using Oracle10g. When I import the .imp file it places the data into the USERS tablespace and also in the tablespace of the users (SOM) that is specified. Is there a simple and easy fix for this? I checked the .imp and it has the USERS embedded in the file.

View 1 Replies View Related

Import Data From PostgreSQL

Jul 20, 2012

I am doing a migration of data from a database PostgreSql for Oracle through a database link and using procedures to read the data and populate the Oracle tables. At the moment I found the following difficulty. What to bring data from one table PostgeSql that has a relationship with pg_largeobject. She returns the following error: ORA-22992 can not use lob locators selected from remote tables

My postgres and structure of the following:

eventresult (id bigint NOT NULL, version bigint NOT NULL, binary_data oid, value numeric (19,2) NOT NULL, event_id bigint NOT NULL, routine_id bigint NOT NULL)
pg_largeobject (oid NOT NULL loid, pageno integer NOT NULL, data bytea)

this relationship in binary_data (eventresult) with loid (pg_largeobject)

My oracle and the following structure:

+"EVENTRESULT" ("ID" NUMBER (19), "VERSION" NUMBER (19) NOT NULL, "BINARY_DATA" BLOB, "VALUE" NUMBER (19, 2) NOT NULL, "event_id" NUMBER (19) NOT NULL, "ROUTINE_ID" NUMBER (19) NOT NULL)+

I created the following procedure to perform the population:

create or replace
THE PROCEDURE ADDEVENTRESULT
count_add INTEGER;
count_add_aud INTEGER;
count_origen INTEGER;
count_origen_aud INTEGER;

[Code]...

Using Oracle 11g

View 2 Replies View Related

Data Pump Import Error

Sep 20, 2010

i am getting the below error while doing an import thru data pump. looks like shared pool is not having enough mem allocated.

whether i can ignore this error? what it is trying to do with the DELETE FROM "SYS"."IMPDP_STATS"; INSERT INTO "SYS"."IMPDP_STATS"

ERROR
=====
. . imported "DBA1KSD"."TDF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

[Code]....

View 6 Replies View Related

Error While Doing Import Using Data Dump?

Dec 8, 2012

we have a requirement to export full database dump from source database to target database.

source database - Oracle 11.2.0.1
OS version - AIX 5.3
character set: UTF8

target database - Oracle 11.2.0.3
OS version - 6.1
character set: AL32UTF8

i did export from source database and give it to DBA of target database as dump files.

when he tried to import using this dump, he got the following error for 3 tables

ORA-02374: conversion error loading table
ORA-12899: value too large for column
ORA-02372: data for row

DBA is telling there is character conversion issue and i need to change the source database character set (NLS_CHARACTERSET) and then export these 3 tables separately.

But on analysis, i found UTF8 is subset of AL32UTF8 and hence oracle would do this conversion implicitly.

My query is:

1. For this issue, only solution is to change the source database character set as same as target database, then do the export or any other way available?

2. If i need to change the source database character set, would it affect other data available there?

3. Is there any way available doing character set conversion while doing "expdp" on the fly?

4. Is this issue comes because of oracle version (11.2.0.1 to 11.2.0.3 ) or OS (AIX 5.3 to 6.1)?

View 2 Replies View Related

SQL & PL/SQL :: Import Data Into Table From Internet

Jun 18, 2012

I have a table which contains curreny rates. I want to populate that table from internet's currency rates site. Is it possible?

View 6 Replies View Related

Server Utilities :: How To Import 11g Data Into 9i

Dec 28, 2010

i am working on oracle 11g r2. is there any way to go back to oracle 9i with use of import/export utility. Should i take downgraded export from oracle 11g for oracle 9i.

View 2 Replies View Related

SQL & PL/SQL :: Excel Data Import Into Oracle?

Jun 25, 2012

how excel data import into oracle

View 1 Replies View Related

Import Data Into A 10g Database On RHEL5?

Sep 2, 2010

I am trying to import data into a 10g Database on RHEL5.

Fol are the type of TABLE_NAME's of the TABLEs on the export dump:

1.ABCD
2.A_BCD
3.XYZ_AM
4. XYZAM

My requirement is:

I need to import only tables 1 & 4. I would like to exclude tables 2 & 3.

This is what I have only par file:

EXCLUDE=TABLE:" LIKE '%!_AM' escape '!' "
EXCLUDE=TABLE:" LIKE 'A!_%' escape '!' "

When I run a query to check it works fine:

$>select TABLE_NAME from dba_tables where TABLE_NAME like 'A!_%' escape '!';

TABLE_NAME
------------------------------
A_...........
A_..............
A_...............

3 rows selected.

But when I run the import using the par file, i keep getting

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-01740: missing double quote in identifier

View 4 Replies View Related

Import Data From Dmp File Using Command IMPDP?

May 2, 2011

I am trying to import data from a dmp file using the command IMPDP Here is the command :

impdp
USERID= core_edb_20112_ct/local
DIRECTORY=dir_core20112
DUMPFILE=CORE_EDB_20112_CT_20110426.DMP
LOGFILE=log_core02112_1.log
SCHEMAS=core_edb_20112_ct
REMAP_SCHEMA=core_edb_20112_ct:core_edb_20112_ct
PARALLEL=4
METRICS=Y TRANSFORM=OID:N
TRANSFORM=SEGMENT_ATTRIBUTES:N
REMAP_TABLESPACE=C64_EDB_TS:C64_EDB_TS

I am trying to import data in the following user : core_edb_20112_ct/local
This user is already created , using the tablespace named C64_EDB_TS
The dmp file resides in the location dir_core20112 ( e:\oracle)

I am getting the following error while i try to import

Import: Release 11.2.0.1.0 - Production on Mon May 2 12:47:54 2011

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-31694: master table "CORE_EDB_20112_CT"."SYS_IMPORT_FULL_01" failed to load/
unload
ORA-02354: error in exporting/importing data
ORA-02368: the following file is not valid for this load operation
ORA-02369: internal number in header in file e:\oracle\core_edb_20112_ct_20110426.dmp is not valid.

The DMP is copied from a different network location into the local drive where the command is running.

View 1 Replies View Related

Forms :: Import Access Data To Oracle?

Jun 2, 2010

when i create db link then it show below:

SQL> conn sys as sysdba
Enter password: ********
Connected.
SQL> show user
USER is "SYS"
SQL> create database LINK orafaccess using 'ORAFACCESS';
Database link created.
SQL> desc emp@orafaccess
ERROR:
ORA-02085: database link ORAFACCESS.APEX.COM connects to HO.WORLD
SQL>

View 10 Replies View Related

Server Utilities :: Import Data In One Tablespace

Nov 6, 2013

I have taken an export using expdp of schema, data of the schema spread across different tablespaces , now i want to import the data to only one tablespace.

View 1 Replies View Related

Forms :: Import Data From Excel To Oracle?

Jun 21, 2013

I have designed a form to import data from an excel worksheet into an Oracle Form and It works pretty fine.

I use either Ole2.get_char_property or Ole2.get_num_property depending to the property of the column to read data but there are some columns used by both property which means that database is designed as VARCHAR2. The user can maintain either a numeric or characteristic data there. So I am looking for a solution to be able to make the program more flexible that he can read the property of the cell in excel file and decide what to use between Ole2.get_char_property and Ole2.get_num_property to read from excel file properly.

solve the problem regarding reading from Varchar2 columns.

View 1 Replies View Related

Forms :: Import Data Through Form Developer

Dec 16, 2011

Following is the coding i am using to run in forms 6i to import the data in oracle on When_Button_Press . it will drop and create a user but not importing the data

FORMS_DDL('DROP USER TEST CASCADE');
MESSAGE('USER DROP TEST');
FORMS_DDL('GRANT DBA TO TEST IDENTIFIED BY TEST');
MESSAGE('USER TEST CREATED');

host ('C:oracleora90BINIMP TEST/TEST IGNORE=y file=D:ACC_INVTEST.DMP');

MESSAGE('IMPORT');

EXIT_FORM;

View 6 Replies View Related

XE :: Import Data From A Dmp File Created Using Expdp?

Feb 20, 2013

I am trying to import data from a dmp file created using expdp. Running on oracle 11g Express and getting following error, and tried to fix but could not succeeded, I have tables exist in POI schema and trying to import them in ghi schema. Created dmp file from poi shcema with two tables= '''REL20_AU_POI'',''ARCHIVE_POI''' and these tables do not exist in ghi schema

declare
l_handle    NUMBER;
begin
l_handle := DBMS_DATAPUMP.open(
operation => 'IMPORT',
job_mode => 'TABLE',

[code].....

View 1 Replies View Related

Server Utilities :: To Import All Data From 8i To 10gR2

Apr 14, 2010

At my current organization there are two instances are running one is at 8i and another one is 10g. I want to migrate all the data from 8i to 10g. I have already taken the export from 8i with the below command:-

exp file=<<PATH>> full=y logfile=<<PATH>> compress=y

Now I want to import it to 10g DB. Can I do the import with that below command?

imp file=<<PATH>> full=y logfile=<<PATH>> ignore=y compile=y

View 3 Replies View Related

Server Utilities :: Import Table Data

Jul 12, 2012

i am trying to use exp/imp utility through cmd and exp/imp is done successfully as per message given at last. but data is not import in targeted user.

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:UsersNeetesh>exp

Export: Release 10.2.0.1.0 - Production on Thu Jul 12 14:18:04 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: scott/tiger@localdb

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > d:/scott_data

(2)U(sers), or (3)T(ables): (2)U > t

Export table data (yes/no): yes > y

Compress extents (yes/no): yes > n

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

C:UsersNeetesh>imp

Import: Release 10.2.0.1.0 - Production on Thu Jul 12 14:20:09 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: localaepuser/flair22@localdb

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Import file: EXPDAT.DMP > d:/scott_data

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set List contents of import file only (yes/no): no > y

Import entire export file (yes/no): no > y

importing SCOTT's objects into LOCALAEPUSER Import terminated successfully without warnings.

C:UsersNeetesh>

what is the problem here

View 6 Replies View Related

Export Data-files To Another Linux Box To Import In Another?

Sep 11, 2012

I am a fairly new dba and we had one of our HP Array cluster's crash to the point where oracle will not startup or mount anymore. I can access the datafiles on the linux server however. Is there a way to export the datafiles to another linux box to import in another database or have I pretty much lost everything (we do have an RMAN backup however it is in a remote location and the only person that knows the password to it is unavailable.

View 4 Replies View Related

How To Import Data From Informix To Oracle 10g Database

Dec 12, 2012

I am working on informix 7.25SE version I need to import data from informix to oracle 10g data base .

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved