SQL> set linesize 150
column blkg_user format a10
column wait_user format a10
column blkg_machine format a15
column wait_machine format a15
column owner format a10
column object_name format a15
SELECT s1.username blkg_user,
s1.machine blkg_machine,
s1.sid blkg_sid,
s2.username wait_user,
s2.machine wait_machine,
s2.sid wait_sid,
lo.object_id blkd_obj_id,
do.owner,
do.object_name
FROM v$lock l1, v$session s1, v$lock l2, v$session s2,
v$locked_object lo, dba_objects do
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.id1 = l2.id1
AND s1.sid = lo.session_id
AND lo.object_id = do.object_id
AND l1.block = 1
AND l2.request > 0;
BLKG_USER BLKG_MACHINE BLKG_SID WAIT_USER WAIT_MACHINE WAIT_SID BLKD_OBJ_ID OWNER OBJECT_NAME
---------- --------------- ---------- ---------- --------------- ---------- ----------- ---------- ---------------
WORK shacmrarpv1a.cn 935 WORK shacmrarpv1a.cn 1041 213046 WORK A2411_W
.db.com .db.com
BLKG_USER BLKG_MACHINE BLKG_SID WAIT_USER WAIT_MACHINE WAIT_SID BLKD_OBJ_ID OWNER OBJECT_NAME
---------- --------------- ---------- ---------- --------------- ---------- ----------- ---------- ---------------
WORK shacmrarpv1a.cn 922 WORK shacmrarpv1a.cn 1001 39074 WORK A3302
.db.com .db.com
set linesize 200
set pagesize 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT a10
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A25
COLUMN machine FORMAT A20
COLUMN logon_time FORMAT A20
column program format a20
column lockwait format a10
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.service_name,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
s.last_call_et AS last_call_et_secs
FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.sid='922'
ORDER BY s.username, s.osuser;
USERNAME OSUSER SID SERIAL# SPID LOCKWAIT STATUS SERVICE_NAME PROGRAM LOGON_TIME LAST_CALL_ET_SECS
--------------- ---------- ---------- ---------- ---------- ---------- -------- --------------- -------------------- -------------------- -----------------
WORK rars 922 38610 33226880 INACTIVE SHRARS JDBC Thin Client 22-JAN-2018 13:37:35 1847
set linesize 200
set pagesize 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT a10
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A25
COLUMN machine FORMAT A20
COLUMN logon_time FORMAT A20
column program format a20
column lockwait format a10
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.service_name,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
s.last_call_et AS last_call_et_secs
FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.username='WORK'
ORDER BY s.username, s.osuser;
USERNAME OSUSER SID SERIAL# SPID LOCKWAIT STATUS SERVICE_NAME PROGRAM LOGON_TIME LAST_CALL_ET_SECS
--------------- ---------- ---------- ---------- ---------- ---------- -------- --------------- -------------------- -------------------- -----------------
WORK rars 922 38610 33226880 INACTIVE SHRARS JDBC Thin Client 22-JAN-2018 13:37:35 1879
WORK rars 1005 6141 55967994 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:49:10 1041
WORK rars 917 59518 39321616 INACTIVE SHRARS JDBC Thin Client 22-JAN-2018 14:59:38 1579
WORK rars 1001 14 52756570 070000007A ACTIVE SHRARS JDBC Thin Client 22-JAN-2018 14:59:26 1879
1D06A8
WORK rars 849 45374 45744206 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:43:45 1576
WORK rars 793 39763 44892214 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:43:47 1573
WORK rars 873 3412 22347876 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:43:50 1542
WORK rars 850 15024 21168280 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:44:48 1512
WORK rars 979 30406 64487634 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:44:22 1515
WORK rars 847 36324 41746494 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:44:51 1489
WORK rars 932 43121 44302482 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:45:15 1476
WORK rars 805 8112 39256282 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:45:27 1382
WORK rars 936 35815 48627840 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:47:01 1368
WORK rars 926 61718 36044984 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:47:15 1308
WORK rars 925 7005 41287708 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:48:15 1267
WORK rars 949 56318 8847596 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:48:30 1258
WORK rars 1049 10179 60162062 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:52:43 988
WORK rars 953 26231 48300058 INACTIVE SHRARS JDBC Thin Client 24-JAN-2018 15:49:06 1253
WORK rars 791 58162 45613256 INACTIVE SHRARS JDBC Thin Client 22-JAN-2018 13:39:09 1578
19 rows selected.
set pagesize 180
column username format a15
select username, sid,SERIAL#
from v$session
where username = 'WORK'
order by username;
USERNAME SID SERIAL#
--------------- ---------- ----------
WORK 791 58162
WORK 793 39763
WORK 805 8112
WORK 847 36324
WORK 849 45374
WORK 850 15024
WORK 873 3412
WORK 917 59518
WORK 922 38610
WORK 925 7005
WORK 926 61718
WORK 932 43121
WORK 936 35815
WORK 949 56318
WORK 953 26231
WORK 979 30406
WORK 1001 14
WORK 1005 6141
WORK 1049 10179
19 rows selected.
set pagesize 1000
select 'ALTER SYSTEM KILL SESSION '||sid || ','|| SERIAL# || ' immediate;'
from v$session
where username = 'WORK'
order by username;
'ALTERSYSTEMKILLSESSION'||SID||','||SERIAL#||'IMMEDIATE;'
----------------------------------------------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION 791,58162 immediate;
ALTER SYSTEM KILL SESSION 793,39763 immediate;
ALTER SYSTEM KILL SESSION 805,8112 immediate;
ALTER SYSTEM KILL SESSION 847,36324 immediate;
ALTER SYSTEM KILL SESSION 849,45374 immediate;
ALTER SYSTEM KILL SESSION 850,15024 immediate;
ALTER SYSTEM KILL SESSION 873,3412 immediate;
ALTER SYSTEM KILL SESSION 917,59518 immediate;
ALTER SYSTEM KILL SESSION 922,38610 immediate;
ALTER SYSTEM KILL SESSION 925,7005 immediate;
ALTER SYSTEM KILL SESSION 926,61718 immediate;
ALTER SYSTEM KILL SESSION 932,43121 immediate;
ALTER SYSTEM KILL SESSION 936,35815 immediate;
ALTER SYSTEM KILL SESSION 949,56318 immediate;
ALTER SYSTEM KILL SESSION 953,26231 immediate;
ALTER SYSTEM KILL SESSION 979,30406 immediate;
ALTER SYSTEM KILL SESSION 1001,14 immediate;
ALTER SYSTEM KILL SESSION 1005,6141 immediate;
ALTER SYSTEM KILL SESSION 1049,10179 immediate;
19 rows selected.
ALTER SYSTEM KILL SESSION '791,58162' immediate;
ALTER SYSTEM KILL SESSION '793,39763' immediate;
ALTER SYSTEM KILL SESSION '805,8112' immediate;
ALTER SYSTEM KILL SESSION '847,36324' immediate;
ALTER SYSTEM KILL SESSION '849,45374' immediate;
ALTER SYSTEM KILL SESSION '850,15024' immediate;
ALTER SYSTEM KILL SESSION '873,3412' immediate;
ALTER SYSTEM KILL SESSION '917,59518' immediate;
ALTER SYSTEM KILL SESSION '922,38610' immediate;
ALTER SYSTEM KILL SESSION '925,7005' immediate;
ALTER SYSTEM KILL SESSION '926,61718' immediate;
ALTER SYSTEM KILL SESSION '932,43121' immediate;
ALTER SYSTEM KILL SESSION '936,35815' immediate;
ALTER SYSTEM KILL SESSION '949,56318' immediate;
ALTER SYSTEM KILL SESSION '953,26231' immediate;
ALTER SYSTEM KILL SESSION '979,30406' immediate;
ALTER SYSTEM KILL SESSION '1001,14' immediate;
ALTER SYSTEM KILL SESSION '1005,6141' immediate;
ALTER SYSTEM KILL SESSION '1049,10179' immediate;
vi /tmp/kill_work_session.sql
@/tmp/kill_work_session.sql
WORK.A2411_W
SQL> set linesize 80
SQL> desc WORK.A2411_W;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBANK_ID NOT NULL VARCHAR2(500)
DDATE NOT NULL VARCHAR2(500)
ROW_NUM NUMBER(20,6)
COL_NUM NUMBER(20,6)
PV NUMBER(20,6)
PV_STR VARCHAR2(500)
SQL> set pagesize 1000
set long 100000
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',
NAME=>'A2411_W',
SCHEMA=>'WORK')
FROM DUAL;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'A2411_W',SCHEMA=>'WORK')
--------------------------------------------------------------------------------
CREATE TABLE "WORK"."A2411_W"
( "DBANK_ID" VARCHAR2(500) NOT NULL ENABLE,
"DDATE" VARCHAR2(500) NOT NULL ENABLE,
"ROW_NUM" NUMBER(20,6),
"COL_NUM" NUMBER(20,6),
"PV" NUMBER(20,6),
"PV_STR" VARCHAR2(500)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
SQL> set pagesize 1000
set long 100000
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',
NAME=>'A3302',
SCHEMA=>'WORK')
FROM DUAL;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'A3302',SCHEMA=>'WORK')
--------------------------------------------------------------------------------
CREATE TABLE "WORK"."A3302"
( "DBANK_ID" VARCHAR2(500) NOT NULL ENABLE,
"DDATE" VARCHAR2(500) NOT NULL ENABLE,
"ROW_NUM" NUMBER(20,6),
"COL_NUM" NUMBER(20,6),
"PV" NUMBER(20,6),
"PV_STR" VARCHAR2(500)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "WORK"
set linesize 150
column table_name format a25
column index_name format a30
column index_type format a15
column tablespace_name format a20
column status format a10
select table_name, index_name, index_type, tablespace_name, status
from user_indexes
WHERE table_name='L_TRAN_ACCT_INNER_TX' and TABLE_OWNER='WORK'
order by table_name, index_name;
no rows selected
set pagesize 1000
set long 100000
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name)
FROM USER_INDEXES
WHERE TABLE_NAME='L_TRAN_ACCT_INNER_TX';
SQL> set pagesize 1000
set long 100000
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'INDEX',
NAME=>'A3302',
SCHEMA=>'WORK')
FROM DUAL;
ERROR:
ORA-31603: object "A3302" of type INDEX not found in schema "WORK"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3241
ORA-06512: at "SYS.DBMS_METADATA", line 4812
ORA-06512: at line 1