版本压缩(Compress)提示"ORA-00060: deadlock detected while waiting for resource".
来源:互联网 发布:隐秘录像 知乎 编辑:程序博客网 时间:2024/05/22 06:53
当我们对版本数据进行Compress操作时,可能会碰到Oracle的错误:"ORA-00060: deadlock detected while waiting for resource".
如果在压缩过程中遇到的 Oracle 错误,当前的死锁类型就是两个sessions请求相同的行对象以竞争/阻止的请求进行更新或删除的操作。
该错误通常只表现在大量用户同时编辑受版本控制的地理数据库时执行压缩时。
要解决死锁错误,SDE 用户必须重新创建 state_lineage 表的索引或表和增加对象块的标头中的初始事务插槽数的值(initrans )
默认情况下,ArcGIS为版本控制创建的所有表和索引设置为 4 。initrans控制根据关键字 DATA_DICTIONARY,dbtune.sde 文件中,但只引用最初创建实例时。
##DATA_DICTIONARYATTRIBUTE_BINARY"BLOB"B_STORAGE "PCTFREE 0 INITRANS 4 # TABLESPACE <Business table tablespace name> STORAGE (INITIAL 40K)"B_INDEX_ROWID "PCTFREE 0 INITRANS 4# TABLESPACE <Data_dictionary index tablespace name> STORAGE (INITIAL 40K) NOLOGGING"B_INDEX_USER "PCTFREE 0 INITRANS 4 # TABLESPACE <Data_dictionary index tablespace name> STORAGE (INITIAL 40K) NOLOGGING"STATES_TABLE "INITRANS 4# TABLESPACE <STATES table tablespace name> STORAGE (INITIAL 1M)"STATES_INDEX "INITRANS 5# TABLESPACE <STATES table's index tablespace name> STORAGE (INITIAL 128K) NOLOGGING"STATE_LINEAGES_TABLE "PCTFREE 0 INITRANS 4# TABLESPACE <STATE_LINEAGES(IOT) table's index tablespace name> STORAGE (INITIAL 7M)"STATE_LINEAGES_INDEX "PCTFREE 0 INITRANS 4# TABLESPACE <table's index tablespace name> STORAGE (INITIAL 5M) NOLOGGING"VERSIONS_TABLE "INITRANS 4# TABLESPACE <VERSIONS table tablespace name> STORAGE (INITIAL 256K)"VERSIONS_INDEX "INITRANS 4# TABLESPACE <VERSIONS table's index tablespace name> STORAGE (INITIAL 128K) NOLOGGING"MVTABLES_MODIFIED_TABLE "INITRANS 4# TABLESPACE <MVTABLES_MODIFIED table tablespace name> STORAGE (INITIAL 2M)"MVTABLES_MODIFIED_INDEX "INITRANS 4 # TABLESPACE <MVTABLES_MODIFIED table's index tablespace name> STORAGE (INITIAL 2M) NOLOGGING"XML_INDEX_TAGS_TABLE "INITRANS 4# TABLESPACE <XML_INDEX_TAGS table tablespace name> STORAGE (INITIAL 1M)"XML_INDEX_TAGS_INDEX "INITRANS 5# TABLESPACE <XML_INDEX_TAGS table's index tablespace name> STORAGE (INITIAL 1M)"END我们查看STATE_LINAGES表的索引对象
SQL> select index_name from user_indexes where table_name='STATE_LINEAGES';INDEX_NAME------------------------------LINEAGES_PKLINEAGE_ID_IDX2查看两个对象的定义
SQL> select dbms_metadata.get_ddl('INDEX','LINEAGES_PK','SDE') from dual;DBMS_METADATA.GET_DDL('INDEX','LINEAGES_PK','SDE')-------------------------------------------------------------------------------- CREATE UNIQUE INDEX "SDE"."LINEAGES_PK" ON "SDE"."STATE_LINEAGES" ("LINEAGE_NAME", "LINEAGE_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SDE"SQL> select dbms_metadata.get_ddl('INDEX','LINEAGE_ID_IDX2','SDE') from dual;DBMS_METADATA.GET_DDL('INDEX','LINEAGE_ID_IDX2','SDE')-------------------------------------------------------------------------------- CREATE INDEX "SDE"."LINEAGE_ID_IDX2" ON "SDE"."STATE_LINEAGES" ("LINEAGE_ID") PCTFREE 0 INITRANS 4 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS STORAGE(INITIAL 5242880 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SDE"根据上面所述,增加索引的INITRANS值
SQL> ALTER INDEX lineages_pk REBUILD INITRANS 8;Index altered.SQL> ALTER INDEX lineages_id_idx2 REBUILD INITRANS 8;Index altered.修改表的INITRANS值
SQL> ALTER TABLE state_lineages INITRANS 8;Table altered.更改现有数据块不会改变表的限制,但只有当新块将被添加为表越大。如果修改表并不能解决死锁问题,那么需要将表本身导出、 删除,和重新创建。它还将要求回新表设置较大的initrans 值分布和有导入的行。
initrans 8 已经足够大了,但是在有很多的并发编辑的情况下,值等于 8 仍可能不能满足。Oracle DBA 应该监视实例和条件搜索阻止等待 ITL 插槽的频率(The Oracle DBA should monitor the instance and search for the frequency of blocking conditions waiting for ITL slots.)。如果是常见的等待事件,然后值应进一步增加。有关如何监视阻塞条件的说明,请参阅 Oracle 文档。
- 版本压缩(Compress)提示"ORA-00060: deadlock detected while waiting for resource".
- ORACLE位图索引导致的ORA-00060: deadlock detected while waiting for resource
- OERR: ORA 60 "deadlock detected while waiting for resource" [ID 18251.1](oracle deadlock 学习资料-03 死锁分析步骤)
- 在多session向表中加载数据时候,个别session报ORA-00060: deadlock detected while waiting for resource
- ORA-00060 Deadlock detected
- DEADLOCK DETECTED ( ORA-00060 )
- ORA-00060: Deadlock detected
- ORA-00060: Deadlock detected
- ORA-00060 Deadlock detected 解读
- DEADLOCK DETECTED (ORA-00060)Trace实例分析
- 解决 ORA-00060: Deadlock detected 小例
- ORA-04020: deadlock detected while trying to lock object 报错问题解决步骤
- ArcGIS版本压缩(Compress)报ORA-00001: unique constraint 的解决方法
- ArcSDE版本压缩(compress)(3)
- ArcSDE版本压缩(Compress)(2)
- ORA-000060: Deadlock detected: deadlock引起数据库挂死
- Oracle 9i 升级中的bug-- Sys.Cdc_alter_ctable_before ORA-04020 deadlock detected while trying to lock object
- What to do with "ORA-60 Deadlock Detected" Errors [ID 62365.1](oracle deadlock 学习资料-05.1)
- 侃一侃vc的std::string
- HDU 2955
- C#之HelloWorld,防止黑屏一闪而过
- android webview goBack不起作用的解决方法
- Hadoop回收站trash-恢复删除的文件
- 版本压缩(Compress)提示"ORA-00060: deadlock detected while waiting for resource".
- Java堆内存的10个要点
- VisualDSP++ 5.0 无法创建LDF文件
- QTP10.0破解
- Ruby学习过程中积累下来的测试代码
- JQMobile(Jquery一样能用)中如何获得远程的json数据并绑定
- 给Android的音量按钮添加监听事件
- Android自动化测试初探: 捕获Activity上的Element
- 六月新版微软一站式示例代码库发布 - 新增20个Windows示例代码 .