There’s a request on the Oracle Developer Forum for assistance to write a report that shows the total space allocation for a table – including its indexes and LOB columns. There is no requirement to allow for partitioned tables, but it’s not entirely clear whether the code should allow for index-organized tables and clustered tables, and there’s no comment about nested tables.
The OP has made an initial attempt to write a suitable query, but the query joins dba_tables to dba_indexes then to dba_lobs then three times to dba_segments (all the joins being outer joins) before aggregating on table name. Unsurprisingly this produces the wrong results because (for example) if a table has two indexes the join from dba_tables to dba_indexes will double the bytes reported for the table. As Andrew Sayer points out in the thread, it would be better to aggregate on each of the separate component types before joining.
This introduces an important, generic, principle for writing code.
- If it’s difficult to write a single statement can you break the task down into separate components that are easier to handle.
- If you can express the problem as a small set of simpler components can you then combine the components
- If this approach works is any loss of efficiency balanced by a gain in clarity and maintainability.
In the case of the OP this isn’t a query that’s going to be runing every few seconds – maybe it will be once per day, maybe three or four times per day. so there’s no need to be worried about making it as efficient as possible – so let’s go for simplicity.
Here’s a query that reports the space allocated for a simple heap table. It references dba_tables and dba_segments, so has to be run by a user with DBA privileges, and at the moment it’s restricted to reporting a single user.
Wait – before I write the query I ought to create a testbed to see if the query is working. So let’s take an empty schema and create a few objects. Let’s create
- a simple heap table with two indexes and two LOB columns.
- an Index Organized Table (IOT) with an overflow and two LOB columns.
- a table with two columns that are a nested table type
Here’s some suitable code:
rem rem Script: sum_table_space.sql rem Author: Jonathan Lewis rem Dated: Nov 2019 rem rem Last tested rem 19.3.0.0 rem 12.2.0.1 rem prompt ============================================================ prompt Simple heap table - copy of all_objects - then add two CLOBs prompt ============================================================ create table test_user.t1 as select * from all_objects; alter table test_user.t1 add (c1 clob) lob (c1) store as basicfile t1_lob1 (disable storage in row); alter table test_user.t1 add (c2 clob) lob (c2) store as basicfile t1_lob2 (disable storage in row); update test_user.t1 set c1 = 'x', c2 = 'x' where rownum <= 125; create index test_user.t1_i1 on test_user.t1(object_name, object_type, object_id); create index test_user.t1_i2 on test_user.t1(object_type, object_name, object_id); prompt ========================================================= prompt Simple IOT table - with a couple of CLOBs in the overflow prompt ========================================================= create table test_user.iot1 ( id number not null, ind_pad varchar2(20) not null, n2 number not null, n3 number not null, padding varchar2(50), ct1 clob, ct2 clob, constraint iot1_pk primary key (id, ind_pad) ) organization index overflow including n3 lob(ct1) store as basicfile iot_lob1( disable storage in row ) lob(ct2) store as basicfile iot_lob2( disable storage in row ) ; insert into test_user.iot1 select rownum, rpad(rownum,20), rownum, rownum, rpad('x',50,'x'), case when mod(rownum,100) = 0 then rpad('x',100,'x') end, case when mod(rownum,100) = 0 then rpad('x',100,'x') end from all_objects where rownum <= 10000 ; commit; prompt ==================================================== prompt Now create types to allow creation of a nested table prompt ==================================================== create type test_user.jpl_item as object (n1 number, v1 varchar2(32)); / create type test_user.jpl_tab_type as table of jpl_item; / create table test_user.demo_nest ( id number, nest1 test_user.jpl_tab_type, nest2 test_user.jpl_tab_type ) nested table nest1 store as t_nested1 return as locator nested table nest2 store as t_nested2 return as locator ; create unique index test_user.tn1_pk on test_user.t_nested1(nested_table_id, n1) compress 1; create unique index test_user.tn2_pk on test_user.t_nested2(nested_table_id, n1) compress 1; create index test_user.tn1_v1 on test_user.t_nested1(v1); create index test_user.tn2_v1 on test_user.t_nested2(v1); insert into test_user.demo_nest values ( 1, test_user.jpl_tab_type( test_user.jpl_item(1,'Item One one'), test_user.jpl_item(2,'Item One two') ), test_user.jpl_tab_type( test_user.jpl_item(1,'Item One one'), test_user.jpl_item(2,'Item One two') ) ); insert into test_user.demo_nest values ( 2, test_user.jpl_tab_type( test_user.jpl_item(1,'Item Two one'), test_user.jpl_item(2,'Item Two two') ), test_user.jpl_tab_type( test_user.jpl_item(1,'Item Two one'), test_user.jpl_item(2,'Item Two two') ) ); commit;
You’ll notice that I’ve prefixed every table, index and type name with a schema name. This is because I set up this test to run as a DBA so I’m creating the objects in one schema while connected to a different schema.
Of course, before trying to report allocations summed by base table, it would be sensible to produce a simple list of the segments we should see so that we know when we’ve reported all of them. So let’s start with that very simple report:
column bytes format 999,999,999,999 column segment_name format a25 break on report on owner on object_name skip 1 compute sum of bytes on report select segment_name, segment_type, bytes from dba_segments where owner = 'TEST_USER' order by segment_type, segment_name ; SEGMENT_NAME SEGMENT_TYPE BYTES ------------------------- ------------------ ---------------- IOT1_PK INDEX 1,048,576 SYS_C008380 INDEX 1,048,576 SYS_C008381 INDEX 1,048,576 SYS_FK0000075579N00002$ INDEX 1,048,576 SYS_FK0000075579N00004$ INDEX 1,048,576 T1_I1 INDEX 5,242,880 T1_I2 INDEX 5,242,880 TN1_PK INDEX 1,048,576 TN1_V1 INDEX 1,048,576 TN2_PK INDEX 1,048,576 TN2_V1 INDEX 1,048,576 SYS_IL0000075565C00027$$ LOBINDEX 1,048,576 SYS_IL0000075565C00028$$ LOBINDEX 1,048,576 SYS_IL0000075572C00006$$ LOBINDEX 1,048,576 SYS_IL0000075572C00007$$ LOBINDEX 1,048,576 IOT_LOB1 LOBSEGMENT 1,048,576 IOT_LOB2 LOBSEGMENT 1,048,576 T1_LOB1 LOBSEGMENT 2,097,152 T1_LOB2 LOBSEGMENT 2,097,152 T_NESTED1 NESTED TABLE 1,048,576 T_NESTED2 NESTED TABLE 1,048,576 DEMO_NEST TABLE 1,048,576 SYS_IOT_OVER_75572 TABLE 1,048,576 T1 TABLE 12,582,912 ---------------- sum 47,185,920
So we now have a list of segments and a sum of bytes to aim for. One thing you might notice, though, is that the name “IOT1” has disappeared; instead the thing we see labelled as a TABLE is its overflow segment, called SYS_IOT_OVER_75572 (the number being the object_id of the table we originally defined. We will have to do something in our code to translate that “sys_iot_over_75572″ to “iot1″ if we want to make our final report easy to understand.
There are a few other oddities in the list but some of them we may be familiar with already – the indexes with names like SYS_IL0000075565C00027$$ are the lob indexes for the four lobs we created. (I deliberately created basicfile LOBs in case the OP wasn’t using the securefile LOBs that most systems are now using). The 75565 in the example I just quoted is the object_id of the base table the C00027 tells us that the LOB is column 27 of the table definition.
You may be wondering about the indexes like SYS_C008380 and SYS_FK0000075579N00002$. We may need to work on those – but I’ll tell you the answer straight away – the former is a unique index on the “nest1” column in the demo_nest table, the latter is a “foreign key” index on the (invisible) nested_table_id column in the nested tables.
Side note: although the sys_fk0000075579N000025 index looks as if it’s supposed to be a “foreign key” index, as suggested by the name, the dependency, and the function, there’s no foreign key constraint declared between the parent table and the nested table. Also, if you check dba_indexes, the generated column has the value ‘N’, even though the name of the index was clearly generated by Oracle internal code. (The index at the other end of the join – sys_c008380 – is, however, flagged with generated = ‘Y’)
Let’s start building our solution with the easiest bit.
select tab.owner, cast('TABLE' as varchar2(11)) object_type, tab.table_name, nvl(seg_tab.bytes,0) bytes from dba_tables tab left outer join dba_segments seg_tab on seg_tab.owner = tab.owner and seg_tab.segment_name = tab.table_name where tab.owner = 'TEST_USER' / OWNER OBJECT_TYPE TABLE_NAME BYTES --------------- ----------- -------------------- ---------------- TEST_USER TABLE T1 12,582,912 TABLE SYS_IOT_OVER_75572 1,048,576 TABLE DEMO_NEST 1,048,576 TABLE IOT1 0 *************** ---------------- sum 14,680,064
Immediately we have to correct two problems – we can see the original Iiot1 table – but it doesn’t have an associated data segment, it has a “top”, which is the primary key index segment, and an “overflow” which is designated by Oracle as a table segment. So we need to check the columns in dba_tables to work out how to get the more appropriate name reported for the table segment, and add a predicate to discard the row with the original name. Here’s “tables only mark 2”:
select tab.owner, cast('TABLE' as varchar2(11)) object_type, case when tab.iot_type = 'IOT_OVERFLOW' then tab.iot_name else tab.table_name end object_name, case when tab.iot_type = 'IOT_OVERFLOW' then tab.table_name end auxiliary_name, nvl(seg_tab.bytes,0) bytes from dba_tables tab left outer join dba_segments seg_tab on seg_tab.owner = tab.owner and seg_tab.table_name = tab.table_name where tab.owner = 'TEST_USER' and nvl(tab.iot_type,'NORMAL') != 'IOT' OWNER OBJECT_TYPE OBJECT_NAME AUXILIARY_NAME BYTES --------------- ----------- -------------------- ------------------------- ---------------- TEST_USER TABLE T1 12,582,912 TEST_USER TABLE IOT1 SYS_IOT_OVER_75572 1,048,576 TEST_USER TABLE DEMO_NEST 1,048,576
I’ve identified the table type using the iot_type column in dba_tables, eliminated rows where the iot_type is ‘IOT’, and reported the iot_name (with table_name as an auxiliary name) when the iot_type is ‘IOT_OVERFLOW’. And we can now check that the result is consistent with the content of dba_segments that lists segment_type = ‘TABLE’.
So let’s move on to indexes.
select ind.table_owner owner, cast('INDEX' as varchar2(11)) object_type, ind.table_name, index_name auxiliary_name, nvl(seg_ind.bytes,0) bytes from dba_indexes ind left outer join dba_segments seg_ind on ind.owner = seg_ind.owner and ind.index_name = seg_ind.segment_name where ind.table_owner = 'TEST_USER' OWNER OBJECT_TYPE TABLE_NAME AUXILIARY_NAME BYTES --------------- ----------- -------------------- ------------------------- ---------------- TEST_USER INDEX T1 SYS_IL0000075565C00027$$ 1,048,576 TEST_USER INDEX T1 SYS_IL0000075565C00028$$ 1,048,576 TEST_USER INDEX T1 T1_I1 5,242,880 TEST_USER INDEX T1 T1_I2 5,242,880 TEST_USER INDEX IOT1 IOT1_PK 1,048,576 TEST_USER INDEX IOT1 SYS_IL0000075572C00007$$ 1,048,576 TEST_USER INDEX IOT1 SYS_IL0000075572C00006$$ 1,048,576 TEST_USER INDEX T_NESTED1 SYS_FK0000075579N00002$ 1,048,576 TEST_USER INDEX T_NESTED2 SYS_FK0000075579N00004$ 1,048,576 TEST_USER INDEX DEMO_NEST SYS_C008380 1,048,576 TEST_USER INDEX DEMO_NEST SYS_C008381 1,048,576 TEST_USER INDEX T_NESTED1 TN1_PK 1,048,576 TEST_USER INDEX T_NESTED2 TN2_PK 1,048,576 TEST_USER INDEX T_NESTED1 TN1_V1 1,048,576 TEST_USER INDEX T_NESTED2 TN2_V1 1,048,576
A quick check shows that we’ve picked up 15 indexes that can be matched against the segments reported by dba_segments – although my query labelled them all as just “INDEX” while dba_segments labels LOB indexes differently. As you can see I’ve reported the table name in each case and reported the index name under the heading auxiliary”_name. This will be a convenience when I’m summing up the space associated with each table.
On to the LOBs – also easy:
select lob.owner, cast('LOB' as varchar2(11)) object_type, lob.table_name, lob.column_name auxiliary_name, nvl(seg_lob.bytes,0) bytes from dba_lobs lob left outer join dba_segments seg_lob on seg_lob.owner = lob.owner and seg_lob.segment_name = lob.segment_name where lob.owner = 'TEST_USER' OWNER OBJECT_TYPE TABLE_NAME AUXILIARY_NAME BYTES --------------- ----------- -------------------- ------------------------- ---------------- TEST_USER LOB T1 C1 2,097,152 TEST_USER LOB T1 C2 2,097,152 TEST_USER LOB IOT1 CT1 1,048,576 TEST_USER LOB IOT1 CT2 1,048,576
And finally the nested tables:
select nst.owner, cast('NESTED' as varchar2(11)) object_type, nst.parent_table_name object_name, table_name auxiliary_name, nvl(seg_nst.bytes,0) bytes from dba_nested_tables nst left outer join dba_segments seg_nst on seg_nst.owner = nst.owner and seg_nst.segment_name = nst.table_name where nst.owner = 'TEST_USER' OWNER OBJECT_TYPE OBJECT_NAME AUXILIARY_NAME BYTES --------------- ----------- -------------------- ------------------------- ---------------- TEST_USER NESTED DEMO_NEST T_NESTED1 1,048,576 TEST_USER NESTED DEMO_NEST T_NESTED2 1,048,576
A quick check tells us that the four pieces of code produce an exact match for all the segments reported in dba_segments – so all we have to do now is stitch the four queries together with UNION ALL then sum(bytes) by owner and table_name.
There are 3 little problems to deal with though.
- We have the predicate “where {owner} = ‘TEST_USER'” appearing 4 times in total, which is a bit messy. If we put this outside the UNION ALL will Oracle be smart enough to push it back inside the UNION ALL as part of its query transformation. It seems to, but the plan (which is several hundred lines long – so I’m not going to look too closely) does change a little, but on my little test system it didn’t make much difference to the performance. Initially it looks as if it might be okay to create a view from this query with no restricition on table owner and allow users to query the view with a schema name.
- The code to report indexes reports the indexes on the nested tables under t_nested1 and t_nested2 – we want the indexes to be associated with table demo_nest, so we need to refine that bit of the code. It needs an outer join to dba_nested_tables to supply the parent_table_name if it exists.
- On my little system the query takes several seconds to run. Should I worry about that. Not until I’ve tested it on a much bigger system, and not until I know how frequently it needs to run. It may be good enough as it stands, and the ease with which I actually modified the first version of my code to handle the nested tables indexing issue is an indication of the benefits of keeping a complex job as the sum of its parts if it’s reasonable to do so.
Let’s go straight to the final (so far) SQL:
select owner, object_name, sum(bytes) from ( select tab.owner, cast('TABLE' as varchar2(11)) object_type, case when tab.iot_type = 'IOT_OVERFLOW' then tab.iot_name else tab.table_name end object_name, case when tab.iot_type = 'IOT_OVERFLOW' then tab.table_name end auxiliary_name, nvl(seg_tab.bytes,0) bytes from dba_tables tab left outer join dba_segments seg_tab on seg_tab.owner = tab.owner and seg_tab.segment_name = tab.table_name where tab.owner = 'TEST_USER' and nvl(tab.iot_type,'NORMAL') != 'IOT' union all select ind.table_owner owner, cast('INDEX' as varchar2(11)) object_type, nvl( nst.parent_table_name, ind.table_name ) table_name, index_name auxiliary_name, nvl(seg_ind.bytes,0) bytes from dba_indexes ind left outer join dba_nested_tables nst on nst.owner = ind.table_owner and nst.table_name = ind.table_name left outer join dba_segments seg_ind on seg_ind.owner = ind.owner and seg_ind.segment_name = ind.index_name where ind.table_owner = 'TEST_USER' union all select lob.owner, cast('LOB' as varchar2(11)) object_type, lob.table_name, lob.column_name auxiliary_name, nvl(seg_lob.bytes,0) bytes from dba_lobs lob left outer join dba_segments seg_lob on seg_lob.owner = lob.owner and seg_lob.segment_name = lob.segment_name where lob.owner = 'TEST_USER' union all select nst.owner, cast('NESTED' as varchar2(11)) object_type, nst.parent_table_name object_name, table_name auxiliary_name, nvl(seg_nst.bytes,0) bytes from dba_nested_tables nst left outer join dba_segments seg_nst on seg_nst.owner = nst.owner and seg_nst.segment_name = nst.table_name where nst.owner = 'TEST_USER' ) where owner = 'TEST_USER' group by owner, object_name order by sum(bytes) / OWNER OBJECT_NAME SUM(BYTES) --------------- -------------------- ---------------- TEST_USER IOT1 6,291,456 TEST_USER DEMO_NEST 11,534,336 TEST_USER T1 29,360,128 ---------------- sum 47,185,920
I wrote this note because my response to the original query was an informal suggestion of taking this approach; then I thought it might be worth writing a general comment about solving big problems by starting with little problems and stitching the pieces together using this case as a concrete example – then I just went ahead to finish the thing off within the boundaries of the original requirements.
There’s a reason, though, why I tend to avoid publishing SQL that might be useful – it’s always possible to overlook bits and end up with people trying to use code that’s not totally appropriate to their system. In this case, of course, there’s a total (but deliberate) failure to handle clusters, partitions and subpartitions, but I don’t know how many other of the “less commonly used” bits of Otacle I haven’t considered. I do know that I haven’t touched on domain indexes (such as text indexes with all their convoluted collections of tables and indexes) but there may be some oddities of (e.g.) advanced queueing, replication, and audit vault that fall outside the range of the possibilities I’ve covered above.
Update 7th Nov 2019
The OP from OTN reported seeing some nested tables in the output – which I thought I’d catered for so I modified the code I have on my sandbox to add a nested table to the IOT, and added a couple of extra indexes on the parent of the nested table, changed a couple of columns and object names to quoted mixed case, and everything still seemed to work okay.
It turned out that the OP had nested tables that were nested 5 deep – which means I needed a hierarchical query to connect the 2nd to 5th (or nth) layer of nesting back to the parent to report the total volume against the top level table. In order to deal with this problem I modified the query in the smallest possible way.
I had two references to the view dba_nested_tables, so I created a hierarchical query based on dba_nested_tables that returned the “oldest ancestor” with each child table rather than the immediate parent. (I’ve written a separate note showing the derivation and testing). Then I turned this query into a “with subquery” (CTE) called top_nested_tables and changed the two calls that the main query made to dba_nested_tables so that they referenced the CTE instead:
with my_nested_tables as ( select /*+ materialize */ owner, parent_table_name, table_name from dba_nested_tables where owner = 'TEST_USER' ), top_nested_tables as ( -- ***** Replacement CTE defined select /*+ materialize */ owner, parent_table parent_table_name, table_name from ( select owner, level, connect_by_root parent_table_name parent_table, table_name from my_nested_tables connect by parent_table_name = prior table_name ) where (owner, parent_table) not in ( select owner, table_name from my_nested_tables ) ) select -- ***** Main select owner, object_name, sum(bytes) total_bytes from ( select tab.owner, cast('TABLE' as varchar2(11)) object_type, case when tab.iot_type = 'IOT_OVERFLOW' then tab.iot_name else tab.table_name end object_name, case when tab.iot_type = 'IOT_OVERFLOW' then tab.table_name end auxiliary_name, nvl(seg_tab.bytes,0) bytes from dba_tables tab left outer join dba_segments seg_tab on seg_tab.owner = tab.owner and seg_tab.segment_name = tab.table_name where tab.owner = 'TEST_USER' and nvl(tab.iot_type,'NORMAL') != 'IOT' union all select ind.table_owner owner, cast('INDEX' as varchar2(11)) object_type, nvl( nst.parent_table_name, ind.table_name ) table_name, index_name auxiliary_name, nvl(seg_ind.bytes,0) bytes from dba_indexes ind left outer join top_nested_tables nst -- ***** view replacement on nst.owner = ind.table_owner and nst.table_name = ind.table_name left outer join dba_segments seg_ind on seg_ind.owner = ind.owner and seg_ind.segment_name = ind.index_name where ind.table_owner = 'TEST_USER' union all select lob.owner, cast('LOB' as varchar2(11)) object_type, lob.table_name, lob.column_name auxiliary_name, nvl(seg_lob.bytes,0) bytes from dba_lobs lob left outer join dba_segments seg_lob on seg_lob.owner = lob.owner and seg_lob.segment_name = lob.segment_name where lob.owner = 'TEST_USER' union all select nst.owner, cast('NESTED' as varchar2(11)) object_type, nst.parent_table_name object_name, table_name auxiliary_name, nvl(seg_nst.bytes,0) bytes from top_nested_tables nst -- ***** view replacement left outer join dba_segments seg_nst on seg_nst.owner = nst.owner and seg_nst.segment_name = nst.table_name where nst.owner = 'TEST_USER' ) where owner = 'TEST_USER' group by owner, object_name order by sum(bytes) ;
If you followed the link to the original Oracle Developer Forum thread you’ll see that the OP has made much more use of the “with subquery” (CTE) construct, – including a rather nice touch of having a CTE that reads “select cast(‘MY_SCHEMA’ as varchar2(30)) from dual” – this is particularly cute because it means you only have to supply a fixed schema name once (rather than repeating it many times) in the query and then using a join to the CTE everywhere the name is needed.
Hi Jonathan,
I did similar thing including partitioned objects a couple of years ago: http://orasql.org/2017/12/16/top-n-biggest-tables-with-lobs-indexes-and-nested-table/
Best regards,
Sayan Malakshinov
Comment by Sayan Malakshinov — November 6, 2019 @ 6:23 pm GMT Nov 6,2019 |
Sayan,
Nice, but doesn’t meet the requirements.
I ran your script against the test schema above with the following results:
As you can see, sys_iot_over_75572 has been reported separately instead of being summed into iot1, and t_nested1 and t_nested2 have not been summed into demo_nest.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — November 6, 2019 @ 7:49 pm GMT Nov 6,2019 |
Hello,
Similar but not exactly the same, I’ve never had to deal with nested tables or IOT’s.
SELECT *
FROM
(
select owner, table_name,
sum(nvl(table_mb,0)) as table_mb,
sum(nvl(index_mb,0)) as index_mb,
sum(nvl(lobsegment_mb,0)) as lobseg_mb,
sum(nvl(lobindex_mb,0)) as lobindex_mb,
sum((nvl(table_mb,0) + nvl(index_mb,0) + nvl(lobsegment_mb,0) + nvl(lobindex_mb,0))) as total_mb,
ratio_to_report((sum((nvl(table_mb,0) + nvl(index_mb,0) + nvl(lobsegment_mb,0) + nvl(lobindex_mb,0)))) )OVER () * 100 as perc
from
(
select owner, table_name, segment_type, segment_name, mb
from
(
select s.owner, s.segment_name, s.segment_type, l.table_name, ‘LS’ as discrim, bytes/1048576 as mb
from dba_segments s,
dba_lobs l
where s.owner = ‘&X_SCHEMA’
and s.owner = l.owner
and s.segment_name = l.segment_name
and s.segment_type IN ( ‘LOBSEGMENT’, ‘LOB PARTITION’)
UNION ALL
select s.owner, s.segment_name, s.segment_type, l.table_name, ‘LI’ as discrim, bytes/1048576 as mb
from dba_segments s,
dba_lobs l
where s.owner = ‘&X_SCHEMA’
and s.owner = l.owner
and s.segment_name = l.index_name
and s.segment_type = ‘LOBINDEX’
UNION ALL
select s.owner, s.segment_name, ‘INDEX’ as segment_type, l.table_name, ‘IN’ as discrim, bytes/1048576 as mb
from dba_segments s,
dba_indexes l
where s.owner = ‘&X_SCHEMA’
and s.owner = l.owner
and s.segment_name = l.index_name
and s.segment_type IN ( ‘INDEX’, ‘INDEX PARTITION’, ‘INDEX SUBPARTITION’)
UNION ALL
select s.owner, s.segment_name, ‘TABLE’ as segment_type, t.table_name, ‘TA’ as discrim, bytes/1048576 as mb
from dba_segments s,
dba_tables t
where s.owner = ‘&X_SCHEMA’
and s.owner = t.owner
and s.segment_name = t.table_name
and s.segment_type IN ( ‘TABLE’, ‘TABLE PARTITION’, ‘TABLE SUBPARTITION’)
)
where mb > 0.1
)
pivot
(
sum(mb)
for segment_type IN
(‘TABLE’ as table_mb,
‘INDEX’ as index_mb,
‘LOBSEGMENT’ as lobsegment_mb,
‘LOBINDEX’ as lobindex_mb
)
)
group by owner, table_name
)
where perc > 1
order by 8
/
Regards,
Frank
Comment by Frank — November 7, 2019 @ 1:03 pm GMT Nov 7,2019 |
Frank,
Thanks for the comment. Sorry I couldn’t do a quick reformat, sometimes I can just add “sourcecode” and “/sourcecode” (in square brackets, not quotes) and see the text regain it’s fixed format and spaces, but sometimes it just doesn’t work.
Your code didn’t do too badly with an IOT – it reported the “sys_iot_over” segment separately, but was happy to pick up all the rest of the bits (I had secsondary indexes and LOBs in my test) and attribute them to a table called IOT1.
Comment by Jonathan Lewis — November 8, 2019 @ 6:21 pm GMT Nov 8,2019 |
[…] note is a little side effect of answering a question about the total space associated with a table, including its indexes, LOB columns and nested tables. The first suggestion […]
Pingback by Nested Tables | Oracle Scratchpad — November 9, 2019 @ 11:28 am GMT Nov 9,2019 |
Hi Jonathan,
thanks for all of your hard work. This has been really helpful in understanding our data. When you say “this is particularly cute,” are you saying that in jest or because you think it is good code? The first rule of writing code should be don’t repeat yourself ( DRY ). Writing good code is hard because it requires a higher level of understanding of the problem than just bolting on more stuff to an existing query.
Thanks again!
“If you followed the link to the original Oracle Developer Forum thread you’ll see that the OP has made much more use of the “with subquery” (CTS) construct, – including a rather nice touch of having a CTE that reads “select cast(‘MY_SCHEMA’ as varchar2(30)) from dual” – this is particularly cute because it means you only have to supply a fixed schema name once in the query rather than having to repeat it several times in the course of the query because you can then inject the scemaname everywhere else as needed by a join to the CTE.”
Comment by Brian Byrd — November 12, 2019 @ 1:51 pm GMT Nov 12,2019 |
Brian.
Thanks for commenting.
I said it because I liked it.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — November 12, 2019 @ 4:48 pm GMT Nov 12,2019 |
Hi Jonathan,
The first couple examples joining dba_tables to dba_segments have an error.
Table_name and segment_name should be flipped. I wonder if it’s deliberate. The surprise when the error code popped up had me paying more attention to the rest of the post.
Thanks for expanding on the ideas in the original forum thread. The script was immediately useful. I’ll write a partitioned version eventually, and reusing your test setup will make it much easier.
Comment by Steven — August 11, 2022 @ 7:42 pm BST Aug 11,2022 |
Steven,
Thanks for pointing out the errors, which I’ve now fixed.
Thanks for letting me know that the script was useful.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — August 11, 2022 @ 8:26 pm BST Aug 11,2022 |