跳至主要内容

关于tablespace和table

今天拿测试库做练习,想来该做点什么,那么就建个tablespace吧。
第一次输入

SQL>CREATE TABLESPACE username DATAFILE '/SERVER/ora9/oradata/ora9 ' SIZE
100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
(后面回想的,前面怎么做的我在putty上也找不到了,SELECT了一个语句不知道不刷到哪里去了)然后发觉username应该是自己的用户名(傻了,-______-)继续写了下面这条
SQL>CREATE TABLESPACE atu DATAFILE '/SERVER/ora9/oradata/ora9/atu.dbf ' SIZE
100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

然后第一条就成了今天郁闷的关键,因为在本来ora9是已经存在的文档目录所有.dbf文件都存放在这个下。

当我自己在linux环境下检查的时候发觉同时存在,就rm掉了,回到sqlplus下

SQL>shutdown immedaite;时候出现以下问题
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'
ORA-27041: unable to open fileLinux Error: 2: No such file or
directory

无解,求助领导。

SQL>select file_id,file_name,tablespace_name from dba_data_files;
1 /SERVER/ora9/oradata/ora9/system01.dbf SYSTEM
2 /SERVER/ora9/oradata/ora9/hyf.dbf HYF
3 /SERVER/ora9/oradata/ora9/cwmlite01.dbf CWMLITE
4 /SERVER/ora9/oradata/ora9/drsys01.dbf DRSYS
5 /SERVER/ora9/oradata/ora9/indx01.dbf INDX
6 /SERVER/ora9/oradata/ora9/odm01.dbf ODM
7 /SERVER/ora9/oradata/ora9/tools01.dbf TOOLS
8 /SERVER/ora9/oradata/ora9/users01.dbf USERS
9 /SERVER/ora9/oradata/ora9/xdb01.dbf XDB
10 /SERVER/ora9/oradata/ora9/t2007q1.dbf T2007Q1
11 /SERVER/ora9/oradata/ora9/t2007q2.dbf T2007Q2
12 /SERVER/ora9/oradata/ora9/t2007q3.dbf T2007Q3
13 /SERVER/ora9/oradata/ora9/t2007q4.dbf T2007Q4
14 /SERVER/ora9/oradata/ora9/undo.dbf UNDO
15 /SERVER/ora9/oradata/ora9 USERDATA
16 /SERVER/ora9/oradata/ora9/atu.dbf ATU

由于我刚刚的失误,导致第15条语句无法加载,而且无法正常的shutdown想直接删除表空间:

SQL>drop tablespace USERDATA ;
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'
ORA-27041: unable to open fileLinux Error: 2: No such file or directory

OFFLINE办法:

SQL>alter tablespace UESRDATA offline;
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'
ORA-27041: unable to open fileLinux Error: 2: No such file or directory

试着更名,可是不能OFFLINE也不能改名.

  • 先SHUTDOWN ABORT;
  • STARTUP MOUNT:
  • Alter database backup controlfile to trace;得到重建控制文件脚本;
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS FORCE LOGGING
ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/SERVER/ora9/oradata/ora9/redo01.log' SIZE 20M,
GROUP 2 '/SERVER/ora9/oradata/ora9/redo02.log' SIZE 20M,
GROUP 3 '/SERVER/ora9/oradata/ora9/redo03.log' SIZE 20M
-- STANDBY LOGFILE
DATAFILE'/SERVER/ora9/oradata/ora9/drsys01.dbf',
'/SERVER/ora9/oradata/ora9/indx01.dbf',
'/SERVER/ora9/oradata/ora9/odm01.dbf',
'/SERVER/ora9/oradata/ora9/tools01.dbf',
'/SERVER/ora9/oradata/ora9/users01.dbf',
'/SERVER/ora9/oradata/ora9/xdb01.dbf',
'/SERVER/ora9/oradata/ora9/t2007q1.dbf',
'/SERVER/ora9/oradata/ora9/t2007q2.dbf',
'/SERVER/ora9/oradata/ora9/t2007q3.dbf',
'/SERVER/ora9/oradata/ora9/t2007q4.dbf',
'/SERVER/ora9/oradata/ora9/undo.dbf',
'/SERVER/ora9/oradata/ora9',
'/SERVER/ora9/oradata/ora9/atu.dbf'
CHARACTER SET ZHS16GBK
并在脚本中删除错误的数据文件: '/SERVER/ora9/oradata/ora9'

