Home » RDBMS Server » Server Utilities » Refresh schema using exp/imp (solaris, oracle 9i)
Refresh schema using exp/imp [message #335530] Tue, 22 July 2008 08:16 Go to next message
daniesh.shaikh@gmail.com
Messages: 62
Registered: February 2008
Location: india
Member
Hi Gurus,
I want to refresh schema using exp/imp utility.
we use to take exp backup of target schema and imp it to destination schema both are on different database.
the problem here is user has asked me to skip one of the objects
that is that particular object should not get imported to target schema.
please help me on this?

[EDITED by LF: fixed a typo in topic's title (was "refersh")]

[Updated on: Tue, 22 July 2008 11:42] by Moderator

Report message to a moderator

Re: Refersh schema using exp/imp [message #335536 is a reply to message #335530] Tue, 22 July 2008 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no way.
Import the object and then drop it.

Regards
Michel

[Updated on: Tue, 22 July 2008 08:52]

Report message to a moderator

Re: Refersh schema using exp/imp [message #335537 is a reply to message #335536] Tue, 22 July 2008 08:36 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

One more reason to upgrade to Oracle 10G Smile

You could then take advantage of the datapump which has the option to EXCLUDE objects.
Re: Refresh schema using exp/imp [message #335579 is a reply to message #335530] Tue, 22 July 2008 15:33 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I would pre-create a dummy object with the same name (but different structure of course, even better a different object altogether) and then the import will fail on that object. QED.
Re: Refresh schema using exp/imp [message #335607 is a reply to message #335579] Tue, 22 July 2008 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This may lead to a very very long import log file (one error per row) which might hide meaningful errors.

Regards
Michel
Re: Refresh schema using exp/imp [message #335644 is a reply to message #335607] Wed, 23 July 2008 02:22 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have performed a test regarding this "dummy" table idea.

An "ordinary" (IMP un/pw FILE=exp.dmp) import does nothing much; as the object already exists, there's the
IMP-00015: following statement failed because the object already exists:
error. A single error per object.

If we perform the import with the IGNORE=Y option, there's one error per column mismatch and yes, it's a mess (something like this):
IMP-00017: following statement failed with ORACLE error 20000:
 "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '4144414D53'; SREC.M"
 "AXVAL := '57415244'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DB"
 "MS_STATS.NUMARRAY(338883673419062000000000000000000000,33904680172375400000"
 "0000000000000000,344238228918531000000000000000000000,349430527950715000000"
 "000000000000000,365069608210433000000000000000000000,3855544461791350000000"
 "00000000000000,385838479141748000000000000000000000,39090908206129100000000"
 "0000000000000,401131737526106000000000000000000000,401293518953263000000000"
 "000000000000,432325845824965000000000000000000000,4325281945375190000000000"
 "00000000000,437883461866196000000000000000000000,45305470107107400000000000"
 "0000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(1,2,3,4,5,6,7,8,9,10,11,12,"
 "13,14); SREC.EPC := 14; DBMS_STATS.SET_COLUMN_STATS(NULL,'"EMP"','"ENAME"',"
 " NULL ,NULL,NULL,14,.0357142857142857,0,srec,6,6); END;"
IMP-00003: ORACLE error 20000 encountered
ORA-20000: Unable to set values for column ENAME: does not exist or insufficient
 privileges
ORA-06512: at "SYS.DBMS_STATS", line 4879
ORA-06512: at "SYS.DBMS_STATS", line 5081
ORA-06512: at line 1

A conclusion?
Michel
Import the object and then drop it.
OK, but if the table is large, import might take some time.

Joy Division
pre-create a dummy object and the import will fail on that object
Perhaps a good idea, without the IGNORE=Y option.

tahpush
10g datapump which has the option to EXCLUDE objects
Probably the best solution (if possible).
Re: Refresh schema using exp/imp [message #335730 is a reply to message #335644] Wed, 23 July 2008 07:49 Go to previous messageGo to next message
daniesh.shaikh@gmail.com
Messages: 62
Registered: February 2008
Location: india
Member
Thanks for the help.
What i will do.
I will import the whole schema and then drop that particular table.
I think this is the better way.
Re: Refresh schema using exp/imp [message #336025 is a reply to message #335730] Thu, 24 July 2008 08:16 Go to previous messageGo to next message
daniesh.shaikh@gmail.com
Messages: 62
Registered: February 2008
Location: india
Member
The problem is that i there is less space on target server that why i have to exclude one table which is very large.
i have to create the dummy structure and then imp the dump file using ingnore=N option is it the correct way?
Re: Refresh schema using exp/imp [message #336028 is a reply to message #336025] Thu, 24 July 2008 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is one possible way as joy_division mentioned it, you just have to take care to have sufficient space for log file and how you will analyze the so many errors you will get.

Regards
Michel

[Updated on: Thu, 24 July 2008 08:53]

Report message to a moderator

Re: Refresh schema using exp/imp [message #336058 is a reply to message #336028] Thu, 24 July 2008 11:40 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course, if schema doesn't contain too many tables and you'd like to move only tables around, you might use TABLES Export option, such as
EXP un/pw FILE=export.dmp TABLES=(dept, emp, bonus)
and leave that single table out of tables' list.
Previous Topic: Logminer problem in 10g
Next Topic: Export from higher version and import to lower
Goto Forum:
  


Current Time: Thu Dec 08 02:09:51 CST 2016

Total time taken to generate the page: 0.15953 seconds