Home » RDBMS Server » Backup & Recovery » Importing with deleteing exsisting Tables
Importing with deleteing exsisting Tables [message #221235] Sun, 25 February 2007 22:36 Go to next message
dude4084
Messages: 221
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
Re: Importing with deleteing exsisting Tables [message #221238 is a reply to message #221235] Sun, 25 February 2007 22:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
imp help=yes
(IGNORE=YES & others)
Re: Importing with deleteing exsisting Tables [message #221240 is a reply to message #221235] Sun, 25 February 2007 22:59 Go to previous messageGo to next message
dude4084
Messages: 221
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.
Re: Importing with deleteing exsisting Tables [message #221297 is a reply to message #221240] Mon, 26 February 2007 03:43 Go to previous message
Mahesh Rajendran
Messages: 10672
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.
Previous Topic: RMAN backup of archive log generated during backup
Next Topic: Partition Backup
Goto Forum:
  


Current Time: Sat Dec 03 10:22:33 CST 2016

Total time taken to generate the page: 0.22035 seconds