9 Replies Latest reply: Jan 21, 2013 2:19 AM by JohnWatson RSS

    Total space taken by Oracle 11g database

    975148
      I had a query as to how to find the total space taken by the Oracle 11g database. Is the following query correct to find the total space taken by the Oracle 11g:-

      select (sga+pga)/1024/1024 as "sga_pga"
      from
      (select sum(value) sga from v$sga),
      (select sum(pga_alloc_mem) pga from v$process)

      I hope my question is clear.

      Please revert with the reply to my query.

      Regards
        • 1. Re: Total space taken by Oracle 11g database
          Aman....
          Well how about issuing show sga and show parameter pga ?

          Aman....
          • 2. Re: Total space taken by Oracle 11g database
            Niket Kumar
            you query will tell space taken in memory on disk you can check from below query/....



            select DataFiles.data_size+TempFiles.temp_size+RedoLogs.redo_size+ControlFile.controlfile_size "total_size in MB"
            from
            ( select sum(bytes)/1024/1024 data_size from dba_data_files ) DataFiles,
            ( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) TempFiles,
            ( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) RedoLogs,
            ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) ControlFile;
            • 3. Re: Total space taken by Oracle 11g database
              JohnWatson
              I'm not sure you PGA query is correct
              orcl> select sum(value) from v$sesstat 
              where statistic# = (select statistic# from v$statname where name='session pga memory');
              
              SUM(VALUE)
              ----------
                76814912
              
              orcl> select sum(pga_alloc_mem) pga from v$process;
              
                     PGA
              ----------
                46878926
              
              orcl>
              You could also look at v$pgastat.
              • 4. Re: Total space taken by Oracle 11g database
                975148
                Sorry about the post, I meant total memory taken by the Oracle 11g database and not the space. Apologies for that, request you to answer the post accordingly.
                • 5. Re: Total space taken by Oracle 11g database
                  Niket Kumar
                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1715881300346604706
                  • 6. Re: Total space taken by Oracle 11g database
                    975148
                    Thanks but memory_max_target is 6448 MB but if use the query from the link, the summation is like 6752 MB which is more than memory_max_target.

                    Request you to please clarify.
                    • 7. Re: Total space taken by Oracle 11g database
                      Niket Kumar
                      please post result of three queries below:
                      select (sga+pga)/1024/1024 as "sga_pga"
                      from 
                      (select sum(value) sga from v$sga),
                      (select sum(pga_alloc_mem) pga from v$process)
                      select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
                        from
                      (
                      select 'sga' nm, sum(value) val
                        from v$sga
                       union all
                      select 'uga', sum(a.value)
                        from v$sesstat a, v$statname b
                       where b.name = 'session uga memory'
                         and a.statistic# = b.statistic#
                       union all
                      select 'pga', sum(a.value)
                        from v$sesstat a, v$statname b
                       where b.name = 'session pga memory'
                         and a.statistic# = b.statistic#
                      )
                       group by rollup(nm);
                      show parameter target
                      • 8. Re: Total space taken by Oracle 11g database
                        975148
                        Sorry, I could not reply because of week-end falling in between. Please find the output of the 3 queries:-

                        Query 1:

                        sga_pga
                        -----------
                        6888.6954

                        Query 2:

                        NM MB
                        ----- ----------
                        pga
                        sga 6419
                        uga 92
                        total 6511

                        Query 3:

                        show parameter target

                        NAME TYPE VALUE
                        ------------------------------------ ----------- ------------------------------
                        archive_lag_target integer 0
                        db_flashback_retention_target integer 1440
                        fast_start_io_target integer 0
                        fast_start_mttr_target integer 0
                        memory_max_target big integer 6448M
                        memory_target big integer 6448M
                        parallel_servers_target integer 64
                        pga_aggregate_target big integer 0
                        sga_target big integer 0

                        Niket, I feel the max memory taken by the DB would not be greater than 6448M as specified by memory_max_target and Oracle would adjust the values for SGA & PGA accordingly even though SGA & PGA added up separately may be more than 6448M.
                        • 9. Re: Total space taken by Oracle 11g database
                          JohnWatson
                          972145 wrote:
                          Thanks but memory_max_target is 6448 MB but if use the query from the link, the summation is like 6752 MB which is more than memory_max_target.

                          Request you to please clarify.
                          Your target is 6448M = 1024*1024*6448 bytes = 6761218048 which is very close to the result your are getting. Given that there is no read consistency in these views and the state of the instance is always changing, I would say this is as close as you are likely to get.