- stop *
- stop mgr
- rm -rf ggate
- SQL> drop user gate01 cascade;
- drop user gate01 cascade
- *
- ERROR at line 1:
- ORA-00604: error occurred at recursive SQL level 2
- ORA-20782: GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP
- object used in GoldenGate replication while trigger is enabled. Consult
- GoldenGate documentation and/or call GoldenGate Technical Support if you wish
- to do so., error stack: ORA-06512: at line 226
- ORA-06512: at line 951
- SQL> drop trigger ggs_ddl_trigger_before;
- Trigger dropped.
- SQL> drop user gate01 cascade;
- drop user gate01 cascade
- *
- ERROR at line 1:
- ORA-00604: error occurred at recursive SQL level 1
- ORA-14452: attempt to create, alter or drop an index on temporary table already
- in use
- 1、查出gate01用户的表：
- conn gate01/gate01
- select table_name from tabs;
- TABLE_NAME
- ------------------------------
- GGS_DDL_PRIMARY_KEYS
- GGS_STICK
- 2、查询对应sid,serial#:
- select sid, serial# from v$session where sid =
- (select sid from v$lock where id1 =
- (select object_id from user_objects where object_name = upper('GGS_STICK')));
- 3、结束session:
- alter system kill session 'sid,serial#';
- drop user gate01 cascade;
drop user 失败 ORA-14452
ORA-14452 attempt to create, alter or drop an index on temporary table already in use
drop user oggdba cascade
Error at line 1
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
删除用户报错
select
trigger_name
,
owner
from
dba_triggers
where
trigger_name
=
'GGS_DDL_TRIGGER_BEFORE'
;
drop
trigger
ggs_ddl_trigger_before
;
drop
user
oggdba
cascade;
解决方案
SELECT
'USER: '
||s
.
username||
' SID: '
||s
.sid
||
' SERIAL #: '
||S
.
SERIAL#
"USER HOLDING LOCK"
FROM
v$lock l
,
dba_objects o
,
v$session s
WHERE
l
.
id1
=
o
.
object_id
AND
s
.sid
=
l
.sid
AND
o
.
owner
=
'OGGDBA'
;
SQL> ALTER SYSTEM KILL SESSION '1143,3'
2 ;
System altered.
SQL> drop user oggdba cascade;
User dropped.
2 ;
System altered.
SQL> drop user oggdba cascade;
User dropped.
此时 就可以吧用户DROP 掉了。
我这个问题有些蹊跷， 因为是在TOAD中执行的DROP 语句，反而1143正是这个TOAD的SESSION。
下面是O 给出的介绍
|
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
CASE
STUDY
$ sqlplus /
as
sysdba
-- SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 3 17:01:31 2009
-- Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
-- CREATE OUR USERS AND GRANT THEM PERMISSIONS
create
user
test1 identified
by
test1;
-- User created.
grant
dba
to
test1;
-- Grant succeeded.
create
user
test2 identified
by
test2;
-- User created.
grant
dba
to
test2;
-- Grant succeeded.
connect
test1 / test1;
-- Connected.
-- THIS IS SESSION #1 ***
-- CREATE THE TEMPORARY TABLE
create
global
temporary
table
g_temp (var1 number);
-- Table created.
-- INSERT A ROW INTO THE TEMPORARY TABLE
insert
into
g_temp
values
(1);
-- 1 row created.
-- OPEN A NEW WINDOW ONTO THE SERVER AND LOGIN TO SQLPLUS AS TEST2
-- *** THIS IS SESSION #2
$ sqlplus test2/test2;
-- SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 3 17:06:35 2009
-- Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
-- Connected to:
-- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
-- With the Partitioning, OLAP and Data Mining options
-- INSERT A ROW INTO THE TEMPORARY TABLE
insert
into
test1.g_temp
values
(2);
-- 1 row created.
-- SWITCH BACK TO SESSION #1
drop
table
g_temp;
-- drop table g_temp
-- *
-- ERROR at line 1:
-- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
-- THIS IS EXPECTED BECAUSE SESSION #2 HAS ROWS IN THIS TABLE
-- TRUNCATE THE TABLE
SQL>
truncate
table
g_temp;
Table
truncated.
-- RE-ATTEMPT THE DROP OF THE TABLE
drop
table
g_temp;
-- drop table g_temp
-- *
-- ERROR at line 1:
-- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
-- THIS IS ALSO EXPECTED BECAUSE A TRUNCATE WILL ONLY REMOVE ROWS FOR THE CURRENT SESSION (SEE SESSION #2 BELOW)
-- SWITCH BACK TO SESSION #2
select
*
from
test1.g_temp;
-- VAR1
-- ----------
-- 2
-- SWITCH BACK TO SESSION #1
-- DETERMINE WHICH USERNAME / SID / SERIAL# IS HOLDING THE LOCKS ON THE TEMPORARY TABLE
SELECT
'USER: '
||s.username||
' SID: '
||s.sid||
' SERIAL #: '
||S.SERIAL#
"USER HOLDING LOCK"
FROM
v$lock l
,dba_objects o
,v$session s
WHERE
l.id1 = o.object_id
AND
s.sid = l.sid
AND
o.owner =
'TEST1'
AND
o.object_name =
'G_TEMP'
;
-- USER HOLDING LOCK
-- --------------------------------------------------------------------------------
-- USER: TEST2 SID: 144 SERIAL #: 28
-- KILL THE SESSION HOLDING THE LOCK
ALTER
SYSTEM KILL SESSION
'144,28'
;
-- System altered.
-- DROP THE TEMPORARY TABLE
DROP
TABLE
G_TEMP;
-- Table dropped.
-- CLEAN UP
connect
/
as
sysdba;
-- Connected.
drop
user
test1;
-- User dropped.
drop
user
test2;
-- User dropped.
|
