[学习笔记] Oracle数据库基础之Oracle入门(二)

# 学习 # · 2021-03-04

表空间和用户权限管理

1、Oracle数据库包含逻辑和物理结构。

(1)数据库的物理结构:指构成数据库的一组操作系统文件。

(2)数据库的逻辑结构:指描述数据组织方式的一组逻辑概念及它们之间的关系。

2、表关系是数据库逻辑结构的一个重要组件。

(1)表空间可以存放各种应用对象,如表、索引。

(2)一个表空间可以与多个数据文件(物理结构)关联。

(3)一个数据库下可以建立多个表空间,一个表空间可以建立多个用户、一个用户下可以建立多个表。

3、表空间的分类:

(1)永久性表空间:一般保存表、视图、过程和索引等的数据。

(2)临时性表空间:用于保存系统中短期活动的数据,如排序数据等。

(3)撤销表空间:用于帮助回退未提交的事务数据。

4、表空间的目的:

(1)对不同用户分配不同的表空间,对不同的模式对象分配不同的表空间,方便对用户数据的操作,对模式对象的管理。

(2)可以将不同数据文件创建到不同的磁盘中,有利于管理磁盘空间,提高I/O性能,有利于备份和恢复数据。

5、表空间操作:

(1)创建表空间:

/*
 * 基本语法
 * tablespacename:需要创建的空间名称
 * datafile:指定组成表空间的一个或多个数据文件(逗号隔开)
 * filename:数据文件的路径和名称
 * size1:指定表空间的初始大小
 * autoextend:自动增长 ,当表空间存储都占满时,自动增长
 * size2:指定的是一次自动增长的大小。
 */
create tablespace tablespacename
datafile 'filename' [ size1 integer [K|M] ]
[ autoextend [OFF|ON] ] NEXT [ size2 integer [K|M] ];
 
create tablespace itcast 
datafile 'c:\itcast.dbf' size 100m
autoextend on next 10m;

(2)查看表空间:

/*
 * 基本语法
 */
select file_name,tablespace_name,bytes,autoextensible
from dba_data_files
where tablespace_name='itcast';

(3)删除表空间:

/*
 * 基本语法
 */
drop tablespace tablespacename;

(4)调整表空间大小:

/*
 * 基本语法
 */
 
--方法一:更改数据文件的大小
alter database datafile 'c:\itcast.dbf'
resize 80M;

--方法二:向表空间内添加数据文件
alter tablespace itcast
ADD datafile 'E:\DATA\tp_hr02.DBF' size 20M
autoextend ON;

(5)更改表空间状态为只读:

alter tablespace itcast READONLY;

6、Sys、System模式:

SYS用户SYSTEM用户
地位Oracle的一个超级用户Oracle默认的系统管理员,拥有DBA权限
作用主要用来维护系统信息和管理实例通常用来管理Oracle数据库的用户、权限和存储等
登录身份只能以SYSDBA或SYSOPER角色登录只能以Normal方式登录

7、自定义用户管理:

(1)创建用户:

/*
 * 基本语法
 * username:用户名
 * password:用户口类,不区分大小写
 * default tablespace_name:默认表空间
 * temporary tablespace_name:默认临时表空间
 */
create user username
identified by password
[default tablespace tablespace_name]
[temporary tablespace tablespace_name]

CREATE USER itcastuser
IDENTIFIED BY itcastuserpwd
DEFAULT tablespace itcast
TEMPORARY tablespace temp

(2)查看用户:

/*
 * 基本语法
 */
select * from dba_users where username='itcastuser';

(3)查看表空间限额:

/*
 * 基本语法
 */
select * from dba_ts_quotas where username='itcastuser';

(4)修改密码:

/*
 * 基本语法
 */
alter user itcastuser identified by newpwd;

(5)删除用户:

/*
 * 基本语法
 */
drop user itcastuser cascade;

(6)更改表空间中的用户限额:

/*
 * 基本语法
 */
alter user itcastuser quota 20M on itcast;

8、数据库权限管理:

(1)系统权限:指被授权用户是否可以连接到数据库上及在数据库中可以进行哪些系统操作。

CREATE SESSION    --连接到数据库
CREATE TABLE    --创建表
CREATE VIEW        --创建视图
CREATE SEQUENCE    --创建序列

(2)对象权限:指用户对数据库中具体对象所拥有的权限。

9、Oracle数据库用户获取权限的途经:

(1)管理员直接向用户授予权限。

(2)管理员将权限授予角色,再将角色授予一个或多个用户。

10、Oracle中常用的系统预定角色:

(1)CONNECT 角色:是授予最终用户的典型权利,最基本的。

ALTER SESSION     --修改会话
CREATE CLUSTER     --建立聚簇
CREATE DATABASE LINK     --建立数据库链接CREATE SEQUENCE --建立序列
CREATE SESSION     --建立会话CREATE SYNONYM --建立同义词CREATE VIEW --建立视图

(2)RESOURCE 角色:授予开发人员的。

CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型

