Oracle笔记-2018-08-03 Oracle - sql

2018-08-02 控制用户访问
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