ITPub博客

首页 > 数据库 > Oracle > SHOW_SPACE 使用整理

SHOW_SPACE 使用整理

原创 Oracle 作者:宋祖强 时间:2016-01-19 15:25:02 0 删除 编辑

ASSM方式:
SQL>  select TABLESPACE_NAME ,STATUS,CONTENTS,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; 


TABLESPACE_NAME                STATUS    CONTENTS  EXTENT_MAN SEGMEN
------------------------------ --------- --------- ---------- ------
SYSTEM                         ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1                       ONLINE    UNDO      LOCAL      MANUAL
SYSAUX                         ONLINE    PERMANENT LOCAL      AUTO
TEMP                           ONLINE    TEMPORARY LOCAL      MANUAL
USERS                          ONLINE    PERMANENT LOCAL      AUTO
JCI01                          ONLINE    PERMANENT LOCAL      AUTO
TS_BIZ                         ONLINE    PERMANENT LOCAL      AUTO
TS_BBS                         ONLINE    PERMANENT LOCAL      AUTO
USERDATA                       ONLINE    PERMANENT LOCAL      AUTO
9 rows selected.

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
BONUS                          USERS
SALGRADE                       USERS
LL_T1                          USERS
EMP                            USERS
T                              USERS
6 rows selected.

//T表所在的表空间是users 的SEGMENT_SPACE_MANAGEMENT为AUTO

create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
as
    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
    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 );
    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 );
end;
/

SQL> exec show_space('T');
Total Blocks............................9216
Total Bytes.............................75497472
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................22025
Last Used Block.........................1024

PL/SQL procedure successfully completed.


以下为非ASSM方式:



SQL> select TABLESPACE_NAME ,STATUS,CONTENTS,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; 


TABLESPACE_NAME                STATUS    CONTENTS  EXTENT_MAN SEGMEN
------------------------------ --------- --------- ---------- ------
SYSTEM                         ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1                       ONLINE    UNDO      LOCAL      MANUAL
SYSAUX                         ONLINE    PERMANENT LOCAL      AUTO
TEMP                           ONLINE    TEMPORARY LOCAL      MANUAL
USERS                          ONLINE    PERMANENT LOCAL      AUTO
JCI01                          ONLINE    PERMANENT LOCAL      AUTO
TS_BIZ                         ONLINE    PERMANENT LOCAL      AUTO
TS_BBS                         ONLINE    PERMANENT LOCAL      AUTO
USERDATA                       ONLINE    PERMANENT LOCAL      AUTO
TEST                           ONLINE    PERMANENT LOCAL      MANUAL


10 rows selected.


SQL>  select TABLE_NAME,TABLESPACE_NAME from user_tables;


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
BONUS                          USERS
SALGRADE                       USERS
LL_T1                          USERS
TEST                           TEST
EMP                            USERS
T                              USERS

create or replace
    procedure show_space
    ( p_segname in varchar2,
      p_owner   in varchar2 default user,
      p_type    in varchar2 default 'TABLE' )
    as
       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
       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 );
       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( 'Free Blocks', l_free_blks );
       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 );
end;
/

SQL> set serverout on 
SQL> exec show_space('TEST');
Free Blocks.............................0
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................55
Unused Bytes............................450560
Last Used Ext FileId....................9
Last Used Ext BlockId...................649
Last Used Block.........................73

PL/SQL procedure successfully completed.


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_analyzed in varchar2 default 'N',
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
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;
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 = '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,
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,
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 ,
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;
/

SQL> exec show_space('TEST');
Free Blocks.............................0
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................55
Unused Bytes............................450560
Last Used Ext FileId....................9
Last Used Ext BlockId...................649
Last Used Block.........................73

PL/SQL procedure successfully completed.


SQL>  select TABLESPACE_NAME ,STATUS,CONTENTS,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; 

TABLESPACE_NAME                STATUS    CONTENTS  EXTENT_MAN SEGMEN
------------------------------ --------- --------- ---------- ------
SYSTEM                         ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1                       ONLINE    UNDO      LOCAL      MANUAL
SYSAUX                         ONLINE    PERMANENT LOCAL      AUTO
TEMP                           ONLINE    TEMPORARY LOCAL      MANUAL
USERS                     ONLINE    PERMANENT LOCAL     AUTO
JCI01                          ONLINE    PERMANENT LOCAL      AUTO
TS_BIZ                         ONLINE    PERMANENT LOCAL      AUTO
TS_BBS                         ONLINE    PERMANENT LOCAL      AUTO
USERDATA                       ONLINE    PERMANENT LOCAL      AUTO
9 rows selected.

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
BONUS                          USERS
SALGRADE                       USERS
LL_T1                          USERS
EMP                            USERS
T                          USERS
6 rows selected.

SQL> exec show_space('T');
BEGIN show_space('T'); END;
*
ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 152
ORA-06512: at "SCOTT.SHOW_SPACE", line 62
ORA-06512: at line 1

SQL> exec show_space('T','AUTO');    // 表名称和表空间管理方式
Total Blocks............................9216
Total Bytes.............................75497472
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................22025
Last Used Block.........................1024

PL/SQL procedure successfully completed.

SQL>  exec show_space('TEST','MANUAL');
Free Blocks.............................0
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................55
Unused Bytes............................450560
Last Used Ext FileId....................9
Last Used Ext BlockId...................649
Last Used Block.........................73

PL/SQL procedure successfully completed.


以下为统计索引:

SQL>  select INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes; 

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
IND_USER_ID                    SCOTT                          T                              USERS       --表空间自动管理
PK_EMP                         SCOTT                          EMP                            USERS
IND_OBJECT_ID                  SCOTT                          TEST                           TEST        --表空间手动管理
PK_DEPT                        SCOTT                          DEPT                           USERS
SQL>  exec show_space('ind_OBJECT_ID','MANUAL','i');   //('索引名称','表空间管理方式','i')
Free Blocks.............................0
Total Blocks............................120
Total Bytes.............................983040
Unused Blocks...........................3
Unused Bytes............................24576
Last Used Ext FileId....................9
Last Used Ext BlockId...................889
Last Used Block.........................5

PL/SQL procedure successfully completed.

SQL> exec show_space('ind_USER_ID','AUTO','i');  //('索引名称','表空间管理方式','i')
Total Blocks............................1280
Total Bytes.............................10485760
Unused Blocks...........................93
Unused Bytes............................761856
Last Used Ext FileId....................9
Last Used Ext BlockId...................2057
Last Used Block.........................35

PL/SQL procedure successfully completed.




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-1979042/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle常用操作
请登录后发表评论 登录
全部评论

注册时间:2014-08-13

  • 博文量
    176
  • 访问量
    277182