ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 同一个用户分属不同表空间的表,然后导入到同一个用户同一个表空间tablespace是否可以

同一个用户分属不同表空间的表,然后导入到同一个用户同一个表空间tablespace是否可以

原创 Linux操作系统 作者:wisdomone1 时间:2012-11-08 13:20:02 0 删除 编辑
                                           
 SQL> conn sys/system@orcl as sysdba                                                      
  Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0                  
  Connected as SYS                                                                        
                                                                                          
  SQL> create tablespace tbs_1 datafile 'c:\tbs_1.dbf' size 1m;                           
                                                                                          
  Tablespace created                                                                      
                                                                                          
  SQL> create tablespace tbs_2 datafile 'c:\tbs_2.dbf' size 1m;                           
                                                                                          
  Tablespace created                                                                      
                                                                                          
  SQL> create tablespace tbs_3 datafile 'c:\tbs_3.dbf' size 1m;                           
                                                                                          
  Tablespace created                                                                      
                                                                                          
  SQL> create user tbs_user identified by system default tablespace tbs_1 account unlock; 
                                                                                          
  User created                                                                            
                                                                                          
  SQL> grant resource,connect to tbs_user;                                                
                                                                                          
  Grant succeeded                                                                         
                                                                                          
  SQL> conn tbs_user/system@orcl                                                          
  Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0                  
  Connected as tbs_user                                                                   
                                                                                          
  SQL> create table t_1(a int) tablespace tbs_1;                                          
                                                                                          
  Table created                                                                           
                                                                                          
  SQL> insert into t_1 values(1);                                                         
                                                                                          
  1 row inserted                                                                          
                                                                                          
  SQL> commit;                                                                            
                                                                                          
  Commit complete                                                                         
                                                                                          
  SQL> create table t_2(a int) tablespace tbs_2;                                          
                                                                                          
  Table created                                                                           
                                                                                          
  SQL> insert into t_2 values(1);                                                         
                                                                                          
  1 row inserted                                                                          
                                                                                          
  SQL> commit;                                                                            
                                                                                          
  Commit complete                                                                         
                                                                                          
  SQL> create table t_3(a int) tablespace tbs_3;                                          
                                                                                          
  Table created                                                                           
                                                                                          
  SQL> insert into t_3 values(1);                                                         
                                                                                          
  1 row inserted                                                                          
                                                                                          
  SQL> commit;                                                                            
                                                                                          
  Commit complete                                                                         
                                                                                          
  SQL> select u.TABLE_NAME,u.TABLESPACE_NAME  from user_tables u;                         
                                                                                          
  TABLE_NAME                     TABLESPACE_NAME                                          
  ------------------------------ ------------------------------                           
  T_1                            TBS_1                                                    
  T_2                            TBS_2                                                    
  T_3                            TBS_3                                                    
                                                                                          
  exp userid=tbs_user/system@orcl tables=t_1,t_2,t_3 file=c:\exp_t_1_2_3.dmp              
                                                                                          
  SQL> conn sys/system@orcl as sysdba                                                     
  Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0                  
  Connected as SYS                                                                        
                                                                                          
  SQL> drop user tbs_user cascade;                                                        
                                                                                          
  User dropped                                                                            
                                                                                          
  SQL> drop tablespace tbs_1 including contents and datafiles;                            
                                                                                          
  Tablespace dropped                                                                      
                                                                                          
  SQL> drop tablespace tbs_2 including contents and datafiles;                            
                                                                                          
  Tablespace dropped                                                                      
                                                                                          
  SQL> drop tablespace tbs_3 including contents and datafiles;                            
                                                                                          
  Tablespace dropped                                                                      
                                                                                          
  SQL> create user tbs_user identified by system default tablespace users account unlock; 
                                                                                          
  User created                                                                            
                                                                                          
  SQL> grant resource,connect to tbs_user;                                                
  Grant succeeded                                                                         
                                                                                          
  --导入                                                                                    
  imp userid=tbs_user/system@orcl tables=t_1,t_2,t_3 file=c:\exp_t_1_2_3.dmp              
                                                                                          
  SQL> conn tbs_user/system@orcl                                                          
  Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0                  
  Connected as tbs_user                                                                   
                                                                                          
  SQL> select u.TABLE_NAME,u.TABLESPACE_NAME  from user_tables u;                         
                                                                                          
  TABLE_NAME                     TABLESPACE_NAME                                          
  ------------------------------ ------------------------------                           
  T_1                            USERS                                                    
  T_2                            USERS                                                    
  T_3                            USERS                                                    
                                                                                          
  小结:同一个用户但分属不同的表空间的dmp,在导入同一个用户时,会默认导入到users表空间                                          

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

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb 工作经历: 中国普天 北京科蓝 北京云和恩墨 北京神州新桥

注册时间:2008-04-04

  • 博文量
    2189
  • 访问量
    12690141