ORA-25191: cannot reference overflow table of an index-organized table

Yesterday I faced below issue when I was trying to grant some privileges to a table :-

grant select,insert,update,delete on AMIT.SYS_IOT_OVER_14456 TO TEST;
                                             *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

Issue :-

This issue comes when we deal with Index Organized tables(IOT).

Solution :-

Issue this statement against the Parent Index Organized table containing the specified overflow table.

SQL> select IOT_NAME from dba_tables where owner='AMIT' and table_name='SYS_IOT_OVER_14456';

IOT_NAME

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

AMIT_TABLE

SQL> grant select,insert,update,delete on AMIT.AMIT_TABLE to TEST;

Grant succeeded.

Any operation related to Index Oraganized table has to be done by above method else it will give ORA-25191.

I hope this article helped you.

Thanks

Amit Rath

How to restore a dropped table using Flashback Drop

Flashback Drop is a feature in Oracle Database through which we can restore table which has been accidentally dropped.

To use this feature FLASHBACK has to be enabled in your database . To enable FlASHBACK
visit Enable Flashback in Oracle

Database has to be in Archive log mode. To enable Archive log mode in Database visit Change Archive mode of Database

This feature uses recycle bin to restore dropped table. We can specify either the name of the table in recycle bin or the original table name to restore. We can also rename the table while restoring it from the recycle bin using "RENAME TO" clause.

PFB example to restore a table and its dependent objects through Flashback Drop :-

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                    TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV   VIEW                               VALID

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

8 rows selected.

SQL> drop table AMIT cascade constraints;

Table dropped.

########### if we use purge in Drop command then we cannot restore it from recycle bin##########

SQL> drop table AMIT cascade constraints purge;

Table dropped.

Query the user_recyclebin to get the details of dropped table and its dependents. Recyclebin is the synonym for user_recyclebin. The system generated name of the dropped object is unique across database. Dropping a table drop the associated indexes , triggers but not Procedures , Functions and views . 

When we restore a table from Recycle bin , the dependent objects such as triggers , indexes etc do not get their original names back, they have their system generated names with them . We have to manually rename them after the table restore operation. So before restoring the table from Recycle bin, make a note of the system generated recycle bin names of the dependent objects. 

After dropping AMIT table , before restoring it run below query :-

SQL> select  object_name, original_name,type, createtime FROM recyclebin;

OBJECT_NAME                                                             ORIGINAL_NAME                    TYPE                      CREATETIME
-----------------------------------------------------------------          --------------------------------   ------------------------- -------------------
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                         D_UI                      INDEX   2013-07-01:11:03:38
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                       ID_NI                     INDEX   2013-07-01:11:03:38
BIN$YagDo75NS0enszVEvCtv+g==$0                           D_NI                      INDEX   2013-07-01:11:03:38
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                           ORDER_NI            INDEX   2013-07-01:11:03:38
BIN$kow3P8xOSwC1fzhLep5wgg==$0                          USERID                 INDEX   2013-07-01:11:03:38
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                              ID_TIME               INDEX   2013-07-01:11:03:39
BIN$MPqYce8sQLGUoxTor3zMVw==$0                        H_NI                      INDEX   2013-07-01:11:03:39
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                       DEACT_H_NI        INDEX   2013-07-01:11:03:39
BIN$kzeXKoZoSwqycBiH6X8FzA==$0                          AMIT_TT              TRIGGER   2013-07-09:12:48:03
BIN$C5EACqXLRZyACHAhLjC/LQ==$0                       AMIT                     TABLE   2013-07-01:11:02:10

Now restore table through Flashback drop :-

SQL> flashback table AMIT to before drop;

Flashback complete.

############ if you want to rename AMIT to TEST then use RENAME ################

SQL> flashback table AMIT to before drop rename to TEST;

Flashback complete

Once the table has been restored , dependent objects also have been restored. Dependent objects like indexes , triggers have their system generated names with them and are in Valid state. Dependent objects like procedures , Functions, View associated with table are in invalid state . We have to compile them manually to make them valid.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME                                                             STATUS
------------------------------------------------------                  --------
BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0                  VALID
BIN$MPqYce8sQLGUoxTor3zMVw==$0                  VALID
BIN$8epaA+Z7RyeIQ+zTiT8r4g==$0                       VALID
BIN$kow3P8xOSwC1fzhLep5wgg==$0                    VALID
BIN$amdEjo63Q5GfqR2aqyLrYg==$0                     VALID
BIN$YagDo75NS0enszVEvCtv+g==$0                    VALID
BIN$zwr7JQ55SOecmnkTOAbGkA==$0                 VALID
BIN$8RsifQMpTIe2DYcWdrZBsQ==$0                    VALID

8 rows selected.

SQL> select TRIGGER_NAME,status from user_triggers where TABLE_NAME='AMIT';

TRIGGER_NAME                                          STATUS
------------------------------                                  --------
BIN$kzeXKoZoSwqycBiH6X8FzA==$0        ENABLED

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 INVALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_VV    VIEW                               INVALID

We have to manully rename Indexes and triggers according to the note we have made before restoring.

SQL> alter trigger "BIN$kzeXKoZoSwqycBiH6X8FzA==$0" rename to AMIT_TT;

Trigger altered.

SQL> alter index "BIN$E0JVkQpIRT6PsqF+EAU1PQ==$0" rename to ID_NI;

