Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

知識 11-09

Oracle12c 與 Oracle11g 創建用戶時有差別。Oracle12C默認為 CDB模式這時創建用戶的時候需要加上 c## 開頭；例如：c##MLQ。

--說明

--需求：創建表空間（MLQ1_hr）和臨時表空間(MLQ1_TEMP)、創建新用戶 c##MLQ1 為其分配指定的表空間和臨時表空間

--為用戶分配基本許可權、為用戶創建表結構、添加數據。

--可能出現的問題

--Oracle12c 默認的表空間為：USERS，

--01、沒有在創建用戶的時候同時指定表空間，單獨執行為用戶分配表空間的時候可能會更改不成功。

--02、在對某張表添加數據的時候，可能會出現對指定的表空間無許可權操作：錯誤編碼：ORA-01950，這個時候重新分配一下表空間即可（6）

1）創建一個資料庫臨時表空間

CREATE TEMPORARY TABLESPACE MLQ1_TEMP
TEMPFILE "E:OracleOracleTablespacesMLQ1TEMP.DBF"
SIZE 50M
AUTOEXTEND ON
NEXT 50 MAXSIZE 100M
EXTENT MANAGEMENT LOCAL;

2）創建一個自動增長的表空間

CREATE tablespace MLQ1_hr
LOGGING Datafile
"E:OracleOracleTablespacesMLQ1MLQ1_hr01.dbf" size 60M Autoextend on
NEXT 50 MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

3）創建用戶並指定表空間

create user c##MLQ1 identified by root
default tablespace MLQ1_hr
temporary tablespace MLQ1_TEMP;

4）刪除用戶

drop user c##MLQ1 cascade;

無法刪除用戶時：查看當前用戶連接狀態

select saddr,sid,serial#,paddr,username,status from v$session where username is not null

結束指定的用戶連接

alter system kill session"（sid）,（serial#）";

5）給用戶授權

--connect role(連接角色)

--臨時用戶，特指不需要建表的用戶，通常只賦予他們connect role.

--connect是使用oracle簡單許可權，這種許可權只對其他用戶的表有訪問許可權，包括select/insert/update和delete等。

--擁有connect role 的用戶還能夠創建表、視圖、序列（sequence）、簇（cluster）、同義詞(synonym)、回話（session）

--和其他數據的鏈（link）

--resource role(資源角色)

--更可靠和正式的資料庫用戶可以授予resource role。

--resource提供給用戶另外的許可權以創建他們自己的表、序列、過程(procedure)、觸發器(trigger)、索引(index)和簇(cluster)。

--dba role(資料庫管理員角色)

--dba role擁有所有的系統許可權

--包括無限制的空間限額和給其他用戶授予各種許可權的能力。system由dba用戶擁有

grant resource,connect to c##MLQ1
grant resource to c##MLQ1

撤銷授權：

revoke connect, resource from c##MLQ1

創建、授權、刪除角色：

除了前面講到的三種系統角色----connect、resource和dba，用戶還可以在oracle創建自己的role。

用戶創建的role可以由表或系統許可權或兩者的組合構成。為了創建role，用戶必須具有create role系統許可權。

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

打開今日頭條，查看更多精彩圖片

1》創建角色
　　語法： create role 角色名;
　　例子： create role testRole;
2》授權角色
　　語法： grant select on class to 角色名;
　　列子： grant select on class to testRole;
　　註：現在，擁有testRole角色的所有用戶都具有對class表的select查詢許可權
3》刪除角色
　　語法： drop role 角色名;
　　例子： drop role testRole;
　　註：與testRole角色相關的許可權將從資料庫全部刪除

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

6）為用戶分配重新分配資料庫表空間

alter user c##MLQ1 quota unlimited on MLQ1_hr;
alter user c##MLQ1 QUOTA unlimited ON MLQ1_hr TEMPORARY TABLESPACE MLQ1_TEMP;

7）刪除表空間

DROP TABLESPACE MLQ1_hr INCLUDING CONTENTS;
drop tablespace MLQ1_hr including contents and datafiles cascade constraints

