• About Me

    Maxwell Miranda
    Oracle DBA Consultant.
  • Category

  • Archives

  • My Blog Calendar

    September 2009
    M T W T F S S
    « Aug   Oct »
  • 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

Display database SGA statistics

libcac NUMBER(10,2);
rowcac NUMBER(10,2);
bufcac NUMBER(10,2);
redlog NUMBER(10,2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;
SELECT VALUE INTO redlog FROM v$sysstat
WHERE name = ‘redo log space requests’;
SELECT 100*(SUM(pins)-SUM(reloads))/SUM(pins) INTO libcac FROM v$librarycache;
SELECT 100*(SUM(gets)-SUM(getmisses))/SUM(gets) INTO rowcac FROM v$rowcache;
SELECT 100*(cur.VALUE + con.VALUE – phys.VALUE)/(cur.VALUE + con.VALUE) INTO bufcac
FROM v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
WHERE cur.statistic# = ncu.statistic#
AND ncu.name = ‘db block gets’
AND con.statistic# = nco.statistic#
AND nco.name = ‘consistent gets’
AND phys.statistic# = nph.statistic#
AND nph.name = ‘physical reads’;
SELECT VALUE INTO spsize FROM v$parameter WHERE name = ‘shared_pool_size’;
SELECT VALUE INTO blkbuf FROM v$parameter WHERE name = ‘db_block_buffers’;
SELECT VALUE INTO logbuf FROM v$parameter WHERE name = ‘log_buffer’;
DBMS_OUTPUT.put_line(‘> ********************’);
DBMS_OUTPUT.put_line(‘> SQL Cache Hit rate = ‘||libcac);
DBMS_OUTPUT.put_line(‘> Dict Cache Hit rate = ‘||rowcac);
DBMS_OUTPUT.put_line(‘> Buffer Cache Hit rate = ‘||bufcac);
DBMS_OUTPUT.put_line(‘> Redo Log space requests = ‘||redlog);
DBMS_OUTPUT.put_line(‘> ‘);
DBMS_OUTPUT.put_line(‘> ****************’);
DBMS_OUTPUT.put_line(‘> Shared Pool Size = ‘||spsize||’ Bytes’);
DBMS_OUTPUT.put_line(‘> DB Block Buffer = ‘||blkbuf||’ Blocks’);
DBMS_OUTPUT.put_line(‘> Log Buffer = ‘||logbuf||’ Bytes’);
DBMS_OUTPUT.put_line(‘> ‘);
libcac < 99 THEN DBMS_OUTPUT.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
rowcac < 85 THEN DBMS_OUTPUT.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
bufcac 100 THEN DBMS_OUTPUT.put_line(‘*** HINT: Log Buffer value is rather low!’);


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: