How to query tablespace size in database

This article mainly shows you "how to query the size of tablespaces in the database". The content is simple and clear, hoping to help you solve your doubts. Let the editor lead you to study and learn the article "how to query the size of tablespaces in the database".

1. Query the size of objects on a tablespace

Select owner,segment_name,segment_type,sum (bytes) / 1024 Universe 1024 as segment_size

From dba_segments

Where tablespace_name='xxx'

Group by owner,segment_name,segment_type

Order by 4

2. Generate statements that query the total number of records in all tables under a user

Select 'select''| | table_name | |''as table_name,count (*) as cnt from'| | table_name | | 'union all' from all_tables where owner='xxx'

3. Query the size and usage of the table space

SELECT a.tablespace_name

Total / (1024 * 1024 * 1024) "size (G)"

Free / (1024 * 1024 * 1024) "remaining size (G)"

(total-free) / (1024 * 1024 * 1024) "use size (G)"

Round ((total-free) / total, 4) * 100 "usage%"

FROM (SELECT tablespace_name, SUM (bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a

(SELECT tablespace_name, SUM (bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

The above is all the contents of the article "how to query the tablespace size in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!