oracle 创建用户及表空间

--1、查询用户下表空间位置
select * from v$datafile  
--2、删除表空间
drop user user2 cascade; 
drop tablespace user_DS_02 including contents and datafiles cascade constraints;
--3、添加表空间
select *From dba_data_files
--4、创建表空间，并默认添加数据文件，大小为2GB，不自动增长
CREATE  SMALLFILE TABLESPACE "user_DS_02" DATAFILE 'E:\temp\ORADB\ORADATA\user_ds_02.DBF' SIZE 1G 
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
-- 5、创建数据用户，用户名和密码默认都为“user”
CREATE USER user2 PROFILE "DEFAULT" IDENTIFIED BY user2 DEFAULT TABLESPACE user_DS_02 TEMPORARY TABLESPACE  TEMP  ACCOUNT UNLOCK;
-- 6、为user2用户分配权限
GRANT CONNECT TO user2;
GRANT RESOURCE TO user2;
grant dba to user2;
grant select any table to user2;
grant create any view to user2;
grant debug any procedure to user2;
grant debug connect session to user2;
grant unlimited tablespace to user2;
--7、只有读取其他用户表
create user JSETI_WZQ identified by abcdef;   -- 假设abcdef是密码 
grant connect,resource to JSETI_WZQ; 
grant select on LYSDC.ly_xxbz_gxjg_grtxfszl to JSETI_WZQ; 
grant select on LYSDC.ly_xxbz_gxjg_jzgjcsjzl to JSETI_WZQ; 

View Code

 

转载于:https://www.cnblogs.com/glingblog/p/6888445.html