Commonly viewed SQL viewing database SQL
1, the name for the table space 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, check the name of the table space physical file 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, see the name and size of the rollback 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, check the control file SELECT NAME FROM V $ ControlFile;
5, check the log file Select MEMBER from V $ logfile;
6. View the use 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 buy, 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 Object 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'
9. View the database creation date and archive SELECT CREATED, LOG_MODE, LOG_MODE FROM V $ DATABASE
