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

This article mainly introduces Oracle how to check the size and use of the tablespace, the article is very detailed, has a certain reference value, interested friends must read it!

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

The above is all the contents of the article "how to check the size and usage of tablespaces by Oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.