Home » RDBMS Server » Server Administration » synonyms (10.2.0.4)
synonyms [message #434783] Thu, 10 December 2009 12:21 Go to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
hi

All the database synonyms are dropped by mistake by a user.
It has become a very critical situation for us to rollback the changes done.
How we can track the ddl commands (drop synonym ) executed in last 24 hours, so that we can create the synonyms again
or any other way to do it

We cannot afford to recover the database from the last backup.
regards
Re: synonyms [message #434784 is a reply to message #434783] Thu, 10 December 2009 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
LOGMINER

[Updated on: Thu, 10 December 2009 12:23]

Report message to a moderator

Re: synonyms [message #434786 is a reply to message #434784] Thu, 10 December 2009 12:27 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
[oracle@ggndclad:/oracle]> uname -a
SunOS ggndclad 5.8 Generic_108528-29 sun4u sparc SUNW,Sun-Fire-V440
[oracle@ggndclad:/oracle]> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 10 18:26:32 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Re: synonyms [message #434788 is a reply to message #434786] Thu, 10 December 2009 13:45 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could also recover the database from backup to a new location and then get all synonym DDL from there.


Re: synonyms [message #434809 is a reply to message #434788] Thu, 10 December 2009 22:19 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
the test/dev database too have the same synonyms .
Is there any way i use some dbms_metadata command to get synonyms definations from the test database
Re: synonyms [message #434810 is a reply to message #434809] Thu, 10 December 2009 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Don't you have application under some sort of Source Code Control system like subversion, etc.?
Re: synonyms [message #434811 is a reply to message #434809] Thu, 10 December 2009 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> desc user_synonyms
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SYNONYM_NAME				   NOT NULL VARCHAR2(30)
 TABLE_OWNER					    VARCHAR2(30)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 DB_LINK					    VARCHAR2(128)

SQL> select synonym_name from user_synonyms where rownum = 1;

SYNONYM_NAME
------------------------------
DEF$_AQCALL

SQL> select dbms_metadata.get_ddl('SYNONYM','DEF$_AQCALL') FROM DUAL;

DBMS_METADATA.GET_DDL('SYNONYM','DEF$_AQCALL')
--------------------------------------------------------------------------------

  CREATE OR REPLACE SYNONYM "SYS"."DEF$_AQCALL" FOR "SYSTEM"."DEF$_AQCALL"
Re: synonyms [message #435016 is a reply to message #434809] Sun, 13 December 2009 13:00 Go to previous message
spabolu
Messages: 7
Registered: December 2009
Junior Member
If you have all the synonyms in the test database, why don't you use expdp synonyms and impdp them in production.

Or directly use impdp from production using NETWORK_LINK datapump parameter.
Previous Topic: ASM performance Help
Next Topic: Best way to move Oracle Home
Goto Forum:
  


Current Time: Mon Jul 01 01:33:47 CDT 2024