oracle 常用操作

oracle 常用操作

1. 创建表空间
   一般在创建用户和相关表时都会先创建表空间，然后再创建用户，并指定该用户操作的表空间为新创建的表空间，防止不同的用户之间操作表导致混乱的问题，表空间与表空间之间的表可以重名。下面是具体实现：（需要先登录管理员账号在 sqlplus 工具命令行下操作）

   DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;--删除test表空间
   
   create tablespace TEST -- 创建表空间 TEST 
   datafile 'D:\oracle\oradata\orcl\TEST.dbf' size 1024M -- 指定具体的物理文件路径为 D:\oracle\oradata\orcl 下，物理文件为 TEST.dbf （与表空间名尽量保持一致，这样不需要额外的说明文档就知道对应关系），size 参数为初始时指定的文件大小
   autoextend on next 10M maxsize unlimited   --每次扩展10M，无限制扩展
   

2. 创建用户及指定表空间

   drop user test cascade; --删除test用户
   create user test --创建 test 用户 
   identified by test -- 密码为test
   default tablespace TEST; --默认操作表空间为TEST
   alter user test quota unlimited on TEST; 修改用户操作表空间到TEST表空间
   

3. 授权

   grant connect,resource to test;
   grant dba to test;
   grant create any procedure to test;
   grant create any procedure to test;
   grant create any trigger to test;
   grant create any view to test;
   grant create any index to test;
   grant create any SYNONYM to test;
   GRANT CTXAPP TO test;
   GRANT EXECUTE ON CTXSYS.CTX_CLS TO test;
   GRANT EXECUTE ON CTXSYS.CTX_DDL TO test;
   GRANT EXECUTE ON CTXSYS.CTX_DOC TO test;
   GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO test;
   GRANT EXECUTE ON CTXSYS.CTX_QUERY TO test;
   GRANT EXECUTE ON CTXSYS.CTX_REPORT TO test;
   GRANT EXECUTE ON CTXSYS.CTX_THES TO test;
   GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO test;
   

4. 操作目录
   oracle 中的目录用来对应实际的物理路径目录，对应关系存储在表 dba_directories，可使用语句 select * from dba_directories; 查询当前已经存在的目录记录

   create directory dataDir AS 'E:\dataDir'; --创建dataDir目录对应实际物理路径为E:\dataDir（应先在e盘下创建dataDir目录）
   Grant read,write on directory dataDir to test; --授权test用户对dataDir目录有读写权限
   

5. 导入导出
   一般都导入导出 dmp 文件用来备份迁移数据，oracle 本身提供两个导出工具：exp 导出对应 imp 导入（数据量较少时使用）、expdp 导出对应 impdp 导入，前者不需要使用目录，后者需要使用目录（导出导入文件存放位置）

   1. exp 导出、imp 导入 更多参考

      # 导出命令 exp
      exp 用户名/用户密码@服务名 file=保存路径.dmp log=日志名.log owner=用户名；
      # 导出test用户的命令如下：
      exp test/test@orcl file=e:/test_exp20180218.dmp log=e:/test_exp_log20180218.log owner=test;
      
      # 导入命令 imp
      imp 用户名/密码@实例名 file=路径.dmp  fromuser=导出用户   touser=导入用户   log=日志路径
      --假如需要忽略已存在表添加ignore=y
      
      # 导入 test 
       imp test/test@orcl file=e:/test_exp20180218.dmp log=e:/test_imp_log20180218.log full=y
      --当导出和导入的用户不一致时需要使用 fromuser=导出用户   touser=导入用户
      

   2. expdp 导出、impdp 导入 更多参考

   # 导出命令 expdp
   expdp 用户名/密码@实例名 directory=目录名 DUMPFILE=文件名.dmp schemas=用户名 logfile=日志名.log version=10.2.0.1.0(导入数据版本号,相同版本不需要此参数，主要是为了解决高版本导出向低版本导入的问题)
   #  导出test用户的命令如下：
   expdp test/test@orcl directory=dataDir DUMPFILE=test_expdp20180218.dmp schemas=test logfile=test_expdp_log20180218.log
   
   # 导入命令 impdp
   impdp 用户名/密码 directory=目录名 dumpfile=dmp文件名.DMP nologfile=y（不需要日志） tables=zlhis.dept remap_schema=导出用户:导入用户（重新映射用户，相同可省略，多个逗号分隔） remap_tablespace=导出表空间:导入表空间,导出表空间2:导入表空间（重新映射表空间，相同可省略，多个逗号分隔） table_exists_action=truncate（表已经存在的动作 有效关键字: (SKIP), APPEND, REPLACE 	和 TRUNCATE） exclude=object_grant（跳过对象授权）
   
   # 导入 test
   impdp test/test directory=dataDir dumpfile=test_expdp20180218.dmp table_exists_action=replace remap_tablespace=test:test logfile=test_impdp_log20180218.log;