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
Posts
4 comments:
thanks for share.
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
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
@Coskan Gundogar
Thank You for your comment
Post a Comment