13 Replies Latest reply: Sep 24, 2013 1:42 AM by dmx78 RSS

    Lob index in dba_segments but not in dba_lobs

    566539
      Hi,

      I see 2 LOB indexes in a tablespace which I want to drop. But I am unable to find the same in DBA_LOBS table. The details on DBA_LOBS table will help me generate the script for moving the LOB to another tablespace.
      ALTER TABLE FILTERS3 MOVE LOB(FILTER_OBJECT) STORE AS ( TABLESPACE LOBTS );
      Below are the queries, I fired to check the same.
      SELECT SEGMENT_NAME,SEGMENT_TYPE,OWNER,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME ='GART_TEMP' AND SEGMENT_TYPE LIKE 'LOB%' ORDER BY 1;
      
      SEGMENT_NAME                                                                      SEGMENT_TYPE       OWNER                          TABLESPACE_NAME                
      --------------------------------------------------------------------------------- ------------------ ------------------------------ ------------------------------ 
      SYS_IL0000113037C00005$$                                                          LOBINDEX           VISUAL                         GART_TEMP                      
      SYS_IL0000113037C00009$$                                                          LOBINDEX           VISUAL                         GART_TEMP                      
      SYS_LOB0000113037C00005$$                                                         LOBSEGMENT         VISUAL                         GART_TEMP                      
      SYS_LOB0000113037C00009$$                                                         LOBSEGMENT         VISUAL                         GART_TEMP                      
      
      SELECT OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME,INDEX_NAME FROM DBA_LOBS WHERE SEGMENT_NAME IN ('SYS_IL0000113037C00005$$','SYS_IL0000113037C00009$$');
      
      no rows selected.
      Are the LOB indexes valid, if yes, How can I move the LOBS to another tablespace?

      Regards,
      Sam
        • 1. Re: Lob index in dba_segments but not in dba_lobs
          813964
          please try as below , i hope it will work . .

          ALTER TABLE VISUAL.FILTERS3 MOVE LOB(FILTER_OBJECT) STORE AS ( TABLESPACE LOBTS );

          Regards ,
          TSBR
          • 2. Re: Lob index in dba_segments but not in dba_lobs
            566539
            TSBR,

            But the LOB's ('SYS_IL0000113037C00005$$','SYS_IL0000113037C00009$$') are not part of FILTERS3 table. The query I shared was only an example.

            My question is, How to find the table and column details which held these LOB's.

            Sam.
            • 3. Re: Lob index in dba_segments but not in dba_lobs
              P.Forstmann
              A LOB is made up of a LOB data segment storing LOB data and a LOB index segment used to access LOB data.
              SYS_LOBxxx is the LOB data segment and SYS_ILxxx is the LOB index segment. As a consequence it is meaningless to try to drop only the LOB index segment (and I don't think it is possible to do so): you can only drop the LOB column to drop data and index segment or maybe try to move the column to be stored in row only if LOB size allows.

              Try instead:
              SQL> select owner, table_name, column_name, segment_name, index_name
                2  from dba_lobs
                3  where index_name in ('SYS_IL0000113037C00005$$','SYS_IL0000113037C00009$$');
              Edited by: P. Forstmann on 1 mars 2012 08:29
              • 4. Re: Lob index in dba_segments but not in dba_lobs
                Jonathan Lewis
                P. Forstmann wrote:

                Try instead:
                SQL> select owner, table_name, column_name, segment_name, index_name
                2  from dba_lobs
                3  where index_name in ('SYS_IL0000113037C00005$$','SYS_IL0000113037C00009$$');
                And just as a little extra note:
                SYS_IL0000113037C00005$$                                                          LOBINDEX           VISUAL                         GART_TEMP                      
                SYS_IL0000113037C00009$$                                                          LOBINDEX           VISUAL                         GART_TEMP                      
                SYS_LOB0000113037C00005$$                                                         LOBSEGMENT         VISUAL                         GART_TEMP                      
                SYS_LOB0000113037C00009$$
                SYS_IL0000113037C00005$$ is the index segment for SYS_LOB0000113037C00005$$, and SYS_IL0000113037C00009$$ is the index segment for SYS_LOB0000113037C00009$$, and they correspond to LOBs in object 113037, (internal) columns 5 and 9 respectively.

                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                Author: <b><em>Oracle Core</em></b>

                Edited by: Jonathan Lewis on Mar 1, 2012 9:44 AM
                Corrected object id
                • 5. Re: Lob index in dba_segments but not in dba_lobs
                  566539
                  Your query returned no rows.

                  And yes, you are right. I want to move the column to be stored in new tablespace.

                  Basically I want to drop the current tablespace. Before doing that I want to make sure all the segments under it are moved to new tablespace.

                  My question is, How can I generate a script like
                  ALTER TABLE FILTERS3 MOVE LOB(FILTER_OBJECT) STORE AS ( TABLESPACE LOBTS );
                  for the LOB's I found from DBA_SEGMENTS.

                  If they are existing as proper LOB's then why am I not able to find the details in DBA_LOBS table?

                  Sam
                  • 6. Re: Lob index in dba_segments but not in dba_lobs
                    566539
                    Hi
                    select * from dba_objects where object_id=113937;
                    
                    no rows selected.
                    Did I get it right?


                    Sam.
                    • 7. Re: Lob index in dba_segments but not in dba_lobs
                      Jonathan Lewis
                      satish_034 wrote:
                      select * from dba_objects where object_id=113937;
                      
                      no rows selected.
                      Did I get it right?
                      You did, I didn't.
                      I aimed for the 0 key and hit the 9 when typing in the object id. (I've corrected it in the previous posting).

                      Which version of Oracle, and is the tablespace a temporary tablespace.
                      Working things the other way round - does dba_lobs report ANYTHING in that tablespace ?


                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      Author: <b><em>Oracle Core</em></b>
                      • 8. Re: Lob index in dba_segments but not in dba_lobs
                        566539
                        Hi
                        select * from dba_objects where object_id=113037;
                         
                        no rows selected.
                        Does the column really exist?

                        Sam.
                        • 9. Re: Lob index in dba_segments but not in dba_lobs
                          Jonathan Lewis
                          satish_034 wrote:
                          select * from dba_objects where object_id=113037;
                          
                          no rows selected.
                          Does the column really exist?
                          I also asked the questions:
                          Which version of Oracle, and is the tablespace a temporary tablespace.
                          Working things the other way round - does dba_lobs report ANYTHING in that tablespace ?
                          We think we know how things should work - we can see an example where they do not appear to be working as we expect.
                          I don't have the database in front of me so I can only ask questions that you have to answer (assuming you want to solve the puzzle).
                          I might hope that some of my questionmight prompt you to think of other questions you could put to the database.

                          At this point I'm trying to find clues about where the owning object might be and whether we have an anomaly due to object movement, the recyclebin, or global temporary tables. If you don't answer the questions asked I can't make any further comment.

                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          Author: <b><em>Oracle Core</em></b>
                          • 10. Re: Lob index in dba_segments but not in dba_lobs
                            920840
                            Sorry Jon, I over read it.

                            >
                            Which version of Oracle, and is the tablespace a temporary tablespace.
                            >
                            11.2.0.1 and the tablespace is not a temporary tablespace.
                            >
                            Working things the other way round - does dba_lobs report ANYTHING in that tablespace ?
                            >
                            Yup, I have found many entries in DBA_LOBS which I have moved to new tablespace. But only these 2 are missing.
                            • 11. Re: Lob index in dba_segments but not in dba_lobs
                              566539
                              Replied with my support ID. But the answers are correct.

                              Sam.
                              • 12. Re: Lob index in dba_segments but not in dba_lobs
                                566539
                                Ultimately I took the pain staking job of checking with developers on each of the LOB column and decided to go ahead and drop the tablespace "GART_TEMP".
                                • 13. Re: Lob index in dba_segments but not in dba_lobs
                                  dmx78

                                  SELECT object_name,original_name,operation,type,dropscn,droptime FROM dba_recyclebin;