1.创建表空间
--删除用户drop user ICDMIPDEV cascade;
2.创建用户
/*
用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;