[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 * 。