Vinod S R’s Blog

Updates from May, 2009 Toggle Comment Threads

• 

  Vinod Surendran 8:04 am on May 14, 2009 Permalink | Reply
  Tags: Oracle ( 4 )   

  How to export sequences using sql 

  Normally sequences are not exported if we give partial export.

  For such cases we can use sql to generate quries for sequence droping and creating

  select ‘drop sequence ‘||sequence_name||’;’ from user_sequences where sequence_name like’SEQ_DB%’;

  select ‘create sequence ‘||sequence_name||
  ‘ minvalue ‘|| MIN_VALUE ||
  ‘ maxvalue ‘|| MAX_VALUE ||
  ‘ start with ‘||last_number||
  ‘ increment by ‘|| INCREMENT_BY ||
  ‘ cache ‘||CACHE_SIZE ||
  decode(ORDER_FLAG,’Y',’ order ‘)||
  decode(CYCLE_FLAG,’Y',’ cycle ‘)||
  ‘;’

  GarykPatton, and KeHoeff are discussing. Toggle Comments

   
  • 

    KeHoeff 5:01 pm on May 28, 2009 Permalink

    hey this is a very interesting article!

  • 

    GarykPatton 7:54 pm on June 15, 2009 Permalink

    How soon will you update your blog? I’m interested in reading some more information on this issue.

  Reply Cancel reply

  Required fields are marked *

  Name *

  Email *

  Website

  

• 

  Vinod Surendran 8:00 am on May 14, 2009 Permalink | Reply
  Tags: Oracle ( 4 )   

  How to track queries in oracle .. 

  In Oracle there is a easy way to track the current queries processed by the database.

  Suppose you want to track the Insert queries in your DB .

  then for that use this query

  select substr(sql_text,instr(sql_text,’INTO “‘),30) table_name,
  rows_processed,
  round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes,
  trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_min
  from   sys.v_$sqlarea
  where  sql_text like ‘INSERT %INTO “%‘
  and  command_type = 2
  and  open_versions > 0;

  Replace the text in blue color (‘INSERT %INTO “%‘) According to your purpose

  JaneRadriges is discussing. Toggle Comments

   
  • 

    JaneRadriges 5:55 pm on June 13, 2009 Permalink

    Hi, very nice post. I have been wonder’n bout this issue,so thanks for posting

• 

  Vinod Surendran 7:57 am on May 14, 2009 Permalink | Reply
  Tags: Oracle ( 4 )   

  How to Get the Locked Tables List In Oracle 

  Here is the query to get the locked tables in oracle

  SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
  S.serial#,
  SUBSTR(O.OWNER||’.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
  DECODE(L.LOCKED_MODE, 0,’NONE’,
  1,’NULL’,
  2,’ROW SHARE’,
  3,’ROW EXCLUSIVE’,
  4,’SHARE’,
  5,’SHARE ROW EXCLUSIVE’,
  6,’EXCLUSIVE’,
  NULL) LOCK_MODE
  FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
  WHERE L.OBJECT_ID = O.OBJECT_ID
  and l.inst_id = s.inst_id
  AND L.SESSION_ID = S.SID
  and s.inst_id = p.inst_id
  AND S.PADDR = P.ADDR(+)
  order by l.inst_id  ;

  And to get the details of a particular session given by the sid in the above query use this query

  select STATUS ,  PROCESS , PROGRAM , LOGON_TIME  from v$session where sid=<SID>

  Enenifa, and KattyBlackyard are discussing. Toggle Comments

   
  • 

    KattyBlackyard 7:20 pm on June 14, 2009 Permalink

    Great post! I’ll subscribe right now wth my feedreader software!

  • 

    Enenifa 3:14 pm on March 2, 2011 Permalink

    If am workin in a bank,what query questions do i ask the oracle db.

• 

  Vinod Surendran 7:54 am on May 14, 2009 Permalink | Reply
  Tags: Oracle ( 4 )   

  How to get the TableSpace usage in Oracle 

  To get the table space usage in oracle use this query

  select    a.TABLESPACE_NAME,
  ROUND(a.BYTES/1024000) “Used (MB)”,
  ROUND(b.BYTES/1024000) “Free (MB)”,
  round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) “% USED”
  from
  (
  select  TABLESPACE_NAME,
  sum(BYTES) BYTES
  from    dba_data_files
  group   by TABLESPACE_NAME
  )
  a,
  (
  select  TABLESPACE_NAME,
  sum(BYTES) BYTES ,
  max(BYTES) largest
  from    dba_free_space
  group   by TABLESPACE_NAME
  )
  b
  where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
  and a.TABLESPACE_NAME like ‘%’
  order      by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

  If You want to list a particular table space  replace  a.TABLESPACE_NAME like ‘%’ with a.TABLESPACE_NAME like ‘MY_TABLE_SPACE’

  To get the temporary tablespace usuage use this query

  SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
  FROM V$temp_space_header
  GROUP BY tablespace_name;

  Dee Mcdaris is discussing. Toggle Comments

   
  • 

    Dee Mcdaris 10:08 pm on October 8, 2011 Permalink

    Read many articles, this is the best I have seen the article.