Index altered.

SQL> alter index "BIN$MPqYce8sQLGUoxTor3zMVw==$0" rename to D_UI;

Like this rename all the indexes . Now compile the procedures and Views.

SQL> alter view AMIT_VV compile;

View altered.

SQL> alter procedure AMIT_PP compile;

Procedure altered.

SQL> select index_name,status from user_indexes where table_name='AMIT';

INDEX_NAME         STATUS
-------------                 --------
D_UI                        VALID
ID_NI                       VALID
D_NI                        VALID
ORDER_NI              VALID
USERID                  VALID
ID_TIME                VALID
H_NI                       VALID
DEACT_H_NI        VALID

SQL> select object_name,object_type,status from user_objects where object_name like '%AMIT%';

OBJECT_NAME                                        OBJECT_TYPE              STATUS
----------------------------------------------          -------------------                 -------
AMIT                                                      TABLE                             VALID
AMIT_PP                                              PROCEDURE                 VALID
AMIT_SEQ                                            SEQUENCE                    VALID
AMIT_TT                                             TRIGGER                         VALID
AMIT_VV    VIEW                               VALID

Restoration of table from Recylebin completed.

I hope this article helped you.

Regards,
Amit Rath

ONLINE Table Redefinition

Yesterday I have restructured one of my normal table to partitioned table. There are two ways to do that :-

1. Export - Import method.
2. Online table Redefinition

1. Export - Import Method :-

This method can be used when we can take downtime of that table :-

a. Export the normal table

expdp user/password directory=datapump dumpfile=expdp_table.dmp logfile=expdp_table.log tables=TEST compression=all &

b. create the partitioned table as the same structure of normal table

c. Drop the normal table

d . Import the dumpfile to the Partition table.

impdp user/password directory=datapump dumpfile=expdp_table.dmp logfile=impdp.log &

 2. Online table Redefinition :-

This method is used when downtime option is not available with us. But to redefine a table by online table redefinition , table must have a primary key.

Consider a table named test has to be redefined. PFB steps:-

1. create a partitioned table having same structure as TEST table has.

SQL> create table test_test (
   2  id number(23),
  3  name varchar2(12),
  4  systemdate date,
  5  id_old number(12)
  6  )
  7  partition by list(id)
  8  (
  9  partition par_01 values (1,2,3),
 10  partition par_02 values (4,5,6),
 11  partition par_03 values (7,8,9),
 12  partition par_04 values (10,11,12)
 13  );

Table created.

2. Check whether table TEST can be redefined or not.if table which has to be redefined don't have any issues then it will give you successful message else error message.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('AMIT','TEST');

PL/SQL procedure successfully completed.
######### TEST table can be redefined #################


SQL> EXEC DBMS_REDEFINITION.can_redef_table('AMIT','TEST_TEST');
BEGIN DBMS_REDEFINITION.can_redef_table('AMIT','TEST_TEST'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "AMIT"."TEST_TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1
############## TEST_TEST table cannot be redefined due to above error##########

3. Start the redefinition :-

SQL> EXEC DBMS_REDEFINITION.start_redef_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.


SQL> SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
SQL>   2    3  BEGIN
 4   DBMS_REDEFINITION.copy_table_dependents(
 5   uname             => 'AMIT',
 6   orig_table        => 'TEST',
 7   int_table         => 'TEST_TEST',
 8   copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
 9   copy_triggers     => TRUE,  -- Default
10   copy_constraints  => TRUE,  -- Default
11   copy_privileges   => TRUE,  -- Default
12   ignore_errors     => FALSE, -- Default
13   num_errors        => l_num_errors);
14   DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
15  END;
16  /
l_num_errors=0

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.sync_interim_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.


SQL> SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'AMIT' and object_name='TEST';

OBJECT_NAME                                  OBJECT_TYPE                         STATUS
---------------------------------         ---------------------------------          ---------------------


TEST                                                   TABLE                                        VALID

TEST                                                  TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID


SQL> select index_name ,table_name,column_name from user_ind_columns where table_name='TEST';

INDEX_NAME                            TABLE_NAME                     COLUMN_NAME

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


TEST_IDX                                   TEST                                   ID_OLD

TEST_PK                                    TEST                                   ID


SQL>  select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='TEST';

TABLE_NAME                       PARTITION_NAME                                             TABLESPACE_NAME                               NUM_ROWS
---------------------------------     ------------------------------------------           ------------------------------                                          ------------------------
TEST                                                     PAR_01                                                USERS
TEST                                                     PAR_02                                                USERS
TEST                                                     PAR_03                                                USERS
TEST                                                    PAR_04                                                 USERS



SQL> exec dbms_stats.gather_table_stats('AMIT','TEST',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='TEST';


TABLE_NAME                           PARTITION_NAME                                TABLESPACE_NAME                                        NUM_ROWS
-----------------------------------     ------------------------------------------    --------------------------------------------                                          ------------------

TEST                                            PAR_01                                    USERS                                                                                        3
TEST                                            PAR_02                                    USERS                                                                                        1
TEST                                            PAR_03                                    USERS                                                                                        2
TEST                                            PAR_04                                     USERS                                                                                        0

Once data reflects in our partitioned table . Drop the newly created table i,e TEST_TEST

I hope this article helped you.

Regards,
Amit Rath