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
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
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.