View common SQL statements of a Database

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;