3 Replies Latest reply: Feb 21, 2013 5:40 PM by Satishbabu Gunukula RSS

    Query to determine if autoextend kicked in

    jfree427
      Is there a way to know if autoextend grew a datafile? DBA_EXTENTS or something?

      Thanks,
      Jarred
        • 1. Re: Query to determine if autoextend kicked in
          TSharma-Oracle
          You can run the below query and check what is the size of your datafile on each day and check whether it is exteneded or not. I do nto think there is any view which tells the required information. You can check 'autoextensible' in dba_data_files to see of datafile is in autoextend mode.


          select
          to_char (sp.begin_interval_time,'dd-mm-yyyy') days,
          ts.tsname,
          max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_mb,
          max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_mb
          from
          dba_hist_tbspc_space_usage tsu,
          dba_hist_tablespace_stat ts,
          dba_hist_snapshot sp,
          dba_tablespaces dt
          where
          tsu.tablespace_id= ts.ts#
          and
          tsu.snap_id = sp.snap_id
          and
          ts.tsname = dt.tablespace_name
          and
          ts.tsname not in ('SYSAUX','SYSTEM')
          group by
          to_char (sp.begin_interval_time,'dd-mm-yyyy'), ts.tsname
          order by ts.tsname, days;
          • 2. Re: Query to determine if autoextend kicked in
            869405
            The way I have done this is I creaed a table in a separate schema and everyday I take a snap shot of my tablespace sizing ran through a job on the db so I have a historical view of my tablespace sizing. This allows me to see the trends of the growth on the database as well as planning for future space. We all know that activity may vary depending on the time of the day or the day of the month or whatever frequency you need to evauate changes. By having a historical DB tablespace table, I can see these these changes as I please.

            If you think once a day is not enough then increase the frequency of your snap shot.

            Here is a sample output of my growth query checking growth for a 30 days period:

            PAST_DATE LATEST_DA DATABASE_ TS_NAME PAST_TS_USED_SPACE LATEST_TS_USED_SPACE Differences/Increase %
            ==============================================================================================
            20-JAN-13 19-FEB-13 PROD SYSAUX 630 685 8.73%
            20-JAN-13 19-FEB-13 PROD SYSTEM 656 658 0.3%
            20-JAN-13 19-FEB-13 PROD TOOLS 1040 1057 1.63%
            20-JAN-13 19-FEB-13 PROD TRANS_IDX 1209 1216 0.58%
            20-JAN-13 19-FEB-13 PROD TRANS_TABLE 2356 2366 0.42%
            20-JAN-13 19-FEB-13 PROD UNDOTBS1 93 144 54.84%
            20-JAN-13 19-FEB-13 PROD USERS 662 672 1.51%
            20-JAN-13 19-FEB-13 PROD USERS_IDX 289 292 1.04%
            20-JAN-13 19-FEB-13 PROD WORK 3638 3667 0.8%
            20-JAN-13 19-FEB-13 PROD WORK_IDX 822 835 1.58%

            Edited by: Pierre on Feb 19, 2013 10:45 AM
            • 3. Re: Query to determine if autoextend kicked in
              Satishbabu Gunukula
              You can refer below link, it should help

              http://www.databasejournal.com/features/oracle/autoextend-oracle-database-file-sizes.html
              http://www.dba-oracle.com/t_v_datafile_database_size.htm

              Regards
              http://www.oracleracexpert.com
              ORA-01031: insufficient privileges
              http://www.oracleracexpert.com/2013/02/ora-01031-insufficient-privileges.html
              How to Change Oracle DBNAME and DBID
              http://www.oracleracexpert.com/2013/02/how-to-change-oracle-dbname-and-dbid.html