Data Import in Oracle (186 Views)
Reply
Occasional Contributor
sadia
Posts: 2
Registered: ‎11-05-2002
Message 1 of 7 (186 Views)

Data Import in Oracle

While importing data I want to overwrite existing data in tables. Is there any argument that I can use with import command (IMP) to do this. Can I use DESTROY with IMP to achieve this.
Honored Contributor
Kwan Fong, Emile
Posts: 1,162
Registered: ‎07-10-2000
Message 2 of 7 (186 Views)

Re: Data Import in Oracle

Hello Sadia,

Are you running Oracle on an MPEiX server? Or on a Windows or UNIX platforms? If the latter, then you are in the wrong room; please call Oracle for assistance.
King is the customer!
Trusted Contributor
B. Hulst
Posts: 255
Registered: ‎12-04-2001
Message 3 of 7 (186 Views)

Re: Data Import in Oracle

Hi,

You could use destroy=Y when using imp but more elegant would be to drop the tables and indexes on it before import them.

After imp you recreate the indexes.

It is faster this way.

- Bob
Occasional Advisor
JJ Smith_1
Posts: 7
Registered: ‎02-28-2005
Message 4 of 7 (186 Views)

Re: Data Import in Oracle

I think you'll find that 'DESTROY' applies to the data file contents - NOT table contents.
Advisor
Stephen Ray Badgett
Posts: 31
Registered: ‎05-22-2006
Message 5 of 7 (186 Views)

Re: Data Import in Oracle

I do not know if this will help but using TRUNCATE table in SQL/PL will also reset the HIGHWATER mark too thus saving some space too.
Advisor
Stephen Ray Badgett
Posts: 31
Registered: ‎05-22-2006
Message 6 of 7 (186 Views)

Re: Data Import in Oracle

also note buy doing this it will also make it so the triggers, sequences and indexes will still be intact and thus not rebuilding them. The sequence might depending on how there used.
Occasional Advisor
JJ Smith_1
Posts: 7
Registered: ‎02-28-2005
Message 7 of 7 (186 Views)

Re: Data Import in Oracle

It's questionable if this should be in this forum as it's clearly an explicit Oracle question. However for the sake of others browsing the forum: There is no parameter possible in the current oracle OS binary 'imp' that allows the overwriting of single table data. This implies that something else needs to be done prior to import. The suggestion that a separate truncate would clear the data is true - the inference that it saves space is false. When truncating it becomes an option to retain the storage space currently allocated or free up any additionally allocated extents, if any, above the initial minimum number of extents. So if a table hasn't grown truncate will not return any space to the table space pot; on the other hand if it has grown and you free up the extra extents then loading data might cause a space management overhead as those extents have to be re-allocated to the table.
So it is how truncate is used that determines what may or may not happen to space, in itself truncate is not a space saver.
The point on moving the HWM to the initial block is true and would greatly speed up subsequent inserts as the process no longer needs to check blocks for space to insert as all blocks would be free until populated, by which time they would be removed from the free list. So in that scenario a truncate could speed the subsequent import.
Personally, if Oracle 'imp' could not meet my needs then I would not obtain my data using Oracle 'exp' - I would write a table unload to produce an OS file and then use sql*ldr to read it in - that oracle product has far more appropriate functionality in the load control, including overwriting table data.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.