Mục đích: Các package, table Core có số lượng giao dịch gọi vào rất lớn nên không làm cẩn thận sẽ bị tăng tải và TREO toàn bộ DB.
--1.Khoá các user dang open (tru SYS%, %GGATE) luu lai thành scrip lock_user.sql
select 'alter user ' || username || ' account lock;' from DBA_USERS
where account_status='OPEN'
--2. Kill các session INACTIVE, ACTIVE theo ung instance
SELECT /*username*/ 'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME
FROM gv$session a, gv$process b
WHERE b.ADDR = a.paddr
AND a.inst_id=b.inst_id
AND B.inst_id = 1
AND A.username not in ('SYS','SYSTEM','SYSMAN','GGATE','GOLDENGATE')
and A.MACHINE not like '%BINHTV%'
and type='USER';
--3. Cap nhat table, package core
--+ Cap nhat bang
ALTER TABLE SUBS
ADD
(
APP_OBJECT varchar2(15),
FOUNDED_PERM_NO varchar(80),
FOUNDED_PERM_DATE DATE,
VISA varchar2(80)
);
--+ Cap nhat package, procedure, function
-- Cac cau lenh kill sesion dang truy cap vao procedure va lock table
SELECT /*call procedure*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr
AND a.inst_id=b.inst_id
--and b.inst_id=4
AND (b.inst_id, a.sid) in (
(select /*+ parallel(8) */ inst_id, sid from gv$access where object like '%pr_call_subs%')
)
and type='USER'
and a.machine not like '%TCTK-BINHTV%' ;
SELECT /*lock table*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr
AND a.inst_id=b.inst_id
--and b.inst_id=3
AND (b.inst_id, a.sid) in
(SELECT /*+ parallel(8)*/ s.inst_id,s.sid
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
and object_name=upper('SUBS'))
--and type='USER'
--ORDER BY username, session_id;
--4. Buil lai các Package invalid:
select 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects
where object_type in ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE','VIEW') and status like 'INVALID' and OWNER like 'BINH_OWNER'
UNION ALL
select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' from dba_objects
where object_type in ('PACKAGE BODY') and status like 'INVALID' and OWNER like 'BINH_OWNER';
--5. Unlock user: script
select 'alter user ' || username || ' account unlock;' from DBA_USERS
where account_status='OPEN'
and username not in ('SYS','SYSTEM','SYSMAN','GGATE','GOLDENGATE') order by username;
