Server Utilities :: Import A Table With Table Already Present With New Columns

Mar 31, 2010

I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.

The new table
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------

It giving error while doing an import.

View 4 Replies


Server Utilities :: Truncate Table And Load It With Data Present In File

Jul 17, 2010

My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.

View 6 Replies View Related

Server Utilities :: Import Table With Different Name

Nov 29, 2006

We have a table in Oracle9i database with around 14 million records and we would like to import that table into 10g database with similar structure. We have exported the table from 9i database and would like to import the table into 10g database within same schema name with different table name as we already have the table with same name in 10g database in same schema. Is it possible to import a table with different table name?

We have a way around to import the table into 10g database in another schema and then push the data into our main table but want to know whether the above requirement is possible.

View 7 Replies View Related

Server Utilities :: Import A Table From Prod To Dev

Jan 26, 2011

I am getting the below error when I import a table from Prod to Dev. I understand this error will be occured if length of the datatype is low. First I got the error when the datatype(length) which is 25 for the column PASSWORD column.Then I increased the length of this column to 45, then it was imported successfully.

why am facing the error when the datatye and length for this table is same in prod and dev? What are the possible ways to import the data without increasing the PASSWORD column length?

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "ANEES"."SALSA_WEB_ACCESS"."PASSWORD" (actual: 28, maximum: 25)

View 7 Replies View Related

Server Utilities :: Skip One Table While Import

May 12, 2010

How to skip one table while import in traditional exp/imp not in DP.

View 4 Replies View Related

Server Utilities :: How To Change Table Name While Import

Jan 18, 2010

I wanted to export a table "emp_production" from Production database then import it as "emp_datawarehouse" in Data warehouse database.Both tables has same structure. I have granted IMPORT FULL DATABASE & EXPORT FULL DATABASE privileges to both schema

I tired with the following syntax

$ Expdp u1/p1@h1[/email] tables= emp_production directory=test dumpfile=test1.dmp
$ Impdp u1/p2@h2[/email] directory=test dumpfile=test1.dmp remap_schema=u1.emp_production:u2.emp_datawarehouse

But I am getting the following error

ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping.

Why this ? "emp_production" table has 150 million rows, every week importing this table then inserting into "emp_datawarehouse" table takes long time.

View 8 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.


Export: Release - 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 - 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.


Import: Release - 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 - 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.


what is the problem here

View 6 Replies View Related

Server Utilities :: Rename Table While Using Datapump Import?

Mar 18, 2013

I have a Datapump Export File which was created in Schema mode.

I have to import the tabelles in a new database where a have to use the REMAP SCHEMA Parameter.

Additionally I would like to add a prefix to tablenames.

For example:

original tablename: THE_TABLE
Name after import: IMP_THE_TABLE

Is there a way to add a prefix while using Datapump Import?

View 5 Replies View Related

Server Utilities :: Import Only Some Table Of User Form

Oct 4, 2011

we have every day full export backup in eacly morning. but some tables's data has been delete unforutnaltely & structure of these tables intact. how do i import only some tables of a user from

daily full export backup? . this has to be done immdediately.

View 2 Replies View Related

Server Utilities :: Partition Table Import Through Data Pump?

Mar 11, 2013


I have a table with RANGE PARTITION. I wanted to import this into another server with the same partitions.

But when I imported the table, The table created with the Partition but the data is not inserted in partition wise.

But I could see the Entire table's ROW COUNT.

View 5 Replies View Related

Server Utilities :: Import Only One Or Two Table From A Schema Export File

May 6, 2012

is it possible to import only one or two table from a schema export file or from a full database export file.

View 2 Replies View Related

Server Utilities :: Import A Flat File Into Oracle And Update Another Table

Jul 5, 2013

I have a text file called ReturnedFile.txt. This is a comma separated text file that contains records for two fields.... Envelope and Date Returned.

At the same time, I have a table in Oracle called Manifest. This table contains the following fields:



I need to write something that imports the ReturnedFile.txt into a temporary Oracle table named UploadTemp, and then compares the data in the Envelope field from UploadTemp with the Envelope field in Manifest. If it's a match, then the DateReturned field in Manifest needs updated with the DateReturned field in UploadTemp.

I've done this with SQL Server no problem, but I've been trying for two days to make this work with Oracle and I can't figure it out. I've been trying to use SQL*Loader, but I can't even get it to run properly on my machine.

I did create a Control file, saved as RetFile.ctl. Below is the contents of the CTL file:

INFILE 'C:OracleTestReturnedFile.txt'


If I could get SQL*Loader running, below is the code I came up with to import the text file and then to do the compare to the Manifest table and update as appropriate:

sqlldr UserJoe/Password123 CONTROL=C:OracleTestRetFile.ctl LOG=RetFile.log BAD=RetFile.bad

