quick oracle tip – how to move tablespaces

here it is a quick tip „how to move oracle tablespace datafile from one file to another“. i’ve spent a while searching for this, so maybe someone will find this useful too. i needed to free up some space on my laptop disk drive by moving huge oracle data files (*.ora) to an external disk. (also thanks to dlabik for consultation).

ALTER TABLESPACE DATA02 OFFLINE;

ALTER TABLESPACE DATA02
RENAME DATAFILE 'D:\oracle\oradata\mboruvka\DATA021.ORA' TO 'G:\oracle\oradata\mboruvka\DATA021.ORA';

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER TABLESPACE DATA02 ONLINE;

note: with this procedure it is not necessary to shutdown db.

Reklamy

3 thoughts on “quick oracle tip – how to move tablespaces

  1. Dlabik 04/02/2006 / 09:19

    You’re getting better 🙂Just wondering – why do you need the recovery step??? It shouldn’t be necessary at all. At the same time you’re missing the last step – backup a controlfile 😉

    To se mi líbí

  2. Dlabik 04/02/2006 / 09:21

    I see, the backup control file is there but it should be at the end of the procedure. It whould reflect the offline status of the file beeing moved otherwise…

    To se mi líbí

  3. Milan Boruvka 04/02/2006 / 09:27

    regarding the <>recovery<> command: I had this problem: < HREF="http://www.cryer.co.uk/brian/oracle/ORA01113.htm" REL="nofollow">ORA-01113: file n needs media recovery<>.

    To se mi líbí

Zanechat Odpověď

Vyplňte detaily níže nebo klikněte na ikonu pro přihlášení:

WordPress.com Logo

Komentujete pomocí vašeho WordPress.com účtu. Odhlásit / Změnit )

Twitter picture

Komentujete pomocí vašeho Twitter účtu. Odhlásit / Změnit )

Facebook photo

Komentujete pomocí vašeho Facebook účtu. Odhlásit / Změnit )

Google+ photo

Komentujete pomocí vašeho Google+ účtu. Odhlásit / Změnit )

Připojování k %s