Oracle12c 与 Oracle11g 创建用户时有差别。Oracle12C默认为 CDB模式 这时创建用户的时候需要加上 c## 开头；例如：c##MLQ。

–说明
–需求：创建表空间（MLQ1_hr） 和 临时表空间(MLQ1_TEMP)、创建新用户 c##MLQ1 为其分配指定的表空间和临时表空间
–为用户分配基本权限、为用户创建表结构、添加数据。

–可能出现的问题
–Oracle12c 默认的表空间为：USERS，
–01、没有在创建用户的时候同时指定表空间，单独执行为用户分配表空间的时候可能会更改不成功。
–02、在对某张表添加数据的时候，可能会出现对指定的表空间无权限操作：错误编码：ORA-01950，这个时候重新分配一下表空间即可（6）

1）创建一个数据库临时表空间

  1. CREATE TEMPORARY TABLESPACE MLQ1_TEMP
  2. TEMPFILE \'E:\Oracle\OracleTablespaces\MLQ1\TEMP.DBF\'
  3. SIZE 50M
  4. AUTOEXTEND ON
  5. NEXT 50 MAXSIZE 100M
  6. EXTENT MANAGEMENT LOCAL;

2）创建一个自动增长的表空间

  1. CREATE tablespace MLQ1_hr
  2. LOGGING Datafile
  3. \'E:\Oracle\OracleTablespaces\MLQ1\MLQ1_hr01.dbf\' size 60M Autoextend on
  4. NEXT 50 MAXSIZE 2048M
  5. EXTENT MANAGEMENT LOCAL;

3）创建用户并指定表空间

  1. create user c##MLQ1 identified by root
  2. default tablespace MLQ1_hr 
  3. temporary tablespace MLQ1_TEMP;

4）删除用户

  1. drop user c##MLQ1 cascade;

无法删除用户时：查看当前用户连接状态

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

结束指定的用户连接

  1. 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用户拥有

  1. grant resource,connect to c##MLQ1
  3. grant resource to c##MLQ1

撤销授权：

  1. revoke connect, resource from c##MLQ1

创建、授权、删除角色：

除了前面讲到的三种系统角色—-connect、resource和dba，用户还可以在oracle创建自己的role。

用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有create role系统权限。

  1. 1》创建角色
  2. 　　语法： create role 角色名;
  3. 　　例子： create role testRole;
    2》授权角色
  4. 　　语法： grant select on class to 角色名;
  5. 　　列子： grant select on class to testRole;
  6. 　　注：现在，拥有testRole角色的所有用户都具有对class表的select查询权限
  8. 3》删除角色
  9. 　　语法： drop role 角色名;
  10. 　　例子： drop role testRole;
  11. 　　注：与testRole角色相关的权限将从数据库全部删除

6）为用户分配重新分配数据库表空间

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

7）删除表空间

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

