what the blog?

Tagged: scripts RSS

• 

  levin 2:30 pm on December 31, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts   

  How to determine undo usage in Oracle 

  Overview

  Undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued. Automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

  This is done by setting the UNDO_RETENTION parameter. The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

  Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

  With the below information gathered by SQL query, you may have a idea to prevent undo space is running out so quickly.
  (More …)

  Raj Gopal, and Chandrasekar Sivasamy are discussing. Toggle Comments

   
  • 

    Chandrasekar Sivasamy 9:12 pm on July 3, 2010 Permalink | Reply

    Thank you so much. It’s very useful. I have cleared my doubt.

  • 

    Raj Gopal 10:12 pm on January 20, 2011 Permalink | Reply

    The query to calculate the required undo tablespace size is very helpful.

  Reply Cancel reply

  Required fields are marked *

  Name *

  Email *

  Website

  

  

  

  
  

  CAPTCHA Code *

  

• 

  levin 2:10 pm on December 31, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts   

  How to check tablespace usage in Oracle 

  Check each schema usage on tablespaces.

  set linesize 300
  set pagesize 20
  set feedback off
  
  select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"
  from dba_segments a, (select tablespace_name, sum(bytes) total_space
                        from dba_data_files
                        group by tablespace_name) b
  where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')
  and a.tablespace_name = b.tablespace_name
  group by a.tablespace_name, a.owner, b.total_space/1024/1024
  order by a.tablespace_name, a.owner;
  

  Check overall tablespaces usage.

  set linesize 300
  set pagesize 20
  set feedback off
  
  select t1.tsp_name, sysdate,
         round(t1.total_space/1024/1024,2) "Total (MB)",
         round((t1.total_space-t2.free_bytes)/1024/1024,2) "Used (MB)",
         round(t2.free_bytes/1024/1024,2) "Free (MB)",
         round(t2.free_bytes/t1.total_space*100,2) "% Free",
         decode(greatest(round(t2.free_bytes/t1.total_space*100,2),20),20,'*',' ') D,
         decode(greatest(round(t2.free_bytes/t1.total_space*100,2),15),15,'*',' ') E
  from (select tablespace_name tsp_name, sum(bytes) total_space
        from sys.dba_data_files
        where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
        group by tablespace_name) t1,
       (select tablespace_name tsp_name, sum(bytes) free_bytes
        from sys.dba_free_space
        where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
        group by tablespace_name) t2
  where t1.tsp_name = t2.tsp_name
  order by tsp_name;
  

   
• 

  levin 11:21 am on December 31, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts   

  How to find active SQL statements in Oracle 

  Everyone has a favorite script to find the active SQL statements. Here is one of mine, it’s RAC friendly too..

  
       set serverout on size 999999
       declare
       begin
       dbms_output.put_line(' ');
       dbms_output.put_line('************* Start report for WAITING sessions with current SQL ***************');
       for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
       vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
       from gv$session_wait vsw, gv$session vs
       where vsw.sid = vs.sid
       and vsw.inst_id = vs.inst_id
       and vs.type <> 'BACKGROUND'
       and vsw.event NOT IN ('rdbms ipc message'
       ,'smon timer'
       ,'pmon timer'
       ,'SQL*Net message from client'
       ,'lock manager wait for remote message'
       ,'ges remote message'
       ,'gcs remote message'
       ,'gcs for action'
       ,'client message'
       ,'pipe get'
       ,'Null event'
       ,'PX Idle Wait'
       ,'single-task message'
       ,'PX Deq: Execution Msg'
       ,'KXFQ: kxfqdeq - normal deqeue'
       ,'listen endpoint status'
       ,'slave wait'
       ,'wakeup time manager'))
       loop
       begin
       dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
       dbms_output.put_line('************************* ******************** ****** *********** ********** *************');
       dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||'
       '|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
       dbms_output.put_line(' SQLText ');
       dbms_output.put_line('****************************************************************');
       for y in (select sql_text
       from gv$sqltext
       where address = x.sql_address
       and hash_value = x.sql_hash_value
       and inst_id = x.inst_id
       order by piece)
       loop
       dbms_output.put_line(y.sql_text);
       end loop;
       end;
       end loop;
       dbms_output.put_line('************** End re! port for sessions waiting with current SQL ****************');
       dbms_output.put_line(' ');
       end;
       /
  

  http://www.oracle.com/technology/oramag/code/tips2004/062104.html

   
• 

  levin 1:21 pm on October 16, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts   

  oracle grant a user to another user’s objects 

  BEGIN
    FOR x IN (SELECT object_name, object_type FROM all_objects WHERE owner='SOURCE_USER' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE','SEQUENCE')) LOOP
      IF x.object_type IN ('TABLE') THEN
        EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON SOURCE_USER.'||x.object_name||' TO TARGET_USER';
      END IF;
      IF x.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
        EXECUTE IMMEDIATE'GRANT EXECUTE ON SOURCE_USER.'||x.object_name||' TO TARGET_USER';
      END IF;
      IF x.object_type IN ('SEQUENCE') THEN
        EXECUTE IMMEDIATE 'GRANT SELECT ON SOURCE_USER.'||x.object_name||' TO TARGET_USER';
      END IF;
    END LOOP;
  END;

   
• 

  levin 1:52 pm on October 15, 2009 Permalink | Reply
  Tags: scripts   

  ls command output in single line 

  ls -1 | awk '{ ORS=" "; print; }'

  or

  printf "%s " *

   
• 

  levin 2:21 am on October 15, 2009 Permalink | Reply
  Tags: oracle ( 17 ), scripts   

  show table size in oracle 

  exec it, enter desired schema name and go!

  (More …)

   
• 

  levin 12:19 am on March 19, 2009 Permalink | Reply
  Tags: imap ( 2 ), linux ( 38 ), scripts   

  Using imapsync to replicate email 

  1. Download and install the latest version of imapsync at http://www.linux-france.org/prj/imapsync/dist/. As of today, the latest version is 1.255.

  2. imapsync requires Mail-IMAPClient 2.2.9. Most recent Linux distribution comes with version > 3.x, so you will need to manually download it at http://search.cpan.org/~djkernen/Mail-IMAPClient-2.2.9/.

  3. Extract it, and install Mail-IMAPClient 2.2.9

  wget http://search.cpan.org/CPAN/authors/id/D/DJ/DJKERNEN/Mail-IMAPClient-2.2...
  tar xzf Mail-IMAPClient-2.2.9.tar.gz
  cd Mail-IMAPClient-2.2.9
  perl Makefile.PL
  make install

  4. You are set to go. The typical command to synchronize from imap mail to a courier imap mail server will be similar to this:

  imapsync --host1 imap.source-mail.com --user1 me@source-mail.com --password1 mypassword \
      --host2 imap.dest-mail.com --user2 me@dest-mail.com --password2 mypassowrd \
      --syncinternaldates --prefix2 INBOX. --split1 100 --split2 100 \
      --exclude Trash|Junk

   
• 

  levin 1:11 am on April 3, 2007 Permalink | Reply
  Tags: aix ( 5 ), scripts, sophos   

  useful sophos scripts 

  When you brought a “Enterprise Product”…….. how come there has no automation tools solve your panic…… they give you the binary, “services?”… but nothing provide to you… why you still buy it…….

  (More …)