ORA-38301: cannot perform DDL/DML over objects in Recycle Bin分析
这几天，在客户的告警日志中，发现大量的ORA-38301的告警，
Thu Jul 30 22:08:34 CST 2015
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
这种情况是怎么回事呢？我查了MOS，其原因是Oracle不支持在回收站里对象进行DML/DDL 操作。
下面，我们来演示一下：
1）确认回收站功能启用
SQL> show parameter recyclebin;
NAME TYPE VALUE
———————————— ———– ——————————
recyclebin string ON
如果是OFF，使用alter system set recyclebin=on;开启回收站功能
2）创建和删除表
SQL> create table t_drop
2 as
3 select * from tab;
Table created.
SQL> drop table t_drop;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
T_DROP BIN$qIuF4JHvDJDgQKjADAsarA==$0 TABLE 2011-07-21:09:46:52
3）查看回收站表中数据
SQL> select * from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
T TABLE
TAB_CRM_CLIENT TABLE
TEST_COUNT TABLE
V_TEST VIEW
TEST TABLE
T_DROP TABLE
TAB_OLCM TABLE
7 rows selected.
4）对回收站中数据进行dml操作
SQL> delete from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
delete from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
5）对回收站中数据进行ddl操作
SQL> drop table “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
drop table “BIN$qIuF4JHvDJDgQKjADAsarA==$0″
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
6）查看回收站，错误重现
Thu Jul 21 09:56:44 2011
performing DML/DDL operation over object in bin.
Thu Jul 21 09:57:19 2011
performing DML/DDL operation over object in bin.
解决办法：
尽量不要使用回收站里的对象操作，可以使用闪回技术
Thu Jul 30 22:08:34 CST 2015
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
performing DML/DDL operation over object in bin.
这种情况是怎么回事呢？我查了MOS，其原因是Oracle不支持在回收站里对象进行DML/DDL 操作。
下面，我们来演示一下：
1）确认回收站功能启用
SQL> show parameter recyclebin;
NAME TYPE VALUE
———————————— ———– ——————————
recyclebin string ON
如果是OFF，使用alter system set recyclebin=on;开启回收站功能
2）创建和删除表
SQL> create table t_drop
2 as
3 select * from tab;
Table created.
SQL> drop table t_drop;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
T_DROP BIN$qIuF4JHvDJDgQKjADAsarA==$0 TABLE 2011-07-21:09:46:52
3）查看回收站表中数据
SQL> select * from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
T TABLE
TAB_CRM_CLIENT TABLE
TEST_COUNT TABLE
V_TEST VIEW
TEST TABLE
T_DROP TABLE
TAB_OLCM TABLE
7 rows selected.
4）对回收站中数据进行dml操作
SQL> delete from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
delete from “BIN$qIuF4JHvDJDgQKjADAsarA==$0″
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
5）对回收站中数据进行ddl操作
SQL> drop table “BIN$qIuF4JHvDJDgQKjADAsarA==$0″;
drop table “BIN$qIuF4JHvDJDgQKjADAsarA==$0″
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
6）查看回收站，错误重现
Thu Jul 21 09:56:44 2011
performing DML/DDL operation over object in bin.
Thu Jul 21 09:57:19 2011
performing DML/DDL operation over object in bin.
解决办法：
尽量不要使用回收站里的对象操作，可以使用闪回技术
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30430420/viewspace-1792072/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
