7 Replies Latest reply on Apr 29, 2012 5:20 PM by sb92075

    IOT overflow ORA-25191:

    user3266490
      Hi,

      Db :10.2
      Os :Aix

      While execute select statement ,we getting error.

      SQL> select * from gei.SYS_IOT_OVER_246638;
      select * from gei.SYS_IOT_OVER_246638
      *
      ERROR at line 1:
      ORA-25191: cannot reference overflow table of an index-organized table

      SQL> select table_name from dba_Tables where table_name='SYS_IOT_OVER_246638';

      TABLE_NAME
      --------------
      SYS_IOT_OVER_2
      46638


      SQL> select segment_type,segment_name from dba_Segments where segment_name='SYS_IOT_OVER_246638';

      SEGMENT_TYPE
      ------------------------------------------------------
      SEGMENT_NAME
      --------------------------------------------------------------------------------
      TABLE
      SYS_IOT_OVER_246638


      SQL> select index_name,index_type,table_name,table_type from dba_indexes where table_name='SYS_IOT_OVER_246638';

      no rows selected


      We want to move that table to another tablespace.

      Please anyone helpme out

      Thanks & Regards,
      VN

      Edited by: user3266490 on Apr 29, 2012 9:08 PM
        • 1. Re: IOT overflow ORA-25191:
          sb92075
          user3266490 wrote:
          Hi,

          Db :10.2
          Os :Aix

          While execute select statement ,we getting error.

          SQL> select * from gei.SYS_IOT_OVER_246638;
          select * from gei.SYS_IOT_OVER_246638
          *
          ERROR at line 1:
          ORA-25191: cannot reference overflow table of an index-organized table

          SQL> select table_name from dba_Tables where table_name='SYS_IOT_OVER_246638';

          TABLE_NAME
          --------------
          SYS_IOT_OVER_2
          46638


          SQL> select segment_type,segment_name from dba_Segments where segment_name='SYS_IOT_OVER_246638';

          SEGMENT_TYPE
          ------------------------------------------------------
          SEGMENT_NAME
          --------------------------------------------------------------------------------
          TABLE
          SYS_IOT_OVER_246638


          SQL> select index_name,index_type,table_name,table_type from dba_indexes where table_name='SYS_IOT_OVER_246638';

          no rows selected


          We want to move that table to another tablespace.

          Please anyone helpme out

          Thanks & Regards,
          VN

          Edited by: user3266490 on Apr 29, 2012 9:08 PM
          what OBJECT_TYPE is 'SYS_IOT_OVER_246638'?
          • 2. Re: IOT overflow ORA-25191:
            user3266490
            Hi,

            Thanks for your reply.

            SQL> select object_Type from dba_objects where object_name='SYS_IOT_OVER_246638';


            OBJECT_TYPE
            --------------
            TABLE


            Thanks & Regards,
            VN
            • 3. Re: IOT overflow ORA-25191:
              sb92075
              user3266490 wrote:
              Hi,

              Thanks for your reply.

              SQL> select object_Type from dba_objects where object_name='SYS_IOT_OVER_246638';


              OBJECT_TYPE
              --------------
              TABLE


              Thanks & Regards,
              VN
              which is why you can not find it when querying DBA_INDEXES!
              • 4. Re: IOT overflow ORA-25191:
                user3266490
                Hi,

                Thanks for your reply.

                SQL> select index_name from dba_indexes where index_name='SYS_IOT_OVER_246638';

                no rows selected


                I don't know the reason.

                Thanks & Regards,
                VN
                • 5. Re: IOT overflow ORA-25191:
                  sb92075
                  user3266490 wrote:
                  Hi,

                  Thanks for your reply.

                  SQL> select index_name from dba_indexes where index_name='SYS_IOT_OVER_246638';

                  no rows selected


                  I don't know the reason.

                  Thanks & Regards,
                  VN
                  TABLE object is different from INDEX object

                  TABLE objects can be found in DBA_TABLES; not DBA_INDEXES
                  • 6. Re: IOT overflow ORA-25191:
                    user3266490
                    Hi,

                    Thanks for your reply

                    How to find the table is iot-overflow or iot-top?

                    How to move iot-overflow and iot-top table in 10g?

                    Thanks & Regards,
                    VN
                    • 7. Re: IOT overflow ORA-25191:
                      sb92075
                      user3266490 wrote:
                      Hi,

                      Thanks for your reply

                      How to find the table is iot-overflow or iot-top?
                      query DBA_TABLES

                      >
                      How to move iot-overflow and iot-top table in 10g?
                      http://www.lmgtfy.com/?q=oracle+MOVE+IOT