1,创建用户
create user userName identified by password;
2.给用户赋权限和角色
权限:
- grant dba to lxg;–授予DBA权限
- grant unlimited tablespace to lxg;–授予不限制的表空间
- grant select any table to lxg;–授予查询任何表
- grant select any dictionary to lxg;–授予 查询 任何字典
- 给用户加锁 alter user scott account lock
- 给用户解锁 alter user scott account unlock;
- 修改用户密码 alter user zzg identified by zzg123
- 删除用户及相关对象 drop user zzg cascade
- 给用户赋权(多个采用逗号间隔) grant create session,create table to zzg
- 分配表空间给用户 alter user zzg default tablespace ts_zzg
可能出现的问题:
oracle 正在连接的用户不能删除,确实要删除的话
1、select sid,serial#,username from v$session where user=’USERNAME’;
2、alter system kill session ‘sid,serial#’;
3、drop user username cascade;
角色:
grant connect,resource to TEST
3.创建表并插入一组数据
reate table T_Employee(
FNumber VARCHAR(20),
FName VARCHAR(20),
FAge int,
FSalary NUMERIC(10,2) ,
PRIMARY KEY(FNumber));
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘DEV001’,’Tom’,25,8300);
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘DEV002’,’Jerry’,28,2300.80);
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘SALES001’,’John’,23,5000);
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘SALES002’,’Kerry’,28,6200);
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘SALES003’,’Stone’,22,1200);
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘HR001’,’Jane’,23,2200.88);
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘HR002’,’Tina’,25,5200.36);
insert into T_Employee(FNumber,FName,FAge,FSalary) values(‘IT001’,’Smith’,28,3900);
insert into T_Employee(FNumber,FAge,FSalary) values(‘IT002’,27,2800);