Oracle Database Discussions

Hi All

I am seeking some advice on something.

I have a Oracle 10g live database with a table used for auditing purposes, this table has 5.8 million rows and contains clob data. When I last performed a full export of this table using data pump the dump file was around 40GB in size and as you can imagine took a long amount of time to complete.

This table is currently located within the main data table space, with that in mind I am looking at moving this table to a new dedicated table space onto dedicated hard disks. Theory being this will allow easier DBA administration regarding separate rman backups of this new table space and improve performance on the main data table space, writes to the audit table will now be on separate disks in a dedicated table space. I have a test system that is a complete copy of live I can use to test this.

Can anyone advise the best way to achieve the transfer of the audit table to the new table space, I intend to create a new table space of the same size on the located on separate dedicated disks.

Option 1

I Presume there are two main options, a full data export of the table and then reimport into the new table space ensuring to rebuild index's? While doing so I presume the database would need to be inaccessible to users? otherwise inserts will be done to the source table I am trying to migrate to the new table space? I think I would need to take the system offline while doing this anyway as the system would perform alot slower while the export is taking place.

Option 2

Use the alter table move table space feature? I am new to the DBA role so I have little knowledge on this function.

Please can you advise which of the above would be the best cause of action, or if deed there is any other options/solutions I am not aware of?

Many Thanks in advance.