Oracle Common Scripts

Oracle Database Storage Usage queries

Create a view using the system SYS or DBA authority account as follows:

2. This is mainly obtained from table dba_data_files,dba_segments two tables in the database.
3. The default database holds a byte unit and the conversion relationship is as follows:
   1024bytes = 1kb
   1024KB = 1M
   1024M = 1G

   CREATE OR REPLACE VIEWV_get_diskspace as SELECTa.tablespace_name, A. Use"Used (MB)", (B.total-A. Use) "Free (MB)", B.total "Total (MB)",round((b.total-A. Use)/B.total,5)* -||'%'"Per_free" from ( SelectTablespace_name,sum(bytes)/(1024x768*1024x768) as  Use  fromdba_segments/*where Tablespace_name not in (' USERS ', ' sysaux ', ' UNDOTBS1 ', ' SYSTEM ', ' UNDOTBS2 ')*/ GROUP  bytablespace_name) A, ( withTablespace_total as ( SELECTTablespace_name,sum(MaxBytes)/(1024x768*1024x768) Total fromdba_data_files TWHERE /*T.tablespace_name not in (' USERS ', ' sysaux ', ' UNDOTBS1 ', ' SYSTEM ', ' UNDOTBS2 ') and*/T.autoextensible='YES' Group  byTablespace_nameUNION  All SELECTTablespace_name,sum(bytes) Total fromdba_data_files TWHERE /*T.tablespace_name not in (' USERS ', ' sysaux ', ' UNDOTBS1 ', ' SYSTEM ', ' UNDOTBS2 ') and*/T.autoextensible='NO' Group  bytablespace_name)SELECTTablespace_name,SUM(total) Total fromTablespace_totalGROUP  bytablespace_name) BWHEREA.tablespace_name=B.tablespace_name;

   Linux Server queries

   1. Du-sch * Use this command to query the space occupied by folders under the current directory

   2. DF-HL Querying disk space remaining

Oracle Common Scripts