Oracle DBA-SQL statement for viewing table space usage

1. SQL statement 1:

-- 1. view the table space name and size <br/> select T. tablespace_name, round (sum (Bytes/(1024*1024), 0) ts_size <br/> from dba_tablespaces T, dba_data_files d <br/> where T. tablespace_name = D. tablespace_name <br/> group by T. tablespace_name; <br/> -- 2. view the name and size of the physical tablespace file <br/> select tablespace_name, <br/> file_id, <br/> file_name, <br/> round (Bytes/(1024*1024), 0) total_space <br/> from dba_data_files <br/> order by tablespace_name; <br/> -- 3. view the rollback segment name and size <br/> select segment_name, <br/> tablespace_name, <br/> r. status, <br/> (initial_extent/1024) initialextent, <br/> (next_extent/1024) nextextent, <br/> max_extents, <br/> v. curext curextent <br/> from dba_rollback_segs R, V $ rollstat v <br/> where R. segment_id = v. USN (+) <br/> order by segment_name; <br/> -- 4. view the control file <br/> select name from V $ controlfile; <br/> -- 5. view log files <br/> select Member from V $ logfile; <br/> -- 6. View table space usage <br/> select sum (bytes)/(1024*1024) as free_space, tablespace_name <br/> from dba_free_space <br/> group by tablespace_name; <br/> select. tablespace_name, <br/>. bytes total, <br/> B. bytes used, <br/> C. bytes free, <br/> (B. bytes * 100)/. bytes "% used", <br/> (C. bytes * 100)/. bytes "% free" <br/> from sys. SM $ ts_avail A, sys. SM $ ts_used B, sys. SM $ ts_free C <br/> where. tablespace_name = B. tablespace_name <br/> and. tablespace_name = C. tablespace_name; <br/> -- 7. view database objects <br/> select owner, object_type, status, count (*) count # <br/> from all_objects <br/> group by owner, object_type, status; <br/> -- 8. view the database version <br/> select version <br/> from product_component_version <br/> where substr (product, 1, 6) = 'oracle '; <br/> -- 9. view the database creation date and archiving method <br/> select created, log_mode, log_mode from V $ database; <br/>

 

2. SQL statement 2:
-- 1G = 1024 MB <br/> -- 1 m = 1024kb <br/> -- 1 k = 1024 bytes <br/> -- 1 m = 11048576 bytes <br/> -- 1G = 1024*11048576 bytes = 11313741824 bytes <br/> select. tablespace_name "tablespace name", <br/> total "tablespace size", <br/> free "tablespace remaining size", <br/> (total-free) "tablespace size", <br/> total/(1024*1024*1024) "tablespace size (g)", <br/> free/(1024*1024*1024) "remaining tablespace size (g)", <br/> (total-free)/(1024*1024*1024) "used tablespace size (g )", <br/> round (total-free)/total, 4) * 100 "usage %" <br/> from (select tablespace_name, sum (bytes) free <br/> from dba_free_space <br/> group by tablespace_name) A, <br/> (select tablespace_name, sum (bytes) total <br/> from dba_data_files <br/> group by tablespace_name) B <br/> where. tablespace_name = B. tablespace_name