oracle 学习之路（一）

来源：互联网发布：奢侈品鉴定知乎编辑：程序博客网时间：2024/05/07 07:19

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);

0 0