4 Replies Latest reply: Mar 27, 2007 8:04 AM by 523511 RSS

    ORA-22868: table with LOBs contains segments in different tablespaces

    sanju2
      Hi All,

      I have a tablespace "AQTBLSPC" consisting of AQ objects like Queue Tables and Indexes and LOBs (which is part of queue table).
      As I was unable to resize the tablespace beyond certain size, I wanted to drop the tablespace and re-create it without loosing objects there in.
      I wanted to move all its objects to a different tablespace. I found no problem with Tables and Indexes but LOBs were real problem.
      I moved only Queue Tables and Indexes to a tablespace "AQTMP".
      I thought LOB index and segments will be automatically created when the objects are moved back in.
      After moving Queue Tables and Indexes from "AQTBLSPC" I tried to drop the tablespace but got the following error -

      ERROR at line 1:
      ORA-22868: table with LOBs contains segments in different tablespaces

      Need your suggestion as how can I overcome this problem.

      Thanks & regards,
        • 1. Re: ORA-22868: table with LOBs contains segments in different tablespaces
          MaximDemenko
          AFAIK Oracle don't support move of aq tables. If it'll be done, it can result in corrupted objects (Bug 2286290). Check the Note 304522.1 how it can be done with Oracle supplied package.
          However, if you already moved your aq tables, it is probably the case for Oracle Support.

          Best regards

          Maxim
          • 2. Re: ORA-22868: table with LOBs contains segments in different tablespaces
            sanju2
            That means I would never be able to resize the tablespace as per my need that contains AQ objects.

            The problem is the current size of the tablespace is in GB where as it has got very few objects and the Queue tables contain no record. I am able t resize it only to a certain limit. I know the tablespace is fragmented and there is no way that it can be defragmented. The only way out is to move objects out to some other tablespace and drop and re-create the original one. And move the objects back.

            I had not dropped the tablespace. I moved the objects back and its working. BUT the resizing problem remains...

            Thanks & regards,
            • 3. Re: ORA-22868: table with LOBs contains segments in different tablespaces
              sanju2
              somebody please help me move all the tablespace objects (that includes AQ objects like Queue tables, Queues and Indexes, and off course LOBs) from one tablespace to another.

              Thanks & regards.
              • 4. Re: ORA-22868: table with LOBs contains segments in different tablespaces
                523511
                Hi,
                Metalink note : 369526.1

                CauseError Details
                ~~~~~~~~~~~~~
                Error: ORA-22868
                Text: table with LOBs contains segments in different tablespaces
                Cause: An attempt was made to drop a tablespace which contains the segment(s) for the LOB columns
                of a table but does not contain the table segment.


                There are still some data in the tablespace, which is used by other tables SolutionTo implement the solution, please execute the following steps:

                1. Identify the table and the tablespace name where the segments belonging to

                1.1 - select to identify the segments :
                ...
                select segment_name, segment_type from dba_segments
                where tablespace_name ='APPLSYSD';
                ...
                1.2 - select to identify the table_name belonging to the segment :
                ...
                select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs
                where SEGMENT_NAME like '<name of segment>';
                ...
                1.3 select to identify the table_name belonging to the segment index :
                ...
                select OWNER, TABLE_NAME, SEGMENT_NAME from dba_lobs
                where INDEX_NAME like '<name of index>;
                ...
                1.4 - select to identify the tablespace for the table :
                ...
                select OWNER, TABLESPACE_NAME from dba_tables
                where TABLE_NAME like '<name of the table identified above>;
                ...

                2. Move the segments into the table's tablespace :

                You can issue a command like this one :
                ...
                ALTER TABLE foo MOVE LOB(lobcol) STORE AS lobsegment
                (TABLESPACE new_tbsp STORAGE (new_storage));
                ...
                This command will move the lob column to a new tablespace as well as the lob's storage attributes.

                3. Verify that the tablespace now is empty and drop it.


                Cheers
                Venkat...