How to build a query that shows the size of segments

Question

I have a database table and I need to utilize the data in the table to tell me about segment sizes for the tables listed in the database table.

Here's an example of the kind of data in there, it's broken up into 4 columns and there are many rows:

TABLE_A    TABLE_B      JOIN_COND            WHERE_CLAUSE
AZ             AT       A.AR_ID = B.AR_ID     A.DE = 'AJS'
AZ1           AT1       A.AR_ID = B.AR_ID     A.DE = 'AJS' AND B.END_DATE > '30-NOV-2015'
AZ2           AT3       A.AR_ID = B.AR_ID     A.DE = 'AJS' AND B.END_DATE > '30-NOV-2015'

Here's what I need to accomplish:

Some sort of loop perhaps? That finds the size of each single "TABLE_A" in kilobytes.
Build a query that would find an estimate of the data (space) that would be needed to create a new table based on a subset of a query something like this:

...

SELECT *
  FROM TABLE_A a, TABLE_B b
 WHERE A.AR_ID = B.AR_ID
   AND A.FININS_CDE = 'AJS'
   AND B.END_DTE > '30-NOV-2015'

... but for every row in the table. So at the end of the process if there were 100 rows in the table, I would get 200 results:

100 rows telling me the size of each table A
100 results telling me the size that would be taken up by the subset with the WHERE clause.

Your sample for the second query shows `select * from`. So that would give you a result set of all the columns from `A` and and all the columns from `B`. That wouldn't be directly comparable with the result set of the first query (just the columns from `A`). Is that what you want? — APC, Feb 17 '19 at 08:44
You can use something like this: https://stackoverflow.com/a/10705088 — , Feb 18 '19 at 13:57
A table is only a single segment. I don't think there is a way to get a "partial segment" size from Oracle. — , Feb 18 '19 at 13:59

score 1 · Accepted Answer · edited Feb 23 '19 at 02:12

You're going to need to use dynamic sql for this. The Oracle documentation is here.

You'll need to build some dynamic sql for each of your tables:

SELECT TABLE_A, 'select segment_name,segment_type,bytes/1024/1024 MB
 from dba_segments
 where segment_type=''TABLE'' and segment_name=''' ||  TABLE_A || ''''
FROM <your meta data table>

Then you'll need to loop over the result set and execute each of the statements and capture the results. Some info about that here.

Once you've executed all of the statements you'll have the answer for 1.

The next part is a little more tricky where you'll need to find the data type sizes for each of the columns, then adding all of these together you'll get the size of one row for one table. You can use vsize to get the size of each column.

Using more dynamic sql you can then build your actual statements and execute them as a SELECT COUNT(*) to get the actual number of rows. Multiply the number of rows by the size of a full row from each table and you'll have your answer. You'll obviously need another loop for that too.

Does that all make sense?

How to build a query that shows the size of segments

1 Answers1