溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“datafile的create/offline/drop/rename方法怎么使用”，在日常操作中，相信很多人在datafile的create/offline/drop/rename方法怎么使用問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”datafile的create/offline/drop/rename方法怎么使用”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
一、創(chuàng)建表空間：
create tablespace test1
datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' size 10M
autoextend on next 1M maxsize 2G
extent management local uniform size 1M
segment space management auto;
Tablespace created.
SQL>
二、更改表空間狀態(tài)：
SYS> alter tablespace test1 read only;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='TEST1';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
TEST1 1 LOCAL UNIFORM AUTO READ ONLY
SYS> alter tablespace test1 read write;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='TEST1';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
TEST1 1 LOCAL UNIFORM AUTO ONLINE
SQL>
三、表空間重命名：（在線修改表空間名）
SYS> alter tablespace test1 rename to sales;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='TEST1';
no rows selected
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='SALES';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
SALES 1 LOCAL UNIFORM AUTO ONLINE
四、查詢表空間信息：
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME;
FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- --------- ---------
/u01/app/oracle/oradata/DBdb/users01.dbf USERS 3466.25 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/undotbs01.dbf UNDOTBS1 3330 ONLINE UNDO
/u01/app/oracle/oradata/DBdb/sysaux01.dbf SYSAUX 740 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/system01.dbf SYSTEM 2800 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/example01.dbf EXAMPLE 338.75 ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test101.dbf SALES 10 ONLINE PERMANENT
6 rows selected.
SQL>
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='SALES';
FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf SALES 10 ONLINE PERMANENT
五、表空間的大小更改三種方式：
1、alter tablespace sales add datafile '/u01/app/oracle/oradata/DBdb/sales02.dbf' size 10M;
2、alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' autoextend on maxsize 2G;
3 alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' resize 50M;
六、脫機
SYS> alter tablespace sales offline;
Tablespace altered.
SQL> select tablespace_name,initial_extent/1024/1024 m,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,status from dba_tablespaces where tablespace_name='SALES';
TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS
------------------------------ ---------- ---------- --------- ------ ---------
SALES 1 LOCAL UNIFORM AUTO OFFLINE
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='SALES';
FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf SALES OFFLINE PERMANENT
SQL>
七、刪除表空間
SYS> create table sales_1 (id number)
2 tablespace sales;
Table created.
SYS> select table_name,tablespace_name from dba_tables where tablespace_name='SALES';
TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------
SALES_1 SALES
SYS> drop tablespace sales;
drop tablespace sales
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
有對象的時候刪除要用如下語句：
drop tablespace sales INCLUDING CONTENTS;
八：OMF
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> !mkdir -p /u01/app/oracle/oradata/omf
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/omf';
System altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/omf
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL> create tablespace test2;
Tablespace created.
SQL> col file_name for a804
SQL> col file_name for a80
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='TEST2';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST2 /u01/app/oracle/oradata/omf/DBDB/datafile/o1_mf_test2_f2x469nq_.dbf
SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafile
o1_mf_test2_f2x469nq_.dbf
SQL>
SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafile
o1_mf_test2_f2x469nq_.dbf
SQL>
SQL> drop tablespace test2;
Tablespace dropped.
SQL> !ls /u01/app/oracle/oradata/omf/DBDB/datafile
SQL>
刪除表空間之后再查看操作系統(tǒng)物理路徑下沒有表空間對應的數(shù)據(jù)文件了。
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/omf
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/DBdb/test2.dbf' size 5m;
Tablespace created.
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='TEST2';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEST2 /u01/app/oracle/oradata/DBdb/test2.dbf
SQL> !ls -lrt /u01/app/oracle/oradata/omf/DBDB/datafile/
total 0
SQL> !ls -lrt /u01/app/oracle/oradata/DBdb/test2.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 11 22:12 /u01/app/oracle/oradata/DBdb/test2.dbf
SQL>
--關(guān)閉OMF:
SQL> alter system set db_create_file_dest='';
System altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
九、修改數(shù)據(jù)文件可用性
可以通過執(zhí)行數(shù)據(jù)文件的在線和離線操作修改數(shù)據(jù)文件的可用性，離線的數(shù)據(jù)文件不能被數(shù)據(jù)庫所訪問，直到它恢復在線狀態(tài)之前。只讀表空間中的數(shù)據(jù)文件也可以被離線或在線，只讀表空間內(nèi)的數(shù)據(jù)文件的在線或離線不影響表空間自身的狀態(tài)，不管怎么樣，在表空間未處于讀寫狀態(tài)之前，這些文件都是不可寫的。
9.1查看之前改名為sales的表空間狀態(tài)：（執(zhí)行的是0ffline）
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='SALES';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf SALES OFFLINE OFFLINE PERMANENT
SQL>
--修改表空間名：
SQL> alter tablespace SALES rename to test1;
alter tablespace SALES rename to test1
*
ERROR at line 1:
ORA-01135: file 6 accessed for DML/query is offline
ORA-01110: data file 6: '/u01/app/oracle/oradata/DBdb/test101.dbf'
SQL> alter tablespace sales online;
Tablespace altered.
SQL> alter tablespace SALES rename to test1;
Tablespace altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
9.2 歸檔模式下的數(shù)據(jù)文件離線
SQL> alter tablespace TEST1 online;
Tablespace altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 488
Next log sequence to archive 490
Current log sequence 490
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' offline;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' online;
alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/DBdb/test101.dbf'
驗證，當offline 數(shù)據(jù)文件時再次online需要recover數(shù)據(jù)文件，而offline表空間則可以執(zhí)行執(zhí)行online，如下進行recover及online數(shù)據(jù)文件；
SQL> recover datafile 6;
Media recovery complete.
SQL>
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 OFFLINE ONLINE PERMANENT
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test101.dbf' online;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
9.2、非歸檔模式下的數(shù)據(jù)文件離線
在非歸檔模式下使用alter database ... offline for drop語句離線數(shù)據(jù)文件。offline關(guān)鍵字標記該數(shù)據(jù)文件離線，不論其是否損壞，所以可以打開數(shù)據(jù)庫；for drop關(guān)鍵字標記該數(shù)據(jù)文件隨后被刪除（只是標記，物理文件還在），該數(shù)據(jù)文件不能再次恢復到在線狀態(tài)。（實際上，在在線日志組還未發(fā)生切換之前，還是可以恢復到在線狀態(tài)的）
9.2.1先模擬在線日志還沒有切換時的offline for drop;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 488
Current log sequence 490
SQL>
--查詢當前l(fā)og日志組：
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 YES INACTIVE
3 50 1 YES INACTIVE
--執(zhí)行offline for drop操作：
SQL> alter database datafile 6 offline for drop;
Database altered.
SQL> !ls /u01/app/oracle/oradata/DBdb/test101.dbf
/u01/app/oracle/oradata/DBdb/test101.dbf
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 YES INACTIVE
3 50 1 YES INACTIVE
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
--查詢在線日志還未切換，可以進行recover；
SQL> recover datafile 6;
Media recovery complete.
SQL>
SQL> alter database datafile 6 online;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 YES INACTIVE
3 50 1 YES INACTIVE
9.2.2模擬在線日志組已發(fā)生切換且日志已被覆蓋后執(zhí)行offline for drop;
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 NO INACTIVE
3 50 1 NO INACTIVE
SQL> alter database datafile 6 offline for drop;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO ACTIVE
2 50 1 NO ACTIVE
3 50 1 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# SIZE_M MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 50 1 NO CURRENT
2 50 1 NO INACTIVE
3 50 1 NO INACTIVE
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
SQL> !ls /u01/app/oracle/oradata/DBdb/test101.dbf
/u01/app/oracle/oradata/DBdb/test101.dbf
SQL> alter database datafile 6 online;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/u01/app/oracle/oradata/DBdb/test101.dbf'
SQL> recover datafile 6;
ORA-00279: change 5518174 generated at 12/12/2017 00:06:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arc
ORA-00280: change 5518174 for thread 1 is in sequence #496
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
沒有歸檔文件可以使用進行恢復datafile 6，所以文件6不能使用。
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
SQL> alter tablespace test1 add datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' size 10m;
Tablespace altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test102.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
十、重命名（修改存儲位置）數(shù)據(jù)文件
步驟如下：
方法1
1、將包含數(shù)據(jù)文件的表空間或者只將某個數(shù)據(jù)文件離線。
2、使用操作系統(tǒng)命令修改數(shù)據(jù)文件名。
3、使用alter database ... rename file '' to '';語句改變數(shù)據(jù)庫中的數(shù)據(jù)文件名。
4、備份數(shù)據(jù)庫。
方法2：
1.將數(shù)據(jù)庫shutdown 啟動到mount下
2.使用操作系統(tǒng)命令修改數(shù)據(jù)文件名。
3.使用alter database ... rename file '' to '';語句改變數(shù)據(jù)庫中的數(shù)據(jù)文件名。
4.啟庫、備份。
方法1：
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test102.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
SQL>
SQL> alter tablespace TEST1 rename datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' to '/u01/app/oracle/oradata/DBdb/test103.dbf';
alter tablespace TEST1 rename datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' to '/u01/app/oracle/oradata/DBdb/test103.dbf'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01121: cannot rename database file 8 - file is in use or recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/DBdb/test102.dbf'
[oracle@wang 2017_12_12]$ oerr ora 01145
01145, 00000, "offline immediate disallowed unless media recovery enabled"
// *Cause: ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE
// ... OFFLINE is only allowed if database is in ARCHIVELOG mode.
// *Action:Take tablespace offline normally or shutdown abort. Reconsider your
// backup strategy. You could do this if you were archiving your logs.
[oracle@wang 2017_12_12]$
開歸檔：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>
嘗試offline:
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test102.dbf' offline;
Database altered.
SQL>
在使用rename改變數(shù)據(jù)文件名稱；
--先物理盤創(chuàng)建對應名稱；
[oracle@wang 2017_12_12]$ cd /u01/app/oracle/oradata/DBdb/
[oracle@wang DBdb]$ cp test102.dbf test1022222.dbf
[oracle@wang DBdb]$ ls -lrt test*
-rw-r----- 1 oracle oinstall 10493952 Dec 12 00:10 test101.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 12 00:32 test2.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 12 00:32 test102.dbf
-rw-r----- 1 oracle oinstall 10493952 Dec 12 00:39 test1022222.dbf
[oracle@wang DBdb]$
--正式操作：
SQL> alter database rename file '/u01/app/oracle/oradata/DBdb/test102.dbf' to '/u01/app/oracle/oradata/DBdb/test1022222.dbf';
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test1022222.dbf TEST1 RECOVER ONLINE PERMANENT
SQL>
online新數(shù)據(jù)文件；
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test1022222.dbf' online;
alter database datafile '/u01/app/oracle/oradata/DBdb/test1022222.dbf' online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle/oradata/DBdb/test1022222.dbf'
--恢復；
SQL> recover datafile 8;
Media recovery complete.
SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/DBdb/test1022222.dbf' online;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test1022222.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
方法2：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
SQL>
rename數(shù)據(jù)文件：
SQL> alter database rename file '/u01/app/oracle/oradata/DBdb/test1022222.dbf' to '/u01/app/oracle/oradata/DBdb/test3333.dbf' ;
alter database rename file '/u01/app/oracle/oradata/DBdb/test1022222.dbf' to '/u01/app/oracle/oradata/DBdb/test3333.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 8 - new file '/u01/app/oracle/oradata/DBdb/test3333.dbf' not found
ORA-01110: data file 8: '/u01/app/oracle/oradata/DBdb/test1022222.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
未發(fā)現(xiàn)/u01/app/oracle/oradata/DBdb/test3333.dbf，進行創(chuàng)建：
[oracle@wang DBdb]$ pwd
/u01/app/oracle/oradata/DBdb
[oracle@wang DBdb]$
[oracle@wang DBdb]$ cp test1022222.dbf test3333.dbf
[oracle@wang DBdb]$
再次執(zhí)行：
SQL> alter database rename file '/u01/app/oracle/oradata/DBdb/test1022222.dbf' to '/u01/app/oracle/oradata/DBdb/test3333.dbf' ;
Database altered.
SQL> alter database open;
Database altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test3333.dbf TEST1 10 ONLINE ONLINE PERMANENT
SQL>
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME;
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/users01.dbf USERS 3466.25 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/undotbs01.dbf UNDOTBS1 3330 ONLINE ONLINE UNDO
/u01/app/oracle/oradata/DBdb/sysaux01.dbf SYSAUX 740 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/system01.dbf SYSTEM 2800 SYSTEM ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/example01.dbf EXAMPLE 338.75 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test2.dbf TEST2 5 ONLINE ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test3333.dbf TEST1 10 ONLINE ONLINE PERMANENT
8 rows selected.
SQL>
十一、刪除數(shù)據(jù)文件
從表空間內(nèi)刪除數(shù)據(jù)文件：語法
alter tablespace tablespace_name { ADD { DATAFILE | TEMPFILE }
[ file_specification [, file_specification ]... ]
| DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
| SHRINK TEMPFILE { 'filename' | file_number } [KEEP size_clause]
| RENAME DATAFILE 'filename' [, 'filename' ]...
TO 'filename' [, 'filename' ]...
| { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
}
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test3333.dbf';
Tablespace altered.
成功?。。。。?！
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf';
alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TEST1 has only one file
報錯表示，表空間內(nèi)僅包含一個數(shù)據(jù)文件，該數(shù)據(jù)文件無法被刪除。
SQL> alter tablespace test1 add datafile '/u01/app/oracle/oradata/DBdb/test101bak.dbf' size 5m;
Tablespace altered.
SQL> select s.file_name,s.tablespace_name,s.bytes/1024/1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s,dba_tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACE_NAME='TEST1';
FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
------------------------------------------------------------ ------------------------------ ---------- ------- --------- ---------
/u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT
/u01/app/oracle/oradata/DBdb/test101bak.dbf TEST1 5 ONLINE ONLINE PERMANENT
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf';
alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST1
[oracle@wang DBdb]$ oerr ora 03263
03263, 00000, "cannot drop the first file of tablespace %s"
// *Cause: Trying to drop the first datafile with which ts is created
// *Action: Cannot drop the first datafile with which ts is created
[oracle@wang DBdb]$
報錯表示不能刪除表空間的第一個數(shù)據(jù)文件
注意：
1、從字典管理遷移到本地管理的只讀表空間內(nèi)的數(shù)據(jù)文件時不能被刪除的。除此之外，其他的只讀表空間內(nèi)的數(shù)據(jù)文件可以刪除。
2、系統(tǒng)表空間內(nèi)的數(shù)據(jù)文件無法被刪除。
3、如果一個本地管理的表空間被離線，則其內(nèi)的數(shù)據(jù)文件無法被刪除。
SQL> alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf';
alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf'
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
4、如果表空間內(nèi)僅包含一個數(shù)據(jù)文件，該數(shù)據(jù)文件無法被刪除。
SQL> alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test3.dbf';
alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test3.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TEST_TBS has only one file
5、如果數(shù)據(jù)文件不為空，該數(shù)據(jù)文件無法被刪除。
6、刪除數(shù)據(jù)文件必須保證數(shù)據(jù)塊處于打開狀態(tài)。
7、不能刪除表空間下的第一個創(chuàng)建的數(shù)據(jù)文件：
SQL> alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf';
alter tablespace test1 drop datafile '/u01/app/oracle/oradata/DBdb/test101.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST1
到此，關(guān)于“datafile的create/offline/drop/rename方法怎么使用”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
