Space check before data LOAD

Here's a script to check table space before loading millions of records; especially useful in data warehousing situations.

Running the following script before loading millions of records allows you to get a fair idea of how much space...

is required to sustain the load and not fail. This also helps you 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 exists.

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. To analyze a table of data warehouse size would run for hours and that would also upset the query plans. Here is the script, which was tested on Sun Solaris 2.6 and Oracle 8i with loads of 30 to 50 million records. Test carefully in your own system before using in production.

REM USAGE:"@path/space_check 
REM ---------------------------------------------------------------------------
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 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 Author: Shankar Govindan
REM Dated: 03/27/2002
REM History: 03/28/2002 Shankar Modified to add tablespace Name too.
PROMPT This script will run a space check and estimate the approx requirements
define tn=	le_Name
define rc=&Total_number_of_records
set echo off
set verify off
spool space_check_&tn.lst

set serveroutput on;

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

-- 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 ('**************************************************************************');
spool off


Here is the output:

SQL> @space_check

This script will run a space check and estimate the approx requirements
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.

About the author

Shankar Govindan works as a Sr. Oracle DBA at CNF, Portland, Oregon. He is Oracle certified in versions 7, 8 and 8i. You can contact him at Note: The above tip was meant for individual use and has nothing to do with the companies I work for or represent.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was last published in November 2002

Dig Deeper on Oracle database design and architecture



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.