[20230510]19c dg無法使用dbms_metadata.get_ddl檢視錶結構定義.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 語法檢視錶結構，索引mysql索引MySql
- 使用dbms_metadata.get_ddl檢視物件的定義語句。物件
- oracle使用dbms_metadata.get_ddl包檢視DDL語句定義Oracle
- Oracle常用檢視錶結構命令Oracle
- [20120307]檢視v$session檢視的定義.txtSession
- 檢視錶、檢視、索引、儲存過程和觸發器的定義的方法索引儲存過程觸發器
- 檢視錶，儲存過程，觸發器定義的方法儲存過程觸發器
- [20240911]檢視超長檢視的定義2.txt
- [20141008]使用bbed檢視索引結構.txt索引
- iovec結構體定義及使用結構體
- 使用treedump事件檢視索引結構事件索引
- MySQL資料庫檢視：檢視定義、建立檢視、修改檢視MySql資料庫
- 【DG】Oracle 19c使用dbca來搭建物理DGOracle
- hacmp無法檢視clstatACM
- 使用man ascii檢視ascii碼錶ASCII
- Dcat Admin::script定義的方法無法使用
- [20170803]如何檢視dg的redo應用率.txt
- 檢視錶大小
- oracle 檢視錶空間使用情況Oracle
- (轉)Sql Server 快速檢視錶結構(表描述及欄位說明)SQLServer
- sql server檢視函式定義SQLServer函式
- 建議開發員少用帶錶連結的檢視(此檢視非物化檢視)
- mysql檢視錶大小MySql
- Oracle檢視錶、儲存過程、觸發器、函式等物件定義語句Oracle儲存過程觸發器函式物件
- 物化檢視job無法執行
- 物化檢視prebuilt和線上重定義UI
- 檢視錶並行度並行
- oracle 檢視錶空間Oracle
- 使用PL/SQL工具比對錶結構,同步表結構SQL
- 利用dbms_metadata.get_ddl檢視DDL語句
- Oracle檢視錶空間大小和使用率Oracle
- 由於物化檢視定義為on commit導致update更新基錶慢的解決方案MIT
- Qt 5模型/檢視結構QT模型
- 檢視ORACLE的使用者對錶的鎖的使用Oracle
- SYS使用者的表無法建立物化檢視日誌
- 【DG】Oracle 19c使用dbca來搭建物理DG--主rac備racOracle
- 分享一個無需定義結構體解析json的包結構體JSON
- [20180814]慎用檢視錶壓縮率指令碼.txt指令碼