[20250514]21c使用dbms_metadata.get_ddl参看临时表定义问题(整理).txt --//前段时间在自己的虚拟机器测试遇到的问题，发现在21c下使用dbms_metadata.get_ddl参看临时表定义，会报ora-04031错误，当时 --//没有做的笔记很乱，重新整理记录如下： 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.问题： --//重启数据库执行如下： SCOTT@book01p> drop table gtt purge ; Table dropped. SCOTT@book01p> create global temporary table gtt ( x int, y int ) on commit preserve rows; Table created. SCOTT@book01p> @ tpt/ddl dept PL/SQL procedure successfully completed. DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER) ----------------------------------------------------------------------- CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13), CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; --//查看普通表正常。 SYS@book> @ sgastatx % -- All allocations: SUBPOOL BYTES MB ------------------------------ ---------- ---------- shared pool (1): 360710144 344 shared pool (Total): 360710144 344 -- Allocations matching "%": SUBPOOL NAME SUM(BYTES) MB ------------------------------ ------------------------------ ---------- ---------- shared pool (1): SQLA 36589544 34.89 free memory 27367432 26.1 KGLH0 23382328 22.3 ksunfy_sess_meta 1 16160256 15.41 SO private sga 11893720 11.34 row cache mutex 9897624 9.44 ASH buffers 8388608 8 private strands 7490560 7.14 KQR X PO 7036496 6.71 KGLS 6650296 6.34 .... SCOTT@book01p> @ tpt/ddl gtt PL/SQL procedure successfully completed. ERROR: ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^b183d1c6","ub1[]: qkexrXformVal") ORA-06512: at "SYS.DBMS_METADATA", line 6781 ORA-06512: at "SYS.DBMS_METADATA", line 2989 ORA-06512: at "SYS.DBMS_METADATA", line 3616 ORA-06512: at "SYS.DBMS_METADATA", line 5058 ORA-06512: at "SYS.DBMS_METADATA", line 5386 ORA-06512: at "SYS.DBMS_METADATA", line 6752 ORA-06512: at "SYS.DBMS_METADATA", line 9815 ORA-06512: at line 1 no rows selected SYS@book> @ sgastatx % -- All allocations: SUBPOOL BYTES MB ------------------------------ ---------- ---------- shared pool (1): 360738592 344.03 shared pool (Total): 360738592 344.03 -- Allocations matching "%": SUBPOOL NAME SUM(BYTES) MB ------------------------------ ------------------------------ ---------- ---------- shared pool (1): free memory 57535160 54.87 XDBSC 17503520 16.69 ksunfy_sess_meta 1 16160256 15.41 SQLA 14519560 13.85 SO private sga 11893720 11.34 KGLH0 11654768 11.11 row cache mutex 9897624 9.44 ASH buffers 8388608 8 private strands 7490560 7.14 row cache hash 6598416 6.29 KGLSG 6309848 6.02 KGLS 6156488 5.87 --//可以发现执行ddl.sql脚本查看临时表定义，XDBSC组件占用很高的内存，实际占用也不大，但是我的测试环境sga --//太小了，导致ora-04031出现问题。 SYS@book> @ ashtop event 1=1 &1min Total Distinct Distinct Distinct Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1 --------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- ----------- 62 1.0 60% | 2025-05-09 11:35:15 2025-05-09 11:36:10 3 43 44 25 .4 24% | SGA: allocation forcing component growth 2025-05-09 11:35:45 2025-05-09 11:36:03 1 15 15 9 .2 9% | row cache lock 2025-05-09 11:35:52 2025-05-09 11:36:03 1 9 9 2 .0 2% | db file async I/O submit 2025-05-09 11:36:07 2025-05-09 11:36:10 1 2 2 2 .0 2% | latch: shared pool 2025-05-09 11:35:49 2025-05-09 11:35:50 1 2 2 1 .0 1% | ADR block file write 2025-05-09 11:35:56 2025-05-09 11:35:56 1 1 1 1 .0 1% | kksfbc child completion 2025-05-09 11:36:07 2025-05-09 11:36:07 1 1 1 1 .0 1% | log buffer space 2025-05-09 11:36:03 2025-05-09 11:36:03 1 1 1 1 .0 1% | rdbms ipc message 2025-05-09 11:35:59 2025-05-09 11:35:59 1 1 1 9 rows selected. --//sga的shared_pool_size增加导致整个数据库临时挂起，而且一旦出现ora-04031错误，基本经常报这个错误。 --//在cdb层面执行也是类似的情况出现： SYS@book> @ tpt/ddl plan_table$ PL/SQL procedure successfully completed. ERROR: ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select o.owner#,o.name,o.nam...","SQLA","tmp") ORA-06512: at "SYS.DBMS_METADATA", line 6781 ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^b183d1c6","kobjn : kkdcchs") ORA-06512: at "SYS.DBMS_METADATA", line 2989 ORA-06512: at "SYS.DBMS_METADATA", line 3616 ORA-06512: at "SYS.DBMS_METADATA", line 5058 ORA-06512: at "SYS.DBMS_METADATA", line 5386 ORA-06512: at "SYS.DBMS_METADATA", line 6752 ORA-06512: at "SYS.DBMS_METADATA", line 9815 ORA-06512: at line 1 no rows selected --//我重启数据库后执行@ tpt/ddl dept后，再次执行： SYS@book> @ sgastatx XDBSC -- All allocations: SUBPOOL BYTES MB ------------------------------ ---------- ---------- shared pool (1): 360710144 344 shared pool (Total): 360710144 344 -- Allocations matching "XDBSC": no rows selected --//没有该组件出现。 --//另外我在11g做相同的测试： SYS@book> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 11.2.0.4.0 BANNER : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL procedure successfully completed. SYS@book> @ sgastatx XDBSC -- All allocations: SUBPOOL BYTES MB ------------------------------ ---------- ---------- shared pool (0 - Unused): 25165824 24 shared pool (1): 171966464 164 shared pool (Total): 197132288 188 -- Allocations matching "XDBSC": no rows selected --//无论如何都没有该XDBSC组件出现。 3.分析： --//猜测一下xdb 表示XML db的信息，sc表示Secure Connection。为什么出现这个问题不是很清楚。 --//仔细看报错,应该执行该语句时报错： ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^b183d1c6","kobjn : kkdcchs") --//似乎也验证是XML的某一些东西。SQLA^b183d1c6中^后出现的16进制表示hash_value. --//0xb183d1c6 = 2978206150 SYS@book> @ sharepool/shp4 '' 2978206150 HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 00000000663FEDD0 0000000067472DE8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 2 00 00 0 0 3606 3606 3606 2978206150 7nfwwwfss7nf6 0 parent handle address 0000000067472DE8 0000000067472DE8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 2 00000000674767B8 00 4064 0 0 4064 4064 2978206150 7nfwwwfss7nf6 65535 --//子游标没有堆0，堆6信息，根本没有分配内存空间。 SYS@book> @ sql_id 7nfwwwfss7nf6 -- SQL_ID = 7nfwwwfss7nf6 come from x$kglob SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192) = 8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128) != 0 AND KU$.SCHEMA_OBJ.NAME = :NAME1 AND KU$.SCHEMA_OBJ.OWNER_NAME = :SCHEMA2; --//注：做了格式化处理。 SYS@book> select sql_text from v$sql where sql_id='7nfwwwfss7nf6'; no rows selected SYS@book> @ bind_cap 7nfwwwfss7nf6 '' no rows selected --//仅仅在x$kglob中看到，似乎在分析该语句阶段就报错了，根本无法执行。 --//删除后面的赋值手工执行看看。 SYS@book> SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0; SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 * ERROR at line 1: ORA-04031: unable to allocate 104 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^c0cc1c5e","kgmtyp : kgmgchd") --//手工执行如下通过： SYS@book> SELECT /*+all_rows*/ KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0; --//输出略。这样问题就集中在SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7'))上，不熟悉这些东西。 --//要么就是这个组件消耗很大的共享池内存，测试环境配置太低了，下午继续测试看看。 4.继续： --//下午继续，我的测试环境设置shared_pool_size，db_cache_size的大小，也就是扩展余地很小，这也是该参数的最小大小。 --//尝试取消db_cache_size设置看看。 SYS@book> show parameter sga_target PARAMETER_NAME TYPE VALUE -------------- ----------- ------ sga_target big integer 768M SYS@book> show parameter shared_pool_size PARAMETER_NAME TYPE VALUE ---------------- ----------- ----- shared_pool_size big integer 344M SYS@book> show parameter db_cache_size PARAMETER_NAME TYPE VALUE -------------- ----------- ------ db_cache_size big integer 404M SYS@book> alter system reset db_cache_size; System altered. --//重启数据库。 SCOTT@book01p> @ tpt/ddl gtt PL/SQL procedure successfully completed. DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER) ---------------------------------------------------- CREATE GLOBAL TEMPORARY TABLE "SCOTT"."GTT" ( "X" NUMBER(*,0), "Y" NUMBER(*,0) ) ON COMMIT PRESERVE ROWS ; --//OK,没有问题。 SYS@book> @ sgastatx XDBSC -- All allocations: SUBPOOL BYTES MB ------------------------------ ---------- ---------- shared pool (1): 381681664 364 shared pool (Total): 381681664 364 -- Allocations matching "XDBSC": SUBPOOL NAME SUM(BYTES) MB ---------------- ----- ---------- ---------- shared pool (1): XDBSC 17446584 16.64 SYS@book> @ hidez ^__shared_pool_size$ SYS@book> @ pr ============================== NUM : 293 N_HEX : 125 CON_ID : 0 NAME : __shared_pool_size DESCRIPTION : Actual size in bytes of shared pool DEFAULT_VALUE : FALSE SESSION_VALUE : 381681664 SYSTEM_VALUE : 381681664 ISSES_MODIFIABLE : FALSE ISSYS_MODIFIABLE : IMMEDIATE PL/SQL procedure successfully completed. SYS@book> @ calcx 377487360/1024/1024 DEC HEX ---------- -------------------- 360.000000 0000000000000168 --//增加16M比前面的344M。 --//这样修改设置如下： --//设置 sga_target 768+16 = 784M，shared_pool_size = 360M SYS@book> alter system set sga_target=800M scope=spfile; System altered. SYS@book> alter system set shared_pool_size=360M scope=spfile; System altered. SYS@book> alter system set db_cache_size=404M scope=spfile; System altered. --//404+360 = 764M,剩下800-764 = 36m。 SYS@book> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. --//开启数据库马上基本不再出现前面的问题，虽然很慢。 SYS@book> @ sharepool/shp4 '' b183d1c6 HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address 0000000068659BD8 000000006868F1A8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 0 000000006866CBC8 000000006861E638 52608 48961560 3606 49017774 49017774 2978206150 7nfwwwfss7nf6 0 parent handle address 000000006868F1A8 000000006868F1A8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 0 0000000068643268 00 4064 0 0 4064 4064 2978206150 7nfwwwfss7nf6 65535 --//堆6占用很大 48961560/1024/1024 = 46.70M.并且再次查看马上释放)。 SYS@book> @ ksmsp 000000006861E638 '' LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 HEAP_DESC --- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- ------------------------- .... SGA 00000000786BFDA8 1 4 SQLA^b183d1c6 4176 freeabl 0 000000006861E638 00000000786BFDA8 00000000786C0DF8 KSMCHPAR=000000006861E638 SGA 00000000786BED58 1 4 SQLA^b183d1c6 4176 freeabl 0 000000006861E638 00000000786BED58 00000000786BFDA8 KSMCHPAR=000000006861E638 SGA 00000000786BDD58 1 4 SQLA^b183d1c6 4096 freeabl 0 000000006861E638 00000000786BDD58 00000000786BED58 KSMCHPAR=000000006861E638 SGA 000000006861DDE0 1 1 KGLH0^b183d1c6 4096 recr 4095 0000000068643268 000000006861DDE0 000000006861EDE0 12009 rows selected. --//怪不得这么容易报ora-4031错误，分析执行计划就占用许多时间，占用太多的chunk。 --//实际上即使以上设置，使用一段时间再次执行还是很容易出现ora-4031错误。可以看出21c不能设置shared_pool_size太小，建立的 --//chunk比11g多许多，缺省_column_tracking_level=53，也导致问题加剧。总而言之，还是测试环境sga_target设置太小，适当做一 --//些调整，注意由于采用hugepages，适当修改内核参数vm.nr_hugepages满足需要。 --//最后设置调整如下： SYS@book> alter system set sga_target=1028M scope=spfile ; System altered. --//注：原来设置1024M报错，要求设置1028.只能修改设置1028M，原因是大于1024时Granule Size 从4M变成16M。 --//db_cache_size=404M，实际上设置的是416M。 SYS@book> alter system set large_pool_size=64M scope=spfile ; System altered. SYS@book> alter system set memoptimize_pool_size=64M scope=spfile ; System altered. SYS@book> alter system set db_cache_size=404M scope=spfile ; System altered. SYS@book> alter system set shared_pool_size=480M scope=spfile ; System altered. SYS@book> show parameter db_cache_size PARAMETER_NAME TYPE VALUE -------------- ----------- ----- db_cache_size big integer 416M SYS@book> @ hidez ^sga_target$|^shared_pool_size$|^db_cache_size$|^memoptimize_pool_size$|^large_pool_size$ NUM N_HEX CON_ID NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ---- ----- ---------- --------------------- ----------------------------------------------------------- ------------- ------------- ------------ ----- --------- 294 126 0 shared_pool_size size in bytes of shared pool FALSE 503316480 503316480 FALSE IMMEDIATE 294 126 2 shared_pool_size size in bytes of shared pool FALSE 503316480 0 FALSE IMMEDIATE 294 126 3 shared_pool_size size in bytes of shared pool FALSE 503316480 0 FALSE IMMEDIATE 296 128 0 large_pool_size size in bytes of large pool FALSE 67108864 67108864 FALSE IMMEDIATE 1797 705 0 sga_target Target size of SGA FALSE 1090519040 1090519040 FALSE IMMEDIATE 1797 705 2 sga_target Target size of SGA FALSE 1090519040 0 FALSE IMMEDIATE 1797 705 3 sga_target Target size of SGA FALSE 1090519040 0 FALSE IMMEDIATE 1876 754 0 db_cache_size Size of DEFAULT buffer pool for standard block size buffers FALSE 436207616 436207616 FALSE IMMEDIATE 1876 754 2 db_cache_size Size of DEFAULT buffer pool for standard block size buffers FALSE 436207616 0 FALSE IMMEDIATE 1876 754 3 db_cache_size Size of DEFAULT buffer pool for standard block size buffers FALSE 436207616 0 FALSE IMMEDIATE 1885 75D 0 memoptimize_pool_size Size of cache for imoltp buffers FALSE 67108864 67108864 FALSE IMMEDIATE 11 rows selected.
[20250514]21c使用dbms_metadata.get_ddl参看临时表定义问题(整理).txt
来源：这里教程网
时间：2026-03-03 21:56:58
作者：
编辑推荐：
- [20250514]21c使用dbms_metadata.get_ddl参看临时表定义问题(整理).txt03-03
- 大表归档，要注意哪些坑？稍有不慎造成业务宕机！03-03
- 京东敢烧钱做外卖，原来是因为电商赚麻了03-03
- [20250514]truncare table相关数据段的确定与恢复.txt03-03
- [20250515]drop table相关数据段的确定与恢复.txt03-03
- [20250515]删除在Pluggable Database设置的参数.txt03-03
- 深入浅出 Rust 并发：RwLock 与 Mutex 在 Tauri 项目中的实践03-03
- 创新靶点与双机制策略崛起，AACR揭幕ADC治疗新纪元03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 京东敢烧钱做外卖，原来是因为电商赚麻了
京东敢烧钱做外卖，原来是因为电商赚麻了
26-03-03 - 深入浅出 Rust 并发：RwLock 与 Mutex 在 Tauri 项目中的实践
- 创新靶点与双机制策略崛起，AACR揭幕ADC治疗新纪元
创新靶点与双机制策略崛起，AACR揭幕ADC治疗新纪元
26-03-03 - Robotaxi新消息密集释放，量产元年来临谁在领跑？
Robotaxi新消息密集释放，量产元年来临谁在领跑？
26-03-03 - Oracle集群+ACFS竟让OGG故障率归零，太不可思议了！
Oracle集群+ACFS竟让OGG故障率归零，太不可思议了！
26-03-03 - 刘强东上街送外卖，美团王兴还睡得着吗？
刘强东上街送外卖，美团王兴还睡得着吗？
26-03-03 - 【KWDB 创作者计划】_KWDB引领数据库技术革新的璀璨之星
【KWDB 创作者计划】_KWDB引领数据库技术革新的璀璨之星
26-03-03 - 全娱乐生态驱动增长，从财报看阿里影业穿越周期的发展韧性
全娱乐生态驱动增长，从财报看阿里影业穿越周期的发展韧性
26-03-03 - 被问能不能不还钱，周鸿祎称360借条“不是我公司”！真相来了
被问能不能不还钱，周鸿祎称360借条“不是我公司”！真相来了
26-03-03 - 通用SQL优化经典等价改写【三】——插入提速
通用SQL优化经典等价改写【三】——插入提速
26-03-03