oracle表空间的创建及dmp 文件的导入

最新推荐文章于 2024-06-06 17:45:13 发布

weixin_30271335

最新推荐文章于 2024-06-06 17:45:13 发布

阅读量124

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/activiti/p/7575109.html

版权

用oracle系统权限的账号登陆

 
       -- 1.创建用户 
      
 
       create 
       user 
       u_name identified  
       by 
       "u_password" 
       ; 
      
 
       --2.赋予权限 
      
 
       grant 
       dba, resource,  
       connect 
       to 
       u_name;  
      
 
       grant 
       create 
       session  
       to 
       u_name; 
      
 
       grant 
       create 
       table 
       to 
       u_name; 
      
 
       grant 
       create 
       view 
       to 
       u_name; 
      
 
       grant 
       create 
       tablespace  
       to 
       u_name; 
      
 
       grant 
       unlimited tablespace  
       to 
       u_name; 
      
 
       grant 
       select 
       any 
       table 
       to 
       u_name; 
      
 
       grant 
       select 
       any 
       dictionary  
       to 
       u_name; 
      
 
       --3.创建目录 dictionary 
      
 
       create 
       directory directory_name  
       as 
       'D:\oracleEnv\Oracle\product\11.2.0\db\backup' 
       ;  
       --路径可以自己指定 
      
 
       --4.赋予目录权限 
      
 
       GRANT 
       read 
       ,write  
       ON 
       DIRECTORY directory_name  
       TO 
       u_name; 
      
 
       --5.创建表空间 
      
 
       create 
       tablespace table_space_name 
      
 
       datafile  
       'D:\oracleEnv\Oracle\product\11.2.0\dbhome_1\oradata\table_space_name.DBF' 
      
 
       size 
       2500M 
      
 
       autoextend  
       on 
       next 
       500M maxsize 12000M;　 
      
 
       --6.修改表空间配置 
      
 
       ALTER 
       USER 
       table_space_name  
       DEFAULT 
       TABLESPACE u_name; 
      
 
       --8.查看表空间 
      
 
       select 
       tablespace_name , file_id,bytes  
       from 
       dba_data_files  
       where 
       tablespace_name= 
       'my_table_space_name' 
       ; 
      
 
       -- 9.导入数据 
      
 
       -简单版　 
      
 
       impdp u_name/u_password@ORCL dumpfile=file_path  
       full 
       =y table_exists_action= 
       replace 
      
 
       --常用版 
      
 
       impdp u_name/u_password@ip_address/space_name directory=my_director  
       full 
       =y dumpfile=my_dmp_file.DMP logfile=my_dmp_file.log table_exists_action= 
       replace 
       ; 
      
 
       --高级版 
      
 
       impdp u_name/u_password@ip_address/space_name directory=my_director  
       full 
       =y dumpfile=my_dmp_file.DMP logfile=my_dmp_file.log 
      
 
       [remap_schema=user_1:user_2] [remap_tablespace =table_space_1:table_space_2] [table_exists_action= 
       replace 
       ] 
      