Re: Moving System Tablespace

>
>I am trying to reorganize a database and am moving some tablespaces to
>different disks.  To do this I am bringing the tablespace offline, then
>issuing an alter tablespace rename datafile command.  Can I do this with
>the system tablespace?  My question is if I take the system tablespace
>offline and rename it, how will the data dictionary get updated?

I don't have an answer to your question but if the answer is NO and you are using UNIX I have a possible alternative. Symbolic links.

If you don't use UNIX don't bother reading any further.

If your system tablespace has a pathname something like /this_dir/system.dbf and you want in /that_dir/system.dbf:

- Bring your database down.
- create the directory /that_dir
- move system.dbf from /this_dir to /that_dir
- delete /this_dir
- ln -s /that_dir /this_dir
- bring your database back up and everything should be ok

The pathname for your system tablespace is still /this_dir/system.dbf but the symbolic link is followed by oracle and the tablespace is found under
/that_dir/system.dbf

In my database all of the pathnames for my datafiles follow symbolic links to directories where the files actually reside.

This part get boring.

I have come up with a directory structure that make it real easy to locate all of my database files.

I have a "home" directory all ALL of my databases called "/dbs". Underneath that I have a "home" directory for each of my databases. These directory names are the same as the database names. Under these database home directories I have symbolic links to the directories where the data files reside.

eg :

/dbs/db1/control_1 - link to /dir1
/dbs/db1/control_2 - link to /dir2
/dbs/db1/control_3 - link to /dir3
/dbs/db1/redo_logs - link to /dir4
/dbs/db1/temp_tables - link to /dir5
/dbs/db1/datafiles/tablespace_1 - link to /dir6
/dbs/db1/datafiles/tablespace_2 - link to /dir7
...
/dbs/db1/control_1 - link to /dir1
/dbs/db1/control_2 - link to /dir2
/dbs/db1/control_3 - link to /dir3
/dbs/db1/redo_logs - link to /dir4
/dbs/db1/temp_tables - link to /dir5
/dbs/db1/datafiles/tablespace_1 - link to /dir6
/dbs/db1/datafiles/tablespace_2 - link to /dir7
...

So in my init.ora my control files for database "db1" are said to be :

  /dbs/db1/control_1/cntdb1.dbf
  /dbs/db1/control_2/cntdb1.dbf
  /dbs/db1/control_3/cntdb1.dbf

while the control files actually are:

  /dir1/cntdb1.dbf
  /dir2/cntdb1.dbf
  /dir3/cntdb1.dbf

The datafiles as defined in the database for "db1" as:   /dbs/db1/datafiles/tablespace_1/tablespace_1.dbf   /dbs/db1/datafiles/tablespace_2/tablespace_2.dbf   ...

while they are found under:
  /dir6/tablespace_1.dbf
  /dir7/tablespace_2.dbf
  ...

The same idea goes for the redo_logs and temp_tables.

I hope this all makes sense. I like it. Contact me directly if you need/want any clarification.

--
Later...                         e-mail: wolkowsb_at_cuug.ab.ca
Brian R. Wolkowski                voice: +1 403 242-7886 (home)
                                         +1 403 263-2600 (work)