12 | 12 | 2017
Latest Articles
Popular Articles

Administration

PL/SQL invalidations when you enable row movement

User Rating:  / 0
PoorBest 

PL/SQL invalidations when you enable row movement at table level.

 

These days the Oracle database allows us to reorg almost online our possible fragmented table and index segments ( from 10G R1 onwards ) ( See
(Almost) Online space recovery ( reset HWM ) ) and we can flashback a table to its previous state ( from 10G R1 onwards ) without the need to restore the entire database to a previous state. Both options require that row movement is enabled at table level. It does not take a hero to enable row movement for a table.




SQL_10G > alter table MY_TABLES enable row movement;

Table altered.


What not that many people know is that there is a difference between 10G and 11G. in 10G enabling row movement does invalidate dependent PL/SQL whereas it does not in 11G.



In 10G : Enabling row movement does invalidate dependent PL/SQL.


SQL_10G > create user TEST10G identified by TEST10G default tablespace users temporary tablespace temp;

User created.

SQL_10G > grant create session,create table,create procedure to TEST10G;

Grant succeeded.

SQL_10G > alter user test10G quota unlimited on users;

User altered.

SQL_10G > connect TEST10G/TEST10G

Connected.

SQL_10G > create table my_tables as select * from all_tables;

Table created.

SQL_10G > create or replace procedure my_proc as
2  tab varchar2(30);
3  cursor lc1 is select table_name from my_tables;
4  begin
5  for i in lc1 loop
6  tab := i.table_name;
7  dbms_output.put_line(tab);
8  end loop;
9  end;
10  /

Procedure created.

SQL_10G > select owner,name,type from all_dependencies where REFERENCED_NAME = 'MY_TABLES';

OWNER                          NAME                           TYPE
------------------------------ ------------------------------ -----------------
TEST10G                        MY_PROC                        PROCEDURE

SQL_10G > column object_name format a30
SQL_10G > select object_name,status from user_objects;

OBJECT_NAME                    STATUS
------------------------------ -------
MY_TABLES                      VALID
MY_PROC                        VALID

SQL_10G > alter table MY_TABLES enable row movement;

Table altered.

SQL_10G > select object_name,status from user_objects;

OBJECT_NAME                    STATUS
------------------------------ -------
MY_TABLES                      VALID
MY_PROC                        INVALID


In 11G : Enabling row movement does NOT invalidate dependent PL/SQL.


SQL_11G > create user TEST11G identified by TEST11G default tablespace users temporary tablespace temp;

User created.

SQL_11G > grant create session,create table,create procedure to TEST11G;

Grant succeeded.

SQL_11G > alter user test11G quota unlimited on users;

User altered.

SQL_11G > connect test11G/TEST11G;
Connected.
SQL_11G > create table my_tables as select * from all_tables;

Table created.

SQL_11G > create or replace procedure my_proc as
2  tab varchar2(30);
3  cursor lc1 is select table_name from my_tables;
4  begin
5  for i in lc1 loop
6  tab := i.table_name;
7  dbms_output.put_line(tab);
8  end loop;
9  /

10  /

Procedure created.


SQL_11G > select owner,name,type from all_dependencies where REFERENCED_NAME = 'MY_TABLES';

OWNER                   NAME                  TYPE
------------------------------ ------------------------------ ------------------
TEST11G                MY_PROC                  PROCEDURE

SQL_11G > column object_name format a30
SQL_11G > select object_name,status from user_objects;

OBJECT_NAME                    STATUS
------------------------------ -------
MY_TABLES                      VALID
MY_PROC                        VALID

SQL_11G > alter table MY_TABLES enable row movement;

Table altered.

SQL_11G > column object_name format a30
SQL_11G > select object_name,status from user_objects;      

OBJECT_NAME               STATUS
------------------------------ -------
MY_PROC                 VALID
MY_TABLES               VALID