大型数据库技术（四）oracle模式对象上

一、数据字典

定义：数据字典是Oracle存放关于数据库内部信息的地⽅，其⽤途是⽤来描述数据库内部的运⾏和管理情况。

Oracle数据字典是表和相关视图的集合，通过它可以 观察到数据库内部的机制和结构。

包括：

–静态数据字典

–动态性能数据字典

1、静态数据字典

①静态数据字典提供的信息包括

–模式对象的定义

–完整性约束

–权限和角色

–为模式对象所分配的存储空间

–数据库操作审核信息

②静态数据字典视图分类

user_    all_    dba_

• 用户视图

–以USER_为前缀，包含了当前用户拥有的全部对象

信息。

• 扩展用户视图

–以ALL_为前缀，是用户视图的超集，包含了当前用

户当前可以访问的全部对象和权限的信息。

• 数据库管理员视图

–以DBA_为前缀，包含了数据库拥有的所有对象和权

限的信息。

③基本数据字典及其说明

④静态数据字典例子

例1：

查询用户在数据库中拥有的所有模式对象 信息。

SELECT object_name, object_id, object_type,created FROM user_objects;

例2：

查询用户c## Trainee的表ROOMS的结构定义信息。

SELECT table_name,column_name,data_type,data_length
From user_tab_columns
WHERE table_name='ROOMS

例3：

查询与用户c## Trainee及其对象相关的权限授予情况 。

SELECT * FROM user_tab_privs

2、动态性能数据字典视图

• 动态性能表记录当前数据库的活动情况和性能参数。

• 查询动态性能表可以了解系统运行状况，诊断和解决 系统运行中所出现的问题。

• Oracle在动态性能表的基础上创建了动态性能数据字 典视图，通常称为 V$视图 。

①常用动态性能视图

②例题

例1： 列出当前所有后台进程及其运行错误。

SELECT name, description, error FROM V$BGPROCESS

例2：查看日志文件的信息

SELECT group#,sequence#,bytes,archived,status,first_change# FROM v$log

二、oracle模式对象

• 模式是与每个Oracle数据库用户相关的一组数据库对 象的集合。

• 模式所有者拥有该模式下所有数据库对象，如表、视 图、索引、同义词、数据库链接、过程、函数、和包 等的全部权限。

三、表

1、数据表的逻辑结构

5种常用数据类型

①字符型

CHAR数据类型 ： CHAR 数据类型⽤于存储固定⻓度的字符串。⼀旦定义了CHAR 类型的列，该列就会⼀直保持声明时所规定的⻓度⼤⼩。当为该列的某个单元格（⾏与列的交叉处就是单元格）赋予⻓度较短的数值后，空余部分Oracle 会⽤空格⾃动填充；如果字段保存的字符⻓度⼤于规定的⻓度，则Oracle 会产⽣错误信息。CHAR 类型的⻓度范围为 1~2000 字节。

VARCHAR2数据类型 ： VARCHAR2 数据类型与 CHAR 类型 相似，都⽤于存储字符串数据。但 VARCHAR2 类型的字段⽤于存储变⻓，⽽⾮固定⻓度的字符串。将字段定义为VARCHAR2 数据 类型时，该字段的⻓度将根据实际字符数据的⻓度⾃动调整（即如果该列的字符串⻓度⼩于定义时的⻓度，系统不会使⽤空格填充，⽽是保留实际的字符串⻓度）。因此，在⼤多数情况下，都会使⽤VARCHAR2 类型替换 CHAR 数据类型。

②数值类型

number（precision，scale）

number（5，2）

③日期事件类型

Oracle 提供的⽇期时间数据类型是 DATE ，它可以存储⽇期和时间的组合数据。

在 Oracle 中，可以使⽤不同的⽅法建⽴⽇期值。其中，最常⽤的获取⽇期值的⽅法是通过SYSDATE 函数，调⽤该函数可以获取当前系统的⽇期值。除此之外，还可以使⽤TO_DATE 函数将数值或字符串转 换为 DATE 类型。 Oracle 默认的⽇期和时间格式由初始化参数NLS_DATE_FORMAT指定，⼀般为 DD-MM-YY。

④LOB类型

LOB 数据类型⽤于⼤型的、未被结构化的数据，例如⼆进制⽂件、图⽚⽂件和其他类型的外部⽂件。LOB 类型的数据可以直接存储在数据库内部，也可以将数据存储在外部⽂件中，⽽将指向数据的指针存储在数据库中。LOB 数据类型分为 BLOB 、 CLOB 和 BFILE 数据类型

⑤ROWID数据类型

