table shrink

Posted by Arsalan on Dec 31, 2016; 5:31am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/table-shrink-tp1843.html


Sir

i can not shrink table, it give error so what will be cause ?

SQL>      alter table tt enable row movement;

Table altered.

SQL>      alter table tt shrink space cascade;
     alter table tt shrink space cascade
*
ERROR at line 1:
ORA-10663: Object has rowid based materialized views


SQL>      alter table tt disable row movement;

---------------------------------------------------------------
example -1

drop materialized view log on tt;
drop materialized view tt_mv;
drop table tt purge;
--------------------------------------------------

 create table tt (id number(23));

 create materialized view log on tt with rowid including new values;

 CREATE MATERIALIZED VIEW tt_mv
 REFRESH fast on demand
 WITH ROWID AS
 SELECT * FROM tt;

insert into tt values(1);
insert into tt values(1);
insert into tt values(1);
commit;

execute dbms_mview.refresh( list => 'tt_mv');


select count(*) from tt;
select count(*) from tt_mv;

     alter table tt enable row movement;
     alter table tt shrink space cascade;
     alter table tt disable row movement;