3 Replies Latest reply: May 17, 2012 2:38 PM by Mark Malakanov (user11181920) RSS

    Relocating a large audit table to a new tablespace

    720882
      Hi
      I have only been a DBA for a couple of years and wondered if I could ask some advice from you all?

      I have an Oralce 10g database audit table for an application that is quite large, contains several millions rows containing clobs. This table currently belongs to the same tablespace as the main schema for the application where all the datafiles reside on the same physical disk.

      I am considering creating a new dedicated audit tablespace, with the view that should may improve performance with relocating the audit table to a seperate dedicated tablespace. The new tablespace for the audit data may have to be located on the same physical disk, do you feel this would still be a worth while excerise as the new tablespace will be located on the same physical disk so there is no reduction in disk IO to the main tablespace?

      If I did relocate the table to the new tablespace, its a large table containing clobs I suspect this will take some time to to relocate/write it to the new tablespace datafiles. During this time if I was unable to take the live system off line I presume any audit table writes would fail and the application may hang.

      I have a test enviorment I intend to test this on and I am aware that after creating relocating the tablespace, I will need to revalidate index's.
        • 1. Re: Relocating a large audit table to a new tablespace
          713555
          Few things.

          1. Can move it online with DBMS_REDEFINITION
          http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_redefi.htm
          and also see MOS note 149564.1

          2. If you have IO due to a problem with the disks it wont solve anything, if you have contention due to chaining or row migration, the table could do with with re-organising and this method can fix that. Prove that first for yourself though with plans pre and post move for any queries hitting that table. A Re-org can also save space as the new table should only have blocks required at the time of the move.

          3. Moving it to its own tablespace will allow you run commands on the tablespace level that you could do otherwise. RMAN or TTS for example.

          Moving it to its own TS can be good, can be bad, depends on the situation. It doesnt look to be bad here.
          • 2. Re: Relocating a large audit table to a new tablespace
            LKBrwn_DBA
            1) Consider partitioning the table
            2) Move the table to a new tablespace
            3) Store the CLOB's in another new tablespace(s).

            For example, I moved a 600+G table as follows:

            1) Create new tablespaces: one for table and one for each partition's CLOB's.
            2) Create partitioned table "AUDIT_NEW"
            3) Use datapump (parallel=4) to export old and import into new.
            4) Synchronize latest partition with latest data from old table
            5) Rename old table to "AUDIT_OLD"
            6) Rename new table to "AUDIT"
            7) Create the local indexes

            Or if you prefer DBMS_REDEFINITION, read this article.
            ;)
            • 3. Re: Relocating a large audit table to a new tablespace
              Mark Malakanov (user11181920)
              The new tablespace for the audit data may have to be located on the same physical disk, do you feel this would still be a worth while excerise as the new tablespace will be located on the same physical disk so there is no reduction in disk IO to the main tablespace?
              Having this TS on same HDD may even slow I/O down. Because most likely the new file will be located on cilinders that are more distant from others tables location. This may cause HHD head to move here and there more then previously. I said may because you cannot predict how Oracle will lay table extents in datafile(s) and how OS will lay out file block on HDD. Theoretically it will slow I/O down. A little bit.
              If you want to separate this table to improve I/O, consider to place it on another HDD or SSD.