ROWID 数据类型被称为 “ 伪列类型 ” ，⽤于在 Oracle 内部保存表 中的每条记录的物理地址。在Oracle 内部通过 ROWID 来定位所需记录的。由于ROWID 实际上保存的是数据记录的物理地址，所以通过 ROWID 来访问数据记录可以获得最快的访问速度。为了便于使⽤，Oracle⾃动为每⼀个表建⽴⼀个名称为 ROWID 的字段，可以对这个字段进⾏查询、更新和删除等操作，设置利⽤ROWID 来访问表中的记录以获得最快的操作速度。

2、创建表语法

CREATE TABLE [user.] TABLE
( { column1 datatype [DEFAULT expn] 
[ column_constraint ] | table_constraint }
[,column2 datatype [DEFAULT expn] 
[ column_constraint ] | table_constraint } ] … )
[ CLUSTER cluster (column1 [ ,column2 ] … ) ]
[PCTFREE n]
[PCTUSED n]
[INITRANS n]
[MAXTRANS n]
[RECOVERABLE | UNRECOVERABLE]
[TABLESPACE tablespace]
[ENABLE | DISABLE]
[AS query]
[CACHE | NO CACHE]
[STORAGE n]



– PCTFREE：块内预留的自由空间百分数，缺省值是10%。
– PCTUSED：块内已使用空间的最小百分比。
– INITRANS：指定表的每一个数据块中分配的事务项初值，
缺省值为1。
– MAXTRANS：指定可同时修改表的数据块的最大事务数。
– RECOVERABLE：指定该表可恢复。
– UNRECOVERABLE：指定该表不可恢复。
– TABLESPACE：指出当前定义的表放置在哪个表空间中。
– query：一个将要用来定义新表的SQL SELECT语句，将由自
查询返回的记录插入到建立的表中。
– STORAGE：用来控制分配给表的存储空间大小，以及当需
要增长时如何使用空间。

例1：

要求创建一个学生信息表mystudents，并且定义主键 以及表空间。

CREATE TABLE mystudents (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3))
tablespace demo;

例2：使用DESCRIBE或DESC命令检查表的属性

DESC mystudents

例3：查看表mystudents的存储参数

SELECT TABLE_NAME,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT
FROM USER_TABLES
WHERE TABLE_NAME='MYSTUDENTS';

特殊创建表：通过select

例4：使⽤CREATE TABLE...AS SELECT 语 句 创 建students表的⼀个副本，代码及运⾏结果如下。

create table student_2 as select * from student;

3、聚簇表和索引聚簇表

• 聚簇表是指一组共享相同列的表。

• 聚簇表所共享的列数据存储在相同的数据块中，而不

是存储在各个表的数据行中。

• 聚簇表中的共享列称作聚簇码。

聚簇表-优快云博客

4、维护数据表

①增加和删除字段

增加：

在students_6表中增加⼀个province（省份）新字段，代码及运⾏结果如下。
alter table students_6 add(province varchar2(10));

删除：

可以通过 ALTER TABLE...DROP 语句删除表中的指定字段。但是不能删除表中所有的字段，也不能删除SYS 模式中任何表的字段。如果仅需要删除⼀个字段，则必须在字段名前指定COLUMN 关键字。

注意column
删除一个字段：
alter table students_6 drop column province;
删除多个字段：
alter table students_6 drop (sex,age);

②修改字段

语法：

alter table table_name modify column_name column_property;
［√］table_name：表⽰要修改的列所在的表名称。
［√］column_name：要修改的列名称。
［√］column_property：要修改列的属性，包括数据类型的⻓度、数字列的精度、列的数据类型和列的默认值等。

将students_6表中的departno字段的⻓度由2更改为4
alter table students_6 modify departno varchar2(4);

③重命名表

alter table table_old_name rename to table_new_name

④改变表空间和存储参数

1、修改表空间
alter table @tablename remove tablespace @newtablespacename;


2修改存储参数
使⽤alter table语句重新设置students_6表的pctfree和pctused参数
alter table students_6 pctfree 25 pctused 45;

⑤删除表

rop table table_name [cascade constraints];

在删除⼀个表的结构时，通常 Oracle 会执⾏以下操作：

［√］删除表中所有的数据。

［√］删除与该表相关的所有索引和触发器。

［ √ ］如果有视图或 PL/SQL 过程依赖于该表，这些视图或PL/SQL过程将被置于不可⽤状态。

［√］从数据字典中删除该表的定义。

［√］回收为该表分配的存储空间。

DROP TABLE 语句有⼀个可选⼦句 CASCADE CONSTRAINTS 。当使⽤该参数时，DROP TABLE

不仅仅删除该表，⽽且所有引⽤这个 表的视图、约束或触发器等也都被删除。

⑥修改表状态

alter table students_5 read only;
alter table students_5 read write;
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值。

5、数据完整性和约束性

①非空约束 not null

