3 Replies Latest reply: Aug 13, 2012 9:05 PM by Mark Malakanov (user11181920) RSS

    How to move/re-org objects of STREAMS schema

    868750
      Dear All,

      We are having replication environment with STREAMS in place from ODBPRD to HDBPRD.Our Oracle eng is 10gR2 and the tablespace is having 80GB of space allocated of which data comprises of 10GB.

      When I'm trying to resize the datafile of STREAMS tablespace, getting the error, cannot resize beyondthe value as objects exist. When we checked, there's a lot of fragmentation in the tablespace.

      Below are the objects exist in streams schema of which we are having some IOT as well:
      OBJECT_TYPE
      -------------------
      SEQUENCE
      QUEUE
      RULE
      PROCEDURE
      DATABASE LINK
      LOB
      RULE SET
      PACKAGE
      PACKAGE BODY
      FUNCTION
      TABLE
      INDEX
      VIEW
      EVALUATION CONTEXT
      TYPE
      JOB


      Can somebody please tell me how to re-org on this tablespace without DOWNTIME?

      Regards,
      Kumar
        • 1. Re: How to move/re-org objects of STREAMS schema
          mk_dba
          Error is very clear. You cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the end of the datafile.
          Also, the tables may be having extents allocated with empty space.
          • 2. Re: How to move/re-org objects of STREAMS schema
            Iordan Iotzov
            The fact that you cannot shrink the size of the file significantly means that the tablespace was most likely almost full at some point in the past. If it was almost full before, it may well get full again. The STREAMS tablespace stores spilled application data, among other things, so the tables grow and shrink depending on the replication load.
            If I were you, I would reevaluate whether reclaiming the disk space is worthwhile.

            Iordan Iotzov
            http://iiotzov.wordpress.com/
            • 3. Re: How to move/re-org objects of STREAMS schema
              Mark Malakanov (user11181920)
              there are commands for moving tables, indexes and LOB segments.
              ALTER TABLE <table> MOVE TABLESPACE ...
              ALTER TABLE <index> REBUILD [ONLINE] TABLESPACE ...
              ALTER TABLE <table> MOVE LOB(<lob>) STORE AS (TABLESPACE ...)

              Heap tables and LOBs can be moved offline only. Means Streams should be most likely stopped.
              IOTs and indexes can be moved online.

              You can try to move first segments that have largest end block # to same TS.
              Most likely they will be moved to beginning of TS.