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

发布时间：2020-12-12 14:57:37 所属栏目：百科来源：网络整理

导读：现场系统报 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)

现场系统报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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!