-4

Is there any space usage display procedure in oracle for an input table like sp_space_used in sql server...

closed as off-topic by Balazs Papp, mustaccio, Colin 't Hart, Paul White says GoFundMonica Jan 25 at 12:57

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Balazs Papp, mustaccio, Colin 't Hart, Paul White says GoFundMonica
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Don't expect it to do it "like SQL Server". Oracle and MSSQL are profoundly different in their architecture - which would impact any sort of space usage analysis. As long as you expect to approach Oracle while wearing your Microsoft goggles, you can expect less than optimal results. "When in Rome . . . " – EdStevens Jan 24 at 22:51
  • If your "use case" is to "blindly follow" a procedure that doesn't apply to the architecture, then there's nothing that can be added. Would you "blindly follow" a procedure to adjust the fuel injectors on an all-electric car? – EdStevens Jan 25 at 17:45
1

AFAIK no. But you can build your own by querying the tables user_segments, user_extents, user_indexes and user_tab_statistics.

I recommend you to familiarize with the Oracle logical storage structure.

In practice, you can forget about table extents allocation and focus in TABLESPACEs allocation and fragmentation.

1

To get the size of all tables the current user owns, you can query user_segments but it's a bit tricky to bring together all the different segment types properly.

I think the following returns the correct size information (but I might have missed some combinations, e.g. I am not sure if index organized tables are handled correctly):

with size_info as (
  select case 
            when sg.segment_type in ('INDEX','LOBINDEX') then ix.table_name
            when sg.segment_type = 'LOBSEGMENT' then ul.table_name
            else sg.segment_name
         end as table_name,
         sg.segment_type,
         sg.bytes / 1024 / 1024 as size_mb
  from user_segments sg
    left join user_indexes ix on sg.segment_name = ix.index_name and sg.segment_type in ('INDEX', 'INDEX PARTITION', 'LOBINDEX')
    left join user_lobs ul on sg.segment_name = ul.segment_name and sg.segment_type = 'LOBSEGMENT'
) 
select table_name, 
       sum(case when segment_type in ('TABLE', 'LOBSEGMENT', 'TABLE PARTITION') then size_mb end) as table_size_mb,
       sum(case when segment_type in ('INDEX', 'INDEX PARTITION', 'LOBINDEX') then size_mb end) as index_size_mb
from size_info 
group by table_name;

I don't know what "reserved" or "unused" is supposed to be.

The "number of rows" could be retrieved from user_tables.num_rows but that's just an estimate from the last time the statistics were updated.

Of course you can add a where clause to restrict that query to just one table.


If you insist on making things more complicated than needed and want to use an unnecessary stored procedure instead of running a simple SQL query, then you can put the above into a procedure:

create or replace procedure get_table_statistics
as
  c1 SYS_REFCURSOR;  
BEGIN
  OPEN c1 FOR 
    .... the query from above ...;

  DBMS_SQL.RETURN_RESULT(c1);
END;
/ 
  • @rock_techie: again: why a procedure? A plain SQL script is much easier to use. In Oracle, procedures are very rarely used to return results. That's what queries are for if you want to encapsulate the query, use a view. If you are migrating from SQL Server, stop doing things the "SQL Server way" - Oracle is very different and usually works better if you don't force unusual habits on it (e.g. the excessive use of temp tables) – a_horse_with_no_name Jan 25 at 7:01
  • 1
    @rock_techie: use a view then so that the code doesn't need to know about the actual query. A procedure is the wrong choice for this in Oracle. Believe me. – a_horse_with_no_name Jan 25 at 7:04
  • There is no way you can run an application that was designed for SQL Server without changes with Oracle. You have to change the application at some point, there is no way around it - if at least introducing an abstraction layer for different DBMS products. Rather than creating clumsy, slow and hard-to-maintain hacks everywhere, start with those changes now. I am pretty sure if you insist on doing everything the "SQL Server" way your migration will fail (or at least you will be left with an application that performs poorly) – a_horse_with_no_name Jan 25 at 7:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.