[20240826]奇怪ORA-01031 insufficient privileges報錯.txt

[20240826]奇怪ORA-01031 insufficient privileges報錯.txt

--//遇到的奇怪ORA-01031 insufficient privileges情況,做一個分析:

1.環境:
SYS@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.

2.測試:
SYS@book01p> select count(1) from hist_head$ ;
COUNT(1)
----------
50435
--//沒有任何問題.

SYS@book01p> select * from hist_head$ ;
select * from hist_head$
*
ERROR at line 1:
ORA-01031: insufficient privileges

--//換成*報錯,感覺是否表出了問題,因為前面透過索引就可以完成執行計劃.

SYS@book01p> select /*+ full(hist_head$) */ count(1) from hist_head$ ;

COUNT(1)
----------
50435

SYS@book01p> select /*+ index(hist_head$) */ * from hist_head$ where rownum=1;
select /*+ index(hist_head$) */ * from hist_head$ where rownum=1
*
ERROR at line 1:
ORA-01031: insufficient privileges
--//仔細看*位置執行hist_head$,仔細看拼寫也沒有錯誤啊.

SYS@book01p> @ desc hist_head$
Name Null? Type
------------------------------- -------- ----------------------------
1 OBJ# NOT NULL NUMBER
2 COL# NOT NULL NUMBER
3 BUCKET_CNT NOT NULL NUMBER
4 ROW_CNT NOT NULL NUMBER
5 CACHE_CNT NUMBER
6 NULL_CNT NUMBER
7 TIMESTAMP# DATE
8 SAMPLE_SIZE NUMBER
9 MINIMUM NUMBER
10 MAXIMUM NUMBER
11 DISTCNT NUMBER
12 LOWVAL RAW(1000)
13 HIVAL RAW(1000)
14 DENSITY NUMBER
15 INTCOL# NOT NULL NUMBER
16 SPARE1 NUMBER
17 SPARE2 NUMBER
18 AVGCLN NUMBER
19 SPARE3 NUMBER
20 SPARE4 NUMBER
21 MINIMUM_ENC RAW(1000)
22 MAXIMUM_ENC RAW(1000)
--//有4個raw型別.難道是不能訪問raw型別嗎?

SYS@book01p> select DENSITY,AVGCLN from hist_head$ where rownum=1;
DENSITY AVGCLN
---------- ----------
0 0

--//這樣看視乎是裡面某個訪問某個欄位時報錯.5個5個一組嘗試.

SYS@book01p> select OBJ#,COL#,BUCKET_CNT,ROW_CNT,CACHE_CNT from hist_head$ where rownum=1;

OBJ# COL# BUCKET_CNT ROW_CNT CACHE_CNT
---------- ---------- ---------- ---------- ----------
10040 3 0 0 0

SYS@book01p> select TIMESTAMP#,SAMPLE_SIZE,MINIMUM,MAXIMUM,DISTCNT from hist_head$ where rownum=1;
select TIMESTAMP#,SAMPLE_SIZE,MINIMUM,MAXIMUM,DISTCNT from hist_head$ where rownum=1
*
ERROR at line 1:
ORA-01031: insufficient privileges

--//分開嘗試發現

SYS@book01p> select MINIMUM from hist_head$ where rownum=1;
select MINIMUM from hist_head$ where rownum=1
*
ERROR at line 1:
ORA-01031: insufficient privileges


SYS@book01p> select MAXIMUM from hist_head$ where rownum=1;
select MAXIMUM from hist_head$ where rownum=1
*
ERROR at line 1:
ORA-01031: insufficient privileges
--//發現MINIMUM,MAXIMUM存在問題.

SYS@book01p> select LOWVAL from hist_head$ where rownum=1;
select LOWVAL from hist_head$ where rownum=1
*
ERROR at line 1:
ORA-01031: insufficient privileges


SYS@book01p> select HIVAL from hist_head$ where rownum=1;
select HIVAL from hist_head$ where rownum=1
*
ERROR at line 1:
ORA-01031: insufficient privileges
--//發現LOWVAL,HIVAL存在問題.

SYS@book01p> select DENSITY,INTCOL#,SPARE1,SPARE2,AVGCLN,SPARE3,SPARE4,MINIMUM_ENC,MAXIMUM_ENC from hist_head$ where rownum=1
2 @ pr
==============================
DENSITY : 0
INTCOL# : 3
SPARE1 : 0
SPARE2 : 2
AVGCLN : 0
SPARE3 :
SPARE4 :
MINIMUM_ENC :
MAXIMUM_ENC :
PL/SQL procedure successfully completed.

--//MINIMUM_ENC,MAXIMUM_ENC也是raw型別,沒有報錯。
SYS@book01p> select DENSITY,INTCOL#,SPARE1,SPARE2,AVGCLN,SPARE3,SPARE4,MINIMUM_ENC,MAXIMUM_ENC from hist_head$ where rownum=1 and MINIMUM_ENC is not null
2 @pr
PL/SQL procedure successfully completed.

SYS@book01p> select DENSITY,INTCOL#,SPARE1,SPARE2,AVGCLN,SPARE3,SPARE4,MINIMUM_ENC,MAXIMUM_ENC from hist_head$ where rownum=1 and MAXIMUM_ENC is not null
2 @pr
PL/SQL procedure successfully completed.
--//MINIMUM_ENC,MAXIMUM_ENC全部都是null.

