需要create的都是对象 已学的对象：表 table

— 普通用户 只能查询user开头的数据字典

select tablespace_name from user_tablespaces;

— dba用户才能够查询

select tablespace_name from dba_tablespaces;

1、创建表空间

-- 创建表空间（需要管理员权限 dba用户） 
-- 语法解释
create tablespace  ts_test                    --表空间名字
  logging                                     --启动重做日志 nologging  加快表空间的创建速度
  datafile 'D:\app\oradata\orcl\ts_test.dbf'  --指定对应的数据文件的位置
  size 10m                                    --设置空间初始值大小
  autoextend on                               --数据文件自动扩展
  next 5m                                     --一次扩展的大小
  maxsize unlimited                           --数据文件最大容量：无限
  extent management local                     --表空间本地管理
  segment space management auto               --存储管理方式，auto为自动方式
     ;
--示例：创建表空间
create tablespace bruce01_tsp               
datafile 'C:\app\86136\oradata\orcl\bruce01_tsp.dbf' 
size 10m 
autoextend on 
next 5m 
maxsize unlimited;
--示例：创建临时表空间
create temporary tablespace bruce01_tsp_tmp
tempfile 'C:\app\86136\oradata\orcl\bruce01_tsp_tmp.dbf'
size 10m 
autoextend on 
next 5m 
maxsize unlimited;

2、删除表空间

-- 如果表空间有数据，需要加关键字 including contents  否则无法删除成功
--  and datafiles  删除表空间对应的文件
drop tablespace '表空间名字' including contents and datafiles;
drop tablespace bruce01_tsp including contents and datafiles;
drop tablespace bruce01_tsp_tmp including contents and datafiles;

3、表空间不足

-- 工作中经常遇到表空间不足，导致程序报错  你需要联系dba或者运维去解决这个问题
--  如果表空间不足,把表空间设置为自动扩展(可能刚开始创建表空间不是自动扩展的)
 alter database datafile   '.......'(数据文件全路径名)  
     autoextend on;  (在创建表空间时，就可以加上这个关键字，表示该表空间自动扩展)
-- 代码演示 
 alter database datafile 'C:\app\86136\oradata\orcl\bruce01_tsp.dbf'
     autoextend on; 
-- 查看表空间是否为自动扩展
 select file_name,autoextensible,increment_by 
   from dba_data_files 
  where tablespace_name = 'users'; 
-- 检查系统中所有表空间总体空间
select b.name
  ,sum(a.bytes / 1000000) as zshu 
  from v$datafile a, v$tablespace b
   where a.ts# = b.ts#
   group by b.name;
-- 查看剩余表空间大小
select tablespace_name   as 表空间
,sum(blocks*8192/1000000) as 剩余空间m
from dba_free_space
group by tablespace_name;
-- 查询oracle表空间的使用情况 (字节换算 byte kb m g)
select b.file_id                                           as 文件id,
       b.tablespace_name                                   as 表空间,
       b.file_name                                         as 物理文件名,
       b.bytes / 1000000                                   as 总字节数,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1000000          as 已使用,
       sum(nvl(a.bytes, 0)) / 1000000                      as 剩余,
       round(sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2)    as 剩余百分比
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
 group by b.tablespace_name, b.file_name, b.file_id, b.bytes
 order by b.tablespace_name;
-- 查看每个表table所占的空间
select segment_name, sum(bytes) / 1000000 as 所占空间m
  from user_extents
 group by segment_name
 order by sum(bytes) desc;

4、用户

1.查询用户

select * from dba_users;

2.锁/解锁 在dba用户下解锁普通用户

alter user SCOtt account unlock; -- 解锁用户

alter user SCOtt account lock;   -- 锁用户

3.创建用户

-- 语法解释
-- 创建新用户，同时指定表空间和临时表空间
create user testuser                  -- 创建用户 testuser
identified by pwd123456               -- 设置登录密码
default tablespace bruce01_tsp        -- 默认表空间 bruce01_tsp 即这个用户下面的对象存在这个表空间
temporary tablespace bruce01_tsp_tmp  -- 临时表空间
account unlock;                       -- 解锁用户 新创建的用户无需解锁直接登录

