删除GoldenGate
来源:互联网 发布:网络营销策划书例文 编辑:程序博客网 时间:2024/06/18 13:13
- 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.
0 0
- 删除GoldenGate
- 卸载、删除GoldenGate的方法
- 删除GOldengate的时候删除不掉
- GoldenGate
- goldengate
- GoldenGate
- GoldenGate
- 源端RAC数据库删除实例操作时GoldenGate的运维流程
- GoldenGate使用
- goldengate精华帖
- GoldenGate 下载
- Oracle Goldengate
- GoldenGate笔记
- goldengate 问题
- Goldengate介绍
- GoldenGate介绍
- Oracle GoldenGate
- GoldenGate Tips
- ORA-00604 ORA-14452 ORA-20783
- sitemesh/decorators装饰器与jsp页面中include的区别
- 编程规范
- HDOJ 2795 Billboard(线段树—找到线段树中>=给定值的第一个点并更新这个点)
- 浅谈spring
- 删除GoldenGate
- android CoordinatorLayout使用
- django 模块 template(filter)
- EvnetBus的原理和使用
- linux下安装jdk
- "网卡eth0,未识别的端口"的解决方法
- 红黑联盟 dos批处理延时技术
- 292. Nim Game
- 冒泡排序及算法优化