–用户操作–
create user user1 identified by 123;
drop user user1 cascade;
alter user user1 account lock/unlock;
–授权操作–
1.系统权限
grant connect,resource,dba to user1;
权限可传递(系统权限,无级联)
grant connect,resource,dba to user1 *with admin option*;
revoke connect from user1;
2.实体权限
grant execute,index,insert,delete,update,select,references,alter to user1 *with grant option*;
–角色操作–
create role role1;
grant connect,resource,dba *on 表* to role1;
grant role1 to user1;
alter role role1 identified by 111;
–资源文件profile–
create profile profile1 LIMIT FAILED_LOGIN_ATTEMPTS 3;
分配给用户
alter user user1 profile profile1;
–表空间–
create /temporay tablespace my_tablespace tempfile|datafile ‘xxx.dbf’/’d://yyy.dbf’ size 10m autoextend on next 5m maxsize 100m;
表空间+用户
create user user2 default|temporay tablespace my_tablespace identified by 123;
alter user user1 default|temporay tablespace my_tablespace;
联机脱机、只读读写
alter tablespace my_tablespace online/offline *read only/read write*;
增加表物理文件
alter tablespace my_tablespace add datafile ‘xxx2.dbf’ size 8m;
删除表物理文件(不可删除第一个数据文件),是否包含数据文件
drop tablespace 表空间名称 including contents and datafiles cascade constraint;
alter tablespace my_tablespace drop ‘xxx2.dbf’ *including contents*;
(持续更新)
查询篇(非专门DB程序员也必须掌握的技巧)
2018-07-21更新一条有意思的查询
select * from score a where degree <(
select avg(degree) from score b where a.sno = b.sno
);
*列出成绩比该课程平均成绩低的同学的成绩 之前想了连接查询查一个表两次,但不知道下手,其实可以嵌套一个表查两次
未经授权,禁止转载。