Home » RDBMS Server » Server Administration » how to delete the duplicated triggers. (oracle 9.2.0.6.0, o/s : sun solaris : 5.9)
how to delete the duplicated triggers. [message #306276] Thu, 13 March 2008 09:35 Go to next message
kesavansundaram
Messages: 181
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 #306280 is a reply to message #306276] Thu, 13 March 2008 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
drop trigger?

Regards
Michel
Re: how to delete the duplicated triggers. [message #306285 is a reply to message #306280] Thu, 13 March 2008 09:55 Go to previous messageGo to next message
kesavansundaram
Messages: 181
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 #306286 is a reply to message #306285] Thu, 13 March 2008 09:57 Go to previous messageGo to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

sir,
one more thing,
for those duplicated triggers owner shows REVERA_AP...
but our live schema is : REVERA_OWNER..... now currently there are 8 triggers alive in live env.
will it affect in live env ?
kesavan
Re: how to delete the duplicated triggers. [message #306289 is a reply to message #306286] Thu, 13 March 2008 10:01 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: database characterset migration from WE8DEC to UTF8
Next Topic: control file and data file recovery
Goto Forum:
  


Current Time: Sat Dec 10 12:39:26 CST 2016

Total time taken to generate the page: 0.09300 seconds