View Database SQL statements in Oracle

1. view 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_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. View table space usage

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