8）在c##MLQ1用户下创建表结构

  1. --创建表emp--
  2. create table emp(
  3. empno number primary key not null,
  4. empname nvarchar2(32) not null,
  5. deptno number not null
  6. );
  7. --创建表dept--
  8. create table dept(
  9. deptno number primary key not null,
  10. deptname nvarchar2(32) not null
  11. );
  13. insert into EMP values(\'2018001\',\'MLQ\',1);

9）删除表空间下所有的表

  1. SELECT \'DROP TABLE \' || TABLE_NAME || \' CASCADE CONSTRAINTS\' V_NAME
  2. FROM DBA_TABLES
  3. WHERE TABLESPACE_NAME = \'USERS\';

10）：查询当前用户所在的表空间

  1. select username,default_tablespace from user_users;

11）：查看所有存在的表空间（查询到的数据可能没有全部展开，点击向下的绿箭头）

  1. select * from dba_tablespaces

12）：查看所有用户

  1. select * from all_users;

13）：查看用户具有怎样的角色

  1. select * from dba_role_privs where grantee=\'C##MLQ1\';

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

  1. select * from dba_roles;

 

Oracle数据库中的约束有五种约束，分别是：

实体完整性：主键约束、唯一性约束(可以为空)

域完整性：check 检查约束

引用完整性：Foreign KEY 外键约束

默认约束(默认值)

SQL语句创建约束需要一个关键字：Costraint

模拟两张表：

  1. ---1、创建模拟的数据表 ---
  2.   --1.1.创建学生表Student
  3.   create table Student(
  4.          StuId NUMBER NOT NULL,     --学生ID
  5.          StuName VARCHAR2(10) NOT NULL, --名称
  6.          Gender VARCHAR2(10)NOT NULL,  -- 性别
  7.          Age NUMBER(2) NOT NULL,    -- 年龄     
  8.          JoinDate DATE NULL,       --入学时间
  9.          ClassId NUMBER NOT NULL,   --班级ID
  10.          Address VARCHAR2(50) NULL   --家庭住址           
  11.   ); 
  12.   --1.2、创建班级表StuClass 
  13.   create table StuClass(
  14.        classId NUMBER not null,     -- 班级ID
  15.        ClassName varchar2(20) not null,  --班级名称
  16.        Notes varchar2(50) null default\'班级信息\',  --备注，默认班级信息
  17.   );

创建数据表约束：

  1.   --2.1）创建主键约束--
  2.   alter table Student add constraint PK_Student_StuId primary key(StuId);
  3.   alter table StuClass add constraint PK_StuClass_ClassId primary key(ClassId);
  5.   --2.2) 创建检查约束--
  6.   alter table Student add constraint CK_Student_Gender check(gender=\'男\' or gender=\'女\');
  7.   alter table Student add constraint CK_Student_Age check(Age>=0 and Age<=100);
  9.   --2.3)创建唯一约束--
  10.   alter table Student add constraint UQ_Student_StuName unique(StuName);
  12.   --2.4)创建默认约束--
  13.   --alter table Student add constraint DF_Student_Address default(\'地址不详\');
  14.   alter table Student Modify Address varchar(50) default \'地址不详\';
  15.   alter table Student Modify JoinDate Date default sysdate;
  17.   --2.5)创建外键约束--
  18.   alter table Student add constraint FK_Student_StuCLass_ClassId foreign key(ClassId) references StuClass(ClassId);

注意：创建表还是约束，与SQL Server基本相同，注意：在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建或者通过Modify函数创建

添加模拟数据：

  1.   --3.1）添加班级信息
  2.   insert into StuClass(ClassId,Classname) values(1,\'一班\');
  3.   insert into StuClass(ClassId,Classname) values(2,\'二班\');
  4.   insert into StuClass(ClassId,Classname) values(3,\'三班\');
  5.    --3.2）添加学生信息
  6.   insert into Student(StuId,Stuname,Gender,Age,ClassId)
  7.   　　values(1,\'关羽\',\'男\',17,1);
  8.   insert into Student(StuId,Stuname,Gender,Age,ClassId)
  9.   　　values(2,\'张飞\',\'男\',16,2);
  10.   insert into Student(StuId,Stuname,Gender,Age,ClassId)
  11.   　　values(3,\'刘备\',\'男\',18,3);

查询模拟数据：

  1. select * from Student;
  2. select * from StuClass;

查询结果：

验证数据表约束：验证Student表的StuName是否唯一（唯一约束）

  1. --插入相同名称--
  2. insert into Student(StuId,Stuname,Gender,Age,ClassId) values(5,\'关羽\',\'男\',18,1);

验证Student表Gender的检查约束：

  1.   --添加性别为未知的数据--
  2.   insert into Student(StuId,Stuname,Gender,Age,ClassId) values(4,\'曹操\',\'未知\',18,1);

 

其他的验证就不一样了，这里不在多多演示。

Face your past without regret. Handle your present with confidence.Prepare for future without fear. keep the faith and drop the fear.

面对过去无怨无悔，把握现在充满信心，备战未来无所畏惧。保持信念，克服恐惧！一点一滴的积累，一点一滴的沉淀，学技术需要不断的积淀！

 

版权声明：本文为mlq2017原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/mlq2017/p/9903988.html