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  |
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: Refresh schema using exp/imp [message #335579 is a reply to message #335530] |
Tue, 22 July 2008 15:33  |
joy_division
Messages: 4963
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 #335644 is a reply to message #335607] |
Wed, 23 July 2008 02:22 |
 |
Littlefoot
Messages: 21809
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 theIMP-00015: following statement failed because the object already exists:
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 #336058 is a reply to message #336028] |
Thu, 24 July 2008 11:40 |
|
Littlefoot
Messages: 21809
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) |
|
|
|
Goto Forum:
Current Time: Sat May 11 02:55:37 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
