Tablespace growth Report

SQL> select TS# from v$tablespace where name='ABC' ;

       TS#
----------
         6

set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
not_in_awr EXCEPTION;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_count number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT count(*) INTO v_count FROM dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
IF v_count = 0 THEN
RAISE not_in_awr;
END IF ;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! NO DATA GROWTH WAS FOUND FOR TABLESPCE '||V_TS_NAME||' !!!');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE DOES NOT EXIST !!!');
WHEN NOT_IN_AWR THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('!!! TABLESPACE USAGE INFORMATION NOT FOUND IN AWR !!!');

END;
/


Summary
========
1) Allocated Space: 2048 MB (2 GB)
2) Used Space: 1558.44 MB (1.52 GB)
3) Used Space Percentage: 76.1 %


History
========
1) Allocated Space on 06-DEC-14: 2048 MB (2 GB)
2) Current Allocated Space on 10-JAN-15: 2048 MB (2 GB)
3) Used Space on 06-DEC-14: 1273 MB (1.24 GB)
4) Current Used Space on 10-JAN-15: 1558.44 MB (1.52 GB)
5) Total growth during last 35 days between 06-DEC-14 and 10-JAN-15: 285.44 MB (.28 GB)
6) Per day growth during last 35 days: 8.16 MB (.01 GB)


Expected Growth
===============
1) Expected growth for next 30 days: 244.66 MB (.24 GB)
2) Expected growth for next 60 days: 489.33 MB (.48 GB)
3) Expected growth for next 90 days: 733.99 MB (.72 GB)

PL/SQL procedure successfully completed.

Datafile Resize Script

set linesize 1000 pagesize 0 feedback off trimspool on
with
      hwm as (
      -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
      select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
      from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
     ),
     hwmts as (
      -- join ts# with tablespace_name
      select name tablespace_name,relative_fno,hwm_blocks
     from hwm join v$tablespace using(ts#)
    ),
    hwmdf as (
     -- join with datafiles, put 5M minimum for datafiles with no extents
     select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
     from hwmts right join dba_data_files using(tablespace_name,relative_fno)
    )
   select
    case when autoextensible='YES' and maxbytes>=bytes
    then -- we generate resize statements only if autoextensible can grow back to current size
     '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
      ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
      ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
    else -- generate only a comment when autoextensible is off
     '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
      ||'M from '||to_char(ceil(bytes/1024/1024),999999)
      ||'M after setting autoextensible maxsize higher than current size for file '
      || file_name||' */'
    end SQL
   from hwmdf
   where
    bytes-hwm_bytes>1*1024*1024*1024 -- resize only if at least 1Gb can be reclaimed
   order by bytes-hwm_bytes desc
   /


Query 2 :
========

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
   a.tablespace_name,
    a.file_name,
   a.bytes/1024/1024 file_size_MB,
    (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
     (select file_id,max(block_id) maximum         
      from dba_extents         
      group by file_id) b,
      dba_extents c,
     (select value db_block_size         
      from v$parameter         
      where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
and a.AUTOEXTENSIBLE='YES'
order by a.tablespace_name,a.file_name);

Tablespace usage report (w/ autoextend)

If you use auto extending table-spaces in your Oracle environment and use Grid Control to monitor them, you will know that it does not take the auto extension into account. The following script will give you a breakout of your TS usage including the auto extension.

============================================================
set pagesize 1000
set lines 200

col tablespace_name format a32 head 'Tablespace|Name'
col total format 999.99 head 'Total(GB)'
col used format 999.99 head 'Used(GB)'
col free format 999.99 head 'Free(GB)'
col pct format 999 head 'Pct|Used'
col next_extent format 999,999 head 'Next|Extent(KB)'
col Contents format a4 head 'Cont'
col Logging format a3 head 'Log'
col Extent_Management format a3 head 'Ext|Mgt'
col Allocation_Type format a5 head 'Alloc|Type'

SELECT inn1.tablespace_name tbs_name
 ,ROUND(inn1.total/1024,2) TOTAL_SPACE_GB
 ,ROUND((inn1.total-inn1.allocated+NVL(inn2.free,0))/1024,2) FREE_SPACE_GB
,ROUND((inn1.allocated-NVL(inn2.free,0))/1024,2) USED_SPACE_GB
,ROUND(((inn1.allocated-NVL(inn2.free,0))/inn1.total)*100) USED_PERCENT
FROM
(select tablespace_name,SUM(bytes)/1024/1024 allocated,SUM(DECODE(autoextensible,'YES',maxbytes,bytes))/1024/1024 total
    FROM dba_data_files
    GROUP BY tablespace_name) inn1,
(SELECT tablespace_name, NVL(SUM(bytes)/1024/1024,0) free
FROM dba_free_space
GROUP BY tablespace_name) inn2
WHERE inn1.tablespace_name= inn2.tablespace_name(+) 
and inn1.tablespace_name = '&tbsname' ;


SELECT file_name, tablespace_name, round(bytes / 1073741842,2) SIZE_GB, autoextensible, (increment_by * 32768) / 1048576 increment_MB, maxbytes / 1073741824 max_size_GB FROM dba_data_files WHERE tablespace_name = '&tbsname';

Convert MSSM to automatic segment space management (ASSM)?

The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace.

You cannot change the segment space management mode of a existing tablespace.

You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management.

To Convert the segment space management mode of a tablespace , you have to re-create the tablespace :

1. Export the contents of the tablespace
2. Drop the Tablespace which needed to be converted
3. Create tablespace with the desired segment space management mode
4. Import the contents of the the dump file to the newly created tablespace

Move or Rename the Tempfile in Oracle

#####  Move tempfile from location  ‘/sumit04/data01/sumit04/temp01.dbf’ to ‘/sumit04/data03/sumit04/temp01.dbf’  #####

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
FILE#  FILE_NAME STATUS
-----------------------------------------------
 1  /sumit04/data01/sumit04/temp01.dbf  ONLINE
 2 /sumit04/data02/sumit04/temp02.dbf   ONLINE

SQL> ALTER DATABASE TEMPFILE '/sumit04/data01/sumit04/temp01.dbf' OFFLINE;

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
 FILE# FILE_NAME STATUS
----------------------------------------------------
 1 /sumit04/data01/sumit04/temp01.dbf  OFFLINE
 2 /sumit04/data02/sumit04/temp02.dbf  ONLINE

#Copy the old temp files to other location(/data03):

SQL> !cp -p /sumit04/data01/sumit04/temp01.dbf /sumit04/data03/sumit04/temp01.dbf;

SQL> ALTER DATABASE RENAME FILE '/sumit04/data01/sumit04/temp01.dbf' TO '/sumit04/data03/sumit04/temp01.dbf';

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
  FILE#   FILE_NAME STATUS
--------------------------------------------------------
  1    /sumit04/data03/sumit04/temp01.dbf OFFLINE
  2   /sumit04/data02/sumit04/temp02.dbf  ONLINE

SQL> ALTER DATABASE TEMPFILE '/sumit04/data03/sumit04/temp01.dbf' ONLINE;

Database altered.

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
 FILE# FILE_NAME STATUS
---------------------------------------------------------
 1 /sumit04/data03/sumit04/temp01.dbf   ONLINE
 2 /sumit04/data02/sumit04/temp02.dbf   ONLINE

#Remove the old temp file
SQL> !rm -rf /sumit04/data01/sumit04/temp01.dbf