Q
Problem solve Get help with specific problems with your technologies, process and projects.

Find size occupied by millions of rows without VSIZE function

One of my tables contains around 10 million records. I want to find out the size occupied (in MB) by the first 8.5 million rows.

I am working on Oracle 9i. One of my tables contains around 10 million records. I want to find out the size occupied (in MB) by the first 8.5 million rows. I cannot possibly use the VSIZE function because my table contains 136 columns (which means writing 136 queries).

You can quickly write the SQL which does use the VSIZE function. It would be difficult to write this query yourself, so let SQL write it for you! Try doing something like the following:

spool temp_script.sql
select 'vsize('||column_name||')+'
from dba_tab_columns where owner='SCOTT' 
  and table_name='EMP';
spool off

The above will contain the start of your SQL statement -- the part with all of those VSIZE commands. It should look similar to the following:

vsize(EMPNO)+
vsize(ENAME)+
vsize(DEPTNO)+
vsize(SAL)+

Simply edit this as follows:

select 
vsize(EMPNO)+
vsize(ENAME)+
vsize(DEPTNO)+
vsize(SAL)
from scott.emp;

Then run the script with "@temp_script" in SQL*Plus.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close