1 Reply Latest reply: Apr 30, 2012 6:22 AM by 415289 RSS

    How can you get the total space used against the maximum possible?

    SteveWelch
      Hello,

      This is likely a silly question but I have to ask it about XE databases.

      I have been using APEX for a number of years now and have a reasonable understanding I think. The one problem I have is monitoring the total size. I have at times run out of space when trying to upgrade so have run the script to delete obsolete schemas that were left behind when previous upgrades were done. I have requested and granted increases in size by making the request and then logging into the 'Internal Workspace' and granting the increase in size. I don't know but it would appear this operation is more involved as when I check I'm not really sure what I'm looking at or if I have really succeeded!

      What I am having trouble with is finding out exactly how large the database is and how much space I have left. I feel this is one area APEX is unfriendly, you need a total space used, then how much total space each schema is using and how much space is left. There is nowhere where you are show the max size and how you are going against it.

      Please put me straight.

      Kind Regards,

      Steve Welch
        • 1. Re: How can you get the total space used against the maximum possible?
          415289
          Try this to check space at database level
          SELECT tablespace_name tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SIZE_MB,
          ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) POSS_FREE_SPACE_MB,
          ROUND(SUM(max_mb)) POSS_MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100)  "USED% based_on_maxsize"
          FROM (
            SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
            0 TOTAL_MB, 0 MAX_MB
            FROM dba_free_space
            GROUP BY tablespace_name
            UNION
            SELECT tablespace_name, 0 CURRENT_MB,
            SUM(bytes)/1024/1024 TOTAL_MB,
            SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
            FROM dba_data_files
            GROUP BY tablespace_name)
          GROUP BY tablespace_name
          /
          schema is using space from tablespace if tablespace have freespace they can use it until quota is fixed.
          to check quota as well,try this

          select * from dba_ts_quotas;


          Thanks
          Kuljeet Pal Singh