ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20230510]19c dg无法使用dbms_metadata.get_ddl查看表结构定义.txt

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

原创 Oracle 作者：lfree 时间：2023-05-11 09:19:30 0 删除 编辑

[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;

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2951365/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： [20230508]能否一行完成(使用tee小技巧).txt
下一篇： [20230510]测试使用tpt ddl脚本是否产生日志.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7970810

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593