Oracle Tablespace Utilization Script

Tablespace Utilization Script for Tablespace Space Used % more than 85 %
==========================================================================

Check the tablespace Utilization.
=========================================

set pages 500 lines 250
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>85
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name;


Check the details of the datafiles for a particular TableSpace 
=====================================================================


set pages 500 lines 250
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name=UPPER('&TABLESPACENAME') order by 1,2,6;



Get DDL for tablespace 
==============================

set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE',upper('&TABLESPACE_NAME')) FROM DUAL;

Move datafile one location to different location online in oracle 12c Version 12.1.0.2 and later

Move datafile one location to different location  online in  oracle 12c Version 12.1.0.2 and later:
==========================================================================================================


Login as sysdba 



to get the file id and datafile loation info using below query 
=========================================================================
select FILE_ID ,FILE_NAME,TABLESPACE_NAME from dba_data_files ;



In OMF (Oracle Managed file name feature)
=====================================================

Error  while move datafile in OMF : 


SQL> ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data21/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data22/abcd_grlw8cvz_.dbf';
ALTER DATABASE MOVE DATAFILE '/oracle/oradata/data1/abcd_grlw8cvz_.dbf' to '/oracle/oradata/data1/abcd_grlw8cvz_.dbf'
*
ERROR at line 1:
ORA-01276: Cannot add file /oracle/oradata/data1/abcd_grlw8cvz_.dbf.  File has an Oracle Managed Files file name.


SQL>

Solution
==============

Set db_create_file_dest before moving the datafile and do not specify the datafile name

SQL> alter session set db_create_file_dest='<DIR_NEW>';
SQL> alter database move datafile 21;




In Normal 
==================

alter database move datafile '/u01/oradata/test/test.dbf' to '/u02/oradata/test/test.dbf';

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR OVER THE LAST WEEK:

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR OVER THE LAST WEEK:

Using below query you can find how many log switch in last week per hour basis. this will help to analyze how much database was busy in last week and what time db is so much busy .

You can set FRA based of log switch analysis .if db is running in archive log mode .




Query :
=============

SET PAGESIZE 500
SET LINESIZE 200
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <8;


Example :


SQL>   2  SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
  3    4  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
  5  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
  6  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
  7  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
  8  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
  9  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
 10   11  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
 12   13   14   15   16  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
 17   18   19  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
 20   21   22   23  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
 24   25  , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
 26   27   28   29   30   31    ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <10; 32

DAY   00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
20/11    55   470    51     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0
19/11    54   417   878    83     0     0     4     5   272   516   175     0     2     0     0     0     0     0    12   159     0     1     4     0
18/11    94   380     0   399   577     0     1     3     0   101     0     0     2     0     0     0     0     1     9   150     0     1     1     0
17/11     0     0     0     1     0     0     5     9     0     0     0     0     2     0     0     0     0     0     3    14     0     1     0     0
16/11    16     0     0     0     0     0     0     0     1     0     0     0     2     0     0     0     0     0     0     3     0     0     0     0
15/11    60   505   707    84     0     0     9     8     0    97     0     1     1     0     0     0     0     1    21   167     0    35     0     0
14/11    58   422     0     0     0     0     5    16     0    96    50    32     2     0     1     0     0     0   807   166     0     1     0     0
13/11     0     0    10    35    35   419    19     7     0    98     0     0     2     0     0     0     0   356   953   163     0     1     0     0

8 rows selected.


SQL>

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

SCRIPT TO LIST THE HISTORY OF LOG SWITCHES PER HOUR IN DAY

Using below query you can find how many redo log switched per hours in oracle database .it will show you how much database is busy .


Login as sys user and run below query .

Query:
============

select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
 from v$log_history 
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
 )
group by Hour
order by Hour;


Example :

SQL> select Hour , round(avg(LOG_SWITCHES)) LOG_SWITCHES
from (
 select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DDD'), count(*) LOG_SWITCHES
  2    3    4   from v$log_history
 group by TRUNC(FIRST_TIME, 'DDD'), trunc(first_time, 'HH')
 order by 1
  5    6    7   )
group by Hour
  8    9  order by Hour;

HO LOG_SWITCHES
-- ------------
00           56
01          439
02          412
03          120
04          306
05          419
06            7
07            8
08          137
09          182
10          113
11           17
12            2
14            1
17          119
18          301
19          117
21            7
22            3

19 rows selected.

SQL>