|
-
Here's a script that will tell you the allocated space for the
table and the used space for the table ...
Change the OWNER, TABLE_NAME in the top of the script for your
structure
/**************************************************/
/* Find the highwater mark in a table */
/* ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K; */
/* gb - PSAConsulting */
/**************************************************/
set serveroutput on
declare
v_user varchar2(50) := 'SYSADM';
v_object_name varchar2(50) := 'PART';
v_object_type varchar2(15) := 'TABLE';
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
v_partition_name varchar2(50);
v_initial number;
v_next number;
v_number_extents number;
v_num_rows number;
v_avg_row_len number;
v_tablespace_name varchar2(30);
begin
IF v_object_type = 'TABLE'
THEN
SELECT initial_extent,next_extent,num_rows,avg_row_len, tablespace_name
INTO v_initial, v_next, v_num_rows, v_avg_row_len, v_tablespace_name
FROM all_tables
WHERE owner = v_user
AND table_name = v_object_name;
ELSE
SELECT initial_extent,next_extent, tablespace_name
INTO v_initial, v_next, v_tablespace_name
FROM all_indexes
WHERE owner = v_user
AND index_name = v_object_name;
END IF;
SELECT count(*)
INTO v_number_extents
FROM dba_extents
WHERE owner = v_user
AND segment_name = v_object_name;
DBMS_SPACE.UNUSED_SPACE( v_user
,v_object_name
,v_object_type
,v_total_blocks
,v_total_bytes
,v_unused_blocks
,v_unused_bytes
,v_last_used_extent_file_id
,v_last_used_extent_block_id
,v_last_used_block
,v_partition_name );
dbms_output.put_line('Object Name: '||v_object_name||' - '||v_object_type||' Tablespace: '||v_tablespace_name);
dbms_output.put_line('. ');
dbms_output.put_line('Number of Extents: '||v_number_extents||' Initial: '||v_initial||' Next: '||v_next);
dbms_output.put_line('......Approx Rows: '||v_num_rows||' Avg Row Len: '||v_avg_row_len);
dbms_output.put_line('Blocks in segment: '||v_total_blocks);
dbms_output.put_line('Bytes in segment(Size): '||v_total_bytes);
dbms_output.put_line('MegaBytes in segment: '||v_total_bytes/1048576);
dbms_output.put_line('Blocks not used: '||v_unused_blocks);
dbms_output.put_line('Bytes not used: '||v_unused_bytes);
dbms_output.put_line('MegaBytes not used: '||v_unused_bytes/1048576);
dbms_output.put_line('File ID - last extent with data(Block): '||v_last_used_extent_file_id);
dbms_output.put_line('File ID - last extent with data(Byte): '||v_last_used_extent_block_id);
dbms_output.put_line('Last block with data: '||v_last_used_block);
end;
/
HTH
Gregg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|