[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 * 。
--//遇到的奇怪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 * 。
