Oracle数据库应用
表空间分类
类别 | 举例 | 说明 |
---|---|---|
永久性表空间 | SYSTEM,USERS | 一般保存表、视图、过程和索引等的数据 |
临时性表空间 | TEMP | 只用于保存系统中短期活动的数据 |
撤销表空间 | UNDO | 用来帮助回退未提交的事务数据 |
PS:一般不需要建临时和撤销表空间,除非把它们转移其他磁盘中以提高性能
创建表空间
-
基于应用性能和管理方面的考虑,最好为不同的子系统创建独立的表空间
-
通过CREATE TABLESPACE命令创建表空间
/*语法*/ CREATE TABLESPACE tablespacename{ DATAFILE 'filename' [SIZE integer[K|M]] [AUTOEXTEND[OFF|ON]]; } /*示例*/ CREATE TABLESPACE tp_hr{ DATAFILE 'd:\data\tp_hr01.dbf' SIZE 60M; }
修改表空间
-
调整表空间大小
--方法一:更改数据文件的大小 ALTER DATABASE DATAFILE 'D:\DATA\tp_hr01.dbf' RESIZE 80M; --方法二:像表空间内添加数据文件 ALTER TABLESPACE tp_hr ADD DATAFILE 'E:\DATA\tp_hr02.dbf' SIZE 20M AUTOEXTEND ON;
-
更改表空间状态只读
ALTER TABLESPACE tp_hr READONLY;
删除表空间
-
删除表空间前先备份再删除
-
通过DROP TABLESPACE命令创建表空间
--语法 DROP TABLESPACE tablespacename [INCLUDING CONTENTS]; --示例 DROP TABLESPACE tp_hr INCLUDING CONTENTS;
登录管理后台
-
SYS和SYSTEM用户都是Oracle的系统用户,它们都使用SYSTEM表空间,SYS拥有更大的权限
SYS用户 SYSTEM用户 地位 Oracle的一个超级用户 Oracle默认的系统管理员,拥有DBA权限 作用 主要用来维护系统信息和管理实例 通常用来管理Oracle数据库的用户、权限和存储等 登录身份 只能以SYSDBA或SYSOPER角色登录 只能以Normal方式登录
创建用户
CREATE USER user
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace]--指定表空间
[TEMPORARY TABLESPACE tablespace]--指定临时表空间
[QUOTA {integer [K|M]|UNLIMITED}ON tablespace--限制语句,该用户在改表空间中的可用大小
[QUOTA {integer [K|M]|UNLIMITED}ON tablespace]...]
[PASSWORD EXPIRE]--密码有效时间
建议:为用户指定默认表空间或临时表空间
权限和角色
用户必须赋予相应的权限
- 权限指执行特定类型SQL命令或访问其他对象的权利
- 系统权限和对象权限
- 系统权限允许用户执行某些数据库操作
- 对象权限允许用户对某一特定对象执行特定的操作
为了简化权限管理,引入了角色的概念
- 角色是具有名称的一组权限的组合
- 常用系统预定义角色
- CONNECT:临时用户
- RESOURCE:更为可靠和正式的用户
- DBA:数据库管理员角色,拥有管理数据库的最高权限
--语法
--分配权限或角色
GRANT privileges or role TO user;
--撤销权限或角色
REVOKE privileges or role FROM user;
--示例
--授予CONNECT和RESOURCE两个角色
GRANT connect,resource TO A_hr;
--撤销CONNECT和RESOURCE两个角色
REVOKE connect,resource TO A_hr;
--允许用户查看 EMP 表中的记录
GRANT SELECT ON SCOTT.emp TO A_hr;
--允许用户更新 EMP 表中的记录
GRANT UPDATE ON SCOTT.emp TO A_hr;
序列
-
序列是用于生成唯一、连续序号的对象
-
序列可以是升序的,亦可以是降序的
-
使用CREATE SEQUENCE语句创建序列
--示例 CREATE SEQUENCE toys_seq START WITH 10 --指定第一个序号从10开始 INCREMENT BY 10 --指定序号之间间隔为10 MAXVALUE 2000 --最大值2000 MINVALUE 10 --最小值10 NOCYCLE --是否允许循环(CYCLE[肯定]) CACHE 10; --指定内存中预先分配的序号数
访问序列
-
通过序列的伪列来访问序列的值
-
NEXTVAL 返回序列的下一个值
-
CURRVAL 返回序列的当前值
INSERT INTO toys(toyid,toyname,toyprice) VALUE(toys_seq.NEXTVAL,'TWENTY',25); SELECT toys_seq.CURRVAL FROM dual;
-
注意:创建序列后不能直接使用seq.CURRVAL!
-
-
SYS_GUID函数
- 生成32位的唯一编码作为主键
-
与SYS_GUID函数区别
- 在不需要并行的环境中使用序列作为主键
- 在并行的环境里或者希望避免使用序列的情况下使用函数
更改和删除序列
使用ALTER SEQUENCE语句修改序列,不能更改序列的START WITH参数
ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;
使用DROP SEQUENCE语句删除序列
DROP SEQUENCE toys_seq;
同义词
-
同义词是现有对象的一个别名
- 简化SQL语句
- 隐藏对象的名称和所有者
- 提供对对象的公共访问
-
同义词共有两种同义词
-
私有同义词
-
只能在其模式内访问,且不能与当前模式的对象同名
--CREATE [OR REPLACE] SYNONYM emp(SCOTT.emp的别名) FOR SCOTT(模式名).emp(表名) CREATE OR REPLACE SYNONYM emp FOR SCOTT.emp;
-
-
公有同义词
-
可被所有的数据库用户访问
--CREATE PUBLIC SYNONYM public_sy_dept(同义词名称) FOR SCOTT.dept; CREATE PUBLIC SYNONYM public_sy_dept FOR SCOTT.dept;
删除同义词
DROP SYNONYM emp; DROP PUBLIC SYNONYM emp_syn;
-
-
索引
索引分类
物理分类 | 逻辑分类 |
---|---|
分区或非分区索引 | 单列或组合索引 |
B树索引 | 唯一或非唯一索引 |
正常或反向键索引 | 基于函数索引 |
位图索引 |
-
B树索引结构
CREATE INDEX index_id ON t(id);
-
位图索引
CRETAE BITMAP INDEX index_bit_job ON emp(job);
-
位图索引适合创建在低基数列上
-
位图索引不能直接存储ROWID,而是存储字节到ROWID的映射
-
减少响应时间,节省空间占用
-
B树索引和位图索引的比较
B树索引 位图索引 适用于高基数列 适用于低基数列 在键上更新相对廉价 更新键行非常昂贵 使用OR谓词的查询效率很低 使用OR谓词的查询效率很高 行级锁定 位图段级锁定 存储较多 存储较少
管理索引
- 索引使用原则
- 表中导入数据后再创建索引,否则每次表中插入数据时都必须更新索引
- 在适当的表和字段上创建索引
- 如果经常检索的数据少于表中的15%,需要创建索引
- 限制表中索引的数目
- 索引越多,再修改表时对索引做出修改的工作量越大
表分区
-
允许用户将一个表分成多个分区
-
用户可以执行查询,只访问表中的特定分区
-
将不同的分区存储在不同的磁盘,提高访问性能和安全性
-
可以独立地备份和恢复每个分区
范围分区
-
以表中的一个列或一组列的值的范围分区
--语法 PATITION BY RANGE(column_name) ( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), ...... [PARTITION partN VALUE LESS THAN(MAXVALUE)] ); --示例 PATITION BY RANGE(sales_date) ( PARTITION P1 VALUE LESS THAN(to_date('2020-01-01','yyyy-mm-dd'), PARTITION P2 VALUE LESS THAN(to_date('2020-01-11','yyyy-mm-dd'), PARTITION P3 VALUE LESS THAN(to_date('2020-01-21','yyyy-mm-dd'), PARTITION P4 VALUE LESS THAN(to_date('2020-01-31','yyyy-mm-dd'), PARTITION P5 VALUE LESS THAN(MAXVALUE) );
以列的值的范围来作为分区依据
间隔分区
-
实现范围分区的自动化
--语法 PARTITION BY RANGE(column_name) INTERVAL(NUMTOYMINTERVAL(n,'interval_unit')) (PARTITION P1 VALUES LESS THAN(range1));
- INTERVAL代表“间隔”,按照后面括号中的定义间隔添加分区
- NUMTOYMINTERVAL(n,‘interval_unit’)函数
- 将n转换成interval_unit所指定的值
- interval_unit可以为:YEAR,MONTH
-