Home » RDBMS Server » Server Utilities » QUERY TO DETERMINE THE SIZE OF TABLE
QUERY TO DETERMINE THE SIZE OF TABLE [message #69640] Tue, 12 February 2002 11:52 Go to next message
Gajanan Kini
Messages: 8
Registered: January 2002
Junior Member
What is a query that I can use to determine the size of a table? thanks

Report message to a moderator

Re: QUERY TO DETERMINE THE SIZE OF TABLE [message #69646 is a reply to message #69640] Wed, 13 February 2002 05:25 Go to previous messageGo to next message
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

Report message to a moderator

Re: QUERY TO DETERMINE THE SIZE OF TABLE [message #70241 is a reply to message #69640] Wed, 08 May 2002 22:25 Go to previous messageGo to next message
SYED AHAMED RAFI
Messages: 1
Registered: May 2002
Junior Member
how to find the size of a table ?

Report message to a moderator

Re: QUERY TO DETERMINE THE SIZE OF TABLE [message #351795 is a reply to message #70241] Fri, 03 October 2008 03:58 Go to previous messageGo to next message
b_kareem@sify.com
Messages: 5
Registered: March 2007
Location: india
Junior Member
Use below script to find allocate space and free space of a table


select table_name,round((blocks*8)/1024,2) "Allocate Space in MB" ,round((num_rows*avg_row_len/1024/1024),2) "Used Space in MB" from user_tables;

Report message to a moderator

Re: QUERY TO DETERMINE THE SIZE OF TABLE [message #351804 is a reply to message #351795] Fri, 03 October 2008 04:47 Go to previous message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Great to answer to a post that is more than 6 years old.

Regadrs
Michel

Report message to a moderator

Previous Topic: manage data availability with limited quota
Next Topic: SQL Loader: finished with process status 2
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 07 19:02:47 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.