目录
一、前言
本篇介绍Oracle数据库、实例、命名空间、段、区、块、用户、模式、角色、权限等概念。
数据库：存储数据的多个物理文件的集合，如控制文件，数据文件，参数文件，日志文件，临时文件等，一个linux用户可以创建多个oracle数据库，数据库实例启动前通过ORACLE_SID标识启动哪个数据库（sqlplus / as sysdba，startup），启动后通过数据库实例名确定连接哪个数据库实例（sqlplus system/1@orcl1，orcl1为数据实例名）
实例：访问数据库文件的一组进程，实例和数据库一般是一一对应（RAC模式为多对一）
表空间：表空间是oracle逻辑存储区域，一个数据库可以创建多个表空间，一个表空间对应多个物理存储文件。表空间分为3种：永久表空间、临时表空间、UNDO表空间，永久表空间保存数据库数据，临时表空间保存临时表、中间排序结果等临时对象，UNDO表空间保存回滚段数据。
用户：用户是访问数据库的人，一个数据库可以创建多个用户，用户和表空间是多对多的关系，一个用户可以在多个表空间中创建表，多个用户也可以在同一个表空间中创建表，一个用户一般有一个默认表空间。
模式：模式是数据库对象的集合，和用户是一一对应的，模式是用来做权限管理的，一个用户创建的所有表都属于他自己的模式，其他人想访问需要有权限且需要加模式前缀（select * from fm.tbank语句中fm即为模式）
角色：权限的集合，方便批量赋权
权限：用户自己模式中的表默认有所有权限，其他模式中的表需要单独赋权才能访问
段：逻辑存储单位，一个表空间包含很多个段，段与数据库对象一一对应，如一个表有一个段
区：一个段包含多个非连续的区，区是最小内存申请单位
块：一个区包含多个连续的块，块是最小内存读写单位，一个块是操作系统文件块的整数倍
关系图如下：
相关sql如下：
-- 查询当前数据库（1条记录，当前数据库信息）
select * from v$database;
-- 查询表空间
select * from dba_tablespaces;
-- 查询用户（每个用户包含一个默认表空间）
select * from dba_users;
-- 查询表（每张表都有一个拥有者和一个表空间）
select * from dba_tables;
-- 查询角色（主要包含角色名称）
select * from dba_roles;
-- 查询用户或角色拥有的系统权限，系统权限只能由DBA授予（privilege:CREATE、SELECT等系统权限，grantee:权限被授予的用户或角色）
select * from dba_sys_privs;
-- 查询用户或角色拥有的表权限（table_name:表名，owner:表拥有者，privilege:SELECT、INSERT等表权限，grantee:权限被授予用户或角色，grantor：授予权限的用户，一般为DBA或表的拥有者）
select * from dba_tab_privs;
-- 查询用户或角色拥有的角色（granted_role:角色/权限的集合，grantee:权限的集合被授予的用户或角色）
select * from dba_role_privs;二、数据库和实例
只有sys用户可以创建数据库、删除数据库、启动数据库、停止数据库。
1.创建数据库
1.方法一，linux通过dbca命令图形化界面创建数据库，参考：在ORACLE中用DBCA创建数据库_njdb-CSDN博客
2.方法二，通过sql命令创建，如创建数据库orcl2（数据库名和实例名相同）：
（1）创建数据文件目录（具体创建哪些目录参考create database脚本和pfile中的目录，脚本执行前目录必现存在）
#ORACLE_BASE= /home/oracle/app
#ORACLE_HOME= /home/oracle/app/oracle/product/11.2.0/dbhome_1
mkdir /home/oracle/app/oradata/orcl2
mkdir /home/oracle/app/fast_recovery_area/orcl2
mkdir /home/oracle/app/admin/orcl2
mkdir /home/oracle/app/admin/orcl2/adump(2) 编写创建数据库脚本createdb.sql（vi /home/oracle/createdb.sql，将下面内容拷贝到文件中）
--文件createdb.sql
CREATE DATABASE orcl2 --数据库名orcl2（一般设置了不建议修改）
USER SYS IDENTIFIED BY 1 --SYS用户密码1
USER SYSTEM IDENTIFIED BY 1 --SYSTEM用户密码1
--指定Oracle日志组
--初始3个组，后续可增加，若出现LGWR等待可考虑增加日志组
--初始最大200M，后续可修改，若出现LGWR等待也可考虑增加日志文件大小
LOGFILE GROUP 1 ('/home/oracle/app/oradata/orcl2/redo01.log') SIZE 200M,
GROUP 2 ('/home/oracle/app/oradata/orcl2/redo02.log') SIZE 200M,
GROUP 3 ('/home/oracle/app/oradata/orcl2/redo03.log') SIZE 200M
--1个日志组也可以包含多个文件，如下（一般是为了冗余备份，需要放在多块磁盘，否则会影响性能）
--LOGFILE GROUP 1 ('/u1/oracle/oradata/orcl2/redo01.log','/u2/oracle/oradata/orcl2/redo01.log') SIZE 200M,
--GROUP 2 ('/u1/oracle/oradata/orcl2/redo02.log','/u2/oracle/oradata/orcl2/redo02.log') SIZE 200M,
--GROUP 3 ('/u1/oracle/oradata/orcl2/redo03.log','/u2/oracle/oradata/orcl2/redo03.log') SIZE 200M
MAXLOGFILES 16 --最多16个日志组
MAXLOGMEMBERS 4 --每个日志组最多4个文件
MAXLOGHISTORY 1
MAXDATAFILES 100 --最多数据文件个数100个
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL --本地区块管理，一般建议本地，性能好
--SYSTEM系统表空间文件
DATAFILE '/home/oracle/app/oradata/orcl2/system01.dbf' SIZE 325M REUSE
--辅助系统表空间文件（为缓解SYSTEM表空间压力分离出来的）
SYSAUX DATAFILE '/home/oracle/app/oradata/orcl2/sysaux01.dbf' SIZE 325M REUSE
--默认用户表空间，创建用户时若未指定默认表空间，就指定到这里
DEFAULT TABLESPACE users DATAFILE '/home/oracle/app/oradata/orcl2/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
--默认临时表空间
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/home/oracle/app/oradata/orcl2/temp01.dbf' SIZE 20M REUSE
--默认UNDO表空间
UNDO TABLESPACE undotbs1 DATAFILE '/home/oracle/app/oradata/orcl2/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
(3) 创建数据库实例启动控制文件initorcl2.ora（文件名必须为"init实例名.ora"，建议放$ORACLE_HOME/dbs目录下，否则启动数据库需要通过startup pfile='/home/oracle/tmp/init.ora'指定目录）
vi $ORACLE_HOME/dbs/initorcl2.ora，将下面内容拷贝到文件中
#文件/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora
#数据库名称，该文件最少可以只指定一个数据库名
db_name="orcl2"
#数据库域，非必填，默认为空
db_domain=""
#控制文件，非必填
control_files=("/home/oracle/app/oradata/orcl2/control01.ctl", "/home/oracle/app/fast_recovery_area/orcl2/control02.ctl")
#归档文件存放的路径，非必填，默认$ORACLE_BASE/fast_recovery_area
db_recovery_file_dest="/home/oracle/app/fast_recovery_area"
#归档文件大小，非必填，默认4182M
db_recovery_file_dest_size=4385144832
#非必填
compatible=11.2.0.4.0
diagnostic_dest=/home/oracle/app
#最大进程数，非必填，默认150
processes=150
#SGA内存大小，默认512M
sga_target=536870912
#安全审计，非必填
audit_file_dest="/home/oracle/app/admin/orcl2/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
#共享连接服务，非必填
dispatchers="(PROTOCOL=TCP) (SERVICE=orcl2XDB)"
#排序、hash连接等可以使用最大PGA内存，非必填
pga_aggregate_target=230686720
#非必填
undo_tablespace=UNDOTBS1（4）开始创建数据库
1.指定数据库实例名称为orcl2（建议与init.ora中数据库名保持一致）
export ORACLE_SID=orcl2
2.使用操作系统验证的方式连接到一个oracle空闲实例
sqlplus / as sysdba
3.启动数据库实例
SQL>startup nomount #默认使用$ORACLE_HOME/dbs/目录下的控制文件initorcl2.ora，也可以通过pfile参数指定
4.执行createdb.sql创建数据库
SQL>@/home/oracle/createdb.sql
5.执行必要的脚本来创建数据字典视图,同义词,PL/SQL包
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
6.创建用户密码校验文件（否则无法远程登录）
linux下执行：orapwd file=$ORACLE_HOME/dbs/orapworcl2 password=1 entries=5 force=y
到此，数据库orcl2创建完成。
2.启动数据库
通过sqlplus / as sysdba连接到一个空闲实例（ORACLE_SID指定实例名称），再通过startup命令启动数据库（"spfile实例名.ora"或"init实例名.ora"指定数据库名称及参数信息），oracle有多种启动数据库的方式，如下：
1.startup
一般场景只用使用该命令，启动数据、装载数据库数据、打开数据运行访问，所有用户可以登录，等于以下三个命令：
startup nomount
alter database mount
alter database open
2.startup nomount
读取"spfile实例名.ora"或"init实例名.ora"文件，启动数据库实例，未装载数据文件，可用于重建控制文件、重建数据库、创建数据库。
3.startup mount
启动数据库并装载数据，未打开，用户不可访问，用于数据库恢复
4.startup restrict
约束方式启动，这种方式能够启动数据库，但只允许具有一定特权的用户访问
5.startup exclusive
排它方式启动数据，只允许当前例程使用数据库
6.startup force
强制启动方式，强制关闭数据库再启动
7.startup pfile=参数文件名
带初始化参数文件的启动方式，先读取参数文件，再按参数文件中的设置启动数据库，如startup pfile='/home/oracle/tmp/init.ora'
若不指定pfile，默认加载的pfile文件顺序为：spfileSID.ora-〉spfile.ora-〉initSID.ora-〉init.ora（spfile优先于pfile），pfile和spfile可以相互转换，如下：
SQL> create spfile from pfile; #pfile生成spfile，源文件和目标文件默认在$ORACLE_HOME/dbs目录下
SQL> create pfile from spfile; #spfile生成pfile，源文件和目标文件默认在$ORACLE_HOME/dbs目录下
SQL> create spfile='xxxxx' from pfile='xxxx'; #指定源文件和目标文件目录和文件名
SQL> create pfile='xxxxx' from spfile='xxxx'; #指定源文件和目标文件目录和文件名
一般建议使用spfile，因为spfile在oracle服务器上，只有一份，pfile在oracle管理端，多个管理员可能有多个文件，容易出错。
3.停止数据库
1.shutdown normal 或 shutdown
正常方式关闭数据库，等所有连接活动释放后才退出
2.shutdown immediate
立即方式关闭数据库，在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源），当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。
3.shutdown abort
直接关闭数据库，正在访问数据库的会话会被突然终止,如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间
4.查询数据库
--查询当前数据库信息
select * from v$database;
--显示当前数据库名称
show parameter db_name;
--显示当前实例名称
show parameter instance_name;
--查询当前数据库参数，包括db_name,instance_name,pfile等参数
select * from v$parameter;5.删除数据库
SQL> shutdown immediate; --立即停止数据库
SQL> startup mount exclusive restrict; --独占、限制方式启动数据库
SQL> drop database; --删除数据库（会删除物理控制文件、数据文件等）
三、表空间、用户、角色、权限、表
1.表空间
表空间创建、删除需要普通DBA权限
创建表空间
create tablespace test --表空间名test
logging
--表空间文件
datafile '/home/oracle/oradata/orcl2/test01.dbf' size 512M
--也可指定多个文件，单个文件有大小限制，表数据存储时是随机存储到某个文件上
--datafile '/home/oracle/oradata/orcl2/test01.dbf' size 512M,'/home/oracle/oradata/orcl2/test02.dbf' size 512M
autoextend on next 512M -- 每512M自动扩展
extent management local --本地管理块
--事后也可以为表空间增加文件
--alter tablespace test add datafile '/home/oracle/oradata/orcl2/test03.dbf' size 512M;
查询表空间
--DBA权限用户执行，数据库所有表空间
select * from dba_tablespaces;
--普通用户执行，当前用户表空间
select * from user_tablespaces;删除表空间
--只能删除空表空间，且不删除物理文件
drop tablespace test;
--删除表空间及表空间上的数据对象，但不删除物理文件
drop tablespace test including contents;
--删除表空间、表空间上的数据对象和物理文件
drop tablespace test including contents and datafiles; 2.用户
内置用户
Oracle内置用户一般拥有系统权限，具体权限可以通过以下sql查询：
--查询SYSTEM用户系统权限列表
select * from dba_sys_privs where grantee in ('SYSTEM');
--查询可以CREATE TABLE的角色和用户列表
select * from dba_sys_privs where privilege in ('CREATE TABLE');sys用户：拥有sysdba角色权限
system用户：拥有普通dba角色权限
用户创建、修改、删除、查询
--创建用户
create user user1 identified by 1 default tablespace test; --不指定时默认表空间就是user表空间
--修改密码
alter user user1 identified by 2;
--查询用户
select * from dba_users; --DBA用户执行，数据库所有用户信息
select * from user_users; --普通用户执行，数据库当前用户信息
--删除用户
drop user user1 cascade; --删除用户及用户下所有的数据
--删除连接状态的用户
alter user user1 account lock; --锁定用户，放在再有登录
select 'alter system kill session ' || ''''||sid ||','||serial# ||''';' from v$session where username=Upper('user1'); --通过该sql生成杀死会话的语句并执行
drop user user1 cascade; --删除用户
--创建角色
create role role1; --只有dba用户可以创建、删除、查询角色
--删除角色
drop role role1;
--查询角色
select * from dba_roles; --查询系统所有角色3.角色
内置角色
Oracle内置角色一般拥有系统权限，具体权限可以通过以下sql查询：
--查询RESOURCE角色系统权限列表
select * from dba_sys_privs where grantee in ('RESOURCE');
--查询可以CREATE TABLE的角色和用户列表
select * from dba_sys_privs where privilege in ('CREATE TABLE');CONNECT角色：是授予最终用户的典型权利，包括以下权限：CREATE SESSION --创建会话
RESOURCE角色：是授予开发人员的，包括以下权限：CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型
DBA角色：普通dba权限，与其他内置角色和自建角色一样，只有数据库实例启动了，它才存在，默认system用户拥有该角色权限，该角色权限也可以赋予自建用户。
sysdba角色：系统dba，是系统权限，只有sys用户拥有该权限，它是由linux系统用户校验其合法性，它只是一个概念，本身并不存在于oracle中，它的存在也不依赖于数据库启动，该角色可以创建数据库、删除数据库、启动数据库、停止数据库等。
角色创建、删除、查询
--创建角色
create role role1; --只有dba用户可以创建、删除、查询角色
--删除角色
drop role role1;
--查询角色
select * from dba_roles; --查询系统所有角色4.权限
oracle权限有两种，一种是create table、create trigger等系统权限，另一种是表的select、update等操作权限。一个用户有2种方式获取权限，一种直接授予，另一种是通过角色间接授予，如下图（以下权限1、权限2、权限3可以是系统权限，也可以是表权限）：
权限授权、撤销、查询
Oracle权限授予有6种组合，如下图：
6种授予方式对应的sql如下：
----一、授权
--系统权限授予角色和用户
--1.将create table等系统权限赋给角色role1(只有DBA用户能赋予)
grant create table to role1;
--2.将create table等系统权限赋给用户user1(只有DBA用户能赋予)
grant create trigger to user1;
--表权限授予角色和用户
--3.将system的student表的权限赋给角色role1(DBA用户或system用户赋予)
grant select,update,insert on system.student to role1;
--4.将system的student表的权限赋给用户user1(DBA用户或system用户赋予)
grant delete on system.student to user1;
--角色权限授予角色和用户
--5.将一个角色权限赋予另一个角色role2
grant connect,role1 to role2;
--6.将角色权限赋予用户user1
grant connect,role1 to user1;
----二、撤销
--系统权限从角色和用户收回
--1.将create table等系统权限从角色role1收回
revoke create table from role1;
--2.将create table等系统权限从用户user1收回
revoke create trigger from user1;
--表权限从角色和用户收回
--3.将system的student表的权限从角色role1收回
revoke select,update,insert on system.student from role1;
--4.将system的student表的权限从用户user1收回
revoke delete on system.student from user1;
--角色权限从角色和用户收回
--5.将角色权限从角色role2收回
revoke connect,role1 from role2;
--6.将角色权限从用户user1收回
revoke connect,role1 from user1;
----三、查询
--查询角色和用户权限
--查询角色和用户拥有的系统权限(1,2)
select * from dba_sys_privs where grantee in ('ROLE1','USER1');
--查询角色和用户拥有的表权限(3,4)
select * from dba_tab_privs where grantee in ('ROLE1','USER1');
--查询角色和用户拥有的角色(5,6)
select * from dba_role_privs where grantee in ('ROLE2','USER1');
--查询指定用户的所有权限(包括直接授予用户的和通过角色间接授予用户)
--用户USER1拥有的系统权限
select * from dba_sys_privs where grantee = 'USER1' or grantee in (select granted_role from dba_role_privs where grantee = 'USER1');
--用户USER1拥有的表权限
select * from dba_tab_privs where grantee = 'USER1' or grantee in (select granted_role from dba_role_privs where grantee = 'USER1');
--当前用户直接拥有的权限（当前用户无法查询到自己通过角色间接拥有的权限）
select * from user_sys_privs;--当前用户拥有的系统权限
select * from user_tab_privs;--当前用户拥有的表权限
select * from user_role_privs;--当前用户拥有的角色5.表
类型
char(n):固定长度字符串,最大2000,保存字符串长度小于n时右补空格
char:单字符,等同于char(1)
varchar2(n):可变长度字符串,最大4000,可做索引的最大长度749
number(m,n):可变长的数值类型,m数据总长度,n小数部分长度,m属于[1,38],n属于[-84,127],表示数据范围[10^-130,10^126),可表示正数、0、负数
number(m):等同于number(m,0)
number:相当于m,n均不固定
integer:整形数字,number的子类型,等同于number(38,0)
float(n):浮点型,number的子类型,等同于number(38,n*0.30103),双精度
float:相当于n不固定
DATE:日期时间DD-MM-YY(HH-MI-SS)
TIMESTAMP:日期时间DD-MM-YY(HH-MI-SS:FF3)
一般情况下建议，字符用char，字符串用varchar2，整形用number(n)，浮点型用number(n,m)。日期时间相关sql：
select sysdate from dual;--到秒
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --到秒
select systimestamp from dual;--到微妙
select to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff ') from dual; --到微妙
select to_char(sysdate,'yyyy-mm-dd hh24:mi') from dual;
select to_char(sysdate, 'yyyy' ) from dual; --年
select to_char(sysdate, 'MM' ) from dual; --月
select to_char(sysdate, 'dd' ) from dual; --日
select to_char(sysdate, 'Q') from dual; --季
select to_char(sysdate, 'iw') from dual; --周--按日历上的那种，每年有52或者53周
--当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual;
--当前时间减去7小时的时间
select sysdate - interval '7' hour from dual;
--当前时间减去7天的时间
select sysdate - interval '7' day from dual;
--当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual;
--当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual;
--时间间隔乘以一个数字
select sysdate,sysdate - 8*interval '7' hour from dual;
--获取当年的一月一号
select to_date(concat((select to_char(sysdate,'yyyy') from dual), '-01-01 00:00:00'),'yyyy-MM-dd HH24:mi:ss') from dual;
--获取这个月的一月一号
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 FROM DUAL;
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1,'yyyy-mm-dd HH24:mi:ss') FROM DUAL; 完整性约束
NOT NULL 非空
UNIQUE Key 唯一键
PRIMARY KEY 主键
FOREIGN KEY 外键
CHECK 自定义检查约束
外键删除主表记录时，依赖表限制方式：
RESTRICT方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。
CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除
SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值
参考：https://www.cnblogs.com/su-chu-zhi-151/p/11181418.html
create table tstudent --学生,主表
(
id number(4) primary key, --主键
name varchar2(100) not null, --非空
email varchar2(25) not null UNIQUE, --非空且唯一
age number(2) check(age > 0 and age < 100) --限制
);
create table tidcard--身份证,依赖表
(
cid number(4),--身份证ID
begin_date date,
end_date date,
phone varchar2(11),
sex char,
id number(4),
constraint pk_tinfo_sid primary key(cid),--主键
constraint phone UNIQUE(phone),
constraint sex check(CASE WHEN sex='男' THEN 1
WHEN sex='女' THEN 1
ELSE 0 END = 1),--限制只能男或女
FOREIGN KEY(id) REFERENCES tstudent(id) ON DELETE CASCADE--外键，删除tstudent记录，tidcard对应记录自动删除
);创建表
create table student
(
id varchar2(50) primary key ,
name char(200） not null,
phone number(11) unique,
class varchar(10)
)tablespace test; --不指定就在默认表空间下
删除表
drop table student cascade constraints; --同时删除约束，如外键
四、多租户模式
oracle支持多租户模式，多租户模式可以使租户之间逻辑上完全隔离，保证使用安全，使用体验上像是给每个租户创建了1个独立的数据库，但实际只创建了1个真实数据库。由于Oracle创建1个数据库会创建大量管理进程、申请大量内存等资源，多租户模式大大降低了这种资源的浪费；
#使用SYS用户创建1个全局容器数据库，名称为mycdb
CREATE DATABASE mycdb;
#使用SYS用户登录到mycdb，创建一个租户数据库mypdb，并指定其管理员用户名及密码
CREATE PLUGGABLE DATABASE mypdb ADMIN USER admin IDENTIFIED BY password FILE_NAME_CONVERT=('pdbseed/', 'mypdb/');
#切换到租户数据库mypdb
ALTER SESSION SET CONTAINER=mypdb;
#在租户数据库中创建租户用户tenant1
CREATE USER tenant1 IDENTIFIED BY password;
#给租户用户赋权
GRANT CONNECT, RESOURCE TO tenant1;
490
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
