Re: Help re-configuring database?

>From: ronnie_at_space.mit.edu (Ron Schnell)
>The person who initially installed our Oracle database created our
>high-activity tables in the SYSTEM tablespace. I am now learning
>that this might eventually cause problems.
>
>What is the proper procedure for moving these tables? I realize that
>it won't be easy, and I will probably need to take the database offline
>for a little while.

Hi,

The easiest way is to do the following:

1. use the exp command to export the tables you want to move.

         eg. exp userid=scott/tiger tables=(tab1,tab2...)

         read the manual or use "exp -help" for more info on exp synatax.

      2) use the import command with an indexfile parameter to generate
         a script to recreate the tables.

         eg.    imp userid=scott/tiger full=y rows=n indexfile=build.sql

      3) edit the generated build.sql file to modify the storage parameters,
         to change the tablespace for each table.

      4) drop the original tables

      5) run the modified build.sql script to recreate the tables in the
         new tablespaces.

      6) use the import command with ignore=y to import the data back into
         the repositioned tables.

         eg.    imp scott/tiger full=y rows=y ignore=y commit=y

         again, check the manual for information about imp.

HTH, Dave.

--
Remove the no-spam bit from my email address to reply.