Windwo环境下在Oracle表空间、账号新建

 ----现记录在Window下新建Oracle11g表空间、账号的SQL,以方便日后使用

 

----删除旧账号、表空间
--DROP user NJDT_YY_DEV_V3 cascade;
--DROP TABLESPACE NJDT_YY_DEV_V3_DATA INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

 

--新建表空间

create tablespace NJDT_YY_DEV_V3_DATA
datafile 'F:/app/TyUsers/oradata/orcl/NJDT_YY_DEV_V3_DATA.dbf' size 100m autoextend on next 50m maxsize 10240m extent management local;

 

--新建账号

create user NJDT_YY_DEV_V3 identified by 123456 account unlock default tablespace NJDT_YY_DEV_V3_DATA TEMPORARY TABLESPACE TEMP;

 

--访问授权

grant connect,resource to NJDT_YY_DEV_V3;

 

--备份还原授权
grant read,write on directory DATA_PUMP_DIR  to NJDT_YY_DEV_V3;

 

--授权表、视图、函数等

grant create any table, view,trigger,index to NJDT_YY_DEV_V3;

 

--导入导出授权
grant EXP_FULL_DATABASE,IMP_FULL_DATABASE to NJDT_YY_DEV_V3;

 

附：

-- 查询数据库服务名称 service_name
select name as service_name from v$database;
-- 查询当前数据库实例名称 instance_name（SID）
select instance_name as SID from v$instance;