Home » RDBMS Server » Server Utilities » Impdp a table with table already present with new columns (Oracle 11g RHEL 64 bit)
Impdp a table with table already present with new columns [message #449615] Wed, 31 March 2010 00:12 Go to next message
dbasamrat
Messages: 4
Registered: March 2010
Location: Bangalore
Junior Member
Hi
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,
can u please suggest how should i got for it, also I am doing a
TABLE_EXISTS_ACTION=TRUNCATE

The new table
SQL> desc "TESTINVENTORY"."TTRANSACTION"
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TRANSACTIONIDNOT NULL CHAR(26)
BRANCHCODE NOT NULL CHAR(3)
EXTERNALSYSTEM NOT NULL CHAR(3)
EXTRACTSYSTEM NOT NULL CHAR(3)
OWNERBRANCHCODE NOT NULL CHAR(3)
TRADEREFERENCE NOT NULL CHAR(20)
PRODUCTGROUP NOT NULL CHAR(2)
PRODUCTTYPE NOT NULL CHAR(3)
PRODUCTSUBTYPE NOT NULL CHAR(4)
COUNTERPARTYCODE NOT NULL CHAR(20)
SUBACCOUNTCODE NOT NULL CHAR(20)
TRANSACTIONTYPE NOT NULL CHAR(1)
COLLATERALISEDIND NOT NULL CHAR(1)
TRANSACTIONCURRENCY NOT NULL CHAR(3)
TRADERID CHAR(15)
TRADEDATE NOT NULL DATE
STARTDATE NOT NULL DATE
EXPIRYDATE DATE
PREFERREDALTERNATESOURCE NOT NULL CHAR(3)
BROKERCODE NOT NULL CHAR(20)
COSTCENTRECODE NOT NULL CHAR(20)
NETTABLEIND NOT NULL CHAR(1)
SETTLEMENTMODE NOT NULL CHAR(1)
LINKEDTRADEREFERENCE CHAR(20)
LINKEDDOMAINCODE CHAR(3)
LINKEDEXTERNALSYSTEM
CHAR(3)
LASTUPDATED NOT NULL DATE
EXTRACTDATETIME NOT NULL DATE
VERSION NOT NULL CHAR(30)
UPDATED NOT NULL DATE


old table
SQL> desc "TESTINVENTORY"."RTRANSACTION"
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TRANSACTIONID NOT NULL CHAR(26 CHAR)
BRANCHCODE NOT NULL CHAR(3 CHAR)
EXTERNALSYSTEM NOT NULL CHAR(3 CHAR)
EXTRACTSYSTEM NOT NULL CHAR(3 CHAR)
OWNERBRANCHCODE NOT NULL CHAR(3 CHAR)
TRADEREFERENCE NOT NULL CHAR(20 CHAR)
PRODUCTGROUP NOT NULL CHAR(2 CHAR)
PRODUCTTYPE NOT NULL CHAR(3 CHAR)
PRODUCTSUBTYPE NOT NULL CHAR(4 CHAR)
COUNTERPARTYCODE NOT NULL CHAR(20 CHAR)
SUBACCOUNTCODE NOT NULL CHAR(20 CHAR)
TRANSACTIONTYPE NOT NULL CHAR(1 CHAR)
COLLATERALISEDIND NOT NULL CHAR(1 CHAR)
TRANSACTIONCURRENCY NOT NULL CHAR(3 CHAR)
TRADERID VARCHAR2(15 CHAR)
TRADEDATE NOT NULL DATE
STARTDATE NOT NULL DATE
EXPIRYDATE DATE
PREFERREDALTERNATESOURCE NOT NULL CHAR(3 CHAR)
BROKERCODE NOT NULL CHAR(20 CHAR)
COSTCENTRECODE NOT NULL CHAR(20 CHAR)
NETTABLEIND NOT NULL CHAR(1 CHAR)
SETTLEMENTMODE NOT NULL CHAR(1 CHAR)
LASTUPDATED NOT NULL DATE
EXTRACTDATETIME NOT NULL DATE
VERSION NOT NULL CHAR(30 CHAR)
UPDATED NOT NULL DATE

It giving error while doing an import.
Re: Impdp a table with table already present with new columns [message #449618 is a reply to message #449615] Wed, 31 March 2010 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop the table before the import.

Regards
Michel
Re: Impdp a table with table already present with new columns [message #449620 is a reply to message #449618] Wed, 31 March 2010 00:30 Go to previous messageGo to next message
dbasamrat
Messages: 4
Registered: March 2010
Location: Bangalore
Junior Member
what if I want to keep the same table structure.
Re: Impdp a table with table already present with new columns [message #449626 is a reply to message #449620] Wed, 31 March 2010 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Import in another table and then move the rows.
Or export only the columns you want to import (by moving the rows into another table as you can't choose the columns on export).

Regards
Michel
Re: Impdp a table with table already present with new columns [message #449628 is a reply to message #449626] Wed, 31 March 2010 01:19 Go to previous message
dbasamrat
Messages: 4
Registered: March 2010
Location: Bangalore
Junior Member
Thanks a lot ...
I tried importing to another table and then moving the data to that table.
Previous Topic: Insert Excel spreadsheet into Oracle issue
Next Topic: .DMP Restoration
Goto Forum:
  


Current Time: Sun Aug 01 18:51:39 CDT 2021