oracle数据库的dbf路径下面磁盘不足,需要把原始路径下面的dbf文件移动到另外一个磁盘路径下, 具体的操作有四步。
1.把整个表空间offline.
2.copy原始路径下的dbf文件到新的路径下面。
3.改变dbf文件的路径。
4.把表空间online。
查询表空间和移动dbf文件的sql如下:
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%';
一次具体的操作过程如下:(一共移动了三个表空间的数据文件,中间的复制操作在linux窗口进行,不在本次操作记录里。)
先用sys用户登录数据库。
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