在重建控制文件后,试着打开数据库:


SQL> alter database open resetlogs;
alter database open resetlogs
*ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/SERVER/ora9/oradata/ora9/system01.dbf'//需要恢复

SQL> recover database using backup controlfile;
ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for
thread
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10703011 for thread 1 is in sequence #108
Specify
log: {=suggested filename AUTO
CANCEL}/SERVER/ora9/oradata/ora9/redo02.log
ORA-00310: archived log contains sequence 107; sequence 108 required
ORA-00334: archived log: '/SERVER/ora9/oradata/ora9/redo02.log'

SQL> recover database using backup controlfile;
ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for
thread
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10703011 for thread 1 is in sequence #108

Specify log: {=suggested filename AUTO
CANCEL}/SERVER/ora9/oradata/ora9/redo03.log (使用的是当前日志)
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9'ORA-01112: media
recovery not started

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
由于系统表空间中,在记录这个数据文件,而在控制文件中没有,所以系统报这个错误.

SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------/SERVER/ora9/oradata/ora9/system01.dbf/SERVER/ora9/oradata/ora9/hyf.dbf/SERVER/ora9/oradata/ora9/cwmlite01.dbf/SERVER/ora9/oradata/ora9/drsys01.dbf/SERVER/ora9/oradata/ora9/indx01.dbf/SERVER/ora9/oradata/ora9/odm01.dbf/SERVER/ora9/oradata/ora9/tools01.dbf/SERVER/ora9/oradata/ora9/users01.dbf/SERVER/ora9/oradata/ora9/xdb01.dbf/SERVER/ora9/oradata/ora9/t2007q1.dbf/SERVER/ora9/oradata/ora9/t2007q2.dbf
NAME--------------------------------------------------------------------------------/SERVER/ora9/oradata/ora9/t2007q3.dbf/SERVER/ora9/oradata/ora9/t2007q4.dbf/SERVER/ora9/oradata/ora9/undo.dbf/SERVER/ora9/product/9.2/dbs/UNNAMED00015/SERVER/ora9/oradata/ora9/atu.dbf
16 rows selected.

SQL> alter database
datafile'/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;
Database
altered.

SQL> alter database open resetlogs;alter database open
resetlogs*ERROR at line 1:
ORA-01113: file 1 needs media recoveryORA-01110: data file 1:
'/SERVER/ora9/oradata/ora9/system01.dbf'

SQL> recover database using backup controlfile;
ORA-00279: change 10712117 generated at 12/19/2007 10:12:24 needed for
thread 1
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10712117 for thread 1 is in sequence #108
Specify
log: {=suggested filename AUTO CANCEL}/SERVER/ora9/oradata/ora9/redo03.log
(当前日志)Log applied.Media recovery complete.

SQL> alter database open resetlogs;alter database open
resetlogs*ERROR at line 1:
ORA-01245: offline file 15 will be lost if RESETLOGS is done
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

SQL> alter database
datafile'/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;Database altered.
SQL> alter database open resetlogs;alter database open resetlogs*ERROR at
line 1:
ORA-01245: offline file 15 will be lost if RESETLOGS is done
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'

SQL> alter database datafile 15 offline drop;Database altered.

SQL> alter database open resetlogs;Database altered.
到此,数据库正常打开,恢复全部完成.

以上是我领导恢复的,借鉴下例子,
其实在做事情的时候首先想到的应该是备份,上次在市府那次也应该是这样,不论你的数据有多么的重要先做好备份是一个好习惯。
其实在更改你的id名想让你的id名字和你的tablespace一致的话其实可以这样。

SQL>SELECT default_tablespace FROM dba_users WHERE username =
'ATU';
结果应该是hyf
SQL>CREATE TABLESPACE atu DATAFILE '/SERVER/ora9/oradata/ora9/atu.dbf'
SIZE 100M;
SQL>ATLER USER atu default tablespace atu;
SQL>SELECT default_tablespace FROM dba_users WHERE username = 'ATU'

