Home » RDBMS Server » Server Utilities » QUERY TO DETERMINE THE SIZE OF TABLE
|
Re: QUERY TO DETERMINE THE SIZE OF TABLE [message #69646 is a reply to message #69640] |
Wed, 13 February 2002 05:25 |
Grant
Messages: 578 Registered: January 2002
|
Senior Member |
|
|
This script will give a report on all tables by tablespace.
accept ts_name prompt "Enter the tablespace name that you wish to check: "
set serveroutput on feedback off echo off veri off
spool unused_space.lst
declare
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_file_id number;
v_block_id number;
v_last_block number;
v_extents number;
v_next number;
v_used number;
v_owner varchar2(12);
v_segment varchar2(80);
v_partition varchar2(80);
cursor index_c is
select a.owner, a.index_name, b.extents, b.next_extent
from sys.dba_indexes a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.index_name = b.segment_name
order by a.index_name;
cursor table_c is
select a.owner, a.table_name, b.extents, b.next_extent
from sys.dba_tables a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.table_name = b.segment_name
order by a.table_name;
cursor cluster_c is
select a.owner, a.cluster_name, b.extents, b.next_extent
from sys.dba_clusters a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.cluster_name = b.segment_name
order by a.cluster_name;
cursor tab_partition_c is
select a.table_owner, a.table_name, a.partition_name, b.extents, b.next_extent
from sys.dba_tab_partitions a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.table_name = b.segment_name
and a.partition_name = b.partition_name
order by table_name;
cursor ind_partition_c is
select a.index_owner, a.index_name, a.partition_name, b.extents, b.next_extent
from sys.dba_ind_partitions a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.index_name = b.segment_name
and a.partition_name = b.partition_name
order by index_name;
begin
dbms_output.enable(640000);
open index_c;
fetch index_c into v_owner, v_segment, v_extents, v_next;
while index_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'INDEX', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
dbms_output.put_line(CHR(10));
dbms_output.put_line('INDEX NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Total Blocks = '||v_total_blocks);
dbms_output.put_line('Total Bytes = '||v_total_bytes);
dbms_output.put_line('. KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused Blocks = '||v_unused_blocks);
dbms_output.put_line('Unused Bytes = '||v_unused_bytes);
dbms_output.put_line('. KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_blocks - v_unused_blocks;
dbms_output.put_line('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used Bytes = '||v_used);
dbms_output.put_line('. KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Last used extents file id = '||v_file_id);
dbms_output.put_line('Last used extents block id = '||v_block_id);
dbms_output.put_line('Last used block = '||v_last_block);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch index_c into v_owner, v_segment, v_extents, v_next;
end loop;
close index_c;
open table_c;
fetch table_c into v_owner, v_segment, v_extents, v_next;
while table_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'TABLE', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('TABLE NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Total Blocks = '||v_total_blocks);
dbms_output.put_line('Total Bytes = '||v_total_bytes);
dbms_output.put_line('. KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused Blocks = '||v_unused_blocks);
dbms_output.put_line('Unused Bytes = '||v_unused_bytes);
dbms_output.put_line('. KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_blocks - v_unused_blocks;
dbms_output.put_line('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used Bytes = '||v_used);
dbms_output.put_line('. KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Last used extents file id = '||v_file_id);
dbms_output.put_line('Last used extents block id = '||v_block_id);
dbms_output.put_line('Last used block = '||v_last_block);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch table_c into v_owner, v_segment, v_extents, v_next;
end loop;
close table_c;
open cluster_c;
fetch cluster_c into v_owner, v_segment, v_extents, v_next;
while cluster_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'CLUSTER', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('CLUSTER NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Total Blocks = '||v_total_blocks);
dbms_output.put_line('Total Bytes = '||v_total_bytes);
dbms_output.put_line('. KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused Blocks = '||v_unused_blocks);
dbms_output.put_line('Unused Bytes = '||v_unused_bytes);
dbms_output.put_line('. KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_blocks - v_unused_blocks;
dbms_output.put_line('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used Bytes = '||v_used);
dbms_output.put_line('. KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Last used extents file id = '||v_file_id);
dbms_output.put_line('Last used extents block id = '||v_block_id);
dbms_output.put_line('Last used block = '||v_last_block);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch cluster_c into v_owner, v_segment, v_extents, v_next;
end loop;
close cluster_c;
open tab_partition_c;
fetch tab_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
while tab_partition_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'TABLE PARTITION', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block, v_partition);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('TABLE NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Partition Name = '||v_partition);
dbms_output.put_line('Total KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch tab_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
end loop;
close tab_partition_c;
open ind_partition_c;
fetch ind_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
while ind_partition_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'INDEX PARTITION', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block, v_partition);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('INDEX NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Partition Name = '||v_partition);
dbms_output.put_line('Total KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch ind_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
end loop;
close ind_partition_c;
end;
/
spool off
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 24 10:43:15 CST 2024
|