搭建一套ORACLE环境

1.创建表空间

/*
 用SYS用户加载
 加载前请先根据现场环境修改文件路径及大小
*/

--DROP TABLESPACE TBS_CSP_MIP_DAT INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TBS_CSP_MIP_DAT 
DATAFILE 'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat001' SIZE 1000M REUSE AUTOEXTEND OFF,
         'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat002' SIZE 1000M REUSE AUTOEXTEND OFF 
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

--DROP TABLESPACE TBS_CSP_MIP_IDX INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TBS_CSP_MIP_IDX 
DATAFILE 'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat003' SIZE 1000M REUSE AUTOEXTEND OFF,
         'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat004' SIZE 1000M REUSE AUTOEXTEND OFF 
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

--DROP TABLESPACE TBS_CSP_MIP_HISDAT INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TBS_CSP_MIP_HISDAT 
DATAFILE 'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat005' SIZE 1000M REUSE AUTOEXTEND OFF,
         'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat006' SIZE 1000M REUSE AUTOEXTEND OFF 
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

--DROP TABLESPACE TBS_CSP_MIP_HISIDX INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE TBS_CSP_MIP_HISIDX 
DATAFILE 'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat007' SIZE 1000M REUSE AUTOEXTEND OFF,
         'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat008' SIZE 1000M REUSE AUTOEXTEND OFF 
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

--DROP TABLESPACE TBS_CSP_MIP_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TBS_CSP_MIP_TEMP
TEMPFILE 'F:\oracle\product\10.2.0\oradata\ICDMIPDEV\mip_dat009' SIZE 1000M REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;  

--删除用户session
select sid,serial# from v$session where username='ICDMIP';  

alter system kill session '61,26168';  

--删除用户drop user ICDMIPDEV cascade;
2.创建用户

-- Create the user 
create user ICDMIPDEV identified by icdmip
  default tablespace TBS_CSP_MIP_DAT
  temporary tablespace TBS_CSP_MIP_TEMP
  profile DEFAULT;
-- Grant/Revoke object privileges 
grant execute on DBMS_LOCK to ICDMIPDEV;
grant read, write on directory DMPDIR to ICDMIPDEV with grant option;
-- Grant/Revoke role privileges 
grant connect to ICDMIPDEV;
grant dba to ICDMIPDEV;
grant exp_full_database to ICDMIPDEV;
grant imp_full_database to ICDMIPDEV;
grant resource to ICDMIPDEV;
-- Grant/Revoke system privileges 
grant create any table to ICDMIPDEV with admin option;
grant select any table to ICDMIPDEV;
grant unlimited tablespace to ICDMIPDEV;