Single SQL to count rows in all tables 2

Calculating disk use including recycle bin

Previously I talked about the link between stats and row counts and how to find the number of rows in your tables with a single SQL, specifically:

But what are you doing when getting a row count, perhaps trying to get an idea of how big the tables are? But the row count is only a rough indicator of the disk space taken by the tables, now you could look at avg row length and get an estimate but there’s a better way: dba_segments which describes the storage allocated for all segments in the database. Let’s look at the regions table:

SELECT *
  FROM dba_segments
 WHERE owner = 'HR'
   AND segment_name LIKE 'REG%'

It also shows the region index. But what we want is to associate the index, partitions, lobs and other with the underlying table name. That information is available in a few data dictionary tables. So we’ll pull that information, SUM up the segments, pivot the data into a single row per table as a summary and then join it back to the dba_tab_statistics used before.

SQL to get table and associated item sizes

Summary of table sizes in HR schema

These numbers show the total size in MB of the tables including associated elements. What you would expect is that if rows are deleted from a table the size would go down, however it isn’t that simple due to the way Oracle measures the number of blocks allocated known as the high water mark. As implied by the name there are only three ways to move the HWM:

1. Insert more rows — increase the HWM
2. DROP the table
3. TRUNCATE the table — reset the HWM

Let’s try truncating and deleting from a table to see the difference.

CREATE TABLE test_delete AS
SELECT LEVEL id, CAST(DBMS_RANDOM.string('a',100) AS VARCHAR2(100)) txt FROM dual
CONNECT BY LEVEL <= 10000;
CREATE TABLE test_truncate AS
SELECT * FROM test_delete;
DELETE FROM test_delete;
TRUNCATE TABLE test_truncate;

The TEST_TRUNCATE table size is reset, the TEST_DELETE table size is not due to the HWM

Only the TRUNCATED table’s size is reset. Note the num_rows and blocks are incorrect for both tables because the statistics are stale as covered in my previous post. The last step is to DROP the tables to clean up our schema.

DROP TABLE hr.test_delete;
DROP TABLE hr.test_truncate;

This doesn’t recover the disk space since recycle bin was introduced in 10g:

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time.

We can look at the dba_recyclebin and join to dba_segments to get the sizes of recycled objects:

Recycle bin

Hopefully this covers your Oracle disk usage needs.