oracle彻底删除物化视图,如何删除多余的物化视图日志表？

最新推荐文章于 2021-04-15 15:55:50 发布

望止洋

最新推荐文章于 2021-04-15 15:55:50 发布

阅读量1.3k

点赞数

文章标签： oracle彻底删除物化视图

ORACLE 11204,

--1

SYS@cloud>create user scott_zhf identified by "1";

User created.

Elapsed: 00:00:02.20

SYS@cloud>grant dba to scott_zhf;

Grant succeeded.

Elapsed: 00:00:00.01

--2

SYS@cloud>connect scott_zhf/1

Connected.

SCOTT_ZHF@cloud>create table deleteme(x number);

Table created.

Elapsed: 00:00:00.10

SCOTT_ZHF@cloud>create table insertme(x number);

Table created.

Elapsed: 00:00:00.00

--3

SCOTT_ZHF@cloud>create materialized view log on deleteme with rowid;

Materialized view log created.

Elapsed: 00:00:00.03

SCOTT_ZHF@cloud>select log_table, master from user_mview_logs;

LOG_TABLE MASTER

------------------------------ ------------------------------

MLOG$_DELETEME DELETEME

Elapsed: 00:00:00.01

SCOTT_ZHF@cloud>rename mlog$_deleteme to old_mlog$_deleteme;

Table renamed.

Elapsed: 00:00:00.02

SCOTT_ZHF@cloud>rename insertme to mlog$_deleteme;

Table renamed.

Elapsed: 00:00:00.01

SCOTT_ZHF@cloud>select log_table, master from user_mview_logs;

LOG_TABLE MASTER

------------------------------ ------------------------------

MLOG$_DELETEME DELETEME

Elapsed: 00:00:00.01

SCOTT_ZHF@cloud>select table_name from tabs;

TABLE_NAME

------------------------------

DELETEME

MLOG$_DELETEME

OLD_MLOG$_DELETEME

Elapsed: 00:00:00.66

--4

SCOTT_ZHF@cloud>drop materialized view log on deleteme;

Materialized view log dropped.

Elapsed: 00:00:00.05

SCOTT_ZHF@cloud>select table_name from tabs;

TABLE_NAME

------------------------------

DELETEME

OLD_MLOG$_DELETEME

Elapsed: 00:00:00.86

SCOTT_ZHF@cloud>drop table OLD_MLOG$_DELETEME

2 /

drop table OLD_MLOG$_DELETEME

ERROR at line 1:

ORA-32417: must use DROP MATERIALIZED VIEW LOG to drop "SCOTT_ZHF"."OLD_MLOG$_DELETEME"

Elapsed: 00:00:00.01

--5

SCOTT_ZHF@cloud>drop materialized view log on deleteme;

drop materialized view log on deleteme

ERROR at line 1:

ORA-12002: there is no materialized view log on table "SCOTT_ZHF"."DELETEME"

Elapsed: 00:00:00.01

SCOTT_ZHF@cloud>select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Elapsed: 00:00:00.01

--6

SCOTT_ZHF@cloud>drop table DELETEME;

Table dropped.

Elapsed: 00:00:00.01

SCOTT_ZHF@cloud>select table_name from tabs;

TABLE_NAME

------------------------------

OLD_MLOG$_DELETEME

Elapsed: 00:00:00.85

SCOTT_ZHF@cloud>select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAME

------------------- --------------------------------------------------------------------------------------------------------------------------------

INDEX I_MLOG$_DELETEME

TABLE OLD_MLOG$_DELETEME

Elapsed: 00:00:00.02

SCOTT_ZHF@cloud>drop index I_MLOG$_DELETEME;

Index dropped.

Elapsed: 00:00:00.02

SCOTT_ZHF@cloud>select object_type, object_name from user_objects;

OBJECT_TYPE OBJECT_NAME

------------------- --------------------------------------------------------------------------------------------------------------------------------

TABLE OLD_MLOG$_DELETEME

Elapsed: 00:00:00.02

--7

SCOTT_ZHF@cloud>drop table OLD_MLOG$_DELETEME;

drop table OLD_MLOG$_DELETEME

ERROR at line 1:

ORA-32417: must use DROP MATERIALIZED VIEW LOG to drop "SCOTT_ZHF"."OLD_MLOG$_DELETEME"

Elapsed: 00:00:00.00

SCOTT_ZHF@cloud>

问，如何删除该对象？