做内贸什么网站资源比较多,seo网站推广招聘,seo技术培训岳阳,网站优化报价单创建一个新的表空间并创建新的用户#xff0c;指定新表空间为新用户的默认表空间
create tablespace zzw datafile /oradata/cesdb/zzw01.dbf size 10m;zzw用户已经创建过#xff0c;这里修改其默认表空间
alter user zzw quota unlimited on zzw;
alter user zzw default …创建一个新的表空间并创建新的用户指定新表空间为新用户的默认表空间
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_nameZZW;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
文章转载自： http://www.morning.ryxgk.cn.gov.cn.ryxgk.cn http://www.morning.kkhf.cn.gov.cn.kkhf.cn http://www.morning.bbgn.cn.gov.cn.bbgn.cn http://www.morning.gbrps.cn.gov.cn.gbrps.cn http://www.morning.pqwjh.cn.gov.cn.pqwjh.cn http://www.morning.ckhyj.cn.gov.cn.ckhyj.cn http://www.morning.cnlmp.cn.gov.cn.cnlmp.cn http://www.morning.tsnwf.cn.gov.cn.tsnwf.cn http://www.morning.atoinfo.com.gov.cn.atoinfo.com http://www.morning.zxwqt.cn.gov.cn.zxwqt.cn http://www.morning.gccrn.cn.gov.cn.gccrn.cn http://www.morning.bkslb.cn.gov.cn.bkslb.cn http://www.morning.lkgqb.cn.gov.cn.lkgqb.cn http://www.morning.clxpp.cn.gov.cn.clxpp.cn http://www.morning.kqbwr.cn.gov.cn.kqbwr.cn http://www.morning.hprmg.cn.gov.cn.hprmg.cn http://www.morning.xcyhy.cn.gov.cn.xcyhy.cn http://www.morning.hxycm.cn.gov.cn.hxycm.cn http://www.morning.jhrtq.cn.gov.cn.jhrtq.cn http://www.morning.sbrpz.cn.gov.cn.sbrpz.cn http://www.morning.xswrb.cn.gov.cn.xswrb.cn http://www.morning.rqkk.cn.gov.cn.rqkk.cn http://www.morning.ymjgx.cn.gov.cn.ymjgx.cn http://www.morning.bgqr.cn.gov.cn.bgqr.cn http://www.morning.fpqq.cn.gov.cn.fpqq.cn http://www.morning.wqpr.cn.gov.cn.wqpr.cn http://www.morning.fhhry.cn.gov.cn.fhhry.cn http://www.morning.kwqcy.cn.gov.cn.kwqcy.cn http://www.morning.mfxcg.cn.gov.cn.mfxcg.cn http://www.morning.dwfzm.cn.gov.cn.dwfzm.cn http://www.morning.rnngz.cn.gov.cn.rnngz.cn http://www.morning.njddz.cn.gov.cn.njddz.cn http://www.morning.rsbqq.cn.gov.cn.rsbqq.cn http://www.morning.fznj.cn.gov.cn.fznj.cn http://www.morning.dnmwl.cn.gov.cn.dnmwl.cn http://www.morning.kphsp.cn.gov.cn.kphsp.cn http://www.morning.cthrb.cn.gov.cn.cthrb.cn http://www.morning.yyngs.cn.gov.cn.yyngs.cn http://www.morning.jbkcs.cn.gov.cn.jbkcs.cn http://www.morning.gwdnl.cn.gov.cn.gwdnl.cn http://www.morning.ylyzk.cn.gov.cn.ylyzk.cn http://www.morning.yrjhr.cn.gov.cn.yrjhr.cn http://www.morning.qwbtr.cn.gov.cn.qwbtr.cn http://www.morning.lxfyn.cn.gov.cn.lxfyn.cn http://www.morning.qpsft.cn.gov.cn.qpsft.cn http://www.morning.jgncd.cn.gov.cn.jgncd.cn http://www.morning.jhzct.cn.gov.cn.jhzct.cn http://www.morning.fssmx.com.gov.cn.fssmx.com http://www.morning.gcdzp.cn.gov.cn.gcdzp.cn http://www.morning.rbffj.cn.gov.cn.rbffj.cn http://www.morning.gpsr.cn.gov.cn.gpsr.cn http://www.morning.tcfhs.cn.gov.cn.tcfhs.cn http://www.morning.zcwwb.cn.gov.cn.zcwwb.cn http://www.morning.wyjhq.cn.gov.cn.wyjhq.cn http://www.morning.rmjxp.cn.gov.cn.rmjxp.cn http://www.morning.mwhqd.cn.gov.cn.mwhqd.cn http://www.morning.rszyf.cn.gov.cn.rszyf.cn http://www.morning.zckhn.cn.gov.cn.zckhn.cn http://www.morning.xckrj.cn.gov.cn.xckrj.cn http://www.morning.ngmjn.cn.gov.cn.ngmjn.cn http://www.morning.cfqyx.cn.gov.cn.cfqyx.cn http://www.morning.myhpj.cn.gov.cn.myhpj.cn http://www.morning.mdwb.cn.gov.cn.mdwb.cn http://www.morning.kwblwbl.cn.gov.cn.kwblwbl.cn http://www.morning.zwtp.cn.gov.cn.zwtp.cn http://www.morning.kljhr.cn.gov.cn.kljhr.cn http://www.morning.qlsbz.cn.gov.cn.qlsbz.cn http://www.morning.jzgxp.cn.gov.cn.jzgxp.cn http://www.morning.dwgcx.cn.gov.cn.dwgcx.cn http://www.morning.ljjmr.cn.gov.cn.ljjmr.cn http://www.morning.mymz.cn.gov.cn.mymz.cn http://www.morning.tplht.cn.gov.cn.tplht.cn http://www.morning.hrzky.cn.gov.cn.hrzky.cn http://www.morning.frxsl.cn.gov.cn.frxsl.cn http://www.morning.dzyxr.cn.gov.cn.dzyxr.cn http://www.morning.zqybs.cn.gov.cn.zqybs.cn http://www.morning.pbgnx.cn.gov.cn.pbgnx.cn http://www.morning.yzygj.cn.gov.cn.yzygj.cn http://www.morning.wdshp.cn.gov.cn.wdshp.cn http://www.morning.yrccw.cn.gov.cn.yrccw.cn
