4 Replies Latest reply on Mar 30, 2016 7:37 AM by Bashar.

    Reclaim space from APPS_TS_TX_DATA in R12.2.4

    Ramaraju

      Hi All,

      We dropped unwanted data and tables and would like to reduce the tablespace APPS_TS_TX_DATA size.

      Is there are any options to quickly free up space at OS level that does not involve significant PROD downtime.

      We would like to release the free space in a datafile in APPS_TS_TX_DATA to the OS level.

       

      While reorganizing the APPS_TS_TX_DATA tablespace using OEM 12c, getting following error. Please help us.

       

      APPS_TS_TX_DATATABLESPACEERRORSystemTablespace APPS_TS_TX_DATA contains the unsupported segment APPS.SYS_LOB0000137260C00004$$ of type LOB SUBPARTITION. Reorganization of this type of segment is not supported. Reorganization of this tablespace is not supported.
      APPS_TS_TX_DATATABLESPACEERRORSystemTablespace APPS_TS_TX_DATA contains the unsupported segment APPS.SYS_LOB0000137319C00004$$ of type LOB SUBPARTITION. Reorganization of this type of segment is not supported. Reorganization of this tablespace is not supported.
      APPS_TS_TX_DATATABLESPACEERRORSystemTablespace APPS_TS_TX_DATA contains the unsupported segment APPS.SYS_LOB0000524029C00004$$ of type LOB SUBPARTITION. Reorganization of this type of segment is not supported. Reorganization of this tablespace is not supported.
      APPS_TS_TX_DATATABLESPACEERRORSystemTablespace APPS_TS_TX_DATA contains the unsupported segment APPS.SYS_LOB0000757779C00004$$ of type LOB SUBPARTITION. Reorganization of this type of segment is not supported. Reorganization of this tablespace is not supported.
      APPS_TS_TX_DATATABLESPACEERRORSystemTablespace APPS_TS_TX_DATA contains the unsupported segment FPA.SYS_LOB0000137437C00004$$ of type LOB SUBPARTITION. Reorganization of this type of segment is not supported. Reorganization of this tablespace is not supported.

       

      Thanks,

      Ramaraju

        • 1. Re: Reclaim space from APPS_TS_TX_DATA in R12.2.4
          VishnuVinnakota

          You shouldn't drop any seeded/Oracle Standard objects of any EBS Product whether you use them or not.

           

          To reclaim space, start at finding the largest objects 1st. There are several Purge Concurrent Programs which you can use as well. If there are any other large tables, then take a call wisely based on the business.

           

          Looks like the error it is showing is about FND_LOBS. Check from DBA_LOBS with that LOB name given whats the table name. You can purge FND_LOBS attachments.

          • 2. Re: Reclaim space from APPS_TS_TX_DATA in R12.2.4
            VishnuVinnakota

            Just to add: Using advanced compression is one of the best options. Though you will need to buy additional license, you can achieve 40-70% compression and also it will control the future growth rate by almost the same %.

            • 3. Re: Reclaim space from APPS_TS_TX_DATA in R12.2.4
              Ramaraju

              Hi Vishnu,

              Following is the dba_lobs table information. How to purge fnd_lobs attachments? Is this correct way to purge the fnd_lobs attachments?

               

              OWNERTABLE_NAMECOLUMN_NAMESEGMENT_NAMETABLESPACE_NAMEINDEX_NAMECHUNKPCTVERSIONRETENTIONFREEPOOLSCACHELOGGINGENCRYPTCOMPRESSIONDEDUPLICATIONIN_ROWFORMATPARTITIONEDSECUREFILESEGMENT_CREATEDRETENTION_TYPERETENTION_VALUE
              APPSAW$ODPCODEAWLOBSYS_LOB0000137260C00004$$SYSTEMSYS_IL0000137260C00004$$819201YESNONENONENONENONENONOT APPLICABLEYESNON/ANO
              APPSAW$BSC_AWAWLOBSYS_LOB0000137319C00004$$SYSTEMSYS_IL0000137319C00004$$819201YESNONENONENONENONENONOT APPLICABLEYESNON/ANO
              FPAAW$FPAPJPAWLOBSYS_LOB0000137437C00004$$APPS_TS_TX_DATASYS_IL0000137437C00004$$8192101YESNONENONONONONOT APPLICABLEYESYESN/ADEFAULT
              APPSAW$XWDEVKIT_BACKUPAWLOBSYS_LOB0000524029C00004$$APPS_TS_TX_DATASYS_IL0000524029C00004$$8192101YESNONENONONONONOT APPLICABLEYESYESN/ADEFAULT
              APPSAW$XWDEVKITAWLOBSYS_LOB0000757779C00004$$APPS_TS_TX_DATASYS_IL0000757779C00004$$8192101YESNONENONONONONOT APPLICABLEYESYESN/ADEFAULT

               

              Thanks,

              Ramaraju

              • 4. Re: Reclaim space from APPS_TS_TX_DATA in R12.2.4
                Bashar.

                Hi,

                 

                You cannot simply purge data from fnd_lobs table. Please have a look at this blog post for more information on the issue:

                 

                https://basharoracle.wordpress.com/2016/02/07/diagnosing-fnd_lobs-space-issues/

                 

                Regards,

                Bashar