oracle创建表空间用户授权

oracle创建表空间用户授权

-- 查询dmp数据文件存储路径
select directory_path from dba_directories where directory_name =  'DATA_PUMP_DIR';
select * from dba_directories;

-- 删除表空间
 /*查询所有表空间物理位置*/
 select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;
 /*删除空的表空间，不包含物理文件*/
 drop tablespace GCFR;
 /*删除非空表空间，不包含物理文件*/
 drop tablespace tablespace_name including contents;
 /*删除非空表空间，包含物理文件*/
  drop tablespace GCFR including contents and datafiles;
 /*如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS*/
 drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
-- 创建表空间示例如下：
select * from dba_tablespaces t WHERE tablespace_name = 'GCFR';
create tablespace GCFR
datafile '/home/oracle/export/GCFR.dbf' size 2G
autoextend on next 200M maxsize unlimited logging
extent management local autoallocate
segment space management auto;

-- 创建用户，
-- 1.默认表空间是users
select * from dba_users ;
create user gla_test identified by gla_test;
grant connect,resource,dba to gla_test;
-- 2.指定表空间是G1CLOUD
create user gla_test02
  identified by gla_test02
  default tablespace GCFR
  temporary tablespace TEMP
  profile DEFAULT;

select * from dba_users WHERE USERNAME IN ('GLA_TEST','BGTPM','GLA_TEST02');
-- 删除数据库
drop user gla_test02 CASCADE;
-- 以下批量执行需要在plsql中
grant read,write on directory DATA_PUMP_DIR to gla_test02;
grant connect to gla_test02 with admin option;
grant dba to gla_test02;
grant exp_full_database to gla_test02;
grant imp_full_database to gla_test02;
grant javasyspriv to gla_test02;
grant java_admin to gla_test02;
grant resource to gla_test02 with admin option;
-- Grant/Revoke system privileges
grant administer database trigger to gla_test02 with admin OPTION;
grant alter any index to gla_test02;
grant alter any procedure to gla_test02;
grant alter any table to gla_test02;
grant alter session to gla_test02;
grant alter tablespace to gla_test02;
grant analyze any to gla_test02;
grant audit any to gla_test02;
grant comment any table to gla_test02;
grant create any index to gla_test02;
grant create any materialized view to gla_test02 with admin option;
grant create any procedure to gla_test02;
grant create any sequence to gla_test02;
grant create any synonym to gla_test02 with admin option;
grant create any table to gla_test02 with admin option;
grant create any trigger to gla_test02;
grant create any view to gla_test02 with admin option;
grant create cluster to gla_test02;
grant create database link to gla_test02;
grant create job to gla_test02;
grant create library to gla_test02;
grant create materialized view to gla_test02;
grant create procedure to gla_test02;
grant create profile to gla_test02;
grant create sequence to gla_test02;
grant create session to gla_test02;
grant create synonym to gla_test02;
grant create table to gla_test02;
grant create trigger to gla_test02;
grant create type to gla_test02;
grant create view to gla_test02;
grant debug any procedure to gla_test02;
grant debug connect session to gla_test02;
grant delete any table to gla_test02 with admin option;
grant drop any index to gla_test02;
grant drop any materialized view to gla_test02;
grant drop any synonym to gla_test02 with admin option;
grant drop any table to gla_test02;
grant drop any view to gla_test02;
grant insert any table to gla_test02;