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
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.
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; /