Oracle View table space size and usage SQL statements

 --1. View the name and size of the table spaceSELECTT.tablespace_name,round(SUM(bytes/(1024x768 * 1024x768)),0) Ts_size fromdba_tablespaces T, dba_data_files DWHERET.tablespace_name=D.tablespace_nameGROUP  byT.tablespace_name;

--2. View the table space physical file name and sizeSELECTTablespace_name,file_id, file_name, round(bytes/(1024x768 * 1024x768),0) Total_space fromDba_data_filesORDER  byTablespace_name;
--3. Check the rollback segment name and sizeSELECTsegment_name, Tablespace_name, R.status, (Initial_extent/ 1024x768) initialextent, (Next_extent/ 1024x768) nextextent, max_extents, V.curext curextent fromDba_rollback_segs R, V$rollstat vWHEREr.segment_id=V.USN (+) ORDER  bysegment_name;
--4. View Control FilesSELECTNAME fromV$controlfile;
--5. Viewing log filesSELECTMEMBER fromV$logfile;
--6. View the usage of table spaceSELECT SUM(bytes)/(1024x768 * 1024x768) asFree_space, Tablespace_name fromDba_free_spaceGROUP  byTablespace_name;SELECTA.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes*  -)/A.bytes "%used ", (C.bytes*  -)/A.bytes "%Free " fromSys.sm$ts_avail A, sys.sm$ts_used B, Sys.sm$ts_free cWHEREA.tablespace_name=B.tablespace_name andA.tablespace_name=C.tablespace_name;

--7. View Database Library ObjectsSELECTOwner, object_type, status,COUNT(*)Count#  fromall_objectsGROUP  byowner, object_type, status;
--8. View the version of the databaseSELECTversion fromproduct_component_versionWHERESUBSTR (Product,1,6)= 'Oracle';
--9. View the date the database was created and how it was archivedSELECTCreated, Log_mode, Log_mode fromv$Database;

Oracle View table space size and usage SQL statements