Friday, April 02, 2010

Objects Invalid when Enable Row Movement

On Oracle 10g, The command "ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT" make the dependent objects be INVALID status. So, we need to recompile the dependent objects or wait for compilation automatic when objects are called.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
PL/SQL Release 10.2.0.4.0
CORE 10.2.0.4.0
TNS for Linux: Version 10.2.0.4.0
NLSRTL Version 10.2.0.4.0

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID

SQL> alter table tb01 enable row movement;

Table altered.

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW INVALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE INVALID

SQL> select * from v_tb01;

SQL> exec PRC_TB01;

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID
This is the issue, when we need to use "enable row movement" command, But on Oracle 11g has changed.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
PL/SQL Release 11.1.0.6.0
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0
NLSRTL Version 11.1.0.6.0

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID

SQL> alter table tb01 enable row movement;

Table altered.

SQL> select object_name, object_type, status from dba_objects where object_name like '%TB01';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
V_TB01 VIEW VALID
TB01 TABLE VALID
PRC_TB01 PROCEDURE VALID

4 comments:

Anonymous said...

thanks for share.

Coskan Gundogar said...

Looks like things are change in 11GR2


HR@ORACOS>
CREATE OR REPLACE PROCEDURE p_test_table (
test_sql out varchar2) is
BEGIN
select substr(sqltext,5) into test_sql from test_table where rownum=1;
dbms_output.put_line(test_sql);
END p_test_table;
2 3 4 5 6 7 /

Procedure created.

HR@ORACOS> create or replace view v_test_table as select * from test_table;

View created.

HR@ORACOS> select owner,object_name,status from dba_objects where object_name like '%TEST_TABLE%';

OWNER OBJECT_NAME STATUS
------------------------------ --------------- -------
HR V_TEST_TABLE VALID
HR P_TEST_TABLE VALID
HR TEST_TABLE VALID

HR@ORACOS> alter table test_table enable row movement;

Table altered.

HR@ORACOS> select owner,object_name,status from dba_objects where object_name like '%TEST_TABLE%';

OWNER OBJECT_NAME STATUS
------------------------------ --------------- -------
HR V_TEST_TABLE VALID
HR P_TEST_TABLE VALID
HR TEST_TABLE VALID

Coskan Gundogar said...

Looks like things are changed on 11GR2


HR@ORACOS> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

HR@ORACOS>
CREATE OR REPLACE PROCEDURE p_test_table (
test_sql out varchar2) is
BEGIN
select substr(sqltext,5) into test_sql from test_table where rownum=1;
dbms_output.put_line(test_sql);
END p_test_table;
2 3 4 5 6 7 /

Procedure created.

HR@ORACOS> create or replace view v_test_table as select * from test_table;

View created.

HR@ORACOS> select owner,object_name,status from dba_objects where object_name like '%TEST_TABLE%';

OWNER OBJECT_NAME STATUS
------------------------------ --------------- -------
HR V_TEST_TABLE VALID
HR P_TEST_TABLE VALID
HR TEST_TABLE VALID

HR@ORACOS> alter table test_table enable row movement;

Table altered.

HR@ORACOS> select owner,object_name,status from dba_objects where object_name like '%TEST_TABLE%';

OWNER OBJECT_NAME STATUS
------------------------------ --------------- -------
HR V_TEST_TABLE VALID
HR P_TEST_TABLE VALID
HR TEST_TABLE VALID

Surachart said...

@Coskan Gundogar

Thank You for your comment