Oracle manages common query statements

1. View the name and size of the table space

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 table space physical file name and size

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. Viewing log files

Select member from V$logfile;

6. View the usage of table space

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 Library objects

Select owner, object_type, Status, COUNT (*) count# from All_objects Group by owner, object_type, status;

8. View the version of the database

Select version from Product_component_version
Where SUBSTR (product,1,6) = ' Oracle ';

Or:

9. View the date the database was created and how it was archived

Select Created, Log_mode, Log_mode from V$database;

10. View Database SID

Select name from V$database;

11. View the Oracle instance listening port

Only need to view the listening profile Listener.ora file, if you need to change this port, just modify the file and then restart the instance

12. (Pending update ...)

Select banner from Sys.v_$version;

Oracle manages common query statements