创建表空间
create tablespace MRMS_ODS_DATA logging datafile 'D:\oracleDirectory\oradata\MRMS_ODS_DATA.dbf' size 1024m autoextend on next 1024m maxsize 20480m extent management local;
create tablespace TS_SCFX logging datafile 'D:\oracleDirectory\oradata\TS_SCFX.dbf' size 1024m autoextend on next 1024m maxsize 20480m extent management local;
创建临时表空间
create temporary tablespace TEMPORARY_DATA_TEMP tempfile 'TEST_TEMPORARY.DBF'
size 500M autoextend on;
删除用户
drop user etl cascade;
删除表空间
drop tablespace MRMS_ODS_DATA including contents and datafiles;
drop tablespace TS_SCFX including contents and datafiles;
创建用户并指定表空间
create user etl identified by etl default tablespace MRMS_ODS_DATA ;
create user mrd identified by mrd default tablespace TS_SCFX ;
创建用户并指定临时表空间
create user test
identified by oracle
default tablespace MRMS_ODS_DATA
temporary tablespace TEMPORARY_DATA_TEMP
profile default;
给用户授予权限
粗粒度
grant connect,resource,dba to etl;
grant connect,resource,dba to mrd;
细粒度
grant connect to etl;
grant resource to etl;
grant unlimited tablespace to etl;
grant select on A.DEPT to B;
grant execute on test_procedure to etl;
更改用户默认表空间
alter user etl default tablespace MRMS_ODS_DATA ;
alter user mrd default tablespace TS_SCFX ;
启用设置
shutdown immediate
startup