| how to delete the duplicated triggers. [message #306276] |
Thu, 13 March 2008 09:35  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
dear sir,
i performed one specific table export and import activity.
first i generated EXPORT command with tables=EFF_REP_TABLE from schema: REVERA_OWNER( THIS IS LIVE SCHEMA )
when i imported, i put the same clause, tables=EFF_REP_TABLE..( I imported in REVERA_AP SCHEMA..)
in import syntax, i put fromuser=revera_owner touser=revera_ap tables=EFF_REP_TABLE
before import, i checked in user_tables whether this table existing...but it is not.
so, i fired.
but there was one synonym created (name: EFF_REP_TABLE ) based on the table EFF_REP_TABLE which is in live schema
and INSERT privilege has been already granted to schema: REVERA_AP on this table EFF_REP_TABLE in live environment.
this thing i was not sure when i perform import activity.
what has happened,
after import, data has been inserted into this synonym in REVERA_AP schema and the same records have been inserted back
in live table ( i.e.live schema: revera_owner ), since export was generated from live table, NOW there is a duplication...
and we are removing this duplicated rows.....this is going in one side.
already there were 4 triggers in THIS TABLE IN LIVE ENVIRONMENT ( revera_owner )
SQL> select trigger_name,trigger_type,table_name,status from user_triggers
2 where table_name = 'EFF_REP_TABLE';
TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS
------------------------------ ---------------- ------------------------------ --------
ERT BEFORE EACH ROW EFF_REP_TABLE ENABLED
EFF_REP_MIS_EXT BEFORE EACH ROW EFF_REP_TABLE ENABLED
EFF_REP_MIS_EXT_INS BEFORE EACH ROW EFF_REP_TABLE ENABLED
EFF_REP_TABLE_TR1 BEFORE EACH ROW EFF_REP_TABLE ENABLED
now after import, there are 4 more triggers....now total 8 triggers are there.
in pl/sql developer tool , when i checked, it shows abv 4 rows only
if i checked in sql navigator, it shows 8 triggers...
for those 4 duplicate triggers, owner shows:REVERA_AP... ( check my attachment )
now how to remove this duplicate triggers ?
guide me
rgds,
kesavan.
|
|
|
|
|
|
| Re: how to delete the duplicated triggers. [message #306285 is a reply to message #306280] |
Thu, 13 March 2008 09:55  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Dear sir,
it is very critial table in live env.
It seems application side, they have set some SCHEDULER and user also using the live data now till 10'o clock.
i hope top level will disable the scheduler by 10'o clock and
then drop this trigger as you told.
but my doubt is, we can disable the trigger also know .... then we can drop it....
if we drop directly , will there be any effect in live data ?
pls cfm once again.
kesavan
|
|
|
|
|
|
| Re: how to delete the duplicated triggers. [message #306289 is a reply to message #306286] |
Thu, 13 March 2008 10:01 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I don't know what do your triggers.
Your schema is a mess, the best is to stop application and to do maintenance.
What do you do if I say you can drop the triggers on the fly?
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
