創建一個新的表空間并創建新的用戶，指定新表空間為新用戶的默認表空間

create tablespace zzw datafile '/oradata/cesdb/zzw01.dbf' size 10m;

zzw用戶已經創建過，這里修改其默認表空間

alter user zzw quota unlimited on zzw;
alter user zzw default tablespace zzw;  

創建一個簡單的測試表

CREATE TABLE t1 (id INT NOT NULL,name VARCHAR(50),
);INSERT INTO t1 (id, name) VALUES (1, 'Alice');
INSERT INTO t1 (id, name) VALUES (2, 'Bob');
INSERT INTO t1 (id, name) VALUES (3, 'Charlie');
INSERT INTO t1 (id, name) VALUES (4, 'David');
INSERT INTO t1 (id, name) VALUES (5, 'Emily');insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;SQL> select count(*) from t1;

接下來創建給表空間添加新的數據文件并插入數據

alter tablespace zzw add datafile '/oradata/cesdb/zzw02.dbf ' size 1m;
select file_id,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='ZZW';SQL> insert into t1 select * from t1;10240 rows created.SQL> insert into t1 select * from t1;20480 rows created.SQL> insert into t1 select * from t1;40960 rows created.SQL> insert into t1 select * from t1;81920 rows created.SQL> insert into t1 select * from t1;163840 rows created.SQL> insert into t1 select * from t1;
insert into t1 select * from t1
*
ERROR at line 1:
ORA-01653: unable to extend table ZZW.T1 by 128 in tablespace ZZW

現在刪除數據文件

$ mv /oradata/cesdb/zzw02.dbf /oradata/cesdb/zzw02.dbf_bak

再次插入數據顯示已經找不到dbf數據庫文件了

SQL> insert into t1 select * from t1;
insert into t1 select * from t1*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/oradata/cesdb/zzw02.dbf '
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

這時連shutdown immediate關庫都關不掉了

SQL> shutdown immediate
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/oradata/cesdb/zzw02.dbf '
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

只能用 shutdown abort 關閉數據庫了

SQL> shutdown abort
ORACLE instance shut down.

因為數據文件丟失啟動數據庫只能起到mount狀態

SQL> startup
ORACLE instance started.Total System Global Area 3240239104 bytes
Fixed Size                  2257600 bytes
Variable Size            2030046528 bytes
Database Buffers         1191182336 bytes
Redo Buffers               16752640 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oradata/cesdb/zzw02.dbf '

然后先 offline 這個數據文件再打開數據庫

SQL> alter database datafile 6 offline;Database altered.SQL> alter database open;Database altered.

重建數據文件的語法

SQL> alter database create datafile 6;Database altered.#or
#alter database create datafile '//oradata/cesdb/zzw02.dbf'  [as '/oradata/cesdb/zzw02.dbf'];

recover 恢復數據文件

SQL> recover datafile 6;
Media recovery complete.

online 數據文件

alter database datafile 6 online;

查看數據文件是否onilne

SQL>  select file#,status from v$datafile_header where file#=6;FILE# STATUS
---------- ----------6 ONLINESQL> select COUNT(*) from t1;COUNT(*)
----------327708