Oracle 數(shù)據(jù)庫(kù)系統(tǒng)
- 使用版本：Oracle 11gR2
- 安裝:https://oracle-base.com/articles/11g/articles-11g#db11g_release_2_installations
- 卸載:https://oracle-base.com/articles/misc/manual-oracle-uninstall
- 啟動(dòng):
net start orcalserviceorcl
0. 歷史
image.png
四人幫創(chuàng)始人(左起為Ed Oates掌实，Bruce Scott，Bob Miner萧锉，Larry Ellison)此 Scott 就是彼 Scott, Scott/tiger, tiger 為他家一只 cat 的名字.他更準(zhǔn)確的說(shuō)是 Oracle 的第一位雇員.用匯編語(yǔ)言寫成的第一版(2.0)甲骨文系統(tǒng)在 1978 年正式推出.2009年 IBM 收購(gòu) SUN 失敗一周后, Oracle 成功收購(gòu) SUN, Oracle 從純軟廠商走向軟硬結(jié)合的唯一能和 IBM 全面抗衡的公司.
幾個(gè)概念:
- RDBMS: Relational Database Management System
一.體系結(jié)構(gòu)
Oracle 服務(wù)器實(shí)例
由內(nèi)存和后臺(tái)進(jìn)程組成的用于訪問(wèn)數(shù)據(jù)庫(kù)的方法,由 SID 標(biāo)識(shí),默認(rèn)為創(chuàng)建數(shù)據(jù)庫(kù)時(shí)的全局?jǐn)?shù)據(jù)庫(kù)名撬即，通過(guò)實(shí)例訪問(wèn)數(shù)據(jù)庫(kù)醒第，實(shí)例是一個(gè)共享存儲(chǔ)區(qū)和一組與文件中的數(shù)據(jù)交互的進(jìn)程谜酒。
啟動(dòng)數(shù)據(jù)庫(kù)三部曲及對(duì)應(yīng)模式:
- NOMOUNT: 啟動(dòng)實(shí)例
- MOUNT: 裝載數(shù)據(jù)庫(kù)
- OPEN: 打開數(shù)據(jù)庫(kù)
命令啟動(dòng):
STARTUP [NOMOUNT | MOUNT | OPEN ] [RESTRICT] [PFILE = 初始化參數(shù)文件]; - FORCE 強(qiáng)制重啟,相當(dāng)于
SHUTDOWN ABORT后 startup - 模式切換:
alter database mount|open
1.物理結(jié)構(gòu)
- Oracle 物理結(jié)構(gòu)是一個(gè)或多個(gè)數(shù)據(jù)文件的集合.
D:\app\oradata\orcl.png
數(shù)據(jù)文件(*.DBF)
- 所在數(shù)據(jù)字典: V$DATAFILE
- 用于保存數(shù)據(jù)庫(kù)的所有數(shù)據(jù)
- 特征:
- 一個(gè)數(shù)據(jù)文件僅與一個(gè)數(shù)據(jù)庫(kù)聯(lián)系
- 一個(gè)數(shù)據(jù)文件只能從屬于一個(gè)表空間
- 一個(gè)表空間可以包含幾個(gè)數(shù)據(jù)文件
- 數(shù)據(jù)庫(kù)容量越界時(shí)數(shù)據(jù)文件可以自動(dòng)擴(kuò)展
數(shù)據(jù)文件目錄
- 創(chuàng)建數(shù)據(jù)文件:
-- 向 ORCL 數(shù)據(jù)庫(kù)的 USERS 表空間添加一個(gè)10M的 user02 數(shù)據(jù)文件.
alter tablespace users
add datafile 'D:\app\oradata\orcl\user02.dbf' size 10M;
- 刪除數(shù)據(jù)文件:
-- 刪除 TEMP 表空間的 temp02 數(shù)據(jù)文件.
alter tablespace temp
drop tempfile
'D:\app\oracle\orcl\temp02.dbf';
控制文件(*.CTL)
- 一個(gè)由 Oracle 進(jìn)程讀寫的二進(jìn)制文件, 記錄數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)(數(shù)據(jù)庫(kù)名,創(chuàng)建時(shí)間, 表空間名,數(shù)據(jù)文件和日志文件名稱等), 用于標(biāo)識(shí)數(shù)據(jù)庫(kù)和日志文件
- 相關(guān)數(shù)據(jù)字典視圖: V$controlfile
重做日志文件(*.log)
- 用于保存用戶對(duì)數(shù)據(jù)庫(kù)進(jìn)行的變更操作
- 數(shù)據(jù)字典視圖: V$log, V$logfile
2.邏輯結(jié)構(gòu)
Oracle 數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)由表空間(tablespace), 段(segment), 區(qū)間(extent), 數(shù)據(jù)塊(data block) 組成.
表空間(tablespace)
數(shù)據(jù)庫(kù)中的基本邏輯結(jié)構(gòu),一系列數(shù)據(jù)文件的集合,一個(gè)數(shù)據(jù)庫(kù)可以有多個(gè)表空間,一個(gè)表空間可以有多個(gè)數(shù)據(jù)文件,一個(gè)數(shù)據(jù)文件只能從屬于一個(gè)表空間
系統(tǒng)用戶表空間數(shù)據(jù)字典:dba_tablespaces
普通用戶表空間數(shù)據(jù)字典:user_tablespaces
-
Oracle 中的6個(gè)默認(rèn)表空間:
image 重看 tablespace 的總空間, 剩余空間, 使用率
select df.tablespace_name "表空間名",totalspace "總空間M",freespace "剩余空間M",round((1-freespace/totalspace)*100,2) "使用率%"
from
(select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs
where df.tablespace_name=fs.tablespace_name order by df.tablespace_name ;
image.png
- 查看一個(gè)表空間的 extent, block 的數(shù)量
-- file_id 為數(shù)據(jù)文件號(hào), block_id, 區(qū)起始數(shù)據(jù)塊號(hào)
select tablespace_name,file_id,extent_id,block_id,blocks
from dba_extents
where tablespace_name='LEO1' order by extent_id;
- 創(chuàng)建表空間
create tablespace <tablespace_name>
datafile <filename1> [, <filename2, ...]
size <filesize> --指定數(shù)據(jù)文件大小
[autoextend [on|off]] next <filesize> -- 指定數(shù)據(jù)文件擴(kuò)展方式:自動(dòng)/非自動(dòng)擴(kuò)展
[maxsize <maxsize>|unlimited] -- 指定數(shù)據(jù)文件為自動(dòng)擴(kuò)展方式時(shí)的最大值或無(wú)限制
[permanent | temperary] --指定表空間的類型為永久/臨時(shí)
擴(kuò)大數(shù)據(jù)庫(kù)的3種方式
- 在表空間增加數(shù)據(jù)文件:
-- 為 system 表空間增加一個(gè) 30M 數(shù)據(jù)文件
alter tablespace system
add datafile 'test01.dbf'
size 30M;
- 增加數(shù)據(jù)文件的大小:
alter database datafile 'filename.dbf' autoextend on next 20m maxsize 1000M - 增加表空間:
create tablespace users datafile 'filename.dbf' - 刪除表空間
drop tablespace tpname including contents and datafiles - 設(shè)置讀寫狀態(tài)
alter tablespace tpname read only - 設(shè)置離線|在線
alter tablespace tpname offline|online - 刪除表空間
-- 可選擇同時(shí)刪除其內(nèi)容和數(shù)據(jù)文件
drop tablespace <tablespace_name>
[including contents [and datafile]]
段(Segment)
由一組連續(xù)/不連續(xù)的區(qū)間組成的邏輯存儲(chǔ)單元,代表特定數(shù)據(jù)類型的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)
根據(jù)段的存儲(chǔ)對(duì)象類型,分成以下4種:
- 數(shù)據(jù)段(data segment)
- 用于存儲(chǔ)表或簇的數(shù)據(jù)
- 索引段(index segment)
- 回滾段(rollback segment)
- 臨時(shí)段(temporary segment)
常見情況為表, 索引都是一個(gè)段
select segment_name, segment_type
from user_segments;
段空間管理
- MSSM(手動(dòng))
- ASSM(自動(dòng))
區(qū)間(extent)
區(qū)間是數(shù)據(jù)庫(kù)存儲(chǔ)空間中分配的一個(gè)邏輯單位,由一組連續(xù)的數(shù)據(jù)塊組成,區(qū)間存儲(chǔ)于段中,是磁盤空間分配的最小單位
- 區(qū)間的分配
- 區(qū)間的大小及數(shù)量
數(shù)據(jù)塊(data block)
- 數(shù)據(jù)塊是 Oracle 管理數(shù)據(jù)庫(kù)存儲(chǔ)空間的最小數(shù)據(jù)儲(chǔ)存單位,一個(gè)數(shù)據(jù)塊對(duì)應(yīng)一定數(shù)量的數(shù)據(jù)庫(kù)空間,標(biāo)準(zhǔn)數(shù)據(jù)塊大小由 DB_BLOCK_SIZE 指定.
- 數(shù)據(jù)塊既是邏輯單位,也是物理單位
相互關(guān)系:
- 數(shù)據(jù)庫(kù)由若干個(gè)表空間組成
- 表空間由一個(gè)或多個(gè)數(shù)據(jù)文件組成
- 表空間存放段
- 段由區(qū)間組成
- 區(qū)間由數(shù)據(jù)塊組成
- 塊是數(shù)據(jù)庫(kù)中最小的分配單元也是數(shù)據(jù)庫(kù)使用的最小 I/O 單元
操作系統(tǒng)的塊(每個(gè)簇字節(jié)數(shù)):
image.png
image.png
3.內(nèi)存結(jié)構(gòu)
分為 SGA, PGA
- SGA(System Global Area):一組由 Oracle 分配的共享的內(nèi)存結(jié)構(gòu),包括:數(shù)據(jù)庫(kù)緩沖存儲(chǔ)區(qū), 共享池,重做日志緩沖區(qū),Java 存儲(chǔ)區(qū), 大型存儲(chǔ)區(qū)
- 查詢 sga 各組件大小
select * from v$sga;
-- 或者
show parameter sga;
- PGA(Program Global Area): 一塊包含一個(gè)服務(wù)進(jìn)程的數(shù)據(jù)和控制信息的內(nèi)存區(qū)域,用于處理 SQL 語(yǔ)句和容納會(huì)話信息
- 查詢數(shù)據(jù)庫(kù)實(shí)例參數(shù):
show parameter instance name - 查詢數(shù)據(jù)庫(kù)名字:
show parameter db_name
select name from v$database - 顯示 SGA 參數(shù)大小及當(dāng)前實(shí)際大小
show sga
select name, current_size from v$buffer_pool
二.用戶管理
登錄
sqlplus /nolog
conn u_name/u_password@domain [as sysdba|sysoper|normal]
系統(tǒng)用戶
- sys ：sys 必須以系統(tǒng)管理員或系統(tǒng)操作員身份登陸:
conn sys/password as sysdba - system :
conn system/password - sysman
普通用戶
- scott：Oracle 數(shù)據(jù)庫(kù)創(chuàng)始人之一名字肢专，默認(rèn)被鎖本姥，默認(rèn)密碼：tiger
解鎖命令：alter user username sccount unlock
修改用戶密碼(記得原密碼):password
修改用戶密碼(忘記原密碼):alter user u_name identified by new_password;
查看用戶
- show user 查看當(dāng)前登陸用戶
- desc dba_users 查看用戶數(shù)據(jù)字典
- select username from dba_users
創(chuàng)建用戶
創(chuàng)建用戶:
create user <usr_name> identified by <usr_password>
default tablespace <tb_name>
quota unlimited on <tb_name>
- docker 下的 oracle 可能沒(méi)有 scott ,可以通過(guò)腳本創(chuàng)建一個(gè)
vim /u01/app/oracle/product/11.2.0/xe/rdbms/admin/scott.sql
Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM UTLSAMPL.SQL
Rem FUNCTION
Rem NOTES
Rem MODIFIED
Rem gdudey 06/28/95 - Modified for desktop seed database
Rem glumpkin 10/21/92 - Renamed from SQLBLD.SQL
Rem blinden 07/27/92 - Added primary and foreign keys to EMP and DEPT
Rem rlim 04/29/91 - change char to varchar2
Rem mmoore 04/08/91 - use unlimited tablespace priv
Rem pritto 04/04/91 - change SYSDATE to 13-JUL-87
Rem Mendels 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFF
rem CONGDON Invoked in RDBMS at build time. 29-DEC-1988
rem OATES: Created: 16-Feb-83
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
SET TERMOUT ON
SET ECHO ON
三.權(quán)限管理
系統(tǒng)權(quán)限
grant privilege [,privilege...] to uname
角色分配
數(shù)據(jù)庫(kù)預(yù)定義角色：
- connect：登錄執(zhí)行基本函數(shù)
- resource：建立用戶自己的數(shù)據(jù)對(duì)象
- dba：所有系統(tǒng)權(quán)限
grant connect,resource to <uname>;
回收權(quán)限
四.Table:表管理
- dba_tables:數(shù)據(jù)庫(kù)中所有的表
- user_tables:當(dāng)前登錄用戶擁有的表對(duì)象
- all_tables:當(dāng)前登錄用戶可以訪問(wèn)的表對(duì)象
-- 刪除表數(shù)據(jù)：delete & truncate
delete table tname; --可撤銷刪除數(shù)據(jù)肩袍，可帶條件，但效率低婚惫，空間不會(huì)及時(shí)釋放
trancate table tname; --清除所有數(shù)據(jù)氛赐，保留表結(jié)構(gòu)，效率高先舷，釋放空間艰管，不可撤銷
drop table tname; -- 刪除表結(jié)構(gòu)， 不可撤銷
數(shù)據(jù)類型
| 數(shù)據(jù)類型 | 最大長(zhǎng)度 | 說(shuō)明 |
|---|---|---|
| char(size) | 2000字節(jié) | 固定長(zhǎng)度,默認(rèn)1字節(jié) |
| varchar2(size) | 4000字節(jié) | 可變長(zhǎng)字符串 |
| number(precision, scale) | 2000 | 包含小數(shù)位的數(shù)值類型 |
視圖
視圖是一個(gè)由 select 語(yǔ)句定義內(nèi)容的作為查看數(shù)據(jù)便捷方式的虛擬表.
- 創(chuàng)建一個(gè)視圖:
create [or replace] [force | noforce] -- force 不考慮基表是否存在
view v_name
as
<select statement>
[with check option | -- 使用視圖進(jìn)行插入或修改操作是必須滿足 select 語(yǔ)句的查詢條件,默認(rèn)不檢查
with read only] --視圖只能用于查詢數(shù)據(jù),默認(rèn)可修改
序列
Oracle 沒(méi)有 MySQL 的自增, 但可以用可自定義更強(qiáng)大的序列替代實(shí)現(xiàn).
-- 創(chuàng)建序列,
create sequence seq_empid
start with 100
increment by 2;
select seq_empid.nextval from dual;
六.完整性約束
- 主鍵約束: primary key
- 非空約束: not null
- 唯一性約束: unique
- 檢查約束: check
- 外鍵約束: foreign key
** 示例 **
create table t_orders(
oid char(8) primary key,--主鍵
uiid char(6) references t_user(uiid),--外鍵--列級(jí)約束
uiid1 char(6) constraint fk_uiid2 references t_user(uiid),--有約束名的外鍵
uiid2 char(6) ,
constraint fk_uiid3 foreign key(uiid2) references t_user(uiid),--表級(jí)有約束名的外鍵
customer varchar2(20) not null,--只能做列級(jí)約束
odate date default sysdate,--默認(rèn)值
dddd char(8) unique,--唯一蒋川，不能重復(fù)
onum number(2) check(onum>0)--自定義約束
);
