Bom Dia Chiappa,
No Banco Destino não mostra nenhum lock, executei todos os seus scripts, alias
não abre nem sessão vindo da origem.
Na Origem, matei todas as sessões, tirei o listener, parei os Jobs, fiquei
sozinho no banco e coloquei a instrução abaixo e não executa.
SQL> begin
2 delete ind_saldo_estoque_diario@prd.fr_lins.gr_bertin
3 where cod_empresa = 40
4 and cod_filial = 3
5 and dat_saldo >= TO_DATE('06/09/2016', 'DD/MM/YYYY');
6 end;
7 /
Como lhe disse, na Origem existe esta mesma tabela e fazendo sem o DB Link,
funciona.
SQL> begin
2 delete ind_saldo_estoque_diario
3 where cod_empresa = 40
4 and cod_filial = 3
5 and dat_saldo >= TO_DATE('06/09/2016', 'DD/MM/YYYY');
6 end;
7 /
PL/SQL procedure successfully completed
SQL> ROLLBACK;
Rollback complete
A rotina em questão, faz um DELETE e depois um INSERT, então imaginei que
poderia haver uma fragmentação. Movi essa tabela na Origem para outra
tablespace e fiz o rebuild dos indices.
Atualizei estatisticas de ambos, e dicionarios tambem. Já fiz um reboot no
Banco de Origem.
No Destino, criei uma tabela e fiz um DELETE e INSERT empsulado sem problemas.
Detalhe: o Banco Origem fica em Hong Kong e o Destino aqui em São Paulo.
O Banco Origem é 9.2.0.8 e o Destino 11.2.0.4, ambos Enterprise.
Grato,
Ednilson Silva
De: oracle_br@yahoogrupos.com.br [mailto:oracle_br@yahoogrupos.com.br]
Enviada em: quinta-feira, 15 de setembro de 2016 20:30
Para: oracle_br@yahoogrupos.com.br
Assunto: Re: RES: RES: RES: [oracle_br] Re: delete
OOOOOpa , estamos chegando em algum lugar : em especial, essa info de
"sessão..... em Library Cache Load Lock" é ABSOLUTAMENTE, COMPLETAMENTE,
TOTALMENTE DIFERENTE da info de "Não gera nenhum bloqueio, ..." que vc tinha
dado em outra msg anterior, né não ?
Muito bem, agora SIM estamos chegando em algum lugar.... ;)
okdoc : o fato que vc consegue compilar com sucesso no banco-destino outros
stored PL/SQLs que usam esse dblink mas (ao que entendo) acessam OUTRAS tabelas
lá no banco-origem CLARAMENTE INDICA que é uma issue com essa tabela - como eu
disse lá na segunda resposta, pode ser uma Transação aberta nessa tabela, pode
ser espera por LOCK causado por algum outro DDL concorrendo com a
criação/compilação da package, E o procedimento é mesmo consultar as views e
tabelas internas de WAITs, de LOCKS, de TRANSAÇÕES e de execução de PL/SQL (e
isso nos DOIS BANCOS !!!) e ver quem/qual sessão tá acessando/mexendo/tem
locks/tá usando PL/SQL que referencia a tal tabela....
Seguem abaixo FYR alguns scripts-exemplo que posso indicar, todos (repito)
devendo ser executados TANTO no banco origem da informação QUANTO no
banco-destino que possui o dblink - só AVISO que :
a. como não tenho um banco 9iR2 aqui facilmente disponível, CONFIRA na doc que
realmente todas as colunas que referencio estão presentes no 9i
b. todas as consultas devem ser executadas nos dois bancos com um usuário
Administrador/DBA
c. afaik vc não disse mas SUPONHO que esse 9iR2 EE é single-instance ou RAC :
se for RAC acesse as GV$ ao invés das V$
d. as views/tabelas internas DBA_xxx e (G)V$xxx via de regra já são
permissionadas para qualquer usuário Administrador - já as X$ normalmente não,
então se vc for executar os scripts com outro user que não o SYS ,
permissione-o adequadamente
e. os scripts que são apenas um SELECT e nada mais podem ser executados
diretamente no prompt do sql*plus (ou da sua GUI preferida), ao passo que os
que possuem outros comandos (como COLUMN, ou outros) aí necessariamente
precisam ser salvos num arquivo .SQL e executados via @nomedoarquivo.sql dentro
do sql*plus
A idéia é executar (nos DOIS BANCOS, repito!) várias vezes cada scripts numa
** outra ** janela/sessão, ao mesmo tempo em que a sessão no banco-origem que
está tentando criar a packahe tá 'congelada'...
Seguem :
==> consulta de Transações abertas
SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec,
b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
==> consulta de Waits de banco (a view DBA_WAITERS não era default no 9iR2
iirc, se vc não a tem iirc vc deve rodar scripts do SYS em
$ORACLE_HOME/rdbms/admin) :
SELECT * from dba_waiters;
==> script para consultar sessões e seus WAITs (dê ENTER nos itens de filtro
para usar o default, que lista Todas as linhas)
SET PAGES 999
column sid_serial format A10
column seq# format 99999
column event format a29 heading "Wait Event" trunc
column state format a15 heading "Wait State" trunc
column secs format 9999999 heading "Waited so|far (sec)"
column wt format 9999999 heading "Waited|Seconds"
column P1TEXT format a38
column P2TEXT format a38
column P3TEXT format a38
prompt
prompt Sessões esperando por sql*net message estão aguardando
prompt por resposta do usuário.
prompt Sessões com wait_time <> 0 => consomem CPU
prompt
prompt Atenção à Coluna State, se ela for :
prompt Waiting => ignore Waited Secs, Waited So Far=tempo até agora
prompt Wait.Short Time => menos q um tick de CPU, ignorar
prompt Wait. Know Time => Waited Secs=tempo total esperado, ignore Wait So Far
prompt
prompt Colunas que podem ser Especificadas como Condição, na Ordem:
prompt
prompt => tabela session_wait, prefixar com A. , colunas : SID/SEQ#, WAIT_TIME
,
prompt EVENT, SECONDS_IN_WAIT, STATE,
prompt
prompt => tabela sess_io, prefixar com B. , colunas : BLOCK_GETS,
prompt CONSISTENT_GETS, PHYSICAL_READS, BLOCK_CHANGES, CONSISTENT_CHANGES,
prompt PnTEXT, Pn (com n 1 a 3)
prompt
prompt => tabela v$session, prefixar com C. , usar nome da coluna na v$session
prompt
accept v_cond_wait DEFAULT 'a.event is not null' prompt "Condições a Aplicar
(opcional):"
accept sid_list DEFAULT a.sid prompt "Lista de SIDs (opcional):"
accept v_orderby DEFAULT 'a.sid, a.wait_time, a.event' prompt "Order by:"
SELECT c.sid || ',' || c.serial# SID_SERIAL, a.seq#, a.wait_time wt , a.event,
a.seconds_in_wait secs, a.state,
b.block_gets, b.consistent_gets, b.physical_reads, b.block_changes,
b.consistent_changes,
a.P1TEXT, a.P1,
a.P2TEXT, a.P2,
a.P3TEXT, a.P3,
c.LOGON_TIME,
c.LAST_CALL_ET,
c.ROW_WAIT_OBJ#,
c.ROW_WAIT_FILE#,
c.ROW_WAIT_BLOCK#,
c.ROW_WAIT_ROW#,
c.LOCKWAIT,
c.CLIENT_IDENTIFIER,
c.MODULE,
c.PROGRAM,
c.USERNAME,
c.OSUSER,
c.CLIENT_INFO
FROM v$session_wait a, v$sess_io b, v$session c
WHERE a.sid = b.sid
AND c.sid = b.sid
AND a.sid in (&sid_list)
AND &v_cond_wait
ORDER BY &v_orderby;
undefine v_cond_wait
undefine sid_list
undefine v_orderby
/
==> consulta simples de Objetos lockados
SELECT
DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
==> script de consulta de locks de DDLs e DMLs
REM +-----------------------------------------------------------------+
REM | FILE : all_dml_ddl_locks.sql |
REM | PURPOSE : Query all DML and DDL locks in the database. This |
REM | script will query critical information about the lock |
REM | including Lock Type, Object Name/Owner, OS/Oracle |
REM | User and Wait time (in minutes). |
REM +-----------------------------------------------------------------+
COLUMN Object FORMAT a30 HEADING 'Object'
COLUMN Type FORMAT a4 HEADING 'Type'
COLUMN UserID FORMAT a20 HEADING 'OS/Oracle'
COLUMN Hold FORMAT a10 HEADING 'Hold'
COLUMN Program FORMAT a35 HEADING 'Program'
COLUMN usercode FORMAT a12 HEADING 'SID/Serial#'
COLUMN WaitMin FORMAT 999G999 HEADING 'Wait Time|(minutes)'
SELECT
a.osuser || ':' || a.username UserID
, a.sid || '/' || a.serial# usercode
, b.lock_type Type, b.mode_held Hold
, c.owner || '.' || c.object_name Object
, ROUND(d.seconds_in_wait/60,2) WaitMin
, a.program Program
FROM
v$session a
, dba_locks b
, dba_objects c
, v$session_wait d
WHERE
a.sid = b.session_id
AND b.lock_type IN ('DML','DDL')
AND b.lock_id1 = c.object_id
AND b.session_id = d.sid
/
==> consultas de LOCKs via objetos criados pelos scripts de catálogo de locks
REM este script depende dos objs criados por catblock.sql
prompt =========================
prompt SYSTEM-WIDE LOCKS - all requests for locks or latches
prompt =========================
prompt
select substr(username,1,12) "User",
substr(lock_type,1,18) "Lock Type",
substr(mode_held,1,18) "Mode Held"
from sys.dba_lock a, v$session b
where lock_type not in ('Media Recovery','Redo Thread')
and a.session_id = b.sid;
prompt
prompt =========================
prompt DDL LOCKS - These are usually triggers or other DDL
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,15) "Name",
substr(a.type,1,20) "Type",
substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
where a.session_id = b.sid;
prompt
prompt =========================
prompt DML LOCKS - These are table and row locks...
prompt =========================
prompt
select substr(username,1,12) "User",
substr(owner,1,8) "Owner",
substr(name,1,20) "Name",
substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
where a.session_id = b.sid;
/
==> script metalink para fornecer detalhes dos LOCKs
SET ECHO off
REM NAME: TFSCLOCK.SQL
REM USAGE:"@path/tfsclock"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on V_$LOCK, V_$SESSION, SYS.USER$, SYS.OBJ$
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The following locking information script provides fully DECODED
REM information regarding the locks currently held in the database.
REM The report generated is fairly complex and difficult to read,
REM but has considerable detail.
REM
REM The TFTS series contains scripts to provide (less detailed) lock
REM information in a formats which are somewhat less difficult to read:
REM TFSMLOCK.SQL and TFSLLOCK.SQL.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM Too complex to show a representative sample here
REM
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM
set lines 200
set pagesize 66
break on Kill on sid on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a30 heading "Table Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command format a25
column sid format 990
select
nvl(S.USERNAME,'Internal') username,
L.SID,
nvl(S.TERMINAL,'None') terminal,
decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') COMMAND,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,1)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/
==> Se vc o tiver (não lembro exatamente, mas iirc no 9iR2 já tem) execute os
scripts de lock fornecidos pela Oracle em $ORACLE_HOME/rdbms/admin/catblock.sql
e $ORACLE_HOME/rdbms/admin/utllock.sql
==> Consulta dos PL/SQLs em execução :
break on sid skip 1
prompt **** Currently Executing Packages ****
SELECT /*+ RULE */
substr(DECODE(o.kglobtyp,
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,
'CLASS'),1,15) "TYPE",
substr(o.kglnaown,1,30) "OWNER",
substr(o.kglnaobj,1,30) "NAME",
s.indx "SID",
s.ksuseser "SERIAL"
FROM
sys.X_$KGLOB o,
sys.X_$KGLPN p,
sys.X_$KSUSE s
WHERE
o.inst_id = USERENV('Instance') AND
p.inst_id = USERENV('Instance') AND
s.inst_id = USERENV('Instance') AND
o.kglhdpmd = 2 AND
o.kglobtyp IN (7, 8, 9, 12, 13) AND
p.kglpnhdl = o.kglhdadr AND
s.addr = p.kglpnses
ORDER BY 4, 2, 1
/
=============>>> Com o resultado desses scripts Certamente vc será capaz de
identificar qual outra sessão (e em qual banco) está lockando a tal tabela, aí
vc elimina a sessão e recria/recompila a package...
[]s
Chiappa