SQL> create table Books
 2 (
3 BookNo number(4) not null, //图书编号，不
为空
 4 BookName varchar2(20), //图书名称
 5 Author varchar2(10), //作者
 6 SalePrice number(9,2), //定价
7 PublisherNo varchar2(4) not null, //出版社编号，
不为空
 8 PublishDate date, //出版⽇期
 9 ISBN varchar2(20) not null //ISBN，不为空
 10 );

SQL> alter table Books modify bookname not null;
alter table @tablename modify @columnname not null;

②主键约束 primary key

例：

创建表 Books_1 ，并为该表定义⾏级主键约束 BOOK_PK（主键列为 BookNo ）

constraint @主键约束名 primary key (主键列，主键列)

SQL> create table Books_1
 2 (
3 BookNo number(4) not null, 
//图书编号
4 BookName varchar2(20), 
//图书名称
5 Author varchar2(10), 
//作者
6 SalePrice number(9,2), 
//定价
7 PublisherNo varchar2(4) not null, 
//出版社编号
8 PublishDate date, 
//出版⽇期
9 ISBN varchar2(20) not null,- 
//ISBN
10 constraint BOOK_PK primary key (BookNo) 
//创建主键和主键约束
 11 );
表已创建



SQL> connect hr/hr 
//在hr模式下
已连接

SQL> create table departments_temp
 2 as select * from departments
 3 where department_id = 30; 
//创建departments_temp P表
表已创建

SQL> alter table departments_temp add primary key(department_id); 
//设置departments_temp表的主键约束
表已更改

添加主键约束：

alter table Books add constraint Books_PK primary key(BookNo);

系统自动分配名称版：

alter table Books add primary key(BookNo);

删除主键约束：

alter table Books_1 drop constraint BOOK_PK;

③唯一性约束 unique

 create table Members(QQ varchar2(20) Constraint QQ_UK unique, //QQ
号，并设置为UNIQUE约束 );
表已创建、

④外键约束

最典型的外键约束是 HR 模式中的 EMPLOYEES 和 DEPARTMENT表 ， 在 该 外 键 约 束 中 ， 外 键 表 EMPLOYEES 中 的 外 键 列DEPARTMENT_ID 引 ⽤ 被 引 ⽤ 表 DEPARTMENTS 中 的

DEMPARTMENT_ID 列，⽽该列也是 DEPARTMENTS表的主键。

在已有引用表的基础上创建外键约束

SQL> create table employees_temp
 2 as select * from employees
 3 where department_id=30; //创建⼀个新表，并将部门编号为30的员⼯记录插⼊
表已创建

 alter table employees_temp 
add constraint temp_departid_fk 
foreign key(department_id) references departments(department_id); 
//创建外键约束，外键列为department_id
表已更改

语法：
alter table @引用表
add constraint @约束名 foreign key(@外键列名) reference @被引用表（@被引用表名）

如果外键列与被引用的列名相同，可去掉references表名后的括号列名

在创建表时一起创建外键约束

 
--学生表
create table student(
       pid varchar2(10) primary key,
       pname varchar2(10)
       
);
--学生选择课程
create table s_course(
       pid varchar(10) constraint FK_pid references student(pid),
       cname varchar(20)
);

在引用表中插入数据，插入的 外键列的值 一定要是 被引用表的外键列值 有的

［√］在定义外键约束时，如果使⽤了关键字 NO ACTION ，那么

当删除被应⽤表中被引⽤类的数据时将违反外键约束，该操作将

被禁⽌执⾏，这也是外键的 “ 默认引⽤类型 ” 。

［√］在定义外键约束时，如果使⽤了关键字 SET NULL ，那么当

被引⽤表中被引⽤列的数据被删除时，外键表中外键列被设置为

NULL ，要使这个关键字起作⽤，外键列必须⽀持 NULL 值。

［√］在定义外键约束时，如果使⽤了 CASCADE 关键字，那么当

被引⽤表中被引⽤列的数据被删除时，外键表中对应的数据也将

被删除，这种删除⽅式通常称作 “ 级联删除 ” ，它在实际应⽤程序

开发中得到⽐较⼴泛应⽤。

级联删除应用;

SQL> alter table employees_temp
 2 add constraint temp_departid_fk2
 3 foreign key(department_id)
 4 references departments_temp on delete cascade;
表已更改

删除外键约束

alter table @tablename drop contraint @contraintname;

 ⑤禁用和激活约束

1、定义约束时禁用 disable

SQL> create table Student
 2 (
 3 StuCode varchar2(4) not null,
 4 StuName varchar2(10) not null,
5 Age int constraint Age_CK check (age > 0 and age <120)
disable,
 6 Province varchar2(20),
 7 SchoolName varchar2(50)
 8 );
表已创建

2、禁用已经存在的约束

alter table @tablename disable constraint @约束名

3、激活

alter table @tablename enable [novalidate |validate] constraint @约束名

⑥删除约束

alter table table_name drop constraint con_name;