Space Check Before Data LOAD (DW)

Reprinted with Permission by Quest Software April 2002

Space Check Before Data LOAD (DW)
Shankar Govindan

Running a script like this before loading millions of records would help the person loading get a fair idea of how much space is required to sustain the load and not fail. This also helps the person loading to estimate and inform the DBA that a load is to happen and space does not seem to be sufficient in a tablespace where the loading table exist.

Basically the script gives us approximate space required for the load based upon the size of the records existing in the table multiplied by the number of records. So the table should have some data to get this info and calculate. It will not work for a table that is empty.

For getting the exact values we need to Analyze the table and then the DBA_TABLE views get populated with the AVG_ROW_LENGTH and then we calculate from there. The analyze for a table of DW size would run for hours and that would also upset the query plans.

Script:
-----------------------------------------x cut here x-------------------------------------------------------------------

SET VERIFY OFF
REM NAME: SPACE_CHECK.SQL
REM USAGE:"@path/space_check <TABLE_NAME> <RECORD COUNT>"
REM -------------------------------------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V$ tables
REM -------------------------------------------------------------------------------------------------------
REM This script takes a input Table and number of records and calculates the
REM space required loading the records.
REM
REM Note: If the table is empty, then the script won’t work. This is a fast way
REM of getting an approximate size for data loads after the initial load.
REM
REM Author: Shankar Govindan
REM Dated: 03/27/2002
REM
REM History: 03/28/2002 Shankar Modified to add tablespace Name too.
REM
PROMPT
PROMPT This script will run a space check and estimate approx requirement
define tn=&Table_Name
define rc=&Total_number_of_records
set echo off
set verify off
spool space_check_&tn.lst

set serveroutput on;

declare
     table_size number;
     record_count number;
     bytes_per_record number;
     load_size number;
     tablespaceName varchar(50);
     free_space number;
begin

-- Check the current size of the table
select sum(bytes) into table_size from dba_segments
where segment_name like '&tn';

-- Check the current number of records in the table.
select count(1) into record_count from &tn;

-- bytes/records will give approx size of each record.
select table_size/record_count into bytes_per_record from dual;

-- Multiply bytes per record by number of records to get load size.
select (to_char(bytes_per_record) * to_char(&rc)) into load_size from dual;

-- Tablespace name
select tablespace_name into tablespaceName from dba_segments
where segment_name like '&tn';

-- check free space for the tablespace where table exists.
select sum(bytes/1024) into free_space from dba_free_space
where tablespace_name = ( select tablespace_name from dba_segments
where segment_name like '&tn');

-- Display results
dbms_output.put_line ('*******************************************************************************');
dbms_output.put_line ('table size : '||to_char (table_size) || ' bytes');
dbms_output.put_line ('record count : '||to_char (record_count) || ' Number');
dbms_output.put_line ('bytes per record : '||to_char (bytes_per_record) || ' bytes');
dbms_output.put_line ('Tablespace Name : '||tablespaceName);
dbms_output.put_line ('Free space : '||to_char (free_space) || ' bytes ');
dbms_output.put_line ('load size : '||to_char (load_size) || ' bytes');
dbms_output.put_line ('********************************************************************************');
end;
/
spool off
-------------------------------------------x cut here x-----------------------------------------------------------------

Output:

SQL> @space_check

This script will run a space check and estimate approx requirement
Enter value for table_name: HELP
Enter value for total_number_of_records: 500
****************************************************************************************
Table size : 1638400 bytes
Record count : 25587 Number
Bytes per record : 64.03251651229139797553445108844335013874 bytes
Tablespace Name : TOOLS
Free space : 144648 bytes
Load size : 32016.2582561456989877672255442216750694 bytes
*****************************************************************************************

PL/SQL procedure successfully completed.

Shankar Govindan works as a Sr. Oracle DBA at CNF, Portland, Oregon. Shankar Govindan is Oracle Certified 7, 8 and 8I, you can contact him at shankargovindan@yahoo.com.
The above scripts were tested on Sun Solaris 2.6 and Oracle 8i with loads of 30 to 50 Million records, I was able to get approx size and setup tablespaces for tables that grew to more than 30Gb each.

Note: The above info as usual is of my individual tests and opinions and has nothing to do with the company I work for or represent. Please test and conclude at your setup.