Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to check the size of the tablespace in Oracle, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. View the name and size of the tablespace

SELECT t.tablespace_name, round (SUM (bytes / (1024 * 1024)), 0) ts_size

FROM dba_tablespaces t, dba_data_files d

WHERE t.tablespace_name = d.tablespace_name

GROUP BY t.tablespace_name

2. View the name and size of the physical file in the tablespace

SELECT tablespace_name

File_id

File_name

Round (bytes / (1024 * 1024), 0) total_space

FROM dba_data_files

ORDER BY tablespace_name

3. View the name and size of the rollback segment

SELECT segment_name

Tablespace_name

R.status

(initial_extent / 1024) initialextent

(next_extent / 1024) nextextent

Max_extents

V.curext curextent

FROM dba_rollback_segs r, v$rollstat v

WHERE r.segment_id = v.usn (+)

ORDER BY segment_name

-- 4. View control files

SELECT NAME FROM v$controlfile

-- 5. View log files

SELECT MEMBER FROM v$logfile

6. Check the use of tablespaces

SELECT SUM (bytes) / (1024 * 1024) AS free_space, tablespace_name

FROM dba_free_space

GROUP BY tablespace_name

SELECT a.tablespace_name

A.bytes total

B.bytes used

C.bytes free

(b.bytes * 100) / a.bytes "% USED"

(c.bytes * 100) / a.bytes "% FREE"

FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c

WHERE a.tablespace_name = b.tablespace_name

AND a.tablespace_name = c.tablespace_name

7. View database objects

SELECT owner, object_type, status, COUNT (*) count#

FROM all_objects

GROUP BY owner, object_type, status

8. Check the version of the database

SELECT version

FROM product_component_version

WHERE substr (product, 1,6) = 'Oracle'

9. View the creation date and archiving method of the database

SELECT created, log_mode, log_mode FROM v$database

-- 1G=1024MB

-- 1M=1024KB

-- 1K=1024Bytes

-- 1M=11048576Bytes

-- 1G=1024*11048576Bytes=11313741824Bytes

SELECT a.tablespace_name Tablespace name

Total Tablespace size

Free "tablespace remaining size"

(total-free) "Tablespace usage size"

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

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

(total-free) / (1024 * 1024 * 1024) "Tablespace usage 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

On how to view the size of the tablespace in Oracle to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.