[20230510]19c dg無法使用dbms_metadata.get_ddl檢視錶結構定義.txt

lfree發表於2023-05-11

[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2951365/，如需轉載，請註明出處，否則將追究法律責任。

相關文章