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

In this issue, the editor will bring you about how to use SQL statements to check the size of the tablespace in Oracle. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Oracle's method of checking the size of the tablespace 1. The code is as follows:

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

Oracle's method to check the size of the tablespace 2. The code is as follows:

-- 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

This is how to use SQL statements to check the size of the tablespace in the Oracle shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.