<script type="text/javascript"> //<![CDATA[ Sys.WebForms.PageRequestManager._initialize('AjaxHolder$scriptmanager1', document.getElementById('Form1')); Sys.WebForms.PageRequestManager.getInstance()._updateControls(['tAjaxHolder$UpdatePanel1'], [], [], 90); //]]> </script>
一直以来,我都认为只要指定用户的默认表空间,向该用户导入数据时,会自动进入到默认表空间。后来发现从System导出的dmp文件在导入时,即使指定新用户的默认表空间,还是要往System表空间中导数据。
上网搜了一下,还是有解决方法的,常见的方法如下:
SQL> create user myhuang identified by myhuang default tablespace myhuang;
SQL> grant resource,connect to myhuang;
SQL> grant dba to myhuang;//赋DBA权限
SQL> revoke unlimited tablespace from myhuang;//撤销此权限
SQL> alter user myhuang quota 0 on system;//将用户在System表空间的配额置为0
SQL> alter user myhuang quota unlimited on myhuang;//设置在用户在myhuang表空间配额不受限。
经过上述设置后,就可以用imp导入数据,数据将会进入指定的myhuang表空间:
C:/Documents and Settings/myhuang>imp system/123456@vdb fromuser=lnxh tous
er=myhuang file=G:/myhuang/lnxh.dmp ignore=y grants=n
顺便说两个小问题:
(1)IMP-00003: 遇到 ORACLE 错误 1658
ORA-01658: 无法为表空间 MYHUANG 中的段创建 INITIAL 区
通常这个问题可以通过Resize增加表空间数据文件大小来解决。
(2)删除表空间
SQL> drop tablespace myhuang including contents and datafiles;
在10g中实验,drop表空间之后,仍然需要手动去删除数据文件。
//2008-08-24补充————————————————————————
另一种比较好的方法:
Create tablespace {tbs_name} datafile ‘{file_path}’ size 500M autoextend on next 10M;
Create user {u_name} identified by {u_pwd} default tablespace {tbs_name} quota unlimited on {tbs_name};
Grant connect,imp_full_database to {u_name};
Imp {u_name}/{u_pwd}@{local_svrname} fromuser={from_user} touser={u_name} file={dmp_file_path} ignore=y tablespaces={tbs_name};
此方法不需要授予新用户DBA权限。
此方法的存在的问题是:可能导致包含BLOB、CLOB字段的表导入失败,这种情况下可以先用sql脚本将表结构建立起来,再导入相应的数据
SQL> grant resource,connect to seagull;
exp/imp命令没有data pump的remap_tablespace参数,因此在进行exp/imp作objects迁移时,就会碰到表空间转换的问题了。下面就关于段存储表空间的不同情况分别说明:
一,目标库存在源库迁移段所在的表空间,且有空闲空间。
这种情况是最理想的情况了,无须作任何处理。
二,目标库存在源库迁移段所在的表空间,且有空闲空间,但由于某种原因,需变更表空间。
1,revoke导入用户unlimit tablespace权限
2,设置导入用户在原有表空间上的配额为0(alter user imp_user quota 0 on tablespace_name);
3,设置导入用户的默认表空间为需变更到的表空间名称
注:在进行导入时,如果不存在段上所属表空间名称,则会自动变更存储在默认表空间,(分区表段除外)
三,目标库不存在源库迁移段所在表空间
这种情况又分两种情况:
1,段为非分区段,此时,会自动变更存储在导入用户默认表空间,不会报错。
2,段为分区段,此时,导入时报不存在表空间的错误,需新建此表空间后,再重新导入。
示例:
1,段为非分区段:
$imp dba/dba fromuser=user1 touser=user rows=n statistics=none ignore=y file=export.dmp log=import.log
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by AEDWF, not by you
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
Import terminated successfully without warnings.
2,段为非分区段:
$imp dba/dba fromuser=user2 touser=user rows=n statistics=none ignore=y file=export.dmp log=import.log
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by AEDW, not by you
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing AEDW's objects into JEFFER
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "ACCIDENT_CARD" ("ACCIDENT_CARD_ID" NUMBER(*,0), "PARTITION_KE"
"Y" NUMBER(*,0), "HASHCODE" RAW(30), "PO_ID" NUMBER(*,0), "ISSUE_DATE" DATE,"
" "CARD_TYPE" NUMBER(10, 0), "EXTERNAL_REFERENCE" VARCHAR2(90), "CARD_FLAG" "
" VALUES LESS THAN (281) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABL"
"ESPACE "EDW_DATA_P28" , PARTITION "P29" VALUES LESS THAN (291) PCTFREE 10 "
"PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "EDW_DATA_P29" , PARTITION "P"
"30" VALUES LESS THAN (301) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 T"
"ABLESPACE "EDW_DATA_P30" , PARTITION "P31" VALUES LESS THAN (311) PCTFREE "
"10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "EDW_DATA_P31" , PARTITION"
" "P32" VALUES LESS THAN (321) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"
"5 TABLESPACE "EDW_DATA_P32" , PARTITION "P33" VALUES LESS THAN (331) PCTFR"
"EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "EDW_DATA_P33" , PARTIT"
"ION "P34" VALUES LESS THAN (341) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS"
" 255 TABLESPACE "EDW_DATA_P34" , PARTITION "P35" VALUES LESS THAN (351) PC"
"TFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "EDW_DATA_P35" , PAR"
"TITION "P36" VALUES LESS THAN (361) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR"
"ANS 255 TABLESPACE "EDW_DATA_P36" , PARTITION "P37" VALUES LESS THAN (371) "
" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "EDW_DATA_P37" , "
"PARTITION "P38" VALUES LESS THAN (381) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 TABLESPACE "EDW_DATA_P38" , PARTITION "P39" VALUES LESS THAN (39"
"1) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "EDW_DATA_P39""
" , PARTITION "P99" VALUES LESS THAN (991) PCTFREE 10 PCTUSED 40 INITRANS 1"
" MAXTRANS 255 TABLESPACE "EDW_DATA_P99" )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EDW_DATA_P00' does not exist