Oracle View table space size and usage SQL statements

SQL1:

--1, view the name and size of the tablespace
SELECT T.tablespace_name, round (SUM (Bytes/(1024x768)), 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 for the Tablespace
SELECT Tablespace_name,
FILE_ID,
file_name,
Round (Bytes/(1024x768), 0) Total_space
From Dba_data_files
ORDER by Tablespace_name;
--3, view rollback segment name and size
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, viewing control files
SELECT NAME from V$controlfile;
--5, viewing log files
SELECT MEMBER from V$logfile;
--6, viewing the usage of tablespaces
SELECT SUM (bytes)/(1024x768 * 1024x768) 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 *)/a.bytes "% used",
(C.bytes * +)/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, viewing Database library objects
SELECT owner, object_type, status, COUNT (*) count#
From All_objects
GROUP by Owner, object_type, status;
--8, viewing the version of the database
SELECT version
From Product_component_version
WHERE substr (product, 1, 6) = ' Oracle ';
--9, viewing the date the database was created and how it was archived
SELECT created, Log_mode, log_mode from V$database;

=============================================== Split Line ======================================================

SQL2:

--1g=1024mb
--1m=1024kb
--1k=1024bytes
--1m=11048576bytes
--1g=1024*11048576bytes=11313741824bytes
Select A.tablespace_name "Table space name",
Total "Table space size",
Free "Table space remaining size",
(Total-free) "Tablespace usage Size",
Total/(1024 * 1024 * 1024) "Table space Size (G)",
Free/(1024 * 1024 * 1024) "Table space remaining size (G)",
(total-free)/(1024 * 1024 * 1024) "Table space use Size (G)",
Round ((total-free)/Total, 4) * 100 "Utilization%"
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

Oracle View table space size and usage SQL statements