OraFAQ Forum: SQL & PL/SQL » synonym deadlock

Home » SQL & PL/SQL » SQL & PL/SQL » synonym deadlock (Oracle 11.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

synonym deadlock [message #601225]

Mon, 18 November 2013 13:58

jreebel
Messages: 2
Registered: November 2013
Location: USA

Junior Member

I have a job I have been running for years which populates a table and then copies all the records into the user accessible table for reporting. Last night this happened for no reason we can think of. Ran fine the night before and every night for years. No changes have been made for a long time. Anybody have any ideas?

SQL> EXECUTE pkg_pop_szbstud.driver;
Currently 201330 FALL
Start basics load: 11/17/13 21:30:02
Initial records: 3794
Double dippers: 9
Multi level futurists: 1
Stealth study abroaders: 3
4 graduated UGs deleted.
End basics load: 11/17/13 21:30:14
start addr/phone load: 11/17/13 21:30:14
Addresses updated: 3803
Counties updated: 2170
Student addresses 1 updated: 3803
Student addresses 2 updated: 3773
Campus email addrs. updated: 3803
End addr/phone load: 11/17/13 21:31:47
Honors program: 153
MBNA: 0
Study abroad: 39

PL/SQL procedure successfully completed.

SQL>
SQL> DROP TABLE szbstud;

Table dropped.

SQL>
SQL> CREATE TABLE szbstud
2 PCTFREE 5
3 AS SELECT * FROM jcu_szbstud;
AS SELECT * FROM jcu_szbstud
*
ERROR at line 3:
ORA-04020: deadlock detected while trying to lock object PUBLIC.SZBSTUD

SQL> CREATE UNIQUE INDEX ix_szbstud_pidm_levl ON szbstud(pidm, levl_code);
CREATE UNIQUE INDEX ix_szbstud_pidm_levl ON szbstud(pidm, levl_code)
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> DROP PUBLIC SYNONYM szbstud;

Synonym dropped.

SQL> CREATE PUBLIC SYNONYM szbstud FOR jcujobs.szbstud;

Synonym created.

SQL> -- Grant to IS staff
SQL> GRANT SELECT ON szbstud TO drowinski, tish,
2 rniemocienski, aklayman, kbrownlie, camoore, jreebel;
GRANT SELECT ON szbstud TO drowinski, tish,
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

John Reebel, John Carroll University

Report message to a moderator

Re: synonym deadlock [message #601237 is a reply to message #601225]

Mon, 18 November 2013 19:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from SQL below

SELECT owner, 
       object_name, 
       object_type, 
       last_ddl_time 
FROM   dba_objects 
WHERE  last_ddl_time > SYSDATE - 10

Report message to a moderator

Re: synonym deadlock [message #601323 is a reply to message #601237]

Tue, 19 November 2013 12:08

jreebel
Messages: 2
Registered: November 2013
Location: USA

Junior Member

I've attached a file with the output in csv format, if that's OK. The problem occurred around 9:30 pm on the 17th. Thanks.

John

Attachment: jcu_results.csv
(Size: 52.00KB, Downloaded 918 times)

Report message to a moderator

Previous Topic:	Function Name ?
Next Topic:	Finding a valid operand between two values in a formula

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 11:43:56 CDT 2024