(3)DBA 角色:拥有全部特权,是系统最高权限,只有 DBA 才可以创建数据库结构,并且系统权限也需要DBA 授出,且DBA 用户可以操作全体用户的任意基表,包括删除。

11、权限管理基本操作:

(1)授予权限:新创建的用户必须授予一定的权限才能进行相关数据库操作。

/*
 * 基本语法
 */
grant 权限|角色 TO 用户名

grant dba to itcast;  --授予DBA角色
grant SELECT ON scott.emp to itcast --允许用户查看 EMP 表中的记录

(2)撤销权限:

/*
 * 基本语法
 */
revoke 权限|角色 from 用户名

revoke dba from itcast;    --撤销DBA两个角色

12、数据库用户安全设计原则:

(1)数据库用户权限授权按照最小分配原则。

(2)数据库用户分为管理、应用、维护、备份四类用户。

(3)不允许使用Sys和System用户建立数据库应用对象。

(4)禁止授予用户dba角色。


序列

1、序列:用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。序列可以按升序或降序排列。

2、序列操作:

(1)创建序列:

/*
 * 基本语法
 * start with:要生成的第一个序列号。升序为最小值,降序为最大值
 * increment by:指定序列号之间的间隔,默认为1,正为升序负为降序
 * maxvalue:指定序列可以生成的最大值
 * nomaxvalue:升序序列最大值为10(27方),降序序列最小值为-1
 * minvalue:指定序列的最小值
 * nominvalue:升序序列最小值为1,降序序列最小值为-10(26方)
 * cycle:指定序列在达到最大最小值后,继续从头开始生成值
 * cache:为了加快访问速度预先分配序列号
 */
create sequence sequence_name
[start with integer]
[increment by integer]
[maxvalue integer|nomaxvalue]
[minvalue integer|nominvalue]
[cycle|nocycle]
[cache integer|nocache]

/* 创建序列。从序号10开始,每次增加1,最大为2000,不循环,再增加会报错 */
create sequence seqpersonid
start with 10
increment by 1
maxvalue 2000
nocycle
cache 30;

(2)访问序列:序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作。

/*
 * nextval:取得序列的下一个内容
 * currval:取得序列的当前内容
 */
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;

(3)更改序列:

/*
 * 基本语法
 */
alert sequence [schema.]sequence_name
[increment by integer]
[maxvalue integer|nomaxvalue]
[minvalue integer|nominvalue]
[cycle|nocycle]
[cache integer|nocache]

alter sequence seqpersonid
maxvalue 5000
cycle;

(4)删除序列:

/*
 * 基本语法
 */
drop sequence [schema.]sequence_name

drop sequence seqpersonid;

(5)使用序列:

--在插入数据时需要自增的主键中可以使用序列
insert into seqpersonid (toyid, toyname, toyprice)
values (seqpersonid.nextval, 'TWENTY', 25);

--使用SYS_GUID函数生成32位的唯一编码作为主键
select sys_guid() from dual;

同义词

1、同义词:同义词是现有对象的一个别名,不占用任何实际的储存空间。

2、同义词的用途:

(1)简化SQL语句。

(2)隐藏对象的名称和所有者。

(3)为分布式数据库的远程对象提供了位置透明性。

(4)提供对象的公共访问。

3、同义词的分类:

(1)私有同义词:只能在其模式内访问,且不能与当前模式的对象同名。

(2)公有同义词:可被所有的数据库用户访问。

4、同义词操作:

(1)创建私有同义词:

/*
 * 基本语法
 * or replace:同义词存在的情况下替换该同义词
 * synonym_name:要创建的同义词的名称
 * object_name:要为之创建同义词的对象的名称
 */
create [or replace] synonym [shema.]synonym_name
for [shema.]object_name;

/* 为A_hr模式下的employee表创建同义词创建同义词SY_EMP */
create synonym SY_EMP for A_hr.employee;
/* 访问同义词 */
select * from SY_EMP;

(2)创建共有同义词:

/*
 * 基本语法
 */
create [or replace] public synonym [shema.]synonym_name
for [shema.]object_name;

/* 在A_hr模式下创建公有同义词public_sy_emp作为A_hr用户employee表的别名 */
create public synonym public_sy_emp for employee;

/* 在A_oe模式下访问同义词 */
select * from public_sy_emp;

(3)删除同义词:

/*
 * 基本语法
 */
drop [public] synonym [shema.]synonym_name

/* 删除私有同义词 */
drop synonym A_oe.sy_emp;
/* 删除共有同义词 */
drop public synonym A_hr.public_sy_emp;

索引

1、索引:索引是与表关联的可选结构,用于加速数据存取的数据对象。合理的使用索引可以大大降低I/O次数,从而提高数据访问性能。

2、索引的分类:

物理分类逻辑分类
分区或非分区索引单列或组合索引
B树索引(标准索引)唯一或非唯一索引
正常或反向键索引基于函数索引
位图索引

(1)B树索引(标准索引):有助于按关键字值的升序和降序扫描索引。

