moving data files online

September 18, 2013 — Leave a comment

oracle 12c introduced a nice feature: move data files online:

SQL*Plus: Release Production on Fri Jun 28 16:48:29 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create tablespace tbs1 datafile '/oradata/dbs300/dbf/tbs1_01.dbf' size 10m;

Tablespace created.

SQL> alter database move datafile '/oradata/dbs300/dbf/tbs1_01.dbf' to '/oradata/dbs300/dbf/tbs1_02.dbf';

Database altered.


the trace file generated gives some more details about the move operation:

Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf
The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created
Blocks copied for file /oradata/dbs300/dbf/tbs1_02.dbf
Move operation committed for file /oradata/dbs300/dbf/tbs1_02.dbf
Move operation completed for file /oradata/dbs300/dbf/tbs1_02.dbf
Moving datafile /oradata/dbs300/dbf/tbs1_01.dbf (16) to /oradata/dbs300/dbf/tbs1_02.dbf

*** 2013-06-28 17:28:26.437
The secondary file /oradata/dbs300/dbf/tbs1_02.dbf is created

The number in brackets (16) is the file id:

SQL> select FILE_ID from dba_data_files where file_name = '/oradata/dbs300/dbf/tbs1_02.dbf';



No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.