Oracle初识

Oracle表空间#

基本概念#

• ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构
• 一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件
• 表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段
• 每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的

SYSTEM表空间#

• system表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息
• 关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)
• 一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便

便于理解，把oracle数据库看作一个实在房间，表空间可以看作这个房间的空间，是可以自由分配

在这空间里面可以堆放多个箱子（箱子可以看作数据库文件），箱子里面再装物件（物件看作表）

用户指定表空间也就是你希望把属于这个用户的表放在那个房间（表空间）里面。

表空间是一个虚拟的概念可以无限大，但是需要由数据文件作为载体。

Segment（段）#

段是指占用数据文件空间的通称，或数据库对象使用的空间的集合；段可以有表段、索引段、回滚段、临时段和高速缓存段等。

Extent（区间）#

• 分配给对象（如表）的任何连续块叫区间；
• 区间也叫扩展，因为当它用完已经分配的区间后，再有新的记录插入就必须在分配新的区间（即扩展一些块）
• 一旦区间分配给某个对象（表、索引及簇），则该区间就不能再分配给其它的对象.

查看表空间：

select * from v$tablespace;

表空间的好处#

• 用户数据与数据字典数据分离，减少竞争
• 应用程序之间的数据分离，防止某个TableSpace脱机后对多个程序造成影响
• 不同磁盘驱动器上存储数据，减少I/O竞争
• 回滚段数据与用户数据分离，防止单磁盘故障造成数据永久丢失
• 可以控制单个TableSpace脱机，提供更高的整体可用性
• 为特定类型数据库使用保留TableSpace，可优化TableSpace的使用
• 可以备份单独的TableSpace

创建表空间#

官方地址：https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tablespaces.html

要创建新的表空间，首先要具有创建表空间系统权限，使用以下SQL语句

create tablespace

或者

create temporary tablespace

在创建表空间之前，必须创建一个数据库以包含它

• 任何数据库中的主表空间都是系统表空间，它包含数据库服务器功能的基本信息，例如数据字典和系统回滚段
• 系统表空间是在数据库创建时创建的第一个表空间
• 它作为任何其他表空间进行管理，但需要更高的权限级别，并且在某些方面受到限制

不能重命名或删除系统表空间，也不能使其脱机

SYSAUS#

SYSAUX表空间作为系统表空间的辅助表空间，在创建数据库时也总是创建的

• 它包含了各种Oracle产品和特性使用的模式，因此这些产品不需要自己的表空间
• 至于系统表空间，SYSAUX表空间的管理需要更高的安全级别，您不能重命名或删除它

创建表空间的步骤因操作系统而异

第一步始终是使用操作系统创建一个目录结构，在该目录结构中分配数据文件

在大多数操作系统中，当您创建新表空间或通过添加数据文件来更改现有表空间时，可以指定数据文件的大小和完全指定的文件名

无论是创建新表空间还是修改现有表空间，数据库都会自动分配和格式化指定的数据文件

UNDO表空间#

create undospace

专门设计为包含撤消记录。这些记录是由数据库生成的记录，用于回滚或撤消对数据库的更改，以恢复、读取一致性或按回滚语句的要求

创建和管理撤消表空间是管理撤消的主要内容

使用

alter tablespace

或

alter database

语句来更改表空间，前提是必须具有ALTER表空间或相应地更改数据库系统权限

cmd plsql#

登录#

Sqlplus user/pwd [as sysdba]

• user，用户名
• pwd，密码

as sysdba 对应下图的列表选项

• show user                                  显示当前用户
• conn 用户名/密码 [as sysdba]   用户切换
• select * from tab                        查看用户下所有表
• desc table                                  查看表结构
• show all                                     查看所有68个系统变量值
• show error　　                          显示错误
• set heading off                           禁止输出列标题，默认值为ON
• set feedback off                         禁止显示最后一行的计数反馈信息，默认值为"对6个或更多的记录，回送ON"
• set timing on                              默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能
• set sqlprompt "SQL> "               设置默认提示符，默认值就是"SQL> "
• set linesize 1000                        设置屏幕显示行宽，默认100
• set autocommit ON                    设置是否自动提交，默认为OFF
• set pause on                              默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页
• set arraysize 1                           默认为15
• set long 1000                             默认为80
• prompt "test"                              用来向屏幕发送消息，当前指令输出 test
• define test = "1122"                    用户自定义变量
• undefine  test                             清除自定义变量

accept#

accept varibale [datatype] [format format] [prompt text] [hide]

定义变量，定制用户提示

• vairbale 指定变量名，如果不存在则新建
• datatype 制定变脸的数据类型 char munber date 默认为char
• format 指定变量的格式
• prompt text 用户输入数据之前的提示性文本
• hide 用于隐藏用户为变量输入的值

替换变量#

• &   如果某个变量前面有&符号，执行该sql语句时，系统会提示用户为该变量提供一个具体的值
• && 如果需要重新使用一个变量并且不希望重新提示输入该值，那么可以使用&&替换便令

SQL> select ename ,sal ,job,hiredate from scott.emp where sal >&salnew;
输入 salnew 的值:  4900
原值    1: select ename ,sal ,job,hiredate from scott.emp where sal >&salnew
新值    1: select ename ,sal ,job,hiredate from scott.emp where sal >4900

ENAME                       SAL JOB                HIREDATE
-------------------- ---------- ------------------ --------------
KING                       5000 PRESIDENT          17-11月-81
————————————————

show命令#

• show all 查看所有系统变量的值
• show errors 查看错误信息
• show parameters 显示初始化参数的值
• show release 查看数据库版本
• show SGA 查看SGA的大小（要有权限）
• show sqlcode 显示数据库操作之后的状态代码
• show user 显示当前连接的用户

