Common query statements for Oracle Management

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

Or:

9. view the database creation date and archiving method

Select created, log_mode, log_mode from V $ database;

10. view the database Sid

Select name from V $ database;

11. view the listening port of an Oracle instance

You only need to view the listener. ora file of the Listener Configuration File. To change the port, you only need to modify the file and restart the instance.

12. (to be updated ....)

Select banner from SYS. V _ $ version;