General Database Discussions


Triggers DDL locks


Hello, I am querying DBA_DDL_LOCKS and and I see that some of the lock types are triggers. My question is, what are some of the reasons that could cause a trigger to be locked? Thanks,23jb
It's possible that your trigger is requesting access to a locked table? You can query DBA_BLOCKERS and DBA_DDL_LOCKS to see the blocker and the locks.  You need to move your questions to General Database Discussions 
23jb wrote: Hello, I am querying DBA_DDL_LOCKS and and I see that some of the lock types are triggers. My question is, what are some of the reasons that could cause a trigger to be locked? Thanks,23jbTrigger is just PL/SQL that runs when  certain action is initiated.Locks are Oracle way for managing contention to critical objects.  SELECT Decode(request, 0, 'Holder: ',                        'waiter: ')        ||vl.sid sess,        status,        id1,        id2,        lmode,        request,        vl.TYPE FROM   v$lock vl,        v$session vs WHERE  ( id1, id2, vl.TYPE ) IN (SELECT id1,                                         id2,                                         TYPE                                  FROM   v$lock                                  WHERE  request > 0)        AND vl.sid = vs.sid ORDER  BY id1,           request;    
23jb wrote: Hello, I am querying DBA_DDL_LOCKS and and I see that some of the lock types are triggers. My question is, what are some of the reasons that could cause a trigger to be locked? Thanks,23jbDoing an action that requires the trigger to executed will put a DDL lock on the trigger to prevent it from being dropped/modified while it is being executed. https://docs.oracle.com/database/121/SQLRF/ap_locks002.htm#SQLRF55509 :Automatic Locks in DDL OperationsA data dictionary (DDL) lock protects the definition of a schema object while it is acted upon or referred to by an ongoing DDL operation. For example, when a user creates a procedure, Oracle Database automatically acquires DDL locks for all schema objects referenced in the procedure definition. The DDL locks prevent these objects from being altered or dropped before procedure compilation is complete.Oracle Database acquires a DDL lock automatically on behalf of any DDL transaction requiring it. Users cannot explicitly request DDL locks. Only individual schema objects that are modified or referenced are locked during DDL operations. The whole data dictionary is never locked.DDL operations also acquire DML locks on the schema object to be modified.
It would be useful to post the query and the results so that we're all clear about what you are seeing. While a trigger is executing, a lock needs to be taken out to ensure that no one can do DDL on the trigger.  I don't know whether that is really what you are seeing, though, or whether you really mean that your trigger is acquiring locks on other objects. Justin
I am querying DBA_DDL_LOCKS and and I see that some of the lock types are triggers.Do you understand that we can NOT SEE ANYTHING unless you actually post it? Don't just say you 'see' something. Post it so we can see what you are talking about. You need to SHOW US: 1. WHAT you do2. HOW you do it3. WHAT results you get My question is, what are some of the reasons that could cause a trigger to be locked? My question is why didn't you first read the Oracle docs to see if they answered YOUR question? The subject you provided for this thread is 'triggers ddl locks'. A simple web search using EXACTLY those same words gives this as the very FIRST result:https://docs.oracle.com/cloud/latest/db112/SQLRF/ap_locks002.htm#SQLRF55509  If you add 'oracle 12' to the search term the very first result is the same doc Andrew posted that you marked as correct. Answer to questions about basic functionality is NOT rocket science. You could have found that same doc/answer yourself using EXACTLY the same words you used for the subject of the thread. The MOST IMPORTANT skill you can have, IMHO, is how to find info for yourself quickly and easily. It doesn't get any easier than what I showed you above. Yet you failed to do that. Please try harder next time.

Related Links

Not able to install crfgui
Rman last command
Trying to access oracle DB from my desktop
Enable trace for specific users
Why does every day we get ora-00054 while gathering stats?
Does the number of columns in a table can affect the performance
how to display both original and edit row
Performance With Multiple Insert Threads
Is my AWR Report showing a Performance Problem
Wasted BLOB space
150 GB shared pool?
to resolve performance issues ( SQL Queries running slow ) what should be the systematic approach
Migrating a Team management from Excel to Database
ROWNUM issue
About database link
Why my transaction log in v$logmnr_contents I can't find they.

Categories

ORACLE DEV
WebCenter Content
Designer
Designer Headstart
OLAP
Industries Archived Fo...
Applications Desktop I...
Content Services
Integration - InterCon...
XQuery
SOA & Process Management
ILM Assistant
Secure Enterprise Search
Technologies (Korean)
Technologies - XML (Ko...
XML Gateway (Korean)
Business Intelligence ...
Essbase
JD Edwards World Archi...
SCM - Asset Lifecycle ...
General EBS Discussion
BPA Suite (Korean)
Technology - LCM: Orac...
WebLogic Portal
WebLogic Server - Clus...
The Coherence Incubato...
weblogic.developer.int...
Tuxedo Newsgroups
Remote APIs
WebLogic Communication...
WebLogic Server - Web ...
BEA WebLogic Portal (C...
版务
程序人生
行业专区
Contact On Demand
Mobile Workforce Manag...
Java Media Framework
JavaServer Pages (JSP)...
Java Message Service (...
JMX
Generics
Other Topics [ARCHIVE]
Other Security APIs, T...
JavaHelp System [ARCHIVE]
Java Plug-In
Solaris 11
Virtual Desktop Infras...
Installation and Integ...
Systems Development an...
Sun Update Connection
WebCenter Archived Forums
Blades General Discussion
Sun Java Desktop System
Oracle Net (SQL*Net、Ne...
Oracle JDeveloper (Jap...
エンタープライズ管理
Oracle Database 11g Ex...
Enterprise Gateway
Oracle University Deli...
DTrace for Oracle Linu...
Endeca Experience Mana...
WebCenter Sites
Oracle JRockit (Japanese)
Engineered Systems
Java Sessions
Houston TX Eloqua Users
Eloqua User Group - Ba...
Cloud Component Beta C...
SRM/Eloqua users
API Management
Java Lambda MOOC
Oracle Marketing Apps
Orphan Threads
Systems Track
Java Community Process
Oracle Supply Chain Ma...
Warehouse Management
NetBeans
OTN Summit Middleware ...
Interact 2014: A Moder...
POUG
Free & Open Source Sof...
SouJava Campinas JUG
Base de Datos Discusió...
Oracle EMEA Partner Sales
Eloqua10 Best Practice...
OMC - Industry Solutio...
The Markies
DBA Plus Community-DBA...
All China Oracle User ...
IDGOUG - (Internationa...
Home of Oracle Certifi...
JavaScript - Nashorn
Oracle Application Bui...
Cloud Accelerate
NightHacking
Dallas Eloqua Users
Oracle Linux
Identity Manager
Database Security - Ge...
C++ Call Interface (OCCI)
엔터프라이즈 관리자 ((Korean))
SQLJ/ODBC (Korean)
产品专区
Integrated Operational...
Storage General Discus...
Solaris Archived Forums
Oracle Enterprise Mana...
Middleware Track
DevOps

Resources

Encrypt Message



code
soft
python
ios
c
html
jquery
cloud
mobile