Home » Developer & Programmer » Designer » Determinng the size to create a tablesize
Determinng the size to create a tablesize [message #90646] Tue, 22 June 2004 06:38 Go to next message
Pit
Messages: 3
Registered: March 2003
Junior Member
hallo,

We have to create a oracle-database.How do we determine the size of the tablesize we need to create? We know, how much records we have and now we need to know the size for creating the tablespace.

thanks in advance

pit
Re: Determinng the size to create a tablesize [message #90647 is a reply to message #90646] Tue, 22 June 2004 08:43 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
A good starting point is to look at your table structure and the indexes you will be creating. Don't try to work it out exactly - you are unlikely to be accurate for mist applications because cardinality estimates are usually just that. Also, because many of your columns are likely to be varchar2, or contain null values, calculating the space required needs to be based on the actual data going into the tables.

Identify your largest few tables (most rows, most columns, largest columns). Create them and their indexes and then fill them up with test data.

You can use this to see the space allocated and space actually used:

REM	To see the actual space allocated and used for tables and 
REM corresponding indexes
REM
set serveroutput on format wrapped feedback off echo off verify off lines 80 trims on 
spool space.lst
declare
-- v_tablepspace varchar2(32) := 'MY_TABLESPACE';
 v_owner varchar2(32) := 'SCOTT';
 
--  cursor ctab is 
--       select a.owner, a.table_name, b.extents
-- 		from dba_tables a, dba_segments b
--		where a.TABLE_NAME = b.segment_name
--		and a.OWNER = b.owner
--		and a.tablespace_name = v_tablepspace 
--		order by a.owner, a.table_name
-- 	;

  cursor ctab is 
        select a.owner, a.table_name, b.extents
 		from dba_tables a, dba_segments b
		where a.TABLE_NAME = b.segment_name
		and a.OWNER = b.owner
		and a.owner = v_owner 
		order by a.owner, a.table_name
 	;

 cursor cind (wowner in varchar2, wtab in varchar2) is 
        select a.owner, a.index_name, b.extents
		from dba_indexes a, dba_segments b
		where a.INDEX_NAME = b.segment_name
		and a.owner = b.owner
		and a.table_name = wtab 
		and a.table_owner = wowner;

 wtotal_blocks               number;
 wtotal_bytes                number;
 wunused_blocks              number;
 wunused_bytes               number;
 wlast_used_extent_file_id   number;
 wlast_used_extent_block_id  number;
 wlast_used_block            number;

 wtaballoc       number;
 wtabused        number;
 wtabfree        number;
 windalloc       number;
 windused        number;
 windfree        number;

 wtottaballoc       number := 0;
 wtottabused        number := 0;
 wtottabfree        number := 0;
 wtotindalloc       number := 0;
 wtotindused        number := 0;
 wtotindfree        number := 0;

begin

 dbms_output.enable (9999999);
 
 dbms_output.new_line ();
-- for rec in (select to_char(sysdate, 'dd Mon yyyy hh24:mi:ss' )||
-- 	 	 	 ' - User ('||v_owner||') on Database ('||global_name||')' Where_am_i 
--			from global_name)
 for rec in (select to_char(sysdate, 'dd Mon yyyy hh24:mi:ss' )||
 	 	 	 ' - User ('||v_owner||') on Database ('||global_name||')' Where_am_i 
			from global_name)
 loop
   dbms_output.put_line (rec.Where_am_i );
 end loop;
  dbms_output.new_line ();

  dbms_output.put_line ('Expanded space report for User :'|| v_owner );
  dbms_output.put_line ('--------------------------------'|| rpad('-', length(v_owner), '-'));
  dbms_output.put_line ('Table Owner.Name(extents)             Tab.   UsedM'||
                        '  FreeM  Ind.   UsedM  FreeM' );
  dbms_output.put_line ('------------------------------------  -----  -----'||
                        '  -----  -----  -----  -----');

 for rtab in ctab loop

   dbms_space.unused_space (rtab.owner, rtab.table_name, 'TABLE', 
                            wtotal_blocks,  wtotal_bytes,  wunused_blocks,
                            wunused_bytes, wlast_used_extent_file_id,
                            wlast_used_extent_block_id, wlast_used_block);

   wtaballoc := wtotal_bytes/1048576;
   wtabused  := (wtotal_bytes - wunused_bytes)/ 1048576;
   wtabfree  := wunused_bytes/1048576;
   windalloc := 0;
   windused  := 0;
   windfree  := 0;

   wtottaballoc := wtottaballoc + wtaballoc;
   wtottabused  := wtottabused  + wtabused;
   wtottabfree  := wtottabfree  + wtabfree;

   for rind in cind (rtab.owner, rtab.table_name) loop

     dbms_space.unused_space (rind.owner, rind.index_name, 'INDEX', 
                              wtotal_blocks,  wtotal_bytes,  wunused_blocks,
                              wunused_bytes, wlast_used_extent_file_id,
                              wlast_used_extent_block_id, wlast_used_block);
     -- print Index detail!
     dbms_output.put_line (rpad((  --rtab.owner || '.' || 
				  '  '||rind.index_name||'('||rind.extents||')'),36) || 
			   lpad(' ', 23) ||
			   lpad(to_char(wtotal_bytes/1048576, '9999.9'), 7) ||
			   lpad(to_char((wtotal_bytes - wunused_bytes)/ 1048576, '9999.9'), 7) ||
			   lpad(to_char(wunused_bytes/1048576, '9999.9'), 7) );
			 
     windalloc := windalloc + wtotal_bytes/1048576;
     windused  := windused + (wtotal_bytes - wunused_bytes)/ 1048576;
     windfree  := windfree + wunused_bytes/1048576;
  
   end loop;

   wtotindalloc := wtotindalloc + windalloc;
   wtotindused  := wtotindused  + windused;
   wtotindfree  := wtotindfree  + windfree;

   dbms_output.put_line (rpad(( -- rtab.owner || '.' || 
				rtab.table_name ||'('||rtab.extents||')' ),36) || 
			 lpad(to_char(wtaballoc, '9999.9'), 7) ||
			 lpad(to_char(wtabused, '9999.9'), 7) ||
			 lpad(to_char(wtabfree, '9999.9'), 7) ||
			 lpad(to_char(windalloc, '9999.9'), 7) ||
			 lpad(to_char(windused, '9999.9'), 7) ||
			 lpad(to_char(windfree, '9999.9'), 7) );

 end loop;

 dbms_output.put_line ('------------------------------------  -----  -----'||
                  '  -----  -----  -----  -----');

 dbms_output.put_line (rpad('TOTAL',36) || 
        		 lpad(to_char(wtottaballoc, '99999.9'), 7) ||
			 lpad(to_char(wtottabused, '99999.9'), 7) ||
			 lpad(to_char(wtottabfree, '99999.9'), 7) ||
			 lpad(to_char(wtotindalloc, '99999.9'), 7) ||
			 lpad(to_char(wtotindused, '99999.9'), 7) ||
			 lpad(to_char(wtotindfree, '99999.9'), 7) );

 dbms_output.put_line 
('------------------------------------------------------------------------------');

     dbms_output.put_line ('Full segment type breakdown is:');
     dbms_output.put_line ('-------------------------------');
 for j in (select rpad(segment_type, 20) seg_type, sum(bytes) / 1048576 All_Seg_Types 
           from dba_segments 
	   where owner = v_owner
	   group by rpad(segment_type, 20)) loop
     dbms_output.put_line (
     	j.seg_type||' '||
     	to_char(j.All_Seg_Types, '99999.99'));
 end loop;
     dbms_output.put_line ('                       --------');
 for k in (select sum(bytes) / 1048576 All_Seg_Types 
           from dba_segments 
	   where owner = v_owner) loop
     dbms_output.put_line ('Total                '||to_char(k.All_Seg_Types, '99999.99'));
 end loop;
end;
/
set feedback on verify on 
spool off

Previous Topic: Import Designer 2000 Web PLSQLs into Designer 9i
Next Topic: ORACLE ER VIEWER
Goto Forum:
  


Current Time: Tue Dec 01 06:35:49 CST 2020