本文介绍: 创建一个新的表空间并创建新的用户,指定新表空间为新用户的默认表空间。这时连shutdown immediate关库都关不掉了。只能用 shutdown abort 关闭数据库了。因为数据文件丢失启动数据库只能起到mount状态。然后先 offline 这个数据文件再打开数据库。接下来创建给表空间添加新的数据文件并插入数据。再次插入数据显示已经找不到dbf数据库文件了。zzw用户已经创建过,这里修改其默认表空间。recover 恢复数据文件。查看数据文件是否onilne。online 数据文件。
创建一个新的表空间并创建新的用户,指定新表空间为新用户的默认表空间
create tablespace zzw datafile '/oradata/cesdb/zzw01.dbf' size 10m;
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
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
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
SQL> shutdown abort
ORACLE instance shut down.
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 '
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'];
SQL> recover datafile 6;
Media recovery complete.
alter database datafile 6 online;
SQL> select file#,status from v$datafile_header where file#=6;
FILE# STATUS
---------- ----------
6 ONLINE
SQL> select COUNT(*) from t1;
COUNT(*)
----------
327708
原文地址:https://blog.csdn.net/weixin_45833902/article/details/134590490
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_16093.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!
声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。