2018-08-02 控制用户访问
1.创建用户
create user u1 identified by oracle;
刚建立好之后不能登录,需要授予权限
2.授予权限
(10g)
grant create session to u1;
grant create table to u1;
grant unlinited tablespace to u1;
(11g)
grant create table to u1;
11g创建完用户后,默认有表空间使用权限
(12c cdb)
create user c##u1 identified by oracle;
grant create session to c##u1;
grant create table to c##u1;
(12c pdb)
create user u1identified by oracle;
conn u1/oracle@wyzc.com:1524/pdbwyzc as sysdba;
grant create session to u1;
grant create table to u1;
2.查看当前用户系统权限
select * from user_sys_privs;
3.回收系统权限
conn / as sysdba
select * from dba_sys_privs where grantee='u1';//dba查看某个用户的权限
revoke create table from u1;u1是表的名字
3.将授权的权限分出
grant create table to scott with admin option;
scott用户可以将create table的权限售给别的权限
4.对表的权限
grant select on scott.emp to u1;
grant update(sal) on scott.emp to u1;
grant select on scott.dept to u1;
revoke select on scott.dept to u1;
grant select on scott.dept to u1 with grant option;//级联授权
对于select insert等的语句级联授权后,如果对于二级权限回收那么二级往下授予的权限将全部被级联收回
5.角色管理
分别对用户分权限很麻烦,多以使用组管理
create role;
grant priviliges to a role;
desc dba_roles;
select role from dba_roles;
role_role_privs
select * from role_role_peivs where role='CONNECT';
select * from role_sys_peivs where role='CONNECT';
select * from role_role_peivs where role='CONNECT';
第22章 管理模式对象
1.对一个表新加一个列
create table e4 as select * from emp;
alter table e4 add(dname varchar2(14));
update e4 set dname=(select dname from dept where depyno=e4.deptno);
2.删除一个列
有限面两种写法
alter table e4 drop (dname);
alter table e4 drop column dname;
如果数据量很大的话直接使用drop会hang在那里等很长时间,多以一般采用打标签的方法,等系统资源使用量不大的时候在drop掉打标签的列
alter table e4 set unused (comm);
alter table e4 set unused column mgr;
alter table e4 drop unused columns;
alter table e4 modify(empno primary key);
有约束的列是不能直接删除的
alter table dept drop (deptno) cascade constraints;
3.11g开始可以设置表为只读
alter table emp read only;
alter table emp read write;
4.索引重建
alter index al_id_i rebuild;
5.flashback回收站
drop tatble salagrade;
show recyclebin;
flashback table salgrade to before drop;
sys用户删除后无法回复
drop table table_name purge;
加了purge关键词后就是永久删除了
6.外部表 临时表
create directory exe as 'tmp';
23章 数据字典
查看有多少数据字典
select * from dict;
select * from dictionary;
知道当前用户有哪些对象
select object_name,object_type,status from user_objects;
与表相关的三个大类
talbe--->
dba_ all_ user_
all_ user_
user_tables all_tables
user_tab_columns all_tab_columns
index--->
user_indexes user_ind_columns
all_indexes all_ind_columns
constraint--->
user_constraints user_cons_columns
all_constraints all_cons_columns
view --->
user_views all_views
sequence --->
user_sequences all_sequences
synonym --->
user_synonmys user_synonyms user_synonmys
directory --->
user_directories all_directories
查看表的约束
select table_name,column_name,index_name,status from user_indexes a natural join user_ind_columns;
comment 注释
给一个表加注释
comment on table dept is 'deptment table';
查看表的注释
select * from user_tab_comments;
查看列的注释
select * from user_col_comments;
给一个列加注释
comment on column dept.dname is 'dept name';
select * from user_col_comments where table_name='DEPT';
取消注释
comment on column dept.dname is '';
2018-08-03
24章 生产环境中的海量数据
select ename from emp natural join (select deptno,max(sal) from emp group by deptno);
1.嵌入式视图
select ename,sal,(select max(sal) from emp) msal from emp;
create table e1 al select * from emp where 0=1;
insert into e1 select * from emp;
alter table e1 add (dname varchar2(14));
update e1 set dname=(select dname from dept where deptno=e1.deptno);
delete e1 where deptno in (select deptno from dept where loc='CHICAGO');
新的知识点
1.insert all和insert first
insert all into tab1(col1,...)
into tab2(col1,...)
select * from tabname;
加条件的插入insert all
insert all when sal>2000 then into ea1 values(empno,ename,sal,deptno)
when sal>2500 then into ea2 values(empno,ename,sal,deptno)
select * from emp;
上面的语句相当于
insert into ea1 select * from emp where sal>2000;
insert into ea2 select * from emp where sal>2500;
加条件的插入 insert first优先插入法
insert first when sal>2000 then into ea1 values(empno,ename,sal,deptno)
when sal>2500 then into ea1 values(empno,ename,sal,deptno)
select * from emp;
这个语句相当于下面的意思
insert into ea1 select * from emp where sal>2000;
insert into ea2 select * from emp where sal<=2000 and sal>2500;
这个语句的意思就是优先取第一条的条件,第一条取走记录后,第二条再取,但是第二条取的时候要注意是第一条记录取反然后合并上第二条,也可以理解为第一次first已经把记录拿走了,所以第二条只能那第一条剩下的,就相当于是第一条记录取反在合并第二条
行转列的插入
create table ea3(id number,week number,w1 number,w2 number,w3 number,w4 number,w5 number);
insert into ea3 values(1,10,800,1200,900,1500,2000);
select * from ea3;
这个时候到的记录是行的记录,创建新表进行转换
create table ea3info (id number,week,number,sales number);
insert all into ea3info values(id,week,w1)
into ea3info values(id,week,w2)
into ea3info values(id,week,w3)
into ea3info values(id,week,w4)
into ea3info values(id,week,w5)
select * from ea3;
2.跟踪数据块的变化
select id,versions_starttime,versions_opration from ea3 versions between scn minvalue and maxvalue where id=1;
不加where 条件就可以查看这个表被做过的全部操作
3.merge
merge into ea1 using ea2 on (ea1.empno-ea2.empno)
when matched then
update set sal=ea2.sal
when not matched then
insert values(ea2.empno.ea2.ename.ea2.sal,ea2.deptno);
25章 时区对的不同
了解一下就行
v$timezone_name
show parameter time_zone
select dbtimezone from dual;
select sessiontimezone from dual;
desc v$timezone_names