{"id":1274,"date":"2014-12-20T15:23:14","date_gmt":"2014-12-20T12:23:14","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=1274"},"modified":"2016-03-02T19:39:37","modified_gmt":"2016-03-02T16:39:37","slug":"how-to-move-datafile-to-different-directory-in-oracle","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/how-to-move-datafile-to-different-directory-in-oracle\/","title":{"rendered":"How to Move Datafile to Different Directory in Oracle"},"content":{"rendered":"<p>You can move datafiles to different directory. You have to make offline tablespace or take mount mode database to change datafiles location. System, temporary and rollback tablespace can move only when database is mount mode. <\/p>\n<p><strong>Show Tablespaces And Datafiles With Locations<\/strong><\/p>\n<blockquote><p>SQL> select tablespace_name, file_name from dba_data_files;<\/p><\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.fatihacar.com\/blog\/resimler\/articleIcon-300x158.jpg\" alt=\"articleIcon\" width=\"300\" height=\"158\" class=\"aligncenter size-medium wp-image-1430\" \/><\/p>\n<p><strong>Change Location Of Normal Datafile<\/strong><\/p>\n<blockquote><p>SQL> alter tablespace TS_TEST offline;<br \/>\n[oracle@testdb oradata] mv \/oradata\/ts_test01.dbf to \/oradata2\/ts_test02.dbf<br \/>\nSQL> alter tablespace TS_TEST rename datafile &#8216;\/oradata\/ts_test01.dbf&#8217; to &#8216;\/oradata2\/ts_test02.dbf&#8217;;<br \/>\nSQL> alter tablespace TS_TEST online;\n<\/p><\/blockquote>\n<p>Task completed.<\/p>\n<p><strong>Change Location Of System, Temporary or Rollback Tablespace&#8217;s Datafiles<\/strong><br \/>\n<!--more--><\/p>\n<blockquote><p>SQL> shutdows immediate;<br \/>\nSQL> startup mount;<br \/>\n[oracle@testdb oradata] mv \/oradata\/system01.dbf to \/oradata2\/system02.dbf<br \/>\nSQL> alter database rename file &#8216;\/oradata\/system01.dbf&#8217; to &#8216;\/oradata2\/system02.dbf&#8217;;<br \/>\nSQL> alter database open;<\/p><\/blockquote>\n<p>Task completed.<\/p>\n<p>You can move <strong>redo log<\/strong> files like system tablespace. Firstly, you have to take mount mode database then you can move with &#8220;alter database rename file&#8221; command. <\/p>\n<p>Second method for redo logs, you can drop redo log when redo log is inactive, after you can create redo log on new location when database is online.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>You can move datafiles to different directory. You have to make offline tablespace or take mount mode database to change datafiles location. System, temporary and rollback tablespace can move only when database is mount mode. Show Tablespaces And Datafiles With Locations SQL> select tablespace_name, file_name from dba_data_files; Change Location Of Normal Datafile SQL> alter tablespace&#8230;<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,20],"tags":[14,32,49],"class_list":["post-1274","post","type-post","status-publish","format-standard","hentry","category-administration","category-oracle-backup-and-recovery","tag-database-administration","tag-oracle-administration","tag-oracle-backup-and-restore"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-ky","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1274","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=1274"}],"version-history":[{"count":6,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1274\/revisions"}],"predecessor-version":[{"id":1490,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1274\/revisions\/1490"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=1274"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=1274"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=1274"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}