关于使用SHOW_SPACE()

paulljw

现在可以了哟！ 谢谢·
还有一个问题： 每次关掉ORACLE后重开再要运行show_space() 时 ，都要重新建一个SHOW_SPACE()  procedure,不然的话就会出错， 出错是指出这个命令被忽略，不能执行。
我的版本是ORACLE8i 的，建立xzh2000 提供的最终混合超级完全无敌版的SHOW_SPACE() 时，
会出现一个警告: 创建的过程带有编译错误。这是不是跟我的ORACLE版本有关系， 谁帮我指个方向好吗？ 先谢哟·

grassbell

用户必须具有 ANALYZE privilege on the object

dbms_space.space_usage 是9i才有的

teddyboy

这个超级无敌宇宙版怎么都执行不了，sysdba创建的，然后grant

SQL> conn / as sysdba
Connected.
SQL> exec show_space(p_segname_1=>'EMP', p_owner_1=>'SCOTT');
BEGIN show_space(p_segname_1=>'EMP', p_owner_1=>'SCOTT'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 59
ORA-06512: at "SYS.SHOW_SPACE", line 55
ORA-06512: at line 1


Elapsed: 00:00:00.00
SQL> grant execute on dbms_space to public;

Grant succeeded.

Elapsed: 00:00:00.00
SQL> exec show_space(p_segname_1=>'EMP', p_owner_1=>'SCOTT');
BEGIN show_space(p_segname_1=>'EMP', p_owner_1=>'SCOTT'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 59
ORA-06512: at "SYS.SHOW_SPACE", line 55
ORA-06512: at line 1


Elapsed: 00:00:00.00
SQL> conn scott/tiger
Connected.
SQL> exec show_space(p_segname_1=>'EMP');
BEGIN show_space(p_segname_1=>'EMP'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SHOW_SPACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Elapsed: 00:00:00.00

请问到底怎样才能执行？oracle9201, windows2000

blue_prince10

我的也不行，记得以前刚装上是行的。是DBA权限，该授的权限也都授了。就是不能运行。
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "SYS.SHOW_SPACE", line 21
ORA-06512: at line 1

melocy

show_space，在一个shema下创建，其他shema都可以访问。
procedure使用invoker（调用者权限），
在procedure中增加一句：authid current_user

D:\>sqlplus mhung/huang

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 16:56:59 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> drop table t1 ;

Table dropped.

Elapsed: 00:00:00.04
SQL> create table t1(c1,c2) as select object_id,object_name from all_objects ;

Table created.

Elapsed: 00:00:00.08

xzh2000 提供的最终混合超级完全无敌版
参见：http://www.itpub.net/239697.html

SQL> create or replace procedure show_space
  2  ( p_segname_1 in varchar2,
  3  p_space in varchar2 default 'MANUAL',
  4  p_type_1 in varchar2 default 'TABLE' ,
  5  p_analyzed in varchar2 default 'N',
  6  p_owner_1 in varchar2 default user)
  7  authid current_user
  8  as
  9  p_segname varchar2(100);
10  p_type varchar2(10);
11  p_owner varchar2(30);
12
13  l_unformatted_blocks number;
14  l_unformatted_bytes number;
15  l_fs1_blocks number;
16  l_fs1_bytes number;
17  l_fs2_blocks number;
18  l_fs2_bytes number;
19  l_fs3_blocks number;
20  l_fs3_bytes number;
21  l_fs4_blocks number;
22  l_fs4_bytes number;
23  l_full_blocks number;
24  l_full_bytes number;
25
26  l_free_blks number;
27  l_total_blocks number;
28  l_total_bytes number;
29  l_unused_blocks number;
30  l_unused_bytes number;
31  l_LastUsedExtFileId number;
32  l_LastUsedExtBlockId number;
33  l_LAST_USED_BLOCK number;
34
35  procedure p( p_label in varchar2, p_num in number )
36  is
37  begin
38  dbms_output.put_line( rpad(p_label,40,'.') ||
39  p_num );
40  end;
41  begin
42  p_segname := upper(p_segname_1); -- rainy changed
43  p_owner := upper(p_owner_1);
44  p_type := p_type_1;
45
46  if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
47  p_type := 'INDEX';
48  end if;
49
50  if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
51  p_type := 'TABLE';
52  end if;
53
54  if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
55  p_type := 'CLUSTER';
56  end if;
57
58
59  dbms_space.unused_space
60  ( segment_owner => p_owner,
61  segment_name => p_segname,
62  segment_type => p_type,
63  total_blocks => l_total_blocks,
64  total_bytes => l_total_bytes,
65  unused_blocks => l_unused_blocks,
66  unused_bytes => l_unused_bytes,
67  LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
68  LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
69  LAST_USED_BLOCK => l_LAST_USED_BLOCK );
70
71  if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
72  dbms_space.free_blocks
73  ( segment_owner => p_owner,
74  segment_name => p_segname,
75  segment_type => p_type,
76  freelist_group_id => 0,
77  free_blks => l_free_blks );
78
79  p( 'Free Blocks', l_free_blks );
80  end if;
81
82  p( 'Total Blocks', l_total_blocks );
83  p( 'Total Bytes', l_total_bytes );
84  p( 'Unused Blocks', l_unused_blocks );
85  p( 'Unused Bytes', l_unused_bytes );
86  p( 'Last Used Ext FileId', l_LastUsedExtFileId );
87  p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
88  p( 'Last Used Block', l_LAST_USED_BLOCK );
89
90
91  /*IF the segment is analyzed */
92  if p_analyzed = 'Y' then
93  dbms_space.space_usage(segment_owner => p_owner ,
94  segment_name => p_segname ,
95  segment_type => p_type ,
96  unformatted_blocks => l_unformatted_blocks ,
97  unformatted_bytes => l_unformatted_bytes,
98  fs1_blocks => l_fs1_blocks,
99  fs1_bytes => l_fs1_bytes ,
100  fs2_blocks => l_fs2_blocks,
101  fs2_bytes => l_fs2_bytes,
102  fs3_blocks => l_fs3_blocks ,
103  fs3_bytes => l_fs3_bytes,
104  fs4_blocks => l_fs4_blocks,
105  fs4_bytes => l_fs4_bytes,
106  full_blocks => l_full_blocks,
107  full_bytes => l_full_bytes);
108  dbms_output.put_line(rpad(' ',50,'*'));
109  dbms_output.put_line('The segment is analyzed');
110  p( '0% -- 25% free space blocks', l_fs1_blocks);
111  p( '0% -- 25% free space bytes', l_fs1_bytes);
112  p( '25% -- 50% free space blocks', l_fs2_blocks);
113  p( '25% -- 50% free space bytes', l_fs2_bytes);
114  p( '50% -- 75% free space blocks', l_fs3_blocks);
115  p( '50% -- 75% free space bytes', l_fs3_bytes);
116  p( '75% -- 100% free space blocks', l_fs4_blocks);
117  p( '75% -- 100% free space bytes', l_fs4_bytes);
118  p( 'Unused Blocks', l_unformatted_blocks );
119  p( 'Unused Bytes', l_unformatted_bytes );
120  p( 'Total Blocks', l_full_blocks);
121  p( 'Total bytes', l_full_bytes);
122
123  end if;
124
125  end;
126  /

Procedure created.

Elapsed: 00:00:00.01
SQL> exec show_space('t1','auto') ;
Total Blocks............................25
Total Bytes.............................102400
Unused Blocks...........................1
Unused Bytes............................4096
Last Used Ext FileId....................5
Last Used Ext BlockId...................16
Last Used Block.........................24

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>

SQL>connect system/manager
Connected.
SQL> drop public synonym show_space ;

Synonym dropped.

Elapsed: 00:00:00.00
SQL>create public synonym show_space for mhung.show_space ;
Synonym created.

Elapsed: 00:00:00.00
SQL> grant execute on show_space to public ;

Grant succeeded.

Elapsed: 00:00:00.00

用scott用户试试
SQL> connect scott/tiger
Connected.
SQL> exec show_space('emp','auto') ;
Total Blocks............................25
Total Bytes.............................102400
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................3
Last Used Ext BlockId...................66
Last Used Block.........................25

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
成功拉

zhpsam

SQL> create table test(name varchar2(20) not null);

表被创建

SQL> select * from test;

NAME
--------------------

SQL> insert into test values('paul');

1 行 已插入

SQL> commit;

提交完成

SQL> exec show_space('TEST');

PL/SQL 过程成功完成

SQL> set serveroutput on;
SQL> exec show_space('TEST');
Free Blocks.............................1
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................1
Last Used Ext BlockId...................50649
Last Used Block.........................2

PL/SQL 过程成功完成

SQL>

teddyboy

下午对partition table进行分析，发现超级无敌混合版中没有，修改了下：

[PHP]
create or replace procedure show_space
( p_segname_1 in varchar2,
p_space in varchar2 default 'MANUAL',
p_type_1 in varchar2 default 'TABLE' ,
p_partition_1 in varchar2 default NULL,
p_analyzed in varchar2 default 'N',
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(30);
p_owner varchar2(30);
p_partition varchar2(50);

l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;

l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;

procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
p_segname := upper(p_segname_1);
p_owner := upper(p_owner_1);
p_type := p_type_1;
p_partition := upper(p_partition_1);

if (p_type_1 = 'i' or p_type_1 = 'I') then
p_type := 'INDEX';
end if;

if (p_type_1 = 't' or p_type_1 = 'T') then
p_type := 'TABLE';
end if;

if (p_type_1 = 'tp' or p_type_1 = 'TP') then
p_type := 'TABLE PARTITION';
end if;

if (p_type_1 = 'ip' or p_type_1 = 'IP') then
p_type := 'INDEX PARTITION';
end if;

if (p_type_1 = 'c' or p_type_1 = 'C') then
p_type := 'CLUSTER';
end if;


dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks );

p( 'Free Blocks', l_free_blks );
end if;

p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );


/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
partition_name => p_partition,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);

end if;

end;
/
[/PHP]

这个应该还不是无敌，因为没有sub partition和log partition...，需要使用的完善吧

可以使用：
[PHP]
SQL> exec show_space('T_STATS_YD_ACCESS_LOG_COMPRESS','AUTO','TP','P200510');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................7
Last Used Ext BlockId...................13472
Last Used Block.........................3

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
[/PHP]

shiguibao

有谁能解释一下这些字段的含义吗？
Free Blocks.............................39085
Total Blocks............................152576
Total Bytes.............................1249902592
Unused Blocks...........................959
Unused Bytes............................7856128
Last Used Ext FileId....................2
Last Used Ext BlockId...................1552393
Last Used Block.........................65

stonerose

SQL> drop public synonym show_space;

Synonym dropped.

Elapsed: 00:00:00.00
SQL> create public synonym show_space for sys.show_space;

Synonym created.

Elapsed: 00:00:00.00
SQL> grant execute on show_space to public;

Grant succeeded.

Elapsed: 00:00:00.00
SQL> grant sysdba to public;

Grant succeeded.

Elapsed: 00:00:00.00
SQL> grant dba to public;

Grant succeeded.

Elapsed: 00:00:00.00

SQL> conn test/test
SQL> exec show_space('T_IDX_SCAN');
BEGIN show_space('T_IDX_SCAN'); END;

*
ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "CTAIS2.SHOW_SPACE", line 72
ORA-06512: at line 1

OracleDBA_FW

SPACE_USAGE
This procedure can only be used on tablespaces that are
created with auto segment space management (ASSM).

if you use SPACE_USAGE on NOT ASSM, you will meet such errors:

ERROR at line 1:
ORA-10614: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 97
ORA-06512: at "APPS.SHOW_SPACE", line 90
ORA-06512: at line 2

so, I think the scripts should be changed to
-----------------------------------------------

  dbms_space.unused_space
           (segment_owner => p_owner,
            segment_name  => p_segname,
            segment_type  => p_type,
            total_blocks  => l_total_blocks,
            total_bytes   => l_total_bytes,
            unused_blocks => l_unused_blocks,
            unused_bytes  => l_unused_bytes,
            LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
            LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
            LAST_USED_BLOCK => l_LAST_USED_BLOCK );

  p( 'Total Blocks', l_total_blocks );
  p( 'Total Bytes', l_total_bytes );
  p( 'Unused Blocks', l_unused_blocks );
  p( 'Unused Bytes', l_unused_bytes );
  p( 'Last Used Ext FileId', l_LastUsedExtFileId );
  p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
  p( 'Last Used Block', l_LAST_USED_BLOCK );
              
  if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
     dbms_space.free_blocks
         (segment_owner => p_owner,
          segment_name  => p_segname,
          segment_type  => p_type,
          freelist_group_id => 0,
          free_blks     => l_free_blks );
     p( 'Free Blocks', l_free_blks );
  ELSE
     dbms_space.space_usage  --notice space_usage just for ASSM
          (segment_owner      => p_owner ,
           segment_name       => p_segname ,
           segment_type       => p_type ,
           unformatted_blocks => l_unformatted_blocks ,
           unformatted_bytes  => l_unformatted_bytes,
           fs1_blocks  => l_fs1_blocks,
           fs1_bytes   => l_fs1_bytes ,
           fs2_blocks  => l_fs2_blocks,
           fs2_bytes   => l_fs2_bytes,
           fs3_blocks  => l_fs3_blocks ,
           fs3_bytes   => l_fs3_bytes,
           fs4_blocks  => l_fs4_blocks,
           fs4_bytes   => l_fs4_bytes,
           full_blocks => l_full_blocks,
           full_bytes  => l_full_bytes);
     dbms_output.put_line(rpad(' ',50,'*'));
     dbms_output.put_line('The segment is analyzed');
     p( '0% -- 25% free space blocks', l_fs1_blocks);
     p( '0% -- 25% free space bytes', l_fs1_bytes);
     p( '25% -- 50% free space blocks', l_fs2_blocks);
     p( '25% -- 50% free space bytes', l_fs2_bytes);
     p( '50% -- 75% free space blocks', l_fs3_blocks);
     p( '50% -- 75% free space bytes', l_fs3_bytes);
     p( '75% -- 100% free space blocks', l_fs4_blocks);
     p( '75% -- 100% free space bytes', l_fs4_bytes);
     p( 'Unused Blocks', l_unformatted_blocks );
     p( 'Unused Bytes', l_unformatted_bytes );
     p( 'Total Blocks', l_full_blocks);
     p( 'Total bytes', l_full_bytes);
  END IF;
end;