[ZT]使用pl/sql包dbms_space时注意某些输入参数必须大写

melocy

来源：http://www.itpub.net/showthread.php?s=&threadid=196067

[小技巧]使用pl/sql包dbms_space时注意某些输入参数必须大写

   日前我在测试Tom Kyte大作《Oracle专家高级编程》中的show_space.sql，在
使用pl/sql包dbms_space时发现输入参数segment_name,segment_owner必须大写。

[oracle@solar oracle]$ sqlplus mhung/huang
                                                                              
SQL*Plus: Release 8.1.7.0.0 - Production on Wed Feb 18 09:06:30 2004
                                                                              
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
                                                                              
                                                                              
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

mhung@SOLAR>; @show_space.sql

Procedure created.

mhung@SOLAR>; desc show_space
PROCEDURE show_space
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
P_SEGNAME                      VARCHAR2                IN
P_OWNER                        VARCHAR2                IN     DEFAULT
P_TYPE                         VARCHAR2                IN     DEFAULT
P_PARTITION                    VARCHAR2                IN     DEFAULT

mhung@SOLAR>; exec show_space('dept') ;
BEGIN show_space('dept') ; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "MHUNG.SHOW_SPACE", line 23
ORA-06512: at line 1

mhung@SOLAR>; exec show_space('DEPT') ;
Free Blocks.............................1
Total Blocks............................5
Total Bytes.............................40960
Unused Blocks...........................3
Unused Bytes............................24576
Last Used Ext FileId....................7
Last Used Ext BlockId...................26484
Last Used Block.........................2

PL/SQL procedure successfully completed.

mhung@SOLAR>; get show_space.sql
  1  create or replace
  2  procedure show_space
  3  ( p_segname in varchar2,
  4    p_owner   in varchar2 default user,
  5    p_type    in varchar2 default 'TABLE',
  6    p_partition in varchar2 default NULL )
  7  as
  8      l_free_blks                 number;
  9      l_total_blocks              number;
10      l_total_bytes               number;
11      l_unused_blocks             number;
12      l_unused_bytes              number;
13      l_LastUsedExtFileId         number;
14      l_LastUsedExtBlockId        number;
15      l_last_used_block           number;
16      procedure p( p_label in varchar2, p_num in number )
17      is
18      begin
19          dbms_output.put_line( rpad(p_label,40,'.') ||
20                                p_num );
21      end;
22  begin
23      dbms_space.free_blocks
24      ( segment_owner     =>; p_owner,
25        segment_name      =>; p_segname,
26        segment_type      =>; p_type,
27       partition_name    =>; p_partition,
28        freelist_group_id =>; 0,
29        free_blks         =>; l_free_blks );
30      dbms_space.unused_space
31      ( segment_owner     =>; p_owner,
32        segment_name      =>; p_segname,
33        segment_type      =>; p_type,
34       partition_name    =>; p_partition,
35        total_blocks      =>; l_total_blocks,
36        total_bytes       =>; l_total_bytes,
37        unused_blocks     =>; l_unused_blocks,
38        unused_bytes      =>; l_unused_bytes,
39        last_used_extent_file_id =>; l_LastUsedExtFileId,
40        last_used_extent_block_id =>; l_LastUsedExtBlockId,
41        last_used_block =>; l_last_used_block );
42      p( 'Free Blocks', l_free_blks );
43      p( 'Total Blocks', l_total_blocks );
44      p( 'Total Bytes', l_total_bytes );
45      p( 'Unused Blocks', l_unused_blocks );
46      p( 'Unused Bytes', l_unused_bytes );
47      p( 'Last Used Ext FileId', l_LastUsedExtFileId );
48      p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
49      p( 'Last Used Block', l_last_used_block );
50  end;
51* /
52

我们知道存储过程show_space调用package dbms_space中free_blocks和unused_space,

[oracle@solar oracle]$ ohome            #在.bash_profile中alias ohome="cd $ORACLE_HOME"
[oracle@solar 8.1.7]$ cd rdbms/admin
[oracle@solar admin]$ grep dbms_space *.sql
......
dbmsutil.sql:Rem         dbms_space            - segment space analysis
dbmsutil.sql:create or replace package dbms_space is
dbmsutil.sql:create or replace public synonym dbms_space for sys.dbms_space
dbmsutil.sql:grant execute on dbms_space to public

以上说明普通用户有执行dbms_space的权限。
我们可以通过简单测试dbms_space.free_blocks来说明某些输入参数必须大写的问题。


mhung@SOLAR>; desc dbms_space
PROCEDURE FREE_BLOCKS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER                  VARCHAR2                IN
SEGMENT_NAME                   VARCHAR2                IN
SEGMENT_TYPE                   VARCHAR2                IN
FREELIST_GROUP_ID              NUMBER                  IN
FREE_BLKS                      NUMBER                  OUT
SCAN_LIMIT                     NUMBER                  IN     DEFAULT
PARTITION_NAME                 VARCHAR2                IN     DEFAULT
......

mhung@SOLAR>; variable l_free_blks number
mhung@SOLAR>; exec dbms_space.free_blocks('mhung','dept','table',0,:l_free_blks)
;
BEGIN dbms_space.free_blocks('mhung','dept','table',0,:l_free_blks) ; END;
                                                                              
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at line 1

mhung@SOLAR>;  exec dbms_space.free_blocks('MHUNG','DEPT','table',0,:l_free_blks) ;

PL/SQL procedure successfully completed.


在我印象中，sql语言是不区分大小写的，出现上面输入参数大小写问题，我是百思不得其解。