oracle – moving tablespace

Posted: April 23, 2008 | Author: | Filed under: programing, Uncategorized | Tags: , | Leave a comment

This hack is taken from friends site.

I want to move my tablespace from C: to my fileserver

ALTER TABLESPACE TEST OFFLINE;

ALTER TABLESPACE TEST RENAME DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\TEST.ORA' TO '\\192.168.1.2\DISK 1\TEST.ORA';

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

ALTER TABLESPACE TEST ONLINE;

Or you can create them right away in fileserver

CREATE SMALLFILE TABLESPACE "TEST" DATAFILE '\\192.168.1.2\disk 1\test.ora' SIZE 400M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;