TABLESPACES AND DATAFILES

08:52 Feroz Khan 0 Comments


    • Tablespace Usage with autoextent consideration


set lines 1000
col TBS_NAME for a25
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
 -- where tablespace_name in ('CP_INDX','CP_DATA','CMN_INDX','CMN_DATA')
    GROUP BY tablespace_name) inn1,
(SELECT tablespace_name, NVL(SUM(bytes)/1024/1024,0) free
FROM dba_free_space
--where tablespace_name in ('CP_INDX','CP_DATA','CMN_INDX','CMN_DATA')
GROUP BY tablespace_name) inn2
WHERE inn1.tablespace_name= inn2.tablespace_name(+)
and inn1.tablespace_name='&tablespace_name'
order by 5 desc;



  • Tablespace Usage without autoextent consideration

colu tablespace format A20
colu total_size_gb format 99999.999
colu free_size_gb format 99999.999
colu Used_size_gb format 99999.999
select df.tablespace_name tablespace, df.bytes/(1024*1024*1024) total_size_gb, (df.bytes/(1024*1024*1024) -sum(fs.bytes)/(1024*1024*1024)) Used_size_gb,
sum(fs.bytes)/(1024*1024*1024) free_size_gb,
round(sum(fs.bytes)*100/df.bytes) free_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) used_pct
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
and df.tablespace_name='&tablespace_name'
group by df.tablespace_name, df.bytes 
order by 1;




Undo tablespace and retention recommendation script



SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
    v_analyse_start_time    DATE := SYSDATE - 7;
    v_analyse_end_time      DATE := SYSDATE;
    v_cur_dt                DATE;
    v_undo_info_ret         BOOLEAN;
    v_cur_undo_mb           NUMBER;
    v_undo_tbs_name         VARCHAR2(100);
    v_undo_tbs_size         NUMBER;
    v_undo_autoext          BOOLEAN;
    v_undo_retention        NUMBER(5);
    v_undo_guarantee        BOOLEAN;
    v_instance_number       NUMBER;
    v_undo_advisor_advice   VARCHAR2(100);
    v_undo_health_ret       NUMBER;
    v_problem               VARCHAR2(1000);
    v_recommendation        VARCHAR2(1000);
    v_rationale             VARCHAR2(1000);
    v_retention             NUMBER;
    v_utbsize               NUMBER;
    v_best_retention        NUMBER;
    v_longest_query         NUMBER;
    v_required_retention    NUMBER;
BEGIN
    select sysdate into v_cur_dt from dual;
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

    v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
    select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;

    DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
    DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
    DBMS_OUTPUT.PUT_LINE('End Time   : ' || v_analyse_end_time);
  
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
    DBMS_OUTPUT.PUT_LINE('--------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
    IF V_UNDO_AUTOEXT THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON'); 
    ELSE
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF'); 
    END IF;
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);

    IF v_undo_guarantee THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
    ELSE
        dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
    END IF;
    DBMS_OUTPUT.PUT_LINE(CHR(9));

    SELECT instance_number INTO v_instance_number FROM V$INSTANCE;

    DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
    DBMS_OUTPUT.PUT_LINE('---------------------------');

    v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
    DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
    DBMS_OUTPUT.PUT_LINE('-------------------------');

    v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
    IF v_undo_health_ret > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Minimum Recommendation           : ' || v_recommendation);
        DBMS_OUTPUT.PUT_LINE('Rationale                        : ' || v_rationale);
        DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
    END IF;
  
    SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
    SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
    SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
    DBMS_OUTPUT.PUT_LINE('------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');

END;
/



Temporary Tablespace Usage



SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024   mb_free
FROM gv$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024  mb_total
FROM gv$tablespace B, gv$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;



To find Session using Temp space.


col SID_SERIAL for a10
set lines 300 pagesize 300
col module for a10 trunc
col PROGRAM for a10 trunc

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid,
         S.module, S.program,
         SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;




col PROGRAM for a33 
col SID_SERIAL for a22 
SELECT a.inst_id,b.tablespace, 
       ROUND(((b.blocks*p.value)/1024/1024/1024),2)||'G' "SIZE", 
       a.sid||','||a.serial# SID_SERIAL, 
       a.username, a.sql_id,a.status,a.LAST_CALL_ET,
       a.program 
  FROM sys.gv_$session a, 
       sys.gv_$sort_usage b, 
       sys.gv_$parameter p 
WHERE p.name  = 'db_block_size' 
   AND a.saddr = b.session_addr 
   and b.tablespace='&TEMP_Tablespace'
ORDER BY b.tablespace, b.blocks desc;




  • Adding Datafile



  • Adding datafiles on mountpoint/filesystem


alter tablespace <tablespace_name> add datafile 'Location/datafile_name.dbf' size 1g autoextend on maxsize unlimited;
example: Alter tablespace tbps add datafile '/u01/data/orcl/tbps1.dbf' size 1g autoextend on maxsize unlimited;


              PS: Maxsize depends on block size of the database if block size is 8k maxsize of datafile will not grow beyond 31.99999 and for 4k block size it will not grow beyond 15.99999


      • Adding datafiles on Diskgroup



    Alter tablespace <tablespace_name> add datafile '+diskgroup_name' size 1g autoextend on maxsize unlimited;

    example : Alter Tablespace tbps add datafile '+DATA' size 1g autoextend on maxsize unlimited;




    Resizing Datafile


    ALTER DATABASE DATAFILE <file_id> RESIZE <SIZE>;


    Resizing maxsize of datafiles


    ALTER  DATABASE DATAFILE <FILE_ID> AUTOEXTEND ON MAXSIZE  <SIZE>;

    Resizing Bigfiles



    ALTER DATABASE DATAFILE  <FILE_ID> AUTOEXTEND ON MAXSIZE  <SIZE>;

     

     

     

     

    0 comments:

    Subscribe to: Comments (Atom)