EN VI

Oracle - PL/SQL: Iterating over table names and retrieving each table size?

How to Oracle - PL/SQL: Iterating over table names and retrieving each table size

I would like to retrieve the sizes of some of the tables in an Oracle database.

I can retrieve the list of the tables names using:

SELECT table_name
FROM all_tables
WHERE table_name LIKE 'TB_%'
ORDER BY table_name ASC;

and I can retrieve the size of a table named 'table_name' using:

SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" 
FROM user_segments 
WHERE segment_name='table_name';

So, my idea was to use a cursor to collect the table names and iterate over the different table names as follows:

DECLARE
  CURSOR my_cursor
  IS
    SELECT table_name
    FROM all_tables
    WHERE table_name LIKE 'TB_%'
    ORDER BY table_name ASC;
BEGIN
  FOR my_table_name IN my_cursor
  LOOP
    SELECT SUM(bytes)/1024/1024 AS "Table Size (MB)" 
    FROM user_segments 
    WHERE segment_name=my_table_name;
  END LOOP;
END

But I get the following error

ORA-06550: line 13 column 28: PLS-00382: expression of the wrong type ORA-6550: line 11, column 9: PLS-00428: an INTO clause is expected in this SELECT statement

However, declaring an additional my_size NUMBER; and modifying the loop as follows:

  LOOP
    SELECT SUM(bytes)/1024/1024 INTO my_size 
    FROM user_segments 
    WHERE segment_name=my_table_name;
    DBMS_OUTPUT.put_line(my_table_name || ' : ' || my_size)
  END LOOP;

Does not really help. What am I missing here?

Solution:

You have a cursor, so my_table_name is a row/record from that cursor. You need to refer to the column/field in that:

...
WHERE segment_name=my_table_name.table_name;

But you also need to select into something, and then use that result, e.g.:

DECLARE
  my_size NUMBER;
  CURSOR my_cursor
  IS
    SELECT table_name
    FROM all_tables
    WHERE table_name LIKE 'TB_%'
    ORDER BY table_name ASC;
BEGIN
  FOR my_table_name IN my_cursor
  LOOP
    SELECT SUM(bytes)/1024/1024
    INTO my_size
    FROM user_segments 
    WHERE segment_name=my_table_name.table_name;

    dbms_output.put_line('Table Size (MB): ' || my_size);
  END LOOP;
END;
/

But you can use a single query, which could be used in a cursor, but don't need to use PL/SQL at all; you can do this simply in plain SQL:

SELECT ut.table_name, SUM(bytes)/1024/1024 AS "Table Size (MB)"
FROM user_tables ut
LEFT JOIN user_segments us
ON us.segment_name = ut.table_name
GROUP BY ut.table_name
ORDER BY ut.table_name;

fiddle