update Manifest m set m.DateReturned =
(select t.DateReturned
from UploadTemp t
where m.Envelope = t.Envelope

That's all I got. As I said, I can't find a way to test it and I have no idea if it's even close.

View 2 Replies View Related

Server Utilities :: IMP-00018 - Partial Import Of Previous Table Completed

Oct 11, 2011

I am trying to import the schema into 11g database, which i took on Oracle 9i database. While import is running, data file is full as auto extension was not enabled.

I got the following error:

. . importing table "WO_GL_ACCOUNT_SUMMARY"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table PWRPLANT.WO_GL_ACCOUNT_SUMMARY by 1024 in tabl
IMP-00018: partial import of previous table completed: 7055845 rows imported.

Then I increased the datafile size and finally Import terminated successfully with warnings. At this point, I want to know whether WO_GL_ACCOUNT_SUMMARY Table was imported with out missing any rows .

View 7 Replies View Related

Server Utilities :: Import Table From Another Database By Using Database Link?

Mar 17, 2010

i would like to import a table from another database by using database link.

impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1

i created the public database link as system user

it gave error like

Import: Release - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA


after that i try to import as system user like

impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1

but error like

Import: Release - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release - Production
UDI-00010: multiple job modes requested, schema and tables.

when i use IMP utility

imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser

View 3 Replies View Related

Fetch All Information From Big Table For Those Key Present In Small Table

Jun 28, 2012

I am having two tables

Table 1 having 16 cror rows .
Table 2 having 1000 rows

I joined both the tables and fetch all inforamtion from big table for those key present in small table.Join query taking more time to fetch the rows .

View 2 Replies View Related

Server Utilities :: Why Duplicated Rows When A PK Is Present Running Sqlldr

Dec 10, 2010

Why duplicated rows when a PK is present running Sqlldr?

View 1 Replies View Related

Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table

Jul 11, 2013

I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.

CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)

-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.

** 9005.nc1 --WO_NO
1239401A - STRUCT
9005 -MARK
9005 --POS
2 --QTY

View 24 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 :: Query To Find Values Are Not Present In Table

Nov 28, 2010

I have table called test script for table is given below

create table TEST
col1 Number


For these values Query is not returning values(3 and 4). So i want generic query to get this result. I am working on it but not able to generate proper query.

View 2 Replies View Related

SQL & PL/SQL :: Find Deptno Present In Dept Table?

Jun 23, 2013

In dept table there is deptno=10,20,30,40

in emp table there is deptno=10,20,30

i want to find the deptno which present in dept table but not

present in emp table.

View 4 Replies View Related

SQL & PL/SQL :: Write A Query Which Shows Data Of One Table Not Present In Other?

Apr 3, 2011

I am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code

I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.

View 9 Replies View Related

Wallet - While Creating A Table Says Master Encryption Key Is Not Present?

Jan 22, 2013

I have created a wallet (11g R2 OEL 5.5) using the OWM.Tried opening the wallet (encryption_wallet_location set in sqlnet.ora). then while creating a table it said the master encryption key is not present. Have created the master key using the following command.

alter system set encryption key identified by "Password";

Here the strange thing i observed is that when we create a wallet using the OWM, it asks for the password and when i open the same wallet the master key is not created and it allows the master key to be generated with the same password that i have created the wallet in the first place with the OWM, with any other passwords it says that the wallet is not open.

After creating the wallet and creating the master key... I have the following questions, and its becoming quite hard to find the solutions as well.

1. Can we have multiple encryption keys... say i want to encrypt a table or column with one key and other with an another key.
2. How many keys can we have for objects in the table? or can we have only one key and many certificates.
3. wallet created, and encrypted tables present, the wallet is not in auto open mode, but somehow the database open after it is shutdown, here no encrypted tablespaces are present.
4. while creating an encrypted tablespace the default storage (encrypt ) has to be added to the add tablespace clause.

View 2 Replies View Related

SQL & PL/SQL :: Query To Find Number Of Primary Keys Present In A Table?

May 17, 2011

is there any query to find number of primary keys present in a table.

View 13 Replies View Related

Server Administration :: Cyrillic Character Set - Import A Table Of Data To Another Database

Aug 31, 2011

I'm try to import a table of data (character set: CL8ISO8859P5) to another database (character set: AL32UTF8) using exp/imp utility.After the import, all Cyrillic text was corrupted!

View 5 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z

Requirement :

From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this

-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.

View 3 Replies View Related

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

Server Utilities :: How To Exclude Table

Feb 6, 2012

I tried to export a schema excluding some table, but expdp exit with this error:

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

The command that I use is:

expdp system/password@ORADB directory=EXPORT_ORA_DIR schemas=maxdb logfile=maxdb.log
dumpfile=maxdb.dump EXCLUDE=TABLE:"IN ('max_table_1','max_table_2')";

Where I made a mistake?

View 2 Replies View Related

Server Utilities :: Importing Table From 9i To 10g

May 31, 2012

I want to import my oracle 9i dump file into oracle 10g version. What should i do ???

View 2 Replies View Related

All Columns Of A Table Are Part Of Primary Key Column Of That Table?

Feb 22, 2011

if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).

should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.

there is a drawback if a most of the tables of a database contain composite primary keys?

View 3 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

Feb 6, 2011

I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:

Query 1

Update TableA A
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)

Query 2
Update TableA A
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)

When i execute these two above queries, it keeps executing indefinitely.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved