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.

SET VERIFY OFF
REM NAME: SPACE_CHECK.SQL
REM USAGE:"@path/space_check 
  
  
   "
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 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;

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

  
 

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 shankargovindan@yahoo.com. 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 first published in November 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close