[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 檢視錶的定義
- Oracle常用檢視錶結構命令Oracle
- [20240911]檢視超長檢視的定義2.txt
- [20181004]12c dba_source檢視定義.txt
- [20180814]慎用檢視錶壓縮率指令碼.txt指令碼
- 【DG】Oracle 19c使用dbca來搭建物理DGOracle
- Dcat Admin::script定義的方法無法使用
- 檢視錶大小
- [20180503]檢視提示使用索引.txt索引
- [20230510]測試使用tpt ddl指令碼是否產生日誌.txt指令碼
- [20231026]bbed檢視索引kd_off結構的問題.txt索引
- oracle 檢視錶空間Oracle
- 【DG】Oracle 19c使用dbca來搭建物理DG--主rac備racOracle
- 分享一個無需定義結構體解析json的包結構體JSON
- 兩種檢視錶空間使用情況的方法
- Qt 5模型/檢視結構QT模型
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- MySQL檢視錶和清空表的常用命令總結MySql
- Salt Highstate資料結構定義資料結構
- [20190225]測試如何使用dg快速主庫.txt
- Java中常見方法詳解合集（方法的定義及語法結構）Java
- 谷歌工具檢視CSS程式碼定義的位置谷歌CSS
- [20210207]使用gdb檢視等待事件11g.txt事件
- 更新win10 1903後事件檢視器無法使用:MMC無法建立管理單元怎麼辦Win10事件
- 檢視錶名和表的行數
- gdb golang 檢視iface 內部結構Golang
- Linux 下樹形結構的檢視Linux
- 資料結構之跳錶資料結構
- 使用檢視格式化來自定義SharePoint
- lua語法-變數的定義與使用變數
- SQL Server 檢視錶佔用空間大小SQLServer
- oracle 19c 無法create table解決Oracle
- 自定義檢視指令
- AndroidStudio資料夾結構檢視講解Android
- [20190320]關於使用smem檢視記憶體使用的問題.txt記憶體
- DG日常檢查命令
- Oracle DG 日常點檢Oracle
- 如何檢視錶中的二進位制流