Re: Oracle 7:moving tablespaces

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> schreef in berichtnieuws E2F6A70FE45242488C865C3BC1245DA703BA7D24_at_lnewton.leeds.lfs.co.uk...
| Try this :
|
| Optional : shutdown and cold backup the whole plot.
|
| create tablespace(s) on the new device(s).
|
| export schemas having objects in the old tablespaces.
|
| alter user <whatever> default tablespace <new_tablespace>
| quota <something> on <new_tablespace>
| quota 0 on <old_tablespace>
| quota 0 on SYSTEM;
|
| Import the user's objects into the new tablespace.
|
|
| Enjoy.
|
| Cheers,
| Norman.
|
| -------------------------------------
| Norman Dunbar
| Database/Unix administrator
| Lynx Financial Systems Ltd.
| mailto:Norman.Dunbar_at_LFS.co.uk
| Tel: 0113 289 6265
| Fax: 0113 289 3146
| URL: http://www.Lynx-FS.com
| -------------------------------------

Sorry Norman, but isn't this a little overkill? Just move the datafiles will do the job too, isn't it?

1. shutdown immediate
2. backup the database
3. move the files on the operating system level to the new directories/names (remember: Unix mv is a destructive command: if the destination file exits it is overwritten without warning).
4. startup mount
5. for every file: alter database rename 'old_filename' to 'new_filename';
6. alter database open;
7. if open fails you have made an error. The file that failed to open will be reported. Query v$datafile (shows info from the controlfile) to see the filenames that the database currently expects.

To facilitate the renames: generate a sql script with sql where initially the new filename equals the old filename. Login in sqlplus and:

set feedback off verify off head off linesize 500 trimspool on spool rename_script.tmp
prompt spool rename_script
select 'alter database rename '||file_name ||' to '||file_name||';' prompt spool off
spool off
exit

Edit rename_script.tmp to reflect the new file names and run it in svrmgrl Received on Fri May 30 2003 - 05:45:59 CDT