資料檔案實驗操作datafile的create/offline/drop/rename等操作
一、建立表空間：
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>
二、更改表空間狀態：
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>
刪除表空間之後再檢視作業系統物理路徑下沒有表空間對應的資料檔案了。
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>
--關閉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>
九、修改資料檔案可用性
可以透過執行資料檔案的線上和離線操作修改資料檔案的可用性，離線的資料檔案不能被資料庫所訪問，直到它恢復線上狀態之前。只讀表空間中的資料檔案也可以被離線或線上，只讀表空間內的資料檔案的線上或離線不影響表空間自身的狀態，不管怎麼樣，在表空間未處於讀寫狀態之前，這些檔案都是不可寫的。
9.1檢視之前改名為sales的表空間狀態：（執行的是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 歸檔模式下的資料檔案離線
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 資料檔案時再次online需要recover資料檔案，而offline表空間則可以執行執行online，如下進行recover及online資料檔案；
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、非歸檔模式下的資料檔案離線
在非歸檔模式下使用alter database ... offline for drop語句離線資料檔案。offline關鍵字標記該資料檔案離線，不論其是否損壞，所以可以開啟資料庫；for drop關鍵字標記該資料檔案隨後被刪除（只是標記，物理檔案還在），該資料檔案不能再次恢復到線上狀態。（實際上，在線上日誌組還未發生切換之前，還是可以恢復到線上狀態的）
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>
--查詢當前log日誌組：
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
--執行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模擬線上日誌組已發生切換且日誌已被覆蓋後執行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>
十、重新命名（修改儲存位置）資料檔案
步驟如下：
方法1
1、將包含資料檔案的表空間或者只將某個資料檔案離線。
2、使用作業系統命令修改資料檔名。
3、使用alter database ... rename file '' to '';語句改變資料庫中的資料檔名。
4、備份資料庫。
方法2：
1.將資料庫shutdown 啟動到mount下
2.使用作業系統命令修改資料檔名。
3.使用alter database ... rename file '' to '';語句改變資料庫中的資料檔名。
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改變資料檔名稱；
--先物理盤建立對應名稱；
[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新資料檔案；
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資料檔案：
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
未發現/u01/app/oracle/oradata/DBdb/test3333.dbf，進行建立：
[oracle@wang DBdb]$ pwd
/u01/app/oracle/oradata/DBdb
[oracle@wang DBdb]$
[oracle@wang DBdb]$ cp test1022222.dbf test3333.dbf
[oracle@wang DBdb]$
再次執行：
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>
十一、刪除資料檔案
從表空間內刪除資料檔案：語法
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
報錯表示，表空間內僅包含一個資料檔案，該資料檔案無法被刪除。
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]$
報錯表示不能刪除表空間的第一個資料檔案
注意：
1、從字典管理遷移到本地管理的只讀表空間內的資料檔案時不能被刪除的。除此之外，其他的只讀表空間內的資料檔案可以刪除。
2、系統表空間內的資料檔案無法被刪除。
3、如果一個本地管理的表空間被離線，則其內的資料檔案無法被刪除。
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、如果表空間內僅包含一個資料檔案，該資料檔案無法被刪除。
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、如果資料檔案不為空，該資料檔案無法被刪除。
6、刪除資料檔案必須保證資料塊處於開啟狀態。
7、不能刪除表空間下的第一個建立的資料檔案：
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
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>
二、更改表空間狀態：
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>
刪除表空間之後再檢視作業系統物理路徑下沒有表空間對應的資料檔案了。
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>
--關閉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>
九、修改資料檔案可用性
可以透過執行資料檔案的線上和離線操作修改資料檔案的可用性，離線的資料檔案不能被資料庫所訪問，直到它恢復線上狀態之前。只讀表空間中的資料檔案也可以被離線或線上，只讀表空間內的資料檔案的線上或離線不影響表空間自身的狀態，不管怎麼樣，在表空間未處於讀寫狀態之前，這些檔案都是不可寫的。
9.1檢視之前改名為sales的表空間狀態：（執行的是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 歸檔模式下的資料檔案離線
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 資料檔案時再次online需要recover資料檔案，而offline表空間則可以執行執行online，如下進行recover及online資料檔案；
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、非歸檔模式下的資料檔案離線
在非歸檔模式下使用alter database ... offline for drop語句離線資料檔案。offline關鍵字標記該資料檔案離線，不論其是否損壞，所以可以開啟資料庫；for drop關鍵字標記該資料檔案隨後被刪除（只是標記，物理檔案還在），該資料檔案不能再次恢復到線上狀態。（實際上，在線上日誌組還未發生切換之前，還是可以恢復到線上狀態的）
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>
--查詢當前log日誌組：
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
--執行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模擬線上日誌組已發生切換且日誌已被覆蓋後執行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>
十、重新命名（修改儲存位置）資料檔案
步驟如下：
方法1
1、將包含資料檔案的表空間或者只將某個資料檔案離線。
2、使用作業系統命令修改資料檔名。
3、使用alter database ... rename file '' to '';語句改變資料庫中的資料檔名。
4、備份資料庫。
方法2：
1.將資料庫shutdown 啟動到mount下
2.使用作業系統命令修改資料檔名。
3.使用alter database ... rename file '' to '';語句改變資料庫中的資料檔名。
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改變資料檔名稱；
--先物理盤建立對應名稱；
[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新資料檔案；
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資料檔案：
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
未發現/u01/app/oracle/oradata/DBdb/test3333.dbf，進行建立：
[oracle@wang DBdb]$ pwd
/u01/app/oracle/oradata/DBdb
[oracle@wang DBdb]$
[oracle@wang DBdb]$ cp test1022222.dbf test3333.dbf
[oracle@wang DBdb]$
再次執行：
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>
十一、刪除資料檔案
從表空間內刪除資料檔案：語法
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
報錯表示，表空間內僅包含一個資料檔案，該資料檔案無法被刪除。
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]$
報錯表示不能刪除表空間的第一個資料檔案
注意：
1、從字典管理遷移到本地管理的只讀表空間內的資料檔案時不能被刪除的。除此之外，其他的只讀表空間內的資料檔案可以刪除。
2、系統表空間內的資料檔案無法被刪除。
3、如果一個本地管理的表空間被離線，則其內的資料檔案無法被刪除。
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、如果表空間內僅包含一個資料檔案，該資料檔案無法被刪除。
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、如果資料檔案不為空，該資料檔案無法被刪除。
6、刪除資料檔案必須保證資料塊處於開啟狀態。
7、不能刪除表空間下的第一個建立的資料檔案：
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2149517/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OFFLINE和DROP資料檔案的理解
- alter database datafile offline drop 與 alter tablespace drop datafile 區別Database
- 利用offline datafile檔案方式遷移資料
- 實驗--檔案操作
- alter database datafile 4 offline drop;Database
- alter database datafile .... offline drop的問題Database
- 表空間與資料檔案的offline和online操作
- alter database datafile offline drop相關問題Database
- QT選擇目錄等常用檔案/資料夾操作QT
- oracle 線上rename資料檔案Oracle
- rename備庫資料檔案
- c#資料操作：資料庫訪問 和 檔案操作C#資料庫
- 非歸檔模式下恢復利用offline drop命令誤刪除的資料檔案模式
- rman copy asm datafile(rename asm datafile)ASM
- [20151028]理解資料檔案offline+drop.txt
- oracle 資料檔案offlineOracle
- Python資料夾與檔案的操作Python
- 重建控制檔案與 datafile offline,tablespace read only
- 用vb操作檔案或資料夾
- 體驗Oracle 10gR2的drop empty datafileOracle 10g
- 教你如何用 Lua 操作檔案中的資料
- Oracle 移動資料檔案的操作方法Oracle
- SCAU 高程綜合實驗：檔案操作與字元處理字元
- python判斷檔案是否存在等操作Python
- Solr json,xml等檔案資料匯入(新增索引)linux下操作SolrJSONXML索引Linux
- 檔案IO操作的最佳實踐
- sqlserver收縮資料庫、收縮資料檔案的操作SQLServer資料庫
- 我的RHCE課堂實驗《檔案和目錄操作》(轉)
- Python_13-Office檔案資料操作Python
- RM 刪除資料檔案恢復操作
- 檔案操作
- 表空間OFFLINE和資料檔案OFFLINE的區別
- 表空間offline,資料檔案offline 的區別(ZT)
- oracle 資料檔案（Datafile ） 大小 限制 說明Oracle
- 資料檔案OFFLINE的3種情況
- tablespace offline 和datafile offline的區別
- C檔案與檔案的操作
- datafile.sql 檢視資料檔案和臨時檔案SQL