[2010.1.29]exp,imp引发的create tablespace问题

最新推荐文章于 2021-04-16 08:07:01 发布

congchenjin2969

最新推荐文章于 2021-04-16 08:07:01 发布

阅读量163

点赞数

文章标签：数据库

发现alert log中有创建tablespace的语句,把db中所有表空间都重建一次,当然,由于db中已经存在同名表空间,语句都出错了, 显然不是执行自定义脚本形成的语句

Fri Jan 29 18:41:28 2010
CREATE TABLESPACE "SYSAUX" BLOCKSIZE 32768 DATAFILE '+DATA1/isddw/datafile/sysaux.1757.705671987' SIZE 5294M AUTOEXTEND ON NEXT 10485760 MAXSIZE 131071M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO
ORA-1543 signalled during: CREATE TABLESPACE "SYSAUX" BLOCKSIZE 32768 DATAFILE '+DATA1/isddw/datafile/sysaux.1757.705671987' SIZE 5294M AUTOEXTEND ON NEXT 10485760 MAXSIZE 131071M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO...
CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 32768 DATAFILE '+DATA1/isddw/datafile/undotbs1.1760.705671989' SIZE 938475520 AUTOEXTEND ON NEXT 5242880 MAXSIZE 131071M EXTENT MANAGEMENT LOCAL
ORA-1543 signalled during: CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 32768 DATAFILE '+DATA1/isddw/datafile/undotbs1.1760.705671989' SIZE 938475520 AUTOEXTEND ON NEXT 5242880 MAXSIZE 131071M EXTENT MANAGEMENT LOCAL ...

[@more@]

经查history,发现是dwisd1上执行了下面语句

97 exp xx/xx@dwdbgc file=/vg1data1/work/cpwang/r_pasture.dmp tables=r_pasture_basic_d direct=y grants=n statistics=none
98 exp xxx/xxx@isddw file=/vg1data1/work/cpwang/r_pasture.dmp full=y grants=n statistics=none
99 imp xxx/xxx@isddw file=/vg1data1/work/cpwang/r_pasture.dmp full=y grants=n statistics=none
100 imp xxx/xxx@isddw file=/vg1data1/work/cpwang/r_pasture.dmp full=y grants=n statistics=none buffer=100000000
101 imp xxx/xxx@isddw file=/vg1data1/work/cpwang/r_pasture.dmp full=y grants=n statistics=none buffer=100000000
102 exp xxx/xxx@dwdbgc file=/vg1data1/work/cpwang/r_pasture.dmp tables=r_pasture_basic_d direct=y grants=n statistics=none
103 imp xxx/xxx@isddw file=/vg1data1/work/cpwang/r_pasture.dmp full=y grants=n statistics=none buffer=100000000

用户的解释是
"刚才有个误操作，复制exp，改了full，但是忘了改成imp了，
不过马上就取消了的啊，还在跑？"

显然,是在执行第二个exp时搞错了,本意是imp,这一步导致oracle执行全库导出,从file#=1开始,文件头部存储的是数据字典信息,会将创建表空间的语句写到dmp文件中 (overwrite了第一个exp创建的dmp),

所以,在前面三个imp操作时,会执行create tablespace操作,用户发现导了很久,没有数据导入想要的表r_pasture_basic_d中后, 意识到dmp文件不对,于是重新exp

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94384/viewspace-1031009/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94384/viewspace-1031009/