1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Size of the Database at a particular date

Discussion in 'General' started by viral, Mar 8, 2012.

  1. viral

    viral Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I want to know if it is at all possible, to get the size of the database on a particluar date?

    I tried searching for this but was not able to get any answer.

    Thanks!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not unless you write a job to collect that information. The data dictionary views are constantly updated and even though there is the dba_hist_tablespace view it's linked to dba_hist_snapshot by the snap_id and usually snapshots persist for about a week so there is a 7-day rolling window of history. If you want any earlier than that you'll need to create a table, a procedure and a job to populate it so you can report on size later.
     
    viral likes this.
  3. viral

    viral Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Thanks a lot ZARGON, for the update,

    Can you just help me with writing the sql query for finding what was the size of database yesterday?

    I really do not know if it will be that easy... but still.

    Thanks again!!!
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Even with the DBA_HIST_TABLESPACE view you won't get the total bytes of the tablespace at a given point in time. You will need to do something like this using a DBA-privileged account:

    Code (SQL):
    SQL> CREATE TABLE db_size_monitor(
      2          check_dt        DATE,
      3          create_bytes    NUMBER,
      4          current_bytes   NUMBER);
    TABLE created.
    SQL>
    SQL> CREATE OR REPLACE PROCEDURE pop_db_size AS
      2          bytesum NUMBER;
      3          crt_bytesum NUMBER;
      4          curr_dt DATE;
      5  BEGIN
      6
      7          SELECT trunc(sysdate), SUM(bytes), SUM(create_bytes)
      8          INTO curr_dt, bytesum, crt_bytesum
      9          FROM sys.v_$datafile;
     10
     11          INSERT INTO db_size_monitor
     12          VALUES (curr_dt, crt_bytesum, bytesum);
     13
     14          commit;
     15
     16  END;
     17  /
    PROCEDURE created.
    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> EXEC pop_db_size;
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SELECT * FROM db_size_monitor;
    CHECK_DT     CREATE_BYTES   CURRENT_BYTES
    --------- --------------- ---------------
    08-MAR-12      3250585600     43618140160
    SQL>