今天在写一个存储过程的时候 由于需要事先判断表空间是否足够,选择较大的一个表空间创建表,因此在过程当中给变量t_be_space赋值

  SELECT TABLESPACE_NAME into  t_be_space FROM (SELECT UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME,
       D.TOT_GROOTTE_MB BKJDX,
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES YSYBKJ,
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),''990.99'') USE_PERCENT,
       F.TOTAL_BYTES FREE_KJ,
       F.MAX_BYTES ZDK
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 5 DESC) WHERE ROWNUM<=1';

Compilation errors for PROCEDURE STATTEST.SP_TEST

Error: PL/SQL: ORA-00942: 表或视图不存在
Line: 33
Text: FROM SYS.DBA_DATA_FILES DD

Error: PL/SQL: SQL Statement ignored
Line: 20
Text: SELECT TABLESPACE_NAME into t_be_spec FROM (SELECT UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME,

SYS.DBA_DATA_FILES 表明明是有权限查看的,select * from SYS.DBA_DATA_FILES ;

FILE_NAME                                                                           FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUTOEXTENSIBLE   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
/dev/rsystem                                                                              1 SYSTEM                         2139095040     261120 AVAILABLE            1 NO                      0          0            0 2139029504      261112
/dev/rundo01                                                                              2 UNDOTBS1                       8581545984    1047552 AVAILABLE            2 NO                      0          0            0 8581480448     1047544
/dev/rundo02                                                                              3 UNDOTBS1                       8581545984    1047552 AVAILABLE            3 NO                      0          0            0 8581480448     1047544
/dev/rundo03                                                                              4
既然可以查看为什么在存储过程当中没有权限查看呢?

加上AUTHID CURRENT_USER语句 还是不能查看.继续看数据字典

OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE        CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------ ----------- ------------- ------------------- ------- --------- --------- ---------
SYS                            DBA_DATA_FILES                                                      3279                VIEW               2007-07-19  2007-07-31 18 2007-07-19:03:14:12 VALID   N         N         N
PUBLIC                         DBA_DATA_FILES                                                      3280                SYNONYM            2007-07-19  2007-07-19 03 2007-07-19:03:14:12 VALID   N         N         N
有一个sys.dba_data_files还有一个同义词

在过程当中换用同义词 结果还是报表或视图不存在.

尝试建成视图,

create view v_DBA_DATA_FILES as select * from sys.DBA_DATA_FILES

ORA-01031: 权限不足

再次尝试创建成表:

SQL> create table v_DBA_DATA_FILES as select * from sys.DBA_DATA_FILES;

Table created

居然可以创建成功,非常惊奇,为什么创建视图没有权限而创建表就有权限呢?于是马上新建一个存储过程

CREATE OR REPLACE PROCEDURE PROC_USE_TS
AS

v_ch number(8);
begin

SELECT COUNT (1) INTO v_ch FROM all_all_tables
     WHERE  table_name =Upper('D_TSNAME'') AND ROWNUM <= 1;

     IF v_ch !=0 THEN
       begin

          execute immediate 'drop table D_TSNAME';
          commit;
       end;
     end if;
 execute immediate '
  CREATE TABLE D_TSNAME AS
  SELECT TABLESPACE_NAME  FROM (SELECT UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME,
       D.TOT_GROOTTE_MB BKJDX,
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES YSYBKJ,
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),''990.99'') USE_PERCENT,
       F.TOTAL_BYTES FREE_KJ,
       F.MAX_BYTES ZDK
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 5 DESC) WHERE ROWNUM<=1;
commit;

END PROC_USE_TS;

编译之后 执行

SQL> exec PROC_USE_TS;

begin PROC_USE_TS; end;

ORA-00942: 表或视图不存在
ORA-06512: 在"STATTEST.PROC_USE_TS", line 18
ORA-06512: 在line 1

检查发现是没有 authid current_user 的原因. 添加之后

SQL> exec PROC_USE_TS

PL/SQL procedure successfully completed

select * from D_TSNAME;

TABLESPACE_NAME
------------------------------
REPORT_TBS

然后将这个这个过程加入刚开始那个过程当中,至此就完成了前一个过程嵌套这个过程之后实现了最初的目的.

但是为什么可以查看到的表 创建成表没有问题而创建成视图就报权限不足呢? 有哪位可以解释一些么?