Oracle 11g ORA-14450: 试图访问已经在使用的事务处理临时表

- 日理万妓 2022-06-16 06:18 911阅读 0赞

现场系统报ORA-14450: 试图访问已经在使用的事务处理临时表。  
**session1:**  
drop table test purge;  
create global temporary table test (id number)   
on commit preserve rows;  
insert into test values(1);  
  
**session2:**  
alter table test modify id number(10);  
alter table test modify id number(10)  
\*  
第 1 行出现错误:  
ORA-14450: 试图访问已经在使用的事务处理临时表  
  
**--这里的TO是Synchronizes DDL and DML operations on a temp object**  
select s.INST\_ID,  
       'alter system kill session ''' || s.SID || ',' || s.SERIAL\# || ''';' exec\_sql,  
       o.object\_name  
  from gV$session s,gv$enqueue\_lock l, dba\_objects o  
 where l.type = 'TO'   
  and s.INST\_ID = l.INST\_ID and l.ID1 =  o.object\_id   
  and s.SID = l.SID  
  and o.object\_name='TEST';   
INST\_ID             EXEC\_SQL                      OBJECT\_NAME  
\------- ----------------------------------------- -----------  
      1 alter system kill session '6637,34738';     TEST  
              
v$enqueue\_lock  type  
ABLock held to ensure that ABMR process is initialized  
ABLock held to ensure that only one ABMR is started in the cluster  
ADSynchronizes accesses to a specific ASM disk AU  
AEPrevent Dropping an edition in use  
AFThis enqueue is used to serialize access to an advisor task  
AGSynchronizes generation use of a particular workspace  
AMASM group block  
AMAllow one ASM password file update per cluster at a time  
AMAllow only one AMDU dump when block read failure  
AMBlock ASM cache freeze  
AMCheck id1 of call for specific purpose  
AMClient group use  
AMPrevent DB instance registration during ASM instance shutdown  
AMPrevent same file deletion race  
AMPrevents a user from being dropped if it owns any open files  
AMRegisters DB instance to ASM client state object hash  
AMReserve a background COD entry  
AMReserve a rollback COD entry  
AMSerializes access to ASM file descriptors  
AMSerializes block repairs  
AMStart ASM cache freeze  
AMSynchronizes disk based allocations/deallocations  
AMSynchronizes disk offlines  
AOSynchornizes access to objects and scalar variables  
ASSynchronizes new service activation  
ATSerializes ‘alter tablespace’ operations  
AVSerialize inst reg and first DG use  
AVSerialize relocating volume extents  
AVSerialize taking the AVD DG enqueue  
AVprevent DG number collisions  
AWGlobal access synchronization to the AW$ table  
AWIn-use generation state for a particular workspace  
AWRow lock synchronization for the AW$ table  
AWSynchronizes user accesses to a particular workspace  
AYAffinity Dictionary test affinity synchronization  
BB2PC distributed transaction branch across RAC instances  
BFAllocate a bloom filter in a parallel statement  
BFPMON bloom filter recovery  
BRLock held to allow cleanup from backup mode during an RMAN proxy-copy backup  
BRLock held to perform a new controlfile autobackup  
BRLock held to prevent file from decreasing in physical size during RMAN backup  
BRLock held to prevent multiple process to update the headers at the same time  
BRLock held to request controlfile autobackups  
BRLock held to serialize file header access during multi-section restore  
BRLock held to serialize section access during multi-section restore  
CASynchronizes various IO calibration runs  
CFSynchronizes accesses to the controlfile  
CICoordinates cross-instance function invocations  
CLSynchronizes accesses to label cache for label comparison  
CLSynchronizes accesses to label cache when dropping a label  
CMindicate ASM diskgroup is mounted  
CMserialize access to instance enqueue  
CMserialize asm diskgroup dismount  
CNduring descriptor initialization  
CNduring registration  
CNduring transaction commit to see concurrent registrations  
COenqueue held be Master in Cleanout Optim  
CQSerializes access to cleanup client query cache registrations  
CRCoordinates fast block range reuse ckpt  
CTLock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources  
CTLock held during change tracking space management operations that affect just the data for one thread  
CTLock held during change tracking space management operations that affect the entire change tracking file  
CTLock held to ensure that change tracking data remains in existence until a reader is done with it  
CTLock held to ensure that only one CTWR process is started in a single instance  
CTLock held while enabling or disabling change tracking in RAC  
CTLock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time  
CURecovers cursors in case of death while compiling  
CXIndex Specific Lock on CTX index  
DBSynchronizes modification of database wide supplementallogging attributes  
DDSynchronizes local accesses to ASM disk groups  
DFEnqueue held by foreground or DBWR when a datafile is brought online in RAC  
DGSynchronizes accesses to ASM disk groups  
DLLock to prevent index DDL during direct load  
DMEnqueue held by foreground or DBWR to syncrhonize database mount/open with other operations  
DNSerializes group number generations  
DORepresents an active disk online operation  
DOSynchronizes Staleness Registry creation  
DOSynchronizes disk onlines and their recovery  
DOSynchronizes startup of MARK process  
DPSynchronizes access to LDAP parameters  
DRSerializes the active distributed recovery operation  
DSPrevents a database suspend during LMON reconfiguration  
DTSerializes changing the default temporary table spaceand user creation  
DVSynchronizes access to lower-version Diana (PL/SQL intermediate representation)  
DWSerialize in memory dispenser operations  
DXSerializes tightly coupled distributed transaction branches  
FASynchronizes accesses to open ASM files  
FBEnsures that only one process can format data blcoks in auto segment space managed tablespaces  
FCLGWR opens an ACD thread  
FCSMON recovers an ACD thread  
FDSynchronization  
FESerializes flashback archive recovery  
FGonly 1 process in the cluster may do ACD relocation in a disk group  
FGresolve race condition to acquire Disk Group Redo Generation Enqueue  
FLEnqueue used to synchronize Flashback Database and and deletion of flashback logs.  
FLSynchronization  
FMSynchronizes access to global file mapping state  
FPSynchronizes various File Object(FOB) operations  
FRbegin recovery of disk group  
FRindicate this ACD thread is alive  
FRwait for lock domain detach  
FSEnqueue used to synchronize recovery and file operations or synchronize dictionary check  
FTallow LGWR to generate redo in this thread  
FTprevent LGWR from generating redo in this thread  
FUThis enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics  
FXARB relocates ACD extent  
HDSerializes accesses to ASM SGA data structures  
HPSynchronizes accesses to queue pages  
HQSynchronizes the creation of new queue IDs  
HVLock used to broker the high water mark during parallel inserts  
HWLock used to broker the high water mark during parallel inserts  
IA   
IDLock held to prevent other processes from performing controlfile transaction while NID is running  
ILSynchronizes accesses to internal label data structures  
IMSerializes block recovery for IMU txn  
IRSynchronizes instance recovery  
IRSynchronizes parallel instance recovery and shutdown immediate  
ISEnqueue used to synchronize instance state changes  
ITSynchronizes accesses to a temp object’s metadata  
JDSynchronizes dates between job queue coordinator and slave processes  
JILock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view  
JQLock to prevent multiple instances from running a single job  
JSLock got during event notification  
JSLock got when adding subscriber to event q  
JSLock got when doing window open/close  
JSLock got when dropping subscriber to event q  
JSLock obtained when cleaning up q memory  
JSLock on internal scheduler queue  
JSLock to prevent job from running elsewhere  
JSLock to recover jobs running on crashed RAC inst  
JSScheduler evt code and AQ sync  
JSScheduler non-global enqueues  
JSSynchronizes accesses to the job cache  
JXstatement  
JXrelease SQL statement resources  
KDDetermine DBRM master  
KMSynchronizes various Resource Manager operations  
KOCoordinates fast object checkpoint  
KPSynchronizes kupp process startup  
KQSynchronization of ASM cached attributes  
KTSynchronizes accesses to the current Resource Manager plan  
MDLock held during materialized view log DDL statements  
MHLock used for recovery when setting Mail Host for AQ e-mail notifications  
MKchanging values in enc$  
MLLock used for recovery when setting Mail Port for AQ e-mail notifications  
MNSynchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session  
MOSerialize MMON operations for restricted sessions  
MRLock used to coordinate media recovery with other uses of datafiles  
MRLock used to disallow concurrent standby role transition attempt  
MSLock held during materialized view refresh to setup MV log  
MVHeld during online datafile move operation or cleanup  
MWThis enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window  
MXLock held to generate a response to the storage server information request when an instance is starting up  
OCSynchronizes write accesses to the outline cache  
ODLock to prevent concurrent online DDLs  
OLSynchronizes accesses to a particular outline name  
OQSynchronizes access to olapi history allocation  
OQSynchronizes access to olapi history closing  
OQSynchronizes access to olapi history flushing  
OQSynchronizes access to olapi history globals  
OQSynchronizes access to olapi history parameter CB  
OTCTX Generic Locks  
OWinitializing the wallet context  
OWterminate the wallet context  
PDPrevents others from updating the same property  
PESynchronizes system parameter updates  
PFSynchronizes accesses to the password file  
PGSynchronizes global system parameter updates  
PHLock used for recovery when setting Proxy for AQ HTTP notifications  
PICommunicates remote Parallel Execution Server Process creation status  
PLCoordinates plug-in operation of transportable tablespaces  
PRSynchronizes process startup  
PSParallel Execution Server Process reservation and synchronization  
PTSynchronizes access to ASM PST metadata  
PVSynchronizes instance shutdown\_slvstart  
PVSynchronizes slave start\_shutdown  
PWDBWR 0 holds enqueue indicating prewarmed buffers present in cache  
PWDirect Load needs to flush prewarmed buffers if DBWR 0 holds enqueue  
RBSerializes ASM rollback recovery operations  
RCCoordinates access to a result-set  
RDupdate RAC load info  
RESynchronize block repair/resilvering operations  
RFCaptures recent Fast-Start Failover Observer heartbeat information  
RFEnsures atomicity of log transport setup  
RFEnsures r/w atomicity of DG configuration metadata  
RFIdentifies which configuration metadata file is current  
RFMeans for detecting when database is being automatically disabled  
RFRecords when FSFO Primary Shutdown is suspended  
RFSynchronizes apply instance failure detection and failover operation  
RFSynchronizes critical apply instance among primary instances  
RFSynchronizes selection of the new apply instance  
RKwallet master key rekey  
RLRAC wallet lock  
RNCoordinates nab computations of online logs during recovery  
ROCoordinates fast object reuse  
ROCoordinates flushing of multiple objects  
RPEnqueue held when resilvering is needed or when datablock is repaired from mirror  
RRConcurrent invocation of DBMS\_WORKLOAD\_\* package API  
RSLock held to make alert level persistent  
RSLock held to prevent aging list update  
RSLock held to prevent deleting file to reclaim space  
RSLock held to prevent file from accessing during space reclaimation  
RSLock held to prevent file from accessing while reusing circular record  
RSLock held to read alert level  
RSLock held to write alert level  
RTThread locks held by CKPT to synchronize thread enable and disable  
RTThread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status  
RUResults of rolling migration CIC  
RUSerializes rolling migration operations  
RWLock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables  
RXSynchronizes relocating ASM extents  
SBSynchronizes Logical Standby metadata operations  
SBSynchronizes table instantiation and EDS operations  
SESynchronizes transparent session migration operations  
SFLock used for recovery when setting Sender for AQ e-mail notifications  
SHShould seldom see this contention as this Enqueue is always  acquired in no-wait mode  
SIPrevents multiple streams tabel instantiations  
SJSerializes cancelling task executed by slave process  
SKSerialize shrink of a segment  
SLsending lock escalate to LCK0  
SLsending lock req for undo to LCK0  
SLsending lock req to LCK0  
SOSynchronizes access to Shared Object (PL/SQL Shared Object Manager)  
SP(1) due to one-off patch  
SP(2) due to one-off patch  
SP(3) due to one-off patch  
SP(4) due to one-off patch  
SQLock to ensure that only one process can replenish the sequence cache  
SRCoordinates replication / streams operations  
SSEnsures that sort segments created during parallel DML operations aren’t prematurely cleaned up  
STSynchronizes space management activities in dictionary-managed tablespaces  
SUSerializes access to SaveUndo Segment  
SWCoordinates the ‘alter system suspend’ operation  
TASerializes operations on undo segments and undo tablespaces  
TBSynchronizes writes to the SQL Tuning Base Existence Cache  
TCLock held to guarantee uniqueness of a tablespace checkpoint  
TCLock of setup of a unqiue tablespace checkpoint in null mode  
TDKTF dumping time/scn mappings in SMON\_SCN\_TIME table  
TEKTF broadcasting  
TFSerializes dropping of a temporary file  
THSerializes threshold in-memory chain access  
TKLock held by MMON to prevent other MMON spawning of Autotask Slave  
TKSerializes spawned Autotask Slaves  
TLSerializes threshold log table read and update  
TMSynchronizes accesses to an object  
TOSynchronizes DDL and DML operations on a temp object  
TPLock held during purge and dynamic reconfiguration of fixed tables.  
TQStreams DDL on queue table  
TQTM access to the queue table  
TSSerializes accesses to temp segments  
TTSerializes DDL operations on tablespaces  
TWLock held by one instance to wait for transactions on all instances to finish  
TXAllocating an ITL entry in order to begin a transaction  
TXLock held by a transaction to allow other transactions to wait for it  
TXLock held on a particular row by a transaction to prevent other transactions from modifying it  
TXLock held on an index during a split to prevent other operations on it  
ULLock used by user applications  
USLock held to perform DDL on the undo segment  
WALock used for recovery when setting Watermark for memory usage in AQ notifications  
WFThis enqueue is used to serialize the flushing of snapshots  
WGacquire lobid local enqueue when deleting fso  
WGacquire lobid local enqueue when locking fso  
WLCoordinates access to redo log files and archive logs  
WLSerialize access to RAC-wide SGA  
WLSerialize access to RFS global state  
WLTesting redo transport access/locking  
WMSynchronizes new WLM Plan activation  
WPThis enqueue handles concurrency between purging and baselines  
WRCoordinates access to logs by Async LNS and ARCH/FG  
XCLock obtained when incrementing XDB configuration version number  
XDSerialize Auto Drop/Add Exadata disk operations  
XDSerialize OFFLINE Exadata disk operations  
XDSerialize ONLINE Exadata disk operations  
XHLock used for recovery when setting No Proxy Domains for AQ HTTP notifications  
XLKeep multiple processes from faulting in the same extent chunk  
XQprevent relocation during \_recovery\_asserts checking  
XQwait for recovery before doing relocation  
XQwait for relocation before doing block purification  
XRLock held during database force logging mode  
XRLock held during database quiesce  
XYLock used for internal testing  
ZAlock held to add partition to std audit table  
ZFlock held to add partition to fga audit table  
ZGCoordinates file group operations  
ZHSynchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load  
ZZlock held for updating global context hash tables