Oracle_ operation of a DBF file on a mobile database

There are not enough disks under the DBF path for the Oracle database to move the DBF file under the original path to another disk path, with four steps.

1. Offline the entire table space.

2.copy the DBF file under the original path to the new path below.

3. Change the path of the DBF file.

4. Put the table space online.

The SQL for querying the tablespace and moving the DBF file is as follows:

Select Ts.rn,
Ts. Tablespace_name,
Df. bytes/1024/1024/1024,
To_number (REGEXP_SUBSTR (ts. Tablespace_name, ' [0-9]{6} ')] DATE_TB,
Df. file_name,
Replace (DF. file_name, '/oradata/db/mos/', ') Os_filename,
' Alter Tablespace ' | | Ts. tablespace_name| | ' offline; ' Sql_offline_ts,
' Alter Tablespace ' | | Ts. tablespace_name| | ' online; ' Sql_online_ts,
' CP ' | | Df. file_name| | '/oradata/db3/p/. ' Os_copy,
' Alter Tablespace ' | | Ts. tablespace_name| | ' rename datafile ' | | Df. file_name| | ' to '/oradata/db3/pm/' | | Replace (DF. file_name, '/oradata/db/mos/', ') | | '; ' Sql_rename_dbf

From (select RowNum RN,
Ts. Tablespace_name from
User_tablespaces TS
where To_number (Regexp_substr (ts. Tablespace_name, ' [0-9]{6} ')] >= 160000
and To_number (REGEXP_SUBSTR (ts. Tablespace_name, ' [0-9]{6} ')] <= 160500
) TS
, Dba_data_files DF

where
Ts. Tablespace_name = df. Tablespace_name
and Lower (DF. file_name) like '%/oradata/db/mos/p_db_dat% ';

A specific procedure is as follows: (a total of three table space data files were moved, the intermediate copy operation is performed in the Linux window, not in this operation record.) ）

Log in to the database with the SYS user first.

sql> alter tablespace pm4h_db_dat_w_150803 offline;

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150803 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150803_mv0_ 0000.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150803_mv0_0000.dbf ';

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150803 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150803_mv0_ 0001.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150803_mv0_0001.dbf ';

Tablespace altered.

Sql>

Sql>

sql> alter tablespace pm4h_db_dat_w_150803 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150803_mv0_ 0002.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150803_mv0_0002.dbf ';

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150803 online;

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150810 offline;

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150810 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150810_mv0_ 0000.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150810_mv0_0000.dbf ';

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150810 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150810_mv0_ 0001.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150810_mv0_0001.dbf ';

Tablespace altered.

Sql>

sql> alter tablespace pm4h_db_dat_w_150810 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150810_mv0_ 0002.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150810_mv0_0002.dbf ';

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150810 online;

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150817 offline;

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150817 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150817_mv0_ 0000.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150817_mv0_0000.dbf ';

Tablespace altered.

Sql> sql> sql> alter tablespace pm4h_db_dat_w_150817 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat _w_150817_mv0_0001.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150817_mv0_0001.dbf ';

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150817 rename DataFile '/oradata/db/pm/pm4h/mos5200/pm4h_db_dat_w_150817_mv0_ 0002.dbf ' to '/oradata/db3/pm/pm4h_db_dat_w_150817_mv0_0002.dbf ';

Tablespace altered.

sql> alter tablespace pm4h_db_dat_w_150817 online;

Tablespace altered.

Sql> exit

Oracle_ operation of a DBF file on a mobile database