[20230510]19c dg无法使用dbms_metadata.get_ddl查看表结构定义.txt

来源：这里教程网时间：2026-03-03 18:47:56 作者：

[20230510]19c dg无法使用dbms_metadata.get_ddl查看表结构定义.txt --//工作中发现的问题,使用tpt ddl在dg中无法查看表结构定义. 1.环境: SYS@192.168.100.237:1521/orcldg> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试: SYS@192.168.100.237:1521/orcldg> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual; ERROR: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.DBMS_METADATA", line 6731 ORA-06512: at "SYS.DBMS_METADATA", line 6516 ORA-06512: at "SYS.DBMS_LOCK", line 378 ORA-06512: at "SYS.DBMS_LOCK", line 411 ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1738 ORA-06512: at "SYS.DBMS_METADATA", line 1216 ORA-06512: at "SYS.DBMS_METADATA", line 1314 ORA-06512: at "SYS.DBMS_METADATA", line 6439 ORA-06512: at "SYS.DBMS_METADATA", line 6572 ORA-06512: at "SYS.DBMS_METADATA", line 9734 ORA-06512: at line 1 no rows selected 3.我找了一台11g的dg测试: SYS@192.168.100.76:1521/dbcndg> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@192.168.100.76:1521/dbcndg> select open_mode from v$database ; OPEN_MODE -------------------- READ ONLY WITH APPLY SYS@192.168.100.76:1521/dbcndg> @ ddl sys.dual C300 ---------------------------------------------------------------------------------- CREATE TABLE "SYS"."DUAL" ( "DUMMY" VARCHAR2(1) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" ; --//也没有任何问题.按照提示视乎与pdb有关.留待以后分析. 4.跟踪看看: SYS@192.168.100.237:1521/orcldg> @ 10046on 12 Session altered. SYS@192.168.100.237:1521/orcldg> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual; ERROR: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.DBMS_METADATA", line 6731 ORA-06512: at "SYS.DBMS_METADATA", line 6516 ORA-06512: at "SYS.DBMS_LOCK", line 378 ORA-06512: at "SYS.DBMS_LOCK", line 411 ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1738 ORA-06512: at "SYS.DBMS_METADATA", line 1216 ORA-06512: at "SYS.DBMS_METADATA", line 1314 ORA-06512: at "SYS.DBMS_METADATA", line 6439 ORA-06512: at "SYS.DBMS_METADATA", line 6572 ORA-06512: at "SYS.DBMS_METADATA", line 9734 ORA-06512: at line 1 no rows selected SYS@192.168.100.237:1521/orcldg> @ 10046off Session altered. $ grep -i update /u01/app/oracle/diag/rdbms/orcldg/orcldg/trace/orcldg_ora_23616.trc select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread,ts.im_imcu_count,ts.im_block_count,ts.im_sys_incarnation,ts.im_stat_update_time,ts.scanrate,nvl(t.acdrflags, 0),nvl(t.acdrtsobj#, 0),t.acdrdefaulttime, nvl(t.acdrrowtsintcol#, 0) from tab$ t,tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, DEFAULT_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISPDB_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH, CON_ID from GV$PARAMETER where inst_id = USERENV('Instance') SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE --//有一条for update的select导致报错. --//如果是正常数据库的跟踪情况如下: $ grep -i update /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_72364.trc select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread,ts.im_imcu_count,ts.im_block_count,ts.im_sys_incarnation,ts.im_stat_update_time,ts.scanrate,nvl(t.acdrflags, 0),nvl(t.acdrtsobj#, 0),t.acdrdefaulttime, nvl(t.acdrrowtsintcol#, 0) from tab$ t,tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE value="ORA$KU$DATAPUMP_SW_UPDATE" STAT #140008605472584 id=1 cnt=1 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=2 pr=2 pw=0 str=1 time=15261 us)' UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400) WHERE ROWID = :B2 STAT #140008605711352 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE DBMS_LOCK_ALLOCATED (cr=1 pr=1 pw=0 str=1 time=261 us)' UPDATE SYS.KU_UTLUSE SET USECNT = USECNT + 1, ENCRYPTCNT = ENCRYPTCNT + :1, ENCRYPT128 = ENCRYPT128 + :2, ENCRYPT192 = ENCRYPT192 + :3, ENCRYPT256 = ENCRYPT256 + :4, ENCRYPTPWD = ENCRYPTPWD + :5, ENCRYPTDUAL = ENCRYPTDUAL + :6, ENCRYPTTRAN = ENCRYPTTRAN + :7, COMPRESSCNT = COMPRESSCNT + :8, COMPRESSBAS = COMPRESSBAS + :9, COMPRESSLOW = COMPRESSLOW + :10, COMPRESSMED = COMPRESSMED + :11, COMPRESSHGH = COMPRESSHGH + :12, PARALLELCNT = PARALLELCNT + :13, FULLTTSCNT = FULLTTSCNT + :14, LAST_USED = CURRENT_TIMESTAMP WHERE UTLNAME = :15 STAT #140008605491640 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE KU_UTLUSE (cr=2 pr=1 pw=0 str=1 time=14501 us)' --//SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE语句带入的绑定变量值是ORA$KU$DATAPUMP_SW_UPDATE. SYS@192.168.100.235:1521/orcl> SELECT * FROM DBMS_LOCK_ALLOCATED WHERE NAME = 'ORA$KU$DATAPUMP_SW_UPDATE'; NAME LOCKID EXPIRATION ---------------------------------------- ---------- ------------------- ORA$KU$DATAPUMP_SW_UPDATE 1073741848 2023-05-21 08:50:27 --//模拟看看: --//session 1: SYS@192.168.100.235:1521/orcl> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 2293 19349 43462 DEDICATED 72364 40 14507 alter system kill session '2293,19349' immediate; SYS@192.168.100.235:1521/orcl> SELECT * FROM DBMS_LOCK_ALLOCATED WHERE NAME = 'ORA$KU$DATAPUMP_SW_UPDATE' for update; NAME LOCKID EXPIRATION ---------------------------------------- ---------- ------------------- ORA$KU$DATAPUMP_SW_UPDATE 1073741848 2023-05-21 08:50:27 --//session 2: SYS@192.168.100.235:1521/orcl> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual; --//挂起. -- Display ASH Wait Chain Signatures script v0.7 by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN FIRST_SEEN LAST_SEEN ------ ---------- ---------- -------------------------------------------------------------------------------------------------------------------------- ------------------- ------------------- 57% 60 1 -> 2293,19349,@1=>288,5239,@1=>enq: TX - row lock contention -> [idle blocker 1,2293,19349 (sqlplus@gxqyydg4 (TNS V1-V3))] 2023-05-11 08:56:01 2023-05-11 08:57:00 --//sid=2293阻塞了sid=288. --//session 1: SYS@192.168.100.235:1521/orcl> commit ; Commit complete. --//session 2: SYS@192.168.100.235:1521/orcl> select dbms_metadata.get_ddl('TABLE','DUAL','SYS') from dual; DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS') ------------------------------------------------------------------------ CREATE TABLE "SYS"."DUAL" SHARING=METADATA ( "DUMMY" VARCHAR2(1) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM" --//执行成功!! SYS@192.168.100.235:1521/orcl> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 288 5239 43493 DEDICATED 74547 129 28604 alter system kill session '288,5239' immediate;

编辑推荐：

返回列表

[20230510]19c dg无法使用dbms_metadata.get_ddl查看表结构定义.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推