Managing Database Storage

Managing Database Storage

One of the responsibilities of a Database Administrator is to manage space of a database. There should always be space for applications to write to a database. Database will go down when there is not enough space left on the disk.  As a DBA, you need to monitor the space and always make sure there is enough space left. There are various tools that the DBA can use to monitor and set an alert on. Oracle Grid and SQL Developer are just few tools provided by Oracle for free. 

Scenario:  A request came to move production table into a development database to test the application. Before you move the table data, you need to find out the size of production table. Next, you need to make sure you have space to target database.  

Let's find the actual size of table in production database. Actual table size = Size of a Table + Size of Index. Do not forget index size, there are times the index can occupy more space than the table therefore you should not ignore the index space usage.

-- Display table size

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024/1024 as GB

from dba_segments

where owner='ORDER_ADMIN'

and segment_name ='ORDER_SUMMARY'

group by OWNER, SEGMENT_NAME, SEGMENT_TYPE;

We know the table size, but we don't know the space required for index(s). We need to find out the name of Index(s) on a table before find the size of Indexes. Let's find the index name!

--Display index on a table

select index_name from dba_indexes where table_name = ‘ORDER_SUMMARY’;

SYS_C005899049

SYS_C005555858

We have two indexes on this table. Now, lets find the size of indexes

--Display Index Size

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024/1024 as GB

from dba_segments

where OWNER='ORDER_ADMIN'

and SEGMENT_NAME  in ('SYS_C005899049', 'SYS_C005555858')

group by OWNER, SEGMENT_NAME, SEGMENT_TYPE;

Combining these two queries result sets will provide you the actual true size of a table. Now, you need to find out do you have enough space available on the target tablespace. You need to find out the free space on  the target tablespace.

Lets see, how you can  find the size space of schema, tablespace and database?

-- Displays each segment size for a given schema.

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024/1024 as GB

from dba_segments

where owner='PRABINBANIYA'

group by OWNER, SEGMENT_NAME, SEGMENT_TYPE;

-- Displays the size of a given schema.

SELECT  SUM(BYTES)/1024/1024/1024 as GB

from dba_segments

where owner='VIP_ADMIN'

group by OWNER;

-- Displays the size of a each schema on your database.

SELECT OWNER, SUM(BYTES)/1024/1024/1024 as GB

from dba_segments

group by OWNER order by OWNER ASC;

--Check free/used space per tablespace

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (MB)",

       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 group by df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

Sample Output

Tablespace                      Size (MB)  Free (MB)     % Free     % Used

------------------------------ ---------- ---------- ---------- ----------

UNDOTBS1                                 65      17.8125         27         73

EXAMPLE                                   100     22.625          23         77

USERS                                         5         1.0625          21         79

TEMP                                           20        2                 10          90

sysaux                                           625     125               54.5       9       

SYSTEM                                      700     9.0625         1             99