4.删除用户

 -- 表示删除用户 test_user01 ,同时删除用户下的所有数据对象
 drop user test_user01 cascade;

5.用户授权

-- 给用户授予最基本的权限 否则无法登录
grant connect,resource to test_user01;

-- 例子
给新建 erp 用户授予 dba 权限
grant dba to erp;

 -- with admin option 表示在获得权限的同时也获得了将这个权限授予其他用户的能力  参数可以省略
 grant dba to test_user01 with admin option;

 -- 回收权限
revoke dba from test_user01;

6.过期时间

-- oracle的安全策略方面默认一个用户的密码有效期为180天，我们可以直接把这个有效期调成永久

--先查询出用户所属的profile名字  默认 default
select username, profile from dba_users; 

-- 确认上面查询到的profile的密码有效期
select * from dba_profiles where profile = 'default' and resource_name='password_life_time';

-- 如果不想让密码过期 那就改成 unlimited 
alter profile default limit password_life_time unlimited; 

-- 密码有效期 90天
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;（亲测有效）

-- Oracle 11g 的【deferred_segment_creation】问题
alter system set deferred_segment_creation = false;

-- Oracle 11g 默认账号密码区分大小写，改为不区分
alter system set sec_case_sensitive_logon = false;

-- 设置最大连接数
alter system set processes=1000 scope=spfile;
alter system set sessions=1105 scope=spfile;

5、建表授权同义词

-- 新用户下面测试 新建表 给表建同义词 把表的权限授给其它用户
-- 工作的第一步就是 建表 授权 同义词


-- 建表
create table stu_infos
(
stu_no          number(10),        
stu_name        varchar2(10),  
sex             varchar2(2),
education       varchar2(10),      
home_address    varchar2(50),      
household_type  varchar2(10),
birthday        date
);


-- 不同的用户下面可以创建相同名字的表

-- test_user02 用户想去查询 test_user01 用户下面的表 
-- 发现查不到
select * from stu_infos;

-- 1> test_user01 用户授权给 test_user02用户
grant select,delete,update,insert on stu_infos to test_user02;

-- 授权之后test_user02用户还是查不到
select * from stu_infos;
-- 需要加 用户名  schema 才能够查到
select * from test_user01.stu_infos;

--  可不可以不带 schema 也可以查询呢？ 可以  必须创建共有同义词

-- 2> 创建共有同义词  同义词与表名一致 同义词是唯一的  这样就确保了这个表在整个库里面是唯一的
create public synonym stu_infos for test_user01.stu_infos;

-- 3> 去dba用户给当前用户授权  创建共有同义词的权限
grant create public synonym to test_user01;

-- 可以 但是需要创建共有同义词
select * from stu_infos;

6、序列

--- 创建一个序列
create sequence myseq
minvalue 1
start with 1
nomaxvalue
increment by 1
nocycle
cache 30;

--
--访问下一个值
select myseq.nextval from dual;

--访问当前值
select myseq.currval from dual;


-- 删除序列
drop sequence myseq;

--- 创建一个序列 没有任何参数
-- 有默认值  默认从1开始 增长是1
create sequence myseq;


-- 从0开始 增长是5
create sequence myseq
minvalue 0
start with 0
increment by 5;

-- 奇数
create sequence myseq
minvalue 1
start with 1
increment by 2;

-- 偶数
create sequence myseq
minvalue 0
start with 0
increment by 2;


-- 授权给到 test_user02 用户
grant select on myseq to test_user02;

-- 创建同义词
create  public synonym  myseq for test_user01.myseq;

-- 删除同义词
drop public synonym  myseq;

-- 无权限 去dba用户给当前用户受相应的权限
grant drop public synonym to test_user01;


-- SO2020091400000001  订单编号
select 'SO'||to_char(sysdate,'yyyymmdd')||lpad(myseq.nextval,8,0)
  from dual;  

-- 利用伪劣 level 一次性多查几行数据
select 'SO'||to_char(sysdate,'yyyymmdd')||lpad(myseq.nextval,8,0)
  from dual
 connect by level <= 10