(2)唯一索引:定义索引的列中任何两行都没有重复值。唯一索引中的索引关键字只能指向表中的一行,在创建主键约束和创建唯一约束时都会创建一个与之对应的唯一索引。

(3)非唯一索引:单个关键字可以有多个与其关联的行。

(4)反向键索引:在保持序列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值来实现。对于连续增长的索引列,反转索引列可以将索引数据分散在多个索引块间,减少I/O瓶颈的发生。

(5)位图索引:最适低基数列,对于大批即时查询,可以减少响应时间。相比其他索引技术,占用空间明显减少。位图索引不应当用在频繁发生DML操作的表上。

(6)组合索引:在表内多列上创建。索引中的列不必与表中的列顺序一致。

(7)基于函数的索引:使用函数或表达式。

3、索引应用:

(1)创建索引:

/*
 * 基本语法
 * unique:指定唯一索引,默认情况下为非唯一索引
 * index_name:所创建索引的名称
 * table_name:为之创建索引的表明
 * column_list:在其创建索引的列名的列表
 * tablespace_name:为索引指定表空间
 */
create [unique] index index_name on table_name (column_list)
[tablespace tablespace_name]

--在薪水级别(salgrade)表中,为级别编号(grade)列创建唯一索引
create unique index index_unique_grade on salgrade(grade);

--在员工(employee)表中,为员工编号(empno)列创建反向键索引
create index index_reverse_empno on employee(empno) REVERSE;

--在员工(employee)表中,为工种(job)列创建位图索引
create bitmap index index_bit_job on employee(job);

--在员工(employee)表中,为员工名称(ename)列创建大写函数索引
create index index_ename on employee(upper(ename));

(2)删除索引:

/*
 * 基本语法
 * ①应用程序不再需要索引时删除索引
 * ②执行批量加载前删除索引
 * ③索引损坏时删除索引
 */
drop INDEX index_name;

(3)重建索引:

/*
 * 基本语法
 * ①用户表被移动到新的表空间时需要重建索引
 * ②索引中包含多项已删除的项时需要重建索引
 * ③需将现有的正常索引转换成反向键索引时重建索引
 */

--将反向键索引更改为正常B树索引
alter index index_reverse_empno rebuild noreverse;

--将索引移到指定表空间
alter index index_name rebuild tablespace tablespace_name;

4、索引使用原则:

(1)表中导入数据后再创建索引,否则每次表中插入数据时都必须更新索引。

(2)在适当的表和字段上创建索引:如果经常检索的数据少于表中的15%,需要创建索引。

(3)限制表中索引的数目:索引越多,在修改表时对索引做出修改的工作量越大。


分区表

1、分区表:Oracle允许用户把一个表中的所有行分为几个部分,并将这些部分储存在不同的位置。被分区的表称为分区表,分成的每个部分称为一个分区。

2、表分区的优点:

(1)用户可以执行查询,只访问表中的特定分区。

(2)将不同的分区存储在不同的磁盘,提高访问性能和安全性。

(3)可以独立地备份和恢复每个分区。

3、表分区的条件:

(1)数据量大于2GB。

(2)已有的数据和新添加的数据有明显的界限划分。

(3)要分区的表不能具有LONG和LONG RAW数据类型的列。

4、分区表的分类:

(1)范围分区(range):以列的值的范围作为分区的划分条件,将记录存放到列值所在的range分区中。

create table SALES1(
    sales_id NUMBER,
    product_id VARCHAR2(5),
    sales_date DATE NOT NULL
)
partition by range(sales_date)
(
   partition P1 values less than(to_date('2013-04-1', 'yyyy-mm-dd')),
   partition P2 values less than(to_date('2013-07-1', 'yyyy-mm-dd')),
   partition P3 values less than(to_date('2013-10-1', 'yyyy-mm-dd')),
   partition P4 values less than(to_date('2014-01-1', 'yyyy-mm-dd')),
   partition P5 values less than(maxvalue)
);

--要查看在第三季度的数据
select * from SALES1 partition(P3);

--要删除第三季度的数据
delete from SALES1 partition(P3);

(2)间隔分区:Oracle11g新引入的分区方法,可以实现范围分区的自动化。

--创建间隔分区表
create table SALES2(
       sales_id NUMBER,
    product_id VARCHAR2(5),
    sales_date DATE NOT NULL
)
partition by range(sales_date)
interval(NUMTOYMINTERVAL(3,'MONTH'))
(partition P1 VALUES less then(to_date('2013-04-1','yyyy/mm/dd')));

--插入数据
insert into sales values(1,'a',to_date('2013-08-1'),10,'1');

--获得分区情况
select table_name,partition_name from user_tab_partitions
where table_name=upper('sales');

--查询输出结果,系统自动根据输入数据情况创建新分区“SYS_P82”
TABLE_NAME    PARTITION_NAME
----------------------------
SALES         P1
SALES         SYS_P82

--查询分区数据
select * from sales2 partition(sys_P82);
如无特殊说明,本博所有文章均为博主原创。

如若转载,请注明出处:一木林多 - https://www.l5v.cn/archives/235/

评论