Is it possible to calculate VSIZE of all columns within a table?

Question

Is it possible to calculate the VSIZE of all columns within a table to return a single aggregate value, without having to explicitly state each column name in the SQL statement? So, a more efficient way to achieve what the following script does:

SELECT 
    SUM(VSIZE(COLUMN_1)) "TOTAL BYTES COL 1" 
   ,SUM(VSIZE(COLUMN_2)) "TOTAL BYTES COL 2" 
   ,SUM(VSIZE(COLUMN_3)) "TOTAL BYTES COL 3" 
    ...
   ,SUM(VSIZE(COLUMN_n)) "TOTAL BYTES COL n" 
 FROM MYSCHEMA.MYTABLE

So, rather than returning an output that looks like this:

| TOTAL BYTES COL 1 | TOTAL BYTES COL 2 | TOTAL BYTES COL 3 | … | TOTAL BYTES COL n | | 237437834748 | 376748438 | 35543643 | ... | 64648729 |

Instead returning an output like this:

| TOTAL BYTES ALL COLUMNS |

| 656393865939 |

I’m working with some tables that have in the region of 200 columns, so curious to learn if there’s a better way to achieve the above without the faff of having to e.g. export all the column names into a list, concatenate into a SQL statement, then export the results out and sum all the column totals to get the total.

Answer 1

Oracle 11g? If it were higher version, you could do it (in a simple manner) in SQL, using a CTE that contains a function. In 11g, create function itself; it accepts table name as a parameter, then dynamically calculates result.

SQL> CREATE OR REPLACE FUNCTION f_size (par_table_name IN VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     l_size  NUMBER;
  5     retval  NUMBER := 0;
  6  BEGIN
  7     FOR cur_r
  8        IN (SELECT table_name, column_name
  9              FROM user_tab_columns
 10             WHERE table_name = DBMS_ASSERT.sql_object_name (par_table_name))
 11     LOOP
 12        EXECUTE IMMEDIATE   'select sum(vsize('
 13                         || cur_r.column_name
 14                         || ')) from '
 15                         || cur_r.table_name
 16           INTO l_size;
 17
 18        retval := retval + l_size;
 19     END LOOP;
 20
 21     RETURN retval;
 22  END f_size;
 23  /

Function created.

Let's try it:

SQL> SELECT f_size ('DEPT') result FROM DUAL;

    RESULT
----------
        68

Is 68 correct?

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> SELECT SUM (VSIZE (deptno)) size_deptno,
  2         SUM (VSIZE (dname)) size_dname,
  3         SUM (VSIZE (loc)) size_loc
  4    FROM dept;

SIZE_DEPTNO SIZE_DNAME   SIZE_LOC
----------- ---------- ----------
          8         33         27

SQL> SELECT 8 + 33 + 27 AS result FROM DUAL;

    RESULT
----------
        68

SQL>

Right; 68 seems to be OK.

---

A CTE option (for future reference):

WITH
   FUNCTION f_size (par_table_name IN VARCHAR2)
      RETURN NUMBER
   IS
      l_size  NUMBER;
      retval  NUMBER := 0;
   BEGIN
      FOR cur_r IN (SELECT column_name
                      FROM user_tab_columns
                     WHERE table_name = par_table_name)
      LOOP
         EXECUTE IMMEDIATE   'select sum(vsize('
                          || cur_r.column_name
                          || ')) from '
                          || par_table_name
            INTO l_size;
         retval := retval + l_size;
      END LOOP;
      RETURN retval;
   END f_size;
SELECT f_size ('DEPT') result
  FROM DUAL;

---

[EDIT] If you want to analyze other users' tables, you have to have appropriate privileges and - instead of user_tab_columns - access all_tab_columns (if you can query dba_tab_columns, you'd be able to fetch data for the whole database!).

Code slightly differs: function gets another parameter (par_owner which is used in cursor's WHERE clause).

Connected as mike, I'll create a new table and grant select privilege to scott: SQL> show user USER is "MIKE" SQL> create table test_scott as 2 select 1 id, 'Littlefoot' name from dual;

Table created.

SQL> grant select on test_scott to scott;

Grant succeeded.

Connected as scott, I'm re-creating the function:

SQL> connect scott@pdb1
Enter password:
Connected.
SQL> CREATE OR REPLACE FUNCTION f_size (par_table_name IN VARCHAR2, par_owner IN VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     l_size  NUMBER;
  5     retval  NUMBER := 0;
  6  BEGIN
  7     FOR cur_r
  8        IN (SELECT table_name, column_name
  9              FROM all_tab_columns
 10             WHERE owner = par_owner
 11               AND table_name = DBMS_ASSERT.sql_object_name (par_table_name))
 12     LOOP
 13        EXECUTE IMMEDIATE   'select sum(vsize('
 14                         || cur_r.column_name
 15                         || ')) from '
 16                         || cur_r.table_name
 17           INTO l_size;
 18        retval := retval + l_size;
 19     END LOOP;
 20
 21     RETURN retval;
 22  END f_size;
 23  /

Function created.

Testing:

SQL> SELECT f_size('TEST', 'MIKE') result FROM dual;

    RESULT
----------
         0

SQL>