Chiappa,
Segue resultados dos scripts, do Banco Origem. No Banco Destino nem chega a
abrir sessão.
No 9i não existe o DBMS_STATS.gather_dictionary_stats então fui executar o
dbms_stats.gather_schema_stats('SYS'); e ocorreu erro.
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 160
ORA-06512: at "SYS.DBMS_STATS", line 10031
ORA-06512: at "SYS.DBMS_STATS", line 10324
ORA-06512: at "SYS.DBMS_STATS", line 10378
ORA-06512: at "SYS.DBMS_STATS", line 10355
ORA-06512: at line 1
Não sei informar como esta as estatísticas do SYS, por isso pensei em executar.
Com qualquer outro usuário executa com sucesso.
SQL> set linesize 132
SQL> 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;
2 3
SID SADDR SES_ADDR USERNAME XIDUSN
USED_UREC USED_UBLK
---------- -------- -------- ------------------------------ ----------
---------- ----------
18 6920A568 6920A568 PRODUCAO 3
1 1
12 6B206C24 6B206C24 PRODUCAO 4
21646 254
SQL> SELECT * from dba_waiters;
no rows selected
SQL> 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;
2 3 4 5 6 7
no rows selected
SQL> SELECT
2 a.osuser || ':' || a.username UserID
3 , a.sid || '/' || a.serial# usercode
4 , b.lock_type Type, b.mode_held Hold
5 , c.owner || '.' || c.object_name Object
, ROUND(d.seconds_in_wait/60,2) WaitMin
6 7 , a.program Program
FROM
v$session a
8 9 10 , dba_locks b
11 , dba_objects c
12 , v$session_wait d
13 WHERE
14 a.sid = b.session_id
15 AND b.lock_type IN ('DML','DDL')
16 AND b.lock_id1 = c.object_id
17 AND b.session_id = d.sid
18 /
USERID
-------------------------------------------------------------
USERCODE
TYPE
---------------------------------------------------------------------------------
--------------------------
HOLD
----------------------------------------
OBJECT
------------------------------------------------------------------------------------------------------------------------------------
WAITMIN PROGRAM
---------- ------------------------------------------------
:PRODUCAO
12/4
DML
Row-X (SX)
PRODUCAO.IND_MOV_TEMPORANEA_EURO
0
:PRODUCAO
12/4
DML
Row-X (SX)
PRODUCAO.IND_MOV_TEMPORANEA_EURO_AUX
0
:PRODUCAO
12/4
DML
Row-X (SX)
PRODUCAO.IND_STATUS_TEMPORANEA_EURO
0
:PRODUCAO
12/4
DML
Row-X (SX)
PRODUCAO.IND_RASTR_TEMPORANEA_EURO
0
:PRODUCAO
12/4
DML
Row-X (SX)
PRODUCAO.IND_FILA_RASTR_TEMP
0
:PRODUCAO
12/4
DML
Row-X (SX)
PRODUCAO.IND_FILA_RASTR_TEMP_HIST
0
:PRODUCAO
12/4
DML
Row-X (SX)
PRODUCAO.IND_RASTR_TEMPORANEA_PER
0
7 rows selected.
SQL> 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;
2 3 4 5 6
User Lock Type Mode Held
------------ ------------------ ------------------
XR Null
PRODUCAO Transaction Exclusive
PRODUCAO JQ Exclusive
PRODUCAO CU Exclusive
PRODUCAO DML Row-X (SX)
PRODUCAO DML Row-X (SX)
PRODUCAO Transaction Exclusive
PRODUCAO DML Row-X (SX)
PRODUCAO DML Row-X (SX)
PRODUCAO DML Row-X (SX)
PRODUCAO DML Row-X (SX)
PRODUCAO DML Row-X (SX)
12 rows selected.
SQL> SQL> 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;
2 3 4 5 6 7 >>> NÃO EXECUTA
SQL> 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;
/
2 3 4 5 6
User Owner Name Mode held
------------ -------- -------------------- -------------
PRODUCAO PRODUCAO IND_PMFP Row-X (SX)
SQL>
User Owner Name Mode held
------------ -------- -------------------- -------------
PRODUCAO PRODUCAO IND_PMFP_PEDIDO Row-X (SX)
PRODUCAO PRODUCAO IND_PMFP Row-X (SX)
SQL> set lines 200
SQL> set pagesize 66
SQL> break on Kill on sid on username on terminal
SQL> column Kill heading 'Kill String' format a13
SQL> column res heading 'Resource Type' format 999
SQL> column id1 format 9999990
SQL> column id2 format 9999990
SQL> column locking heading 'Lock Held/Lock Requested' format a40
SQL> column lmode heading 'Lock Held' format a20
SQL> column request heading 'Lock Requested' format a20
SQL> column serial# format 99999
SQL> column username format a10 heading "Username"
SQL> column terminal heading Term format a6
SQL> column tab format a30 heading "Table Name"
SQL> column owner format a9
SQL> column LAddr heading "ID1 - ID2" format a18
SQL> column Lockt heading "Lock Type" format a40
SQL> column command format a25
SQL> column sid format 990
SQL> select
2 nvl(S.USERNAME,'Internal') username,
3 L.SID,
4 nvl(S.TERMINAL,'None') terminal,
5 decode(command,
6 0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
7 decode(command,
8 0,'BACKGROUND',
9 1,'Create Table',
10 2,'INSERT',
11 3,'SELECT',
12 4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
13 6,'UPDATE',
14 15 7,'DELETE',
16 8,'DROP',
17 9,'CREATE INDEX',
18 10,'DROP INDEX',
19 11,'ALTER INDEX',
20 12,'DROP TABLE',
21 13,'CREATE SEQUENCE',
22 14,'ALTER SEQUENCE',
23 15,'ALTER TABLE',
24 16,'DROP SEQUENCE',
25 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',
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
42 43 44 45 46 47 48 49 50 51 43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
52 53 54 55 56 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',
57 58 59 60 61 62 63 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',
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
80 81 82 83 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',
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
100 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,
101 102 103 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',
104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
120 121 122 123 124 125 126 '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',
127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142
143 144 145 '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)',
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161
162 163 164 165 166 167 168 169 170 171 172 173 174 '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)',
175 176 177 178 179 180 181 182 '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)',
183 184 185 186 '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)',
187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202
'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)',
203 '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)',
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219
220 221 222 223 '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)',
224 225 226 227 228 229 230 231 232 233 234 235 '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
/
236 237 238 239 240 241 242 243 244 245 246
Username SID Term Table Name COMMAND
Lock Held Lock Requested ID1 - ID2 Lock Type
---------- ---- ------ ------------------------------ -------------------------
-------------------- -------------------- ------------------
----------------------------------------
PRODUCAO 18 pts/1 None SELECT
Exclusive NONE 196625-5354311 TX - Transaction
enqueue lock
SQL> SELECT /*+ RULE */
2 substr(DECODE(o.kglobtyp,
3 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,
'CLASS'),1,15) "TYPE",
4 substr(o.kglnaown,1,30) "OWNER",
5 6 substr(o.kglnaobj,1,30) "NAME",
7 s.indx "SID",
8 s.ksuseser "SERIAL"
9 FROM
10 sys.X_$KGLOB o,
11 sys.X_$KGLPN p,
12 sys.X_$KSUSE s
13 WHERE
14 o.inst_id = USERENV('Instance') AND
15 p.inst_id = USERENV('Instance') AND
16 s.inst_id = USERENV('Instance') AND
17 o.kglhdpmd = 2 AND
18 o.kglobtyp IN (7, 8, 9, 12, 13) AND
19 p.kglpnhdl = o.kglhdadr AND
20 s.addr = p.kglpnses
21 ORDER BY 4, 2, 1
22 /
sys.X_$KSUSE s
*
ERROR at line 12:
ORA-00942: table or view does not exist
Grato,
Ednilson Silva
Tecnologia da Informação
JBS S/A
De: oracle_br@yahoogrupos.com.br [mailto:oracle_br@yahoogrupos.com.br]
Enviada em: sexta-feira, 16 de setembro de 2016 11:34
Para: oracle_br@yahoogrupos.com.br
Assunto: Re: RES: RES: RES: RES: [oracle_br] Re: delete
Bom dia - então, como eu disse, no ** instante ** em que a sessão tá sendo
bloqueada, CADÊ o resultado dos scripts nesse momento, EM ESPECIAL o scripts de
WAITs e o de Transações ? Pois NECESSARIAMENTE se a sessão tá sendo bloqueada
e/ou está esperando por um recurso isso TEM QUE aparecer nas colunas de WAIT da
V$SESSION e no registro correspondente da V$SESSION_WAIT - mostra pra gente
esses resultados, coletados nesse momento E com várias execuções, que a gente
pode palpitar...
[]s
Chiappa
OBS : iirc no 9i não era default o parâmetro TIMED_STATISTICS - confirme que
esse cara tá setado, pois se não vc Não Vai ver info de waits....
Preferencialmente, STATISTICS_LEVEL deve estar pelo menos como TYPICAL,
também...