SYS@book01p> select * from v$reserved_words where KEYWORD in ('MINIMUM','MAXIMUM','LOWVAL','HIVAL')
2 @pr
==============================
KEYWORD : MINIMUM
LENGTH : 7
RESERVED : N
RES_TYPE : N
RES_ATTR : N
RES_SEMI : N
DUPLICATE : N
CON_ID : 0
PL/SQL procedure successfully completed.
--//就1個MINIMUM是保留字.RES*欄位的值都是N，表示怎麼意思。

SCOTT@book01p> create table t1( MINIMUM number);
Table created.

SCOTT@book01p> select * from t1;
no rows selected

SCOTT@book01p> insert into t1 values (1);
1 row created.

SCOTT@book01p> commit ;
Commit complete.

SCOTT@book01p> select * from t1;
MINIMUM
----------
1
--//建表也沒有問題啊.

select
OBJ#
,COL#
,BUCKET_CNT
,ROW_CNT
,CACHE_CNT
,NULL_CNT
,TIMESTAMP#
,SAMPLE_SIZE
--,MINIMUM
--,MAXIMUM
,DISTCNT
--,LOWVAL
--,HIVAL
,DENSITY
,INTCOL#
,SPARE1
,SPARE2
,AVGCLN
,SPARE3
,SPARE4
,MINIMUM_ENC
,MAXIMUM_ENC
from hist_head$ ;
--//測試確實只要不包括這4個欄位,查詢不存在問題.
--//難道21c做了一些限制,一些欄位不可以查詢嗎?

Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, NULL COLUMN_NAME
from sys.dba_tab_privs
where table_name = 'HIST_HEAD$'
and owner='SYS'
UNION ALL
Select PRIVILEGE, GRANTEE, GRANTABLE, GRANTOR, COLUMN_NAME
from sys.dba_col_privs
where table_name = 'HIST_HEAD$'
and owner='SYS'
order by grantee;
--//沒有輸出.

SELECT o.object_name, o.object_type,
NVL(d.alt, '-/-') alt,
NVL(d.aud, '-/-') aud,
NVL(d.com, '-/-') com,
NVL(d.del, '-/-') del,
NVL(d.gra, '-/-') gra,
NVL(d.ind, '-/-') ind,
NVL(d.ins, '-/-') ins,
NVL(d.loc, '-/-') loc,
NVL(d.ren, '-/-') ren,
NVL(d.sel, '-/-') sel,
NVL(d.upd, '-/-') upd,
--NVL(d.REF, '-/-') REF,
NVL(d.exe, '-/-') exe,
NVL(d.cre, '-/-') cre,
NVL(d.rea, '-/-') rea,
NVL(d.wri, '-/-') wri,
NVL(d.fbk, '-/-') fbk
FROM sys.USER_OBJ_AUDIT_OPTS d, sys.USER_OBJECTS o
WHERE d.object_name(+) = o.object_name
and d.object_type(+) = decode(o.object_type, 'MATERIALIZED VIEW', 'TABLE', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'PROCEDURE', 'LIBRARY', 'PROCEDURE', O.OBJECT_TYPE)
and (d.aud is null or (d.aud <> chr(0) || '/' || chr(0)))
and o.object_type = 'TABLE'
and o.object_name ='HIST_HEAD$';

OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD EXE CRE REA WRI FBK
------------------------------ ------------------------------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
HIST_HEAD$ TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

--//查到這裡,無法知道為什麼,那位知道.

SYS@book01p> analyze table hist_head$ validate structure cascade;
Table analyzed.

3.透過轉儲看看:

SYS@book01p> select rowid from hist_head$ where rownum=1;
ROWID
------------------
AAAABEAABAAAEw6AAZ

SYS@book01p> @ rowid AAAABEAABAAAEw6AAZ
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
68 1 19514 25 0x404C3A 1,19514 alter system dump datafile 1 block 19514
;

SYS@book01p> alter system dump datafile 9 block 19514;
System altered.
--//絕對檔案號是9.
SYS@book> select file#,rfile#,name from v$datafile;
FILE# RFILE# NAME
---------- ---------- --------------------------------------------------
1 1 /u01/oradata/BOOK/system01.dbf
3 3 /u01/oradata/BOOK/sysaux01.dbf
4 4 /u01/oradata/BOOK/undotbs01.dbf
5 1 /u01/oradata/BOOK/pdbseed/system01.dbf
6 4 /u01/oradata/BOOK/pdbseed/sysaux01.dbf
7 7 /u01/oradata/BOOK/users01.dbf
8 9 /u01/oradata/BOOK/pdbseed/undotbs01.dbf
9 1 /u01/oradata/BOOK/book01p/system01.dbf
10 4 /u01/oradata/BOOK/book01p/sysaux01.dbf
11 9 /u01/oradata/BOOK/book01p/undotbs01.dbf
12 12 /u01/oradata/BOOK/book01p/users01.dbf
11 rows selected.


block_row_dump:
tab 0, row 0, @0x1f66
tl: 58 fb: --H-FL-- lb: 0x2 cc: 18
col 0: [ 2] c1 05
col 1: [ 2] c1 2b
col 2: [ 2] c1 02
col 3: [ 1] 80
col 4: [ 1] 80
col 5: [ 3] c2 17 5f
col 6: [ 7] 78 7c 08 18 10 05 38
col 7: [ 3] c2 02 02
col 8: [ 1] 80
col 9: [ 1] 80
col 10: [ 2] c1 02
col 11: [ 1] 80
col 12: [ 1] 80
col 13: [ 2] c1 02
col 14: [ 2] c1 2b
col 15: [ 2] c1 02
col 16: [ 2] c1 03
col 17: [ 2] c1 03
--//看不出任何問題。總之就是不能select * 。