现在看就是atu了原来在hyf下我所建立的表转到atu下
SQL>ALTER table atu.a move atu
现在所看到的是a表转到atu这个tablespace了,然后批量转需要做一个脚本,脚本目前还暂时不会做。刚刚也没看清楚生成后在sql,command下完成。

P.S. (网络上找的消息)
数据文件就好像水桶。
水桶中没有被水淹过的地方(高水位以上), 空间可以回收。

alter datafile datafile FILE resize xxxM ;

如果水桶都被淹过了,那就有点困难了。
一种方法是alter table move tablespace 命令。

比如表T的tablespace为system,现在要把它移动到tablespace users,
语法是: alter table T move tablespace users;
如果是索引,比如是idx1,要移动到tablespace ts_idx,语法是: alter index idx1 rebuild
tablespace ts_idx ;

另一种方法是使用exp/imp。

方法二:
alter database create datafile 15 as ’/ server/ora9.../ora9 ' reuse;recover datafile 15
。。。。似乎是因为我乱删东西造成的

今天买了双鞋子,几个人看了都不好,我觉得差不多,便宜么,能打就好了,打得时候看着是何不适合。


评论

此博客中的热门博文

4 steps to delete account in Gerrit DB

4 steps to delete account in DB. Delete from accounts where preferred_email=’’; delete from account_ssh_keys where account_id=''; delete from account_external_ids where external_id='gerrit:*’; delete from account_external_ids where external_id='username:*’; whatever it was in H2 database and postgres db . H2: ssh -p 24198 localhost gerrit gsql Postgres: psql

男人35岁前成功的12条黄金法则

第一章:一个目标 一艘没有航行目标的船,任何方向的风都是逆风 1、你为什么是穷人,第一点就是你没有立下成为富人的目标 2、你的人生核心目标是什么? 杰出人士与平庸之辈的根本差别并不是天赋、机遇,而在于有无目标。 3、起跑领先一步,人生领先一大步:成功从选定目标开始 4、贾金斯式的人永远不会成功 为什么大多数人没有成功?真正能完成自己计划的人只有5%,大多数人不是将自己的目标舍弃,就是沦为缺乏行动的空想 5、 如果你想在35岁以前成功,你一定在25至30岁之间确立好你的人生目标 6、 每日、每月、每年都要问自己:我是否达到了自己定下的目标 第二章:两个成功基点 站好位置,调正心态,努力冲刺,35岁以前成功 (一)人生定位 1、 人怕入错行:你的核心竞争力是什么? 2、 成功者找方法,失败者找借口 3、 从三百六十行中选择你的最爱 人人都可以创业,但却不是人人都能创业成功 4、 寻找自己的黄金宝地 (二)永恒的真理:心态决定命运,35岁以前的心态决定你一生的命运 1、 不满现状的人才能成为富翁 2、 敢于梦想,勇于梦想,这个世界永远属于追梦的人 3、 35岁以前不要怕,35岁以后不要悔 4、 出身贫民,并非一辈子是贫民,只要你永远保持那颗进取的心。中国成功人士大多来自小地方 5、 做一个积极的思维者 6、 不要败给悲观的自己 有的人比你富有一千倍,他们也会比你聪明一千倍么?不会,他们只是年轻时心气比你高一千倍。 人生的好多次失败,最后并不是败给别人,而是败给了悲观的自己。 7、 成功者不过是爬起来比倒下去多一次 8、 宁可去碰壁,也不要在家里面壁 克服你的失败、消极的心态 (1) 找个地方喝点酒 (2) 找个迪厅跳跳舞 (3) 找帮朋友侃侃山 (4) 积极行动起来 第三章:三大技巧 1、管理时间:你的时间在哪里,你的成就就在哪里。 把一小时看成60分钟的人,比看作一小时的人多60倍 2、你不理财,财不理你 3、自我管理,游刃有余 (1) 创业不怕本小,脑子一定要好 (2) 可以开家特色店 (3) 做别人不愿做的生意 第四章:四项安身立命的理念 35岁以前一定要形成个人风格 1、做人优于做事 做事失败可以重来,做人失败却不能重来 (1) 做人要讲义气 (2) 永不气馁 2、豁达的男人有财运,豁达的女人有帮夫运 35岁以前搞定婚姻生活 3、忠诚的原则: