SQL statement for Oracle to view table space usage

Oracle queries table space usage SQL statement SQL1: www.2cto.com -- 1. queries the table space name and size 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 tablespace physical file. SELECT tablespace_name, file_id, file_name, round (bytes/(1024*1024), 0) total_space FROM dba_data_files order by tablespace_name; -- 3. Check the rollback segment name and size. SELECT segment_name, tablespace_name, r. status, (initial_ext Ent/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 the control file select name from v $ controlfile; -- 5. view the log file select member from v $ logfile; -- 6. View table space usage select sum (bytes)/(1024*1024) AS free_space, tablespace_name FROM dba_free_space group by tablespace_name; SELECT. tablespace_name,. bytes total, B. bytes used, c. bytes free, (B. bytes * 100)/. bytes "% USED", (c. bytes * 100)/. bytes "% FREE" FROM sys. sm $ ts_avail a, sys. sm $ ts_used B, sys. sm $ ts_free c WHERE. tablespace_name = B. tablespace_name AND. 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. view the database version SELECT version FROM product_component_version WHERE substr (product, 1, 6) = 'oracle '; -- 9. view the database creation date and archiving method SELECT created, log_mode, log_mode FROM v $ database; SQL2: -- 1G = 1024 MB -- 1 M = 1024KB -- 1 K = 1024 Bytes -- 1 M = 11048576 Bytes -- 1G = 1024*11048576 Bytes = 11313741824 Bytes SELECT. tablespace_name "tablespace name", total "tablespace size", free "tablespace remaining size", (total-free) "tablespace used size", total/(1024*1024*1024) "tablespace size (G)", free/(1024*1024*1024) "remaining tablespace size (G)", (total-free)/(1024*1024*1024) "tablespace 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. tablespace_name = B. tablespace_name