7、普通视图

-- 视图的优点
1.安全性
2.屏蔽复杂性


-- 去到dba用户给scott用户授权
grant create view to scott;

-- 回收权限
revoke create view from scott; 

--
-- 删除视图
drop view emp_v;

-- 创建视图
-- 把一个查询结果集创建成一个视图
-- 视图是没有真实数据的 数据在基表
create view emp_v
    as 
 select * from emp;

-- 这个查询你想怎么写就怎么写
create view emp_v001
    as 
 select e.*,d.dname,d.loc 
   from emp  e
   join dept d on e.deptno = d.deptno;


-- 视图的查询
select * from emp_v;
select * from emp_v001;


-- 授删除共有同义词的权限
grant drop public synonym to scott;
-- 删除共有同义词
drop public synonym emp_v;
drop public synonym emp_v001;


--  创建同义词
create public synonym emp_v for scott.emp_v;
create public synonym emp_v001 for scott.emp_v001;


-- 视图的授权
-- 为什么只给查询的权限  安全性
grant select on emp_v to test_user01;
grant select on emp_v001 to test_user01;

8、物化视图

— 物化视图
— 与普通视图的区别：真实存在数据的 普通视图的数据在基表
物化视图看成是, 一个定时运行的计算JOB+一个存计算结果的表

创建时生成数据：
分为两种：build immediate 和 build deferred，
build immediate是在创建物化视图的时候就生成数据。
build deferred则在创建时不生成数据，以后根据需要在生成数据。
如果不指定，则默认为build immediate。

刷新模式：
物化视图有二种刷新模式：
在创建时refresh mode是 on demand 还是 on commit。
on demand 顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;
on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
一般用这种方法在操作基表时速度会比较慢。
创建物化视图时未作指定，则Oracle按 on demand 模式来创建。

上面说的是刷新的模式，针对于如何刷新，则有三种刷新方法：
— 增量或者全量
完全刷新（COMPLETE）： 会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
快速刷新（ FAST ）： 采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。
对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

关于快速刷新：Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。

物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。
还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

-- 去到dba用户给scott用户授权
 grant create materialized view to scott;

-- 回收权限
revoke create materialized view from scott; 


-- 创建一张测试源表
drop table emp_test;
create table emp_test as select * from emp;


-- 删除物化视图
drop materialized view emp_test_mv;


-- 创建物化视图
create materialized view emp_test_mv
refresh fast 
on commit 
as
select * from emp_test; 


-- 在源表创建主键  否则报错
alter table emp_test add constraint pk_emp_test_empno primary key(empno) ; 


-- 删除物化视图日志
drop materialized view log on emp_test;

-- 在源表建立物化视图日志  否则报错  日志指定为主键类型
create materialized view log on emp_test    
with primary key;


-- 验证
SELECT * FROM emp_test_mv;


-- 新增  修改 删除
update emp_test e set sal = 9999;
commit;


delete from emp_test e where e.deptno <> 10;
commit;


insert into emp_test select * from emp where deptno = 20;
commit;


-- 删除物化视图
drop materialized view emp_test_mv;


--  在代码里面指定物化视图的刷新时间
-- 在每天18：58进行刷新
create materialized view emp_test_mv
refresh fast 
start with sysdate
next to_date(concat(to_char( sysdate,'dd-mm-yyyy'),'19:05:00'),'dd-mm-yyyy hh24:mi:ss')  
as
select * from emp_test; 


-- 指定三分钟刷新一次
create materialized view emp_test_mv
refresh FAST     
START WITH  sysdate  
next SYSDATE + 1/24/20
as
select * from emp_test; 


-- 验证
select * from emp_test;
select * from emp_test_mv;

update emp_test set sal = 777;
commit;


-- 完全刷新不需要基于 物化视图的日志
-- 删除物化视图
drop materialized view emp_test_mv;

-- 删除物化视图日志
drop materialized view log on emp_test;

---

create materialized view emp_test_mv
refresh complete 
on commit 
as
select * from emp_test;