• About Me

    Maxwell Miranda
    Oracle DBA Consultant.
  • Category

  • Archives

  • My Blog Calendar

    August 2009
    M T W T F S S
        Sep »
  • RSS Oracle Feeds

    • Flashback database to date back and forth
      Code: --------- SELECT NAME, TIME FROM v$restore_point order by time; NAME TIME ---------------------------------------- ----------------------------------- RST_PNT_20180306_1135 06-MAR-18 AM *Got flashbacked to this 6th Mar* RST_PNT_20180308_0132 08-MAR-18 AM RST_PNT_20180315_0332 15-MAR-18 AM RST_PNT […]
    • Downgrade database from Oracle to Oracle using catdwgrd.sq
      Downgrade database from Oracle to Oracle Like to know if any one successfully downgraded the database from version Oracle 12cR2 ( Release 2) to Oracle 11g Release 2 ( using manual method of catdwgrd.sql. Please share your views and inputs as we are unable to downgrade it successfully. Refereed the https://docs.oracle.com/en/databa […]
    • New user in oracle having access to all tables
      Hi, I have created a new user in oracle database as follows create new user u1 identified by u1; grant create session to u1; when I login with new user ,it has access to all tables in other schema except system and sys tables. i want to create a user who does not have access to any table on any schema. how it can be achieved? Thanks
    • Auditing userid creation, deletion and changes to roles granted.
      Is there an oracle auditing option to audit userid creation, deletion and changes made to the userid (system/object privileges granted or revoked)?
    • ORACLE SQL - Adding Results from Multiple Select Statements
      I apologize if this question is not in the correct area. I have eight select statements, each to get the sum of particular information. These are not simple queries, so I will not include them here. I have made up a simpler version (based on some code I found in a forum) as shown below. So, I have eight sums. I need a ninth sum that is the total of all eight […]
    • Cannot change undo_tablespace parameter in RAC node
      Hi, I am trying to change the undo tablespace in DB instance, unsuccessfully. Approach 1: SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string *UNDOTBS2* SQL> SQL> ALTER SYS […]
  • Advertisements

Calculate SGA size – current db usage

This script will calculate the size of the SGA of an existing database based upon the
current usage. This script is particularly useful for getting a SGA size of an existing
development database and scaling up the figures for a production database that does not
yet exist. Run this script on the development database and alter the number of users
constant (l_numusers) to be what is expected on the production database for the number
of concurrent users. It assumes that there is 30% free memory on top of the calculated.
This can be altered by changing the l_uplift PL/SQL constant.

The script default to using the maximum number of concurrent users which have logged on,
but you can amend this script to calculate for any number by changing the PL/SQL
constant l_numusers.

set serverout on
l_uplift CONSTANT NUMBER := 0.3; /* i.e. 30% above calculated */

l_numusers NUMBER DEFAULT 50; /* Change this to a predicted number if not an existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;

IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
dbms_output.put_line(‘Maximum concurrent users on this database = ‘
dbms_output.put_line(‘Calculating SGA for = ‘
||TO_CHAR(l_numusers)||’ concurrent users’);

SELECT avg(value)*l_numusers,max(value)*l_numusers INTO l_avg_uga,l_max_uga
FROM V$SESSTAT s, V$STATNAME n WHERE s.statistic# = n.statistic# AND n.name = ‘session uga memory max’;

SELECT sum(sharable_mem) INTO l_sum_sql_shmem FROM V$SQLAREA;

SELECT sum(sharable_mem) INTO l_sum_obj_shmem FROM V$DB_OBJECT_CACHE;

l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;

dbms_output.put_line(‘Recommended Shared_pool size between :’
|| TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) )
||’ and ‘
|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) )
||’ bytes’);

dbms_output.put_line(‘Recommended Shared_pool size between :’
(l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||’ and ‘
(l_total_max + (l_total_max * l_uplift )) /(1024*1024) ,0) )
||’ M bytes’);


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: