Free Space at tablespace level

One of the most basic administration task of Oracle DBA is to monitor the free space in tablespaces. Below script will give the total space allocated in megabytes, total used space in megabytes and how much percent of tablespace is free.

Here is the query:

select a.tablespace_name , A.Allocated "Total Space allocated", B.Freespace "Total Free space" , round(b.freespace/a.allocated*100) "% Free" from
(select tablespace_name ,sum(bytes)/1024/1024 Allocated from dba_data_files group by tablespace_name) A ,(select tablespace_name,sum(bytes)/1024/1024 Freespace
from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name;

Example:


SQL> select a.tablespace_name , A.Allocated "Total Space allocated", B.Freespace "Total Free space" , round(b.freespace/a.allocated*100) "% Free" from
(select tablespace_name ,sum(bytes)/1024/1024 Allocated from dba_data_files group by tablespace_name) A ,(select tablespace_name,sum(bytes)/1024/1024 Freespace
  2    3  from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name;
  4
TABLESPACE_NAME    Total Space allocated       Total Free space     % Free
--------------------------    ---------------------        ----------------    ----------
UNDOTBS1                                  30                                23.375              78
SYSAUX                                     240                                 2.4375              1
USERS                                           5                                   1.9375             39
SYSTEM                                     480                                  7.125               1
EXAMPLE                                  100                                 31.75                32
ABC                                             25                                 24.8125            99

6 rows selected.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Script to calculate free tablespace considering maxsize clause.


WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT NEW_TBS.TABLESPACE_NAME,
 ROUND (SUM (NEW_TBS.MYSIZE) / (1024 * 1024)) TOTSIZE,
 ROUND (SUM (GROWTH) / (1024 * 1024)) GROWTH,
 ROUND ((SUM (NVL (FREEBYTES, 0))) / (1024 * 1024)) DFS,
 ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)
 ) TOTFREE,
 ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))
 / SUM (NEW_TBS.MYSIZE)
 * 100
 ) PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME
 ORDER BY 6
 ;
Also please find below more simpler scripts which just gives u tablespace details which matches following criteria:

Free space < 20 GB  & Free percentage < 20 %

WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT * FROM
 (SELECT 'TABLESPACE NAME:',NEW_TBS.TABLESPACE_NAME,
 'FREE MB:', ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)) AS TOTFREE,
 'FREE PERCENT:', ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))/ SUM (NEW_TBS.MYSIZE)* 100) AS PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME) A
 WHERE A.PERC < 20 AND A.TOTFREE < 20480
 ORDER BY 6;

Achive log generation per day oracle

Sometimes we may face issues like since there was high number of archive log generation; the filesystem may get full. This will result into non functioning of database.

It may happen that a database is generating 35 archives log on weekdays and more than 100 archive logs weekends due to heavy transactions on weekend. If we have the archive log generation data handy we can plan our action accordingly.

By using this query we can get the trend of the number of archives logs generated on per day basis, minimum and maximum sequence of the archive log and physical space occupied at OS level in megabytes(MB).
Note: We can get the actual size of archive log generated only if we have not specified any value to ARCHIVE_LAG_TARGET.

Here the query goes as :

select trunc(first_time) on_date,
       thread# thread,
       min(sequence#) min_sequence,
       max(sequence#) max_sequence,
       max(sequence#) - min(sequence#) nos_archives,
       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from   v$log_history,
       (select avg(bytes/1024/1024) log_avg_mb
        from   v$log)
group  by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time) asc


Example:

SQL> select trunc(first_time) on_date,
       thread# thread,
       min(sequence#) min_sequence,
       max(sequence#) max_sequence,
       max(sequence#) - min(sequence#) nos_archives,
       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from   v$log_history,
       (select avg(bytes/1024/1024) log_avg_mb
        from   v$log)
group  by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time) asc;

ON_DATE        THREAD MIN_SEQUENCE MAX_SEQUENCE NOS_ARCHIVES REQ_SPACE_MB
---------- ---------- ------------ ------------ ------------ ------------

06/05/2013          1           23           28            5          625
06/08/2013          1           29           29            0            0
06/09/2013          1           30           30            0            0
06/10/2013          1           31           31            0            0
06/11/2013          1           32           32            0            0
06/12/2013          1           33           33            0            0
06/13/2013          1           34           42            8         1000
06/14/2013          1           43           83           40         5000
06/15/2013          1           84           84            0            0
06/16/2013          1           85           85            0            0
06/17/2013          1           86           86            0            0
06/18/2013          1           87          158           71         8875
06/19/2013          1          159          159            0            0
06/21/2013          1          160          183           23         2875


In the above example, we can identify that on 18th June 2013; 8875 MB of achive log was generated. The minimum sequence was 87 and maximum sequence was 158.
Total 71 number of archive logs generated.

Few people may get output only for last seven, simultaneously few can get the output for as long as 5 years. This all depends upon the size of the control file.

Hope this helps some way to the DBA's. Good luck.

Identify locked objects at oracle

This query helps the DBA and application developer to identify the locked objects. This query gives you the detail for locked object,owner of object and the hostname of the user who has acquired lock on object.

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,b.osuser, b.machine
 FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

Example:

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id;

OWNER      OBJECT_NAM OBJECT_TYPE                SID    SERIAL# STATUS   OSUSER          MACHINE
---------- ---------- ------------------- ---------- ---------- -------- --------------- ---------------
ASHISH     T1         TABLE                      148         10 INACTIVE oracle          oracle10g.works
                                                                                         tation.com

Above example, shows table t1 is locked. User has occupied lock on oracle10g.workstation.com machine.

Most waiting session in Oracle

While working on performance issues of the database. Many times DBA needs to identify the user session which is waiting the most for the resources.

The below query will identify the most waiting sessions for resources.

select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;


Example:


SQL> select sesion.sid,
  2  sesion.username,
  3  sum(active_session_history.wait_time +
  4  active_session_history.time_waited) ttl_wait_time
  5  from v$active_session_history active_session_history,
  6  v$session sesion
  7  where active_session_history.sample_time between sysdate - 60/2880 and sysdate
  8  and active_session_history.session_id = sesion.sid
  9  group by sesion.sid, sesion.username
 10  order by 3;

       SID USERNAME                       TTL_WAIT_TIME
---------- ------------------------------ -------------
       145                                                                  0
       164     USER1                                             113
       166                                                          66819
       151     TEST                                        4885256


Here we can identify the user session with sid 151 waited for 4885256 seconds.

Good luck.