zbb20170816 Oracle Oracle View tablespace, data file size, and usage SQL statements

Oracle Oracle View tablespace, data file size, and usage SQL statements

--Table Space--1g=1024MB--1m=1024KB--1k=1024Bytes--1m=11048576Bytes--1g=1024x768*11048576bytes=11313741824BytesSELECT A.tablespace_name"Table space Name", Total"Table Space Size", Free"table space remaining size", ( total-free)"table space usage size", Total/ (1024x768*1024x768*1024x768)"table space Size (G)", Free/ (1024x768*1024x768*1024x768)"table Space remaining size (G)", ( total-free)/(1024x768*1024x768*1024x768)"Table space usage size (G)", round ( total-free)/Total,4) * - "Utilization%"From (SELECT tablespace_name, SUM (bytes) free from Dba_free_space GROUP by Tablespace_name) A, (SELECT tablespace_name, SUM (bytes) Total from Dba_data_files GROUP by Tablespace_name) b WHERE a.table Space_name=B.tablespace_name;--Data FilesSelectb.file_name Physical file name, B.tablespace_name tablespace, B.bytes/1024x768/1024x768size M, (b.bytes-Sum (NVL (A.bytes,0))) /1024x768/1024x768used M, substr (b.bytes-Sum (NVL (A.bytes,0))/(b.bytes) * -,1,5) utilization fromDba_free_space A, dba_data_files bwherea.file_id =b.file_id GROUP by B.tablespace_name, B.file_name, b.bytes order by B.tablespace_name; --1, view the name and size of the Tablespace select T.tablespace_name, round (SUM (bytes/ (1024x768*1024x768)),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 table space physical file name and size select Tablespace_name, file_id, file_name, round (bytes/ (1024x768*1024x768),0) Total_space from Dba_data_files ORDER by Tablespace_name;--3, view rollback segment name and size select Segment_name, Tablespace_name, R.status, (Initial_extent/1024x768) initialextent, (Next_extent/1024x768) nextextent, max_extents, V.curext curextent from Dba_rollback_segs R, V$rollstat v WHERE r.segment_id /c1>= V.usn (+) ORDER by Segment_name;--4, view Control file select NAME from V$controlfile;--5, view log files select MEMBER from V$logfile;--6, view usage of tablespace Select SUM (bytes)/ (1024x768*1024x768as 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* -)/A.bytes"% used", (C.bytes* -)/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';--9, view the date the database was created and how to archive select created, Log_mode, log_mode from V$database;

zbb20170816 Oracle Oracle View tablespace, data file size, and usage SQL statements