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

  现场系统报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
AB Lock held to ensure that ABMR process is initialized
AB Lock held to ensure that only one ABMR is started in the cluster
AD Synchronizes accesses to a specific ASM disk AU
AE Prevent Dropping an edition in use
AF This enqueue is used to serialize access to an advisor task
AG Synchronizes generation use of a particular workspace
AM ASM group block
AM Allow one ASM password file update per cluster at a time
AM Allow only one AMDU dump when block read failure
AM Block ASM cache freeze
AM Check id1 of call for specific purpose
AM Client group use
AM Prevent DB instance registration during ASM instance shutdown
AM Prevent same file deletion race
AM Prevents a user from being dropped if it owns any open files
AM Registers DB instance to ASM client state object hash
AM Reserve a background COD entry
AM Reserve a rollback COD entry
AM Serializes access to ASM file descriptors
AM Serializes block repairs
AM Start ASM cache freeze
AM Synchronizes disk based allocations/deallocations
AM Synchronizes disk offlines
AO Synchornizes access to objects and scalar variables
AS Synchronizes new service activation
AT Serializes ‘alter tablespace’ operations
AV Serialize inst reg and first DG use
AV Serialize relocating volume extents
AV Serialize taking the AVD DG enqueue
AV prevent DG number collisions
AW Global access synchronization to the AW$ table
AW In-use generation state for a particular workspace
AW Row lock synchronization for the AW$ table
AW Synchronizes user accesses to a particular workspace
AY Affinity Dictionary test affinity synchronization
BB 2PC distributed transaction branch across RAC instances
BF Allocate a bloom filter in a parallel statement
BF PMON bloom filter recovery
BR Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
BR Lock held to perform a new controlfile autobackup
BR Lock held to prevent file from decreasing in physical size during RMAN backup
BR Lock held to prevent multiple process to update the headers at the same time
BR Lock held to request controlfile autobackups
BR Lock held to serialize file header access during multi-section restore
BR Lock held to serialize section access during multi-section restore
CA Synchronizes various IO calibration runs
CF Synchronizes accesses to the controlfile
CI Coordinates cross-instance function invocations
CL Synchronizes accesses to label cache for label comparison
CL Synchronizes accesses to label cache when dropping a label
CM indicate ASM diskgroup is mounted
CM serialize access to instance enqueue
CM serialize asm diskgroup dismount
CN during descriptor initialization
CN during registration
CN during transaction commit to see concurrent registrations
CO enqueue held be Master in Cleanout Optim
CQ Serializes access to cleanup client query cache registrations
CR Coordinates fast block range reuse ckpt
CT Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
CT Lock held during change tracking space management operations that affect just the data for one thread
CT Lock held during change tracking space management operations that affect the entire change tracking file
CT Lock held to ensure that change tracking data remains in existence until a reader is done with it
CT Lock held to ensure that only one CTWR process is started in a single instance
CT Lock held while enabling or disabling change tracking in RAC
CT Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
CU Recovers cursors in case of death while compiling
CX Index Specific Lock on CTX index
DB Synchronizes modification of database wide supplementallogging attributes
DD Synchronizes local accesses to ASM disk groups
DF Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG Synchronizes accesses to ASM disk groups
DL Lock to prevent index DDL during direct load
DM Enqueue held by foreground or DBWR to syncrhonize database mount/open with other operations
DN Serializes group number generations
DO Represents an active disk online operation
DO Synchronizes Staleness Registry creation
DO Synchronizes disk onlines and their recovery
DO Synchronizes startup of MARK process
DP Synchronizes access to LDAP parameters
DR Serializes the active distributed recovery operation
DS Prevents a database suspend during LMON reconfiguration
DT Serializes changing the default temporary table spaceand user creation
DV Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW Serialize in memory dispenser operations
DX Serializes tightly coupled distributed transaction branches
FA Synchronizes accesses to open ASM files
FB Ensures that only one process can format data blcoks in auto segment space managed tablespaces
FC LGWR opens an ACD thread
FC SMON recovers an ACD thread
FD Synchronization
FE Serializes flashback archive recovery
FG only 1 process in the cluster may do ACD relocation in a disk group
FG resolve race condition to acquire Disk Group Redo Generation Enqueue
FL Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
FL Synchronization
FM Synchronizes access to global file mapping state
FP Synchronizes various File Object(FOB) operations
FR begin recovery of disk group
FR indicate this ACD thread is alive
FR wait for lock domain detach
FS Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT allow LGWR to generate redo in this thread
FT prevent LGWR from generating redo in this thread
FU This enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics
FX ARB relocates ACD extent
HD Serializes accesses to ASM SGA data structures
HP Synchronizes accesses to queue pages
HQ Synchronizes the creation of new queue IDs
HV Lock used to broker the high water mark during parallel inserts
HW Lock used to broker the high water mark during parallel inserts
IA  
ID Lock held to prevent other processes from performing controlfile transaction while NID is running
IL Synchronizes accesses to internal label data structures
IM Serializes block recovery for IMU txn
IR Synchronizes instance recovery
IR Synchronizes parallel instance recovery and shutdown immediate
IS Enqueue used to synchronize instance state changes
IT Synchronizes accesses to a temp object’s metadata
JD Synchronizes dates between job queue coordinator and slave processes
JI Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ Lock to prevent multiple instances from running a single job
JS Lock got during event notification
JS Lock got when adding subscriber to event q
JS Lock got when doing window open/close
JS Lock got when dropping subscriber to event q
JS Lock obtained when cleaning up q memory
JS Lock on internal scheduler queue
JS Lock to prevent job from running elsewhere
JS Lock to recover jobs running on crashed RAC inst
JS Scheduler evt code and AQ sync
JS Scheduler non-global enqueues
JS Synchronizes accesses to the job cache
JX statement
JX release SQL statement resources
KD Determine DBRM master
KM Synchronizes various Resource Manager operations
KO Coordinates fast object checkpoint
KP Synchronizes kupp process startup
KQ Synchronization of ASM cached attributes
KT Synchronizes accesses to the current Resource Manager plan
MD Lock held during materialized view log DDL statements
MH Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK changing values in enc$
ML Lock used for recovery when setting Mail Port for AQ e-mail notifications
MN Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO Serialize MMON operations for restricted sessions
MR Lock used to coordinate media recovery with other uses of datafiles
MR Lock used to disallow concurrent standby role transition attempt
MS Lock held during materialized view refresh to setup MV log
MV Held during online datafile move operation or cleanup
MW This enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window
MX Lock held to generate a response to the storage server information request when an instance is starting up
OC Synchronizes write accesses to the outline cache
OD Lock to prevent concurrent online DDLs
OL Synchronizes accesses to a particular outline name
OQ Synchronizes access to olapi history allocation
OQ Synchronizes access to olapi history closing
OQ Synchronizes access to olapi history flushing
OQ Synchronizes access to olapi history globals
OQ Synchronizes access to olapi history parameter CB
OT CTX Generic Locks
OW initializing the wallet context
OW terminate the wallet context
PD Prevents others from updating the same property
PE Synchronizes system parameter updates
PF Synchronizes accesses to the password file
PG Synchronizes global system parameter updates
PH Lock used for recovery when setting Proxy for AQ HTTP notifications
PI Communicates remote Parallel Execution Server Process creation status
PL Coordinates plug-in operation of transportable tablespaces
PR Synchronizes process startup
PS Parallel Execution Server Process reservation and synchronization
PT Synchronizes access to ASM PST metadata
PV Synchronizes instance shutdown_slvstart
PV Synchronizes slave start_shutdown
PW DBWR 0 holds enqueue indicating prewarmed buffers present in cache
PW Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
RB Serializes ASM rollback recovery operations
RC Coordinates access to a result-set
RD update RAC load info
RE Synchronize block repair/resilvering operations
RF Captures recent Fast-Start Failover Observer heartbeat information
RF Ensures atomicity of log transport setup
RF Ensures r/w atomicity of DG configuration metadata
RF Identifies which configuration metadata file is current
RF Means for detecting when database is being automatically disabled
RF Records when FSFO Primary Shutdown is suspended
RF Synchronizes apply instance failure detection and failover operation
RF Synchronizes critical apply instance among primary instances
RF Synchronizes selection of the new apply instance
RK wallet master key rekey
RL RAC wallet lock
RN Coordinates nab computations of online logs during recovery
RO Coordinates fast object reuse
RO Coordinates flushing of multiple objects
RP Enqueue held when resilvering is needed or when datablock is repaired from mirror
RR Concurrent invocation of DBMS_WORKLOAD_* package API
RS Lock held to make alert level persistent
RS Lock held to prevent aging list update
RS Lock held to prevent deleting file to reclaim space
RS Lock held to prevent file from accessing during space reclaimation
RS Lock held to prevent file from accessing while reusing circular record
RS Lock held to read alert level
RS Lock held to write alert level
RT Thread locks held by CKPT to synchronize thread enable and disable
RT Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU Results of rolling migration CIC
RU Serializes rolling migration operations
RW Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
RX Synchronizes relocating ASM extents
SB Synchronizes Logical Standby metadata operations
SB Synchronizes table instantiation and EDS operations
SE Synchronizes transparent session migration operations
SF Lock used for recovery when setting Sender for AQ e-mail notifications
SH Should seldom see this contention as this Enqueue is always  acquired in no-wait mode
SI Prevents multiple streams tabel instantiations
SJ Serializes cancelling task executed by slave process
SK Serialize shrink of a segment
SL sending lock escalate to LCK0
SL sending lock req for undo to LCK0
SL sending lock req to LCK0
SO Synchronizes 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
SQ Lock to ensure that only one process can replenish the sequence cache
SR Coordinates replication / streams operations
SS Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
ST Synchronizes space management activities in dictionary-managed tablespaces
SU Serializes access to SaveUndo Segment
SW Coordinates the ‘alter system suspend’ operation
TA Serializes operations on undo segments and undo tablespaces
TB Synchronizes writes to the SQL Tuning Base Existence Cache
TC Lock held to guarantee uniqueness of a tablespace checkpoint
TC Lock of setup of a unqiue tablespace checkpoint in null mode
TD KTF dumping time/scn mappings in SMON_SCN_TIME table
TE KTF broadcasting
TF Serializes dropping of a temporary file
TH Serializes threshold in-memory chain access
TK Lock held by MMON to prevent other MMON spawning of Autotask Slave
TK Serializes spawned Autotask Slaves
TL Serializes threshold log table read and update
TM Synchronizes accesses to an object
TO Synchronizes DDL and DML operations on a temp object
TP Lock held during purge and dynamic reconfiguration of fixed tables.
TQ Streams DDL on queue table
TQ TM access to the queue table
TS Serializes accesses to temp segments
TT Serializes DDL operations on tablespaces
TW Lock held by one instance to wait for transactions on all instances to finish
TX Allocating an ITL entry in order to begin a transaction
TX Lock held by a transaction to allow other transactions to wait for it
TX Lock held on a particular row by a transaction to prevent other transactions from modifying it
TX Lock held on an index during a split to prevent other operations on it
UL Lock used by user applications
US Lock held to perform DDL on the undo segment
WA Lock used for recovery when setting Watermark for memory usage in AQ notifications
WF This enqueue is used to serialize the flushing of snapshots
WG acquire lobid local enqueue when deleting fso
WG acquire lobid local enqueue when locking fso
WL Coordinates access to redo log files and archive logs
WL Serialize access to RAC-wide SGA
WL Serialize access to RFS global state
WL Testing redo transport access/locking
WM Synchronizes new WLM Plan activation
WP This enqueue handles concurrency between purging and baselines
WR Coordinates access to logs by Async LNS and ARCH/FG
XC Lock obtained when incrementing XDB configuration version number
XD Serialize Auto Drop/Add Exadata disk operations
XD Serialize OFFLINE Exadata disk operations
XD Serialize ONLINE Exadata disk operations
XH Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XL Keep multiple processes from faulting in the same extent chunk
XQ prevent relocation during _recovery_asserts checking
XQ wait for recovery before doing relocation
XQ wait for relocation before doing block purification
XR Lock held during database force logging mode
XR Lock held during database quiesce
XY Lock used for internal testing
ZA lock held to add partition to std audit table
ZF lock held to add partition to fga audit table
ZG Coordinates file group operations
ZH Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
ZZ lock held for updating global context hash tables