Levicorp's WebNote Moving into http://levicorp.com

Just another web note

  • Hear This

    Radio Rodja 756 AM
  • January 2018
    M T W T F S S
    « Jul    
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  

SQL Scripts

/* Script for check Locking in Database */

    SELECT sid, DECODE(block,0,’NO’,’YES’) BLOCKER,
    DECODE(request,0,’NO’,’YES’) WAITER
    FROM v$lock
    WHERE request > 0 OR block > 0
    ORDER BY block DESC;

/* Find the current statement using OS PID */

SET lin 145
SET pages 1000
COL sql_text format a64
COL sid format 9999
COL serial# format 99999
COL username format a8
COL module format a10
COL action format a20
COL process HEADING ‘Client|PID’ format a6

SELECT   n.sql_text, s.SID, s.serial#, s.process, s.username, s.module,
         s.action
    FROM v$sqltext_with_newlines n, v$session s, v$process p
   WHERE n.address =
            DECODE (RAWTOHEX (s.sql_address),
                    ’00’, s.prev_sql_addr,
                    s.sql_address
                   )
     AND s.paddr = p.addr
     AND p.spid = &pid
ORDER BY piece;

/* Script for show the hidden init parameter */

SELECT a.ksppinm “Parameter”, b.ksppstvl “Session Value”,
       c.ksppstvl “Instance Value”
  FROM x$ksppi a, x$ksppcv b, x$ksppsv c
 WHERE a.indx = b.indx AND a.indx = c.indx;

/* Skeleton Script for compile invalid objects */

SELECT    ‘alter ‘
       || decode (object_type,’PACKAGE BODY’,’PACKAGE’, object_type)
       || ‘ ‘
       || owner
       || ‘.’
       || object_name
       || ‘ compile ‘
       || DECODE (object_type,
                  ‘PACKAGE BODY’, ‘body;’,
                  ‘PACKAGE’, ‘package;’,
                  ‘;’
                 )
  FROM dba_objects
 WHERE status = ‘INVALID’;

/* Skeleton script for drop-create db_link in 9i */

Set Pages 1000
SELECT    ‘drop ‘
       || DECODE (u.NAME, ‘PUBLIC’, ‘public ‘)
       || ‘database link ‘
       || CHR (10)
       || DECODE (u.NAME, ‘PUBLIC’, NULL, u.NAME || ‘.’)
       || l.NAME
       || CHR (10)
       || ‘;’ text
  FROM SYS.link$ l, SYS.user$ u
 WHERE l.owner# = u.user#
UNION ALL
SELECT    ‘create ‘
       || DECODE (u.NAME, ‘PUBLIC’, ‘public ‘)
       || ‘database link ‘
       || CHR (10)
       || DECODE (u.NAME, ‘PUBLIC’, NULL, u.NAME || ‘.’)
       || l.NAME
       || CHR (10)
       || ‘ connect to ‘
       || l.userid
       || ‘ identified by ‘
       || l.PASSWORD
       || ‘ using ”’
       || l.HOST
       || ””
       || CHR (10)
       || ‘;’ text
  FROM SYS.link$ l, SYS.user$ u
 WHERE l.owner# = u.user#;

Advertisements

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: