This discussion is archived
9 Replies Latest reply: Jan 21, 2013 12:19 AM by JohnWatson RSS

Total space taken by Oracle 11g database

975148 Newbie
Currently Being Moderated
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.... Oracle ACE
    Currently Being Moderated
    Well how about issuing show sga and show parameter pga ?

    Aman....
  • 2. Re: Total space taken by Oracle 11g database
    Niket Kumar Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points