OraFAQ Forum: Backup & Recovery » Importing with deleteing exsisting Tables

Home » RDBMS Server » Backup & Recovery » Importing with deleteing exsisting Tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Importing with deleteing exsisting Tables [message #221235]

Sun, 25 February 2007 22:36

dude4084
Messages: 222
Registered: March 2005
Location: Mux

Senior Member

Hi

My objective is that I want to copy whole data from office PC to home PC on daily basis and vice versa.

The method i have thought is to export data with EXP utility and taking the DMP file home on flash drive and then importing it home PC.

First time, Import is OK. But from second time and onwards, i have to delete the exsisting tables before Import is executed.. Is there any way that Tables are replaced/deleted with the execution of Import command?

Please help to solve my problem. Give comments as well on the method i am using.

-Dude

Report message to a moderator

Re: Importing with deleteing exsisting Tables [message #221238 is a reply to message #221235]

Sun, 25 February 2007 22:52

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
Oracle Version ?

hope 10g then you can use "TABLE_EXISTS_ACTION=REPLACE"

with expdp or impdp


SQL> create directory data as 'c:\data';

Directory created.

SQL> grant read,write on directory data to scott;

Grant succeeded.

SQL> host expdp scott/tiger directory=data dumpfile=emp tables=emp

Export: Release 10.1.0.2.0 - Production on Monday, 26 February, 2007 8:42

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=data dumpfile=
emp tables=emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\DATA\EMP.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 08:43


SQL> host impdp scott/tiger directory=data dumpfile=emp table_exists_action=repl
ace

Import: Release 10.1.0.2.0 - Production on Monday, 26 February, 2007 8:45

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=data dumpfile=e
mp table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                               7.820 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 08:45

if < 10g then you can create one drop table script before import just run drop tables script and import your data.

regards
Taj

[Updated on: Sun, 25 February 2007 23:04]

Report message to a moderator

Re: Importing with deleteing exsisting Tables [message #221239 is a reply to message #221235]

Sun, 25 February 2007 22:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

imp help=yes
(IGNORE=YES & others)

Report message to a moderator

Re: Importing with deleteing exsisting Tables [message #221240 is a reply to message #221235]

Sun, 25 February 2007 22:59

dude4084
Messages: 222
Registered: March 2005
Location: Mux

Senior Member

Sorry, i forgot to mention
Its Oracle 8i
Windows 2K.

Thanks anacedent for the tip.

I will check and see.
Thanks everyone for quick response.

Report message to a moderator

Re: Importing with deleteing exsisting Tables [message #221297 is a reply to message #221240]

Mon, 26 February 2007 03:43

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Is there any way that Tables are replaced/deleted with the execution of Import command
Nope. The old fashion import will append by default.
The only workaround is to remove the existing data prior to import.
This could be easily automated.

Report message to a moderator

Previous Topic:	RMAN backup of archive log generated during backup
Next Topic:	Partition Backup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 20:38:07 CST 2024