save命令#

save filename [create] [append] [replace]

在SQL*PLUS中可以执行一条或若干条sql命令，它们被存放在缓冲区中，且缓冲区中只能存放最近刚执行的sql或pl/sql ，（后进的覆盖之前的）

如果要保存以往执行过的语句，要使用save

get命令#

get filename

get命令就是用来读取的，将保存后的文件读取到缓冲区中进行显示

edit命令#

edit filename

使用edit命令可以将缓冲区的内容复制到一个文件中 ，默认文件afiedt.buf ，当然也可以制定一个已经命了名存在的文件

@命令#

@ filename

用于执行存放在脚本文件中的命令：与start命令作用相同。

spool命令#

spool filename [create] or [append] or [replace] out

将输出结果以及语句都保存在文本文件中

[create] or [append] or [replace] 三选一

out 启动该功能

Oracle用户、权限和角色#

用户管理#

创建用户（指定密码）

create user itcast identified by password;

由于scott普通用户没有权限创建用户，因此先登录管理员用户

在cmd窗口中登陆上管理员用户

SQL> conn / as sysdba

修改密码#

alter user itcast identified by password2;

锁定与解锁用户#

alter user itcast account lock;
alter user itcast account unlock;

删除用户#

drop user xx;

权限#

授予权限#

grant 权限1,权限2,… to 用户;

收回权限#

revoke 权限1,权限2,… from 用户;

设置用户的空间配额#

alter user itcast quota unlimited on users;

• quota       容量
• unlimited 无限制
• on users  在users这个表空间上没有限制

create session#

• oracle中create session系统权限，是创建会话的权限。
• create session 允许使用这个用户在服务器上创建session
• 通俗的说，就是允许这个用户登录。
• oracle中应该很多角色应该都包含这个系统权限。

查询管理员拥有的权限

select * from session_privs;

角色#

创建角色#

create role role1;

给角色授予权限#

grant create session, create table, create view to role1;

给角色收回权限#

revoke create session from role1;

删除角色#

drop role role1;

Oracle建表#

• Oracle表是Oracle数据库的核心，是存储数据的逻辑基础
• Oracle表是一个二维的数据结构，有列字段和对应列的数据构成一个数据存储的结构
• 可以简单看成行和列的二维表，列代表着Oracle字段（column），行代表着一行数据（即一条数据记录）

数据类型	类型解释
VARCHAR2(length)	字符串类型：存储可变的长度的字符串，length:是字符串的最大长度，默认不填的时候是1，最大长度不超过4000。
CHAR(length)	字符串类型：存储固定长度的字符串，length:字符串的固定长度大小，默认是1，最大长度不超过2000。
NUMBER(a,b)	数值类型：存储数值类型，可以存整数，也可以存浮点型。a代表数值的最大位数：包含小数位和小数点，b代表小数的位数。例子： number(6,2)，输入123.12345，实际存入：123.12 。 number(4,2)，输入12312.345，实际春如：提示不能存入，超过存储的指定的精度。
DATA	时间类型：存储的是日期和时间，包括年、月、日、时、分、秒。例子： 内置函数sysdate获取的就是DATA类型
TIMESTAMP	时间类型：存储的不仅是日期和时间，还包含了时区。例子： 内置函数systimestamp获取的就是timestamp类型
CLOB	大字段类型：存储的是大的文本，比如：非结构化的txt文本，字段大于4000长度的字符串。
BLOB	二进制类型：存储的是二进制对象，比如图片、视频、声音等转换过来的二进制对象

建表示例

-- Create table
create table STUDENT.stuinfo
(
  stuid      varchar2(11) not null,--学号：'S'+班号（7位数）+学生序号（3位数）(1)
  stuname    varchar2(50) not null,--学生姓名
  sex        char(1) not null,--性别
  age        number(2) not null,--年龄
  classno    varchar2(7) not null,--班号：'C'+年级（4位数）+班级序号（2位数）
  stuaddress varchar2(100) default '地址未录入',--地址 (2)
  grade      char(4) not null,--年级
  enroldate  date,--入学时间
  idnumber   varchar2(18) default '身份证未采集' not null--身份证
)
tablespace USERS --(3)
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table STUDENT.stuinfo --(4)
  is '学生信息表';
-- Add comments to the columns 
comment on column STUDENT.stuinfo.stuid -- (5)
  is '学号';
comment on column STUDENT.stuinfo.stuname
  is '学生姓名';
comment on column STUDENT.stuinfo.sex
  is '学生性别';
comment on column STUDENT.stuinfo.age
  is '学生年龄';
comment on column STUDENT.stuinfo.classno
  is '学生班级号';
comment on column STUDENT.stuinfo.stuaddress
  is '学生住址';
comment on column STUDENT.stuinfo.grade
  is '年级';
comment on column STUDENT.stuinfo.enroldate
  is '入学时间';
comment on column STUDENT.stuinfo.idnumber
  is '身份证号';

约束有六种：https://www.cnblogs.com/YC-L/p/14597958.html

添加约束

-- Create/Recreate primary, unique and foreign key constraints 
alter table STUDENT.STUINFO
  add constraint pk_stuinfo_stuid primary key (STUID);
  --把stuid当做主键，主键字段的数据必须是唯一性的（学号是唯一的）
   
-- Create/Recreate check constraints 
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_age
  check (age>0 and age<=50);--给字段年龄age添加约束，学生的年龄只能0-50岁之内的
   
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_sex
  check (sex='1' or sex='2');
   
alter table STUDENT.STUINFO
  add constraint ch_stuinfo_GRADE
  check (grade>='1900' and grade<='2999');