Sys-Admin Information’s

Share Knowledge, Distribute Knowledge, Capture & Modify Knowledge, Create Knowledge

Archive for the ‘Scripting’ Category

Show Locking Proccess

without comments

SQL> SELECT s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status, s1.SQL_ID
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2;

Written by Eriek Regandono

January 21st, 2008 at 1:36 pm

Posted in Oracle, Scripting

Kill Many* Jobs

without comments

When I want to shutdown the oracle database. I must kill many session client who connect to the database and running in background Os…

bash> ps -ef|grep -i local=no
oracle 19027     1  0   Sep 07 ?        0:01 oracleSERVER (LOCAL=NO)
oracle 25918     1  0   Sep 07 ?        0:03 oracleSERVER (LOCAL=NO)
oracle 16403     1  0   Sep 07 ?        0:00 oracleSERVER (LOCAL=NO)
oracle 18858     1  0   Sep 06 ?        0:01 oracleSERVER (LOCAL=NO)
oracle  7836     1  0   Sep 07 ?        0:02 oracleSERVER (LOCAL=NO)
oracle  9021     1  0   Sep 07 ?        0:00 oracleSERVER (LOCAL=NO)
oracle 27940     1  0   Sep 06 ?        0:00 oracleSERVER (LOCAL=NO)
oracle 25505     1  0   Sep 07 ?        0:01 oracleSERVER (LOCAL=NO)
oracle  2427     1  0   Sep 07 ?        0:00 oracleSERVER  (LOCAL=NO)
oracle 26640     1  0   Sep 06 ?        0:01 oracleSERVER (LOCAL=NO)
oracle 15440     1  0 19:23:09 ?        0:01 oracleSERVER (LOCAL=NO)
oracle 16712     1  0   Sep 07 ?        0:00 oracleSERVER (LOCAL=NO)
oracle 11544     1  0   Sep 06 ?        0:00 oracleSERVER (LOCAL=NO)
oracle  1411     1  0   Sep 07 ?        0:01 oracleSERVER (LOCAL=NO)
oracle 18909     1  0   Sep 06 ?        0:00 oracleSERVER (LOCAL=NO)
oracle 15607     1  0   Sep 06 ?        0:01 oracleSERVER (LOCAL=NO)
oracle  2873     1  0   Sep 06 ?        0:01 oracleSERVER (LOCAL=NO)
oracle 21197     1  0 17:36:49 ?        0:00 oracleSERVER (LOCAL=NO)
oracle 22318     1  0   Sep 06 ?        0:02 oracleSERVER (LOCAL=NO)

Show this the trick (when Nobody give up..:D)

bash> ps -ef|grep LOCAL|awk '{print $2'}|xargs kill -9

Tada..

Written by Hendrawan

September 13th, 2007 at 9:26 am

Posted in *Nix, Oracle, Scripting

Export Script (Oracle)

without comments

Yesterday, I was export the database for backup mode, Coz the database not in archived log mode, so export was the solved. I used this in HP-UX
Note:
1. Change ‘TABLE’ with your table and ‘SEPT’ with your partition table name.
2. Change ‘SCHEMA’ with your shema where table place.
3. Change ‘compress’ with your ‘compress’ tool, like (g)un(zip), etc.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

DECLARE
   vname LONG := NULL;
   vtable_name VARCHAR2 (50) := 'TABLE';
BEGIN
   FOR i IN (SELECT table_name, partition_name
               FROM dba_tab_partitions
              WHERE table_owner NOT IN ('SYS', 'SYSTEM')
                AND table_name = vtable_name
                AND partition_name NOT LIKE '%06%'
		AND partition_name LIKE '%SEPT%')
   LOOP
      vname :=
            'exp "''/ as sysdba''" buffer=50000000 file='
         || vtable_name
         || '-'
         || i.partition_name
         || ' tables=SCHEMA.'
         || vtable_name
         || ':'
         || i.partition_name
         || ' statistics=none log='
         || vtable_name
         || '-'
         || i.partition_name
         || '.log
compress -v '
         || vtable_name
         || '-'
         || i.partition_name
         || '.dmp';
      DBMS_OUTPUT.PUT_LINE (vname);
   END LOOP;
END;

Written by Hendrawan

September 12th, 2007 at 4:06 pm

Posted in Export-Import, Oracle, Scripting