8）在c##MLQ1用戶下創建表結構

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

--創建表emp--
create table emp(
empno number primary key not null,
empname nvarchar2(32) not null,
deptno number not null
);
--創建表dept--
create table dept(
deptno number primary key not null,
deptname nvarchar2(32) not null
);
insert into EMP values("2018001","MLQ",1);

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

9）刪除表空間下所有的表

SELECT "DROP TABLE " || TABLE_NAME || " CASCADE CONSTRAINTS" V_NAME
FROM DBA_TABLES
WHERE TABLESPACE_NAME = "USERS";

10）：查詢當前用戶所在的表空間

select username,default_tablespace from user_users;

11）：查看所有存在的表空間（查詢到的數據可能沒有全部展開，點擊向下的綠箭頭）

select * from dba_tablespaces

12）：查看所有用戶

select * from all_users;

13）：查看用戶具有怎樣的角色

select * from dba_role_privs where grantee="C##MLQ1";

14）：查看oracle中所有的角色

select * from dba_roles;

1）：Oracle的創建表和創建約束的Sql語句— —

Oracle資料庫中的約束有五種約束，分別是：

實體完整性：主鍵約束、唯一性約束(可以為空)

域完整性：check 檢查約束

引用完整性：Foreign KEY 外鍵約束

默認約束(默認值)

SQL語句創建約束需要一個關鍵字：Costraint

模擬兩張表：

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

---1、創建模擬的數據表 ---
--1.1.創建學生表Student
create table Student(
StuId NUMBER NOT NULL, --學生ID
StuName VARCHAR2(10) NOT NULL, --名稱
Gender VARCHAR2(10)NOT NULL, -- 性別
Age NUMBER(2) NOT NULL, -- 年齡
JoinDate DATE NULL, --入學時間
ClassId NUMBER NOT NULL, --班級ID
Address VARCHAR2(50) NULL --家庭住址
);
--1.2、創建班級表StuClass
create table StuClass(
classId NUMBER not null, -- 班級ID
ClassName varchar2(20) not null, --班級名稱
Notes varchar2(50) null default"班級信息", --備註，默認班級信息
);

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

創建數據表約束：

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

--2.1）創建主鍵約束--
alter table Student add constraint PK_Student_StuId primary key(StuId);
alter table StuClass add constraint PK_StuClass_ClassId primary key(ClassId);
--2.2) 創建檢查約束--
alter table Student add constraint CK_Student_Gender check(gender="男" or gender="女");
alter table Student add constraint CK_Student_Age check(Age>=0 and Age<=100);
--2.3)創建唯一約束--
alter table Student add constraint UQ_Student_StuName unique(StuName);
--2.4)創建默認約束--
--alter table Student add constraint DF_Student_Address default("地址不詳");
alter table Student Modify Address varchar(50) default "地址不詳";
alter table Student Modify JoinDate Date default sysdate;
--2.5)創建外鍵約束--
alter table Student add constraint FK_Student_StuCLass_ClassId foreign key(ClassId) references StuClass(ClassId);

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

注意：創建表還是約束，與SQL Server基本相同，注意：在Oracle中default是一個值，而SQL Server中default是一個約束，因此Oracle的default設置可以在建表的時候創建或者通過Modify函數創建

添加模擬數據：

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

--3.1）添加班級信息
insert into StuClass(ClassId,Classname) values(1,"一班");
insert into StuClass(ClassId,Classname) values(2,"二班");
insert into StuClass(ClassId,Classname) values(3,"三班");
--3.2）添加學生信息
insert into Student(StuId,Stuname,Gender,Age,ClassId)
　　values(1,"關羽","男",17,1);
insert into Student(StuId,Stuname,Gender,Age,ClassId)
　　values(2,"張飛","男",16,2);
insert into Student(StuId,Stuname,Gender,Age,ClassId)
　　values(3,"劉備","男",18,3);

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南

查詢模擬數據：

select * from Student;
select * from StuClass;

查詢結果：

Oracle基本語法（創建：表空間、用戶、授權、約束等）使用指南