oracle数据库系统表-清除oracle表数据

DBA 每天都会使用许多大大小小的指令。 本文总结了这些说明，希望能帮助您查看。 喜欢记得转发分享+“在看”。

1.启动和关闭Oracle数据库

1.数据库启动

以 SYSDBA 身份登录

数据库启动命令：STARTUP [启动选项]

数据库分三个阶段启动：

2.关闭数据库

以 SYSDBA 身份登录

数据库关闭命令：SHUTDOWN [启动选项]

数据库关闭分为三个阶段：

2.启动和关闭oracle监听进程

Listener lsnrctl：提供数据库访问，默认端口1521

为了使客户端用户能够连接到 Oracle 实例，启动侦听器

1.开始监控

启动（START）监控是Oracle用户在操作系统下执行的命令。 可以直接在LSNRCTL后加参数，也可以在命令提示符后操作。

2.关闭显示器

注意：先启动监视器，再启动数据库。

三、表空间

1.创建表空间

参数说明：

表空间名称：表空间名称

DATAFILE：指定组成表空间的一个或多个数据文件，有多个数据文件时用逗号隔开

filename：数据文件在表空间中的路径和名称

SIZE：指定文件的大小，用K指定大小以千字节为单位，用M指定大小以兆字节为单位

AUTOEXTEND：用于启用或禁用数据文件的自动扩展

例子：

2.表空间管理

(1)调整表空间大小。 当表空间满时，可以使用ALTER语句调整表空间的大小。

方法一：改变数据文件的大小，指定数据文件的存放路径。 通过使用RESIZE关键字，用于指定调整后的表空间的大小

方法二：在表空间中添加数据文件。为表空间添加一个新的数据文件

(2)改变表空间的读写状态。

 ALTER  TABLESPACE  表空间名  READ   WRITE ;      --使表空间可读写 ALTER  TABLESPACE  表空间名  READ   ONLY ;       --使表空间只读

(3)删除表空间，可以通过DROP语句删除表空间oracle数据库系统表，加上表空间的名称。

DROP TABLESPACE 表空间名称[包括内容]；

其中 INCLUDING CONTENTS 是可选的。 如果你删除了一个仍然包含数据的表空间，你需要添加这个选项。

4.用户管理

1.创建用户

例子：

2.修改用户密码

ALTER USER 用户名 IDENTIFIED BY 密码；

3. 删除用户。 使用 DROP USER 命令删除用户。 当用户拥有模式对象时，不能删除用户，但必须使用CASCADE选项来删除用户和用户模式对象。

DROP USER 用户名 CASCADE；

创建用户前准备：

1）选择用户名和密码

2）识别用户需要存放对象的表空间

3）确定每个表空间的配额

4）分配默认表空间和临时表空间

5）创建用户

6) 授予用户权限和角色

5.数据库权限管理

1.系统权限

系统权限是指有权在数据库中执行某些系统级别的操作，或者对某些类型的对象执行某些操作

常用的系统权限如下：

CREATE SESSION：连接数据库

CREATE TABLE：创建一个表

CREATE VIEW：创建视图

CREATE SEQUENCE：创建序列

2.对象权限

对象权限是指对特定模式对象执行操作的权利。 只能为架构对象设置管理对象权限，包括：表、视图、序列和存储过程。

Oracle数据用户获取权限有两种方式：

(1)直接给用户授予权限

(2) 将权限授予角色，再将角色授予一个或多个用户。 使用角色可以更方便、更高效地管理权限，所以数据库管理员通常使用角色来给用户授予权限，而不是直接给用户授予权限。

Oracle中常见的系统预定义角色如下：

(1)CONNECT：有连接数据库的权限

(2)RESOURCE：具有创建表、触发器、过程等的权限。

(3)DBA：数据库管理员的角色，拥有管理数据库的最高权限

3、新创建的用户必须被授予一定的权限才能进行相关的数据库操作。

授权是通过 GRANT 语句，取消授权是通过 REVOKE 语句。

授予权限的语法如下：

授予权限 | 用户名的角色；

撤销权限的语法如下：

撤销权限 | 来自用户名的角色；

6.交易控制

COMMIT：提交事务，即在事务中永久保存对数据库的修改。

ROLLBACK：回滚事务，即取消对数据库所做的任何修改

自动提交：设置自动提交

关闭自动提交：set autocommit off

7.索引

索引是oracle的一个对象，是一种与表关联的可选结构，它提供了一种快速访问数据的方式，提高了数据库的检索性能。 索引使数据库程序无需扫描整个表即可找到所需的数据。 就像一本书的目录，你可以通过目录快速找到你需要的信息，而不用阅读整本书。

一、指数特点

正确使用索引可以提高查询速度

可以索引表的一列或多列

建立无限数量的索引

索引需要磁盘存储，可以指定表空间，由oracle自动维护

索引对用户透明，查找时是否使用索引由oracle自己决定

Oracle数据库管理系统在访问数据时使用以下三种方法：

当没有索引或者不选择使用索引时，使用全表扫描方式

2.指标分类

（1）创建普通索引（B树索引）

参数说明：

index_name：创建索引的名称

tablename：要为其创建索引的表的名称

columnname：创建索引的列名列表，可以基于多个列创建索引，以逗号分隔

tablespace：指定索引的表空间

(2) 创建唯一索引

保证定义索引的列中没有两行具有重复值。 唯一索引中的索引键只能指向表中的一行。

(3) 创建反向键索引

与常规 B 树索引相反，反向键索引反转索引列的字节，同时保持列的顺序。 反向键索引通过反转索引键的数据值，使索引的修改均匀分布在整个索引树上。 主要用于多个实例同时访问一个数据库的场景。

(4) 创建位图索引

优点：与B树索引相比，基于位图索引列的查询可以减少响应时间。

与其他索引技术相比，位图索引占用的空间要少得多。

（五）其他指标

复合索引：在表的多个列上创建。 索引中的列不必与表中的列顺序相同，也不必彼此相邻。

基于函数的索引：要创建的索引需要使用表中一个或多个列的函数或表达式，基于函数的索引也可以创建为B树索引或位图索引

3. 创建索引的原则

经常搜索的列可以作为索引列

经常排序、分组的列可以用作索引

可以索引经常用作连接的列（主键/外键）

将索引放在单独的表空间中，不要放在有回滚段、临时段和表的表空间中

对于大型索引，请考虑使用 NOLOGIN 子句创建大型索引。

根据业务数据的频率，定期重新生成或重组索引并进行碎片整理

4.查看索引

索引相关的数据字典：

USER_INDEXS：用户创建的索引信息

USER_IND_COLUMNS：索引相关的表列信息

5.维护索引

(1)重建索引

索引需要维护。 如果索引表中有大量的删除和插入操作，那么索引就会非常大，因为删除操作后，被删除值的索引空间不能自动重用。 对于大表和DML操作非常频繁的表，索引维护非常重要。 Oracle 提供了rebuild 命令来重建索引。 允许索引空间重用删除值占用的空间，使索引更加紧凑。

(2) 合并索引分片

合并索引碎片可以释放部分磁盘空间，这是索引维护的一种重要方法，也是维护磁盘空间的一种方式，类似于磁盘碎片整理，释放未使用的空间以供重新使用。

(3)重命名索引

(4) 删除索引

8.查看

视图是不占用物理空间的虚拟表，因为视图本身的定义语句保存在数据字典中，视图中的数据是从一个或多个实际表中获取的。 用于生成视图的表称为视图的基表。 一个视图也可以从另一个视图生成。

一、观点优势：

1) 提供另一个级别的表安全

2）隐藏数据的复杂性：一个视图可能定义了多表连接，但用户可以在不知道多表连接语句的情况下查询数据。

3）简化用户SQL命令：查询视图时，不需要写复杂的查询语句，只需要查询视图名即可。

4）隔离基表结构的变化：视图创建后，如果修改了表的结构，不会影响视图。

5) 换个角度提供数据，通过重命名列：比如在销售系统中，每天下班前必须汇总当天的数据。 在销售人员眼中，汇总表就变成了每天的销售统计表。 在财务人员眼里，这张销售表就成了每天的销售报表

2.创建视图

参数说明：

OR REPLACE：此选项将重新创建视图（如果它已存在）。

FORCE：如果使用该关键字，则无论基表是否存在，都会创建视图。

NOFORCE：这是默认的，如果使用这个关键字，只有当基表存在时才会创建视图。

VIEW_NAME：要创建的视图的名称

别名：指定视图查询选择的表达式或列的别名。 别名的数量必须与视图选择的表达式的数量相匹配。

select_statement：选择语句

WITH CHECK OPTION ：此选项指定只能插入或更新视图可访问的行。 术语约束表示赋予 CHECK OPTION 约束的名称。

WITH READ ONLY：该选项保证不能对该视图进行任何修改操作。

3. DML语句和复杂视图

DML 语句是指用于修改数据的插入、删除和更新语句。 因为视图是一个虚拟表，所以这些语句也可以用于视图。 一般不通过视图修改数据，而是直接修改基表，因为规则比较清晰。

在视图上使用 DML 语句具有以下限制（相对于表）。

1）DML语句只能修改视图中的一个基表。

2）如果记录的修改违反了基表的约束，则视图不能更新。

3）如果创建的视图中包含join operators、DISTINCT operators、set operators、aggregate functions、groupby clause，则无法更新视图。

4) 如果创建包含伪列表达式的视图，将无法更新视图。

一个简单的视图是基于单个基表，不包括函数和分组函数，那么你可以在这个视图中进行插入、更新、删除操作，实际上是在基表中插入、更新和删除行。

复杂视图从多个表中提取数据，包括功能分组功能。 复杂视图不一定能够进行 DML 操作。

4.查询视图

从用户视图中选择视图名称；

5.删除视图

删除视图 view_name；

6.物化视图

含义：是有物理存储的特殊视图，占用物理空间，就像表一样

是远程数据的本地副本，或者用于根据数据表的总和生成汇总表

物化视图中的两个重要概念：查询重写和物化视图同步

(1)查询重写：重写sql语句。 当用户使用sql语句查询基表时，如果已经建立了基于这些表的物化视图，则oracle会自动计算并使用物化视图完成查询。 在某些情况下，它可以节省查询时间并减少系统 I/O。 Oracle 将这种查询优化技术称为查询重写。 参数QUERY_REWRITE_ENABLED 决定是否使用rewritten query，为Boolean。 在创建物化视图时，需要使用ENABLE_QUERY REWRITE开启查询重写功能。 该参数的值可以通过SHOW命令查看。

(2)物化视图的同步：

物化视图是基于表创建的，所以当基表发生变化时，需要同步数据更新物化视图中的数据，以保持物化视图中数据与基表数据的一致性. Oracle为物化视图提供了两种刷新方式来决定何时刷新，即ON COMMIT和ON DEMAND。

ON COMMIT模式：表示在提交基表的DML操作事务的同时刷新物化视图。

ON DEMAND模式：表示在用户需要时更新物化视图。 可以通过DBMS_MVIEW.REFRESH等方式手动刷新，也可以通过JOB定时刷新。

选择刷新方式后，还需要选择刷新类型。 刷新类型指定刷新时如何在基表和物化视图之间同步数据。 Oracle 提供了以下四种刷新类型。

COMPLETE：完全刷新整个物化视图。

FAST：使用增量刷新，只刷新自上次刷新后所做的修改。

FORCE：oracle在刷新的时候会判断是否可以快速刷新。 如果可以，它将使用 FAST 方法oracle数据库系统表，否则将使用 COMPLETE 方法。

NEVER：实体化视图不执行任何刷新。

默认为 FORCE 刷新类型。

创建物化视图

(1)授予权限，具有创建物化视图的权限，QUERY REWRITE的权限，以及访问和创建创建物化视图涉及的表的权限。

SCOTT 用户的演示

(2)创建物化视图日志

(3)创建物化视图语句

在：

bulid immediate：这个参数的意思是立即创建物化视图，也可以选择build deferred。 该参数表示实体化视图确定后不会立即执行，而是延迟执行，在使用视图时再创建。

Reffesh fast：刷新数据类型选择FAST类型。

ON COMMIT：更新基表时，提交后立即更新物化视图。

ENABLE QUERY REWRITE：启用查询重写功能。 在创建物化视图时明确声明启用了查询重写。

As：定义如下查询语句。

查询体：物化视图的查询内容。 SQL语句的查询结果集输出到物化视图，存储在oracle自动创建的表中。

(4)删除物化视图

删除实体化视图 view_name；

9.顺序

序列用于生成唯一的、连续的整数数据库对象。 序列通常用于自动生成主键值或唯一键值。 序列可以按升序或降序排序。

1. 创建序列

参数说明：

2.访问顺序

创建序列后，可以通过 NEXTVAL 和 CURRVAL 伪列访问其值。 可以从伪列中选择值。 但他们的价值观是无法被操纵的。

NETXVAL：创建序列后第一次使用NEXTVAL，会返回序列的初始值。 对 NETXVAL 的后续引用将使用 INCREMENT BY 子句的值递增序列值并返回此新值。

CURRVAL：返回序列的当前值，也就是上次引用NEXTVAL时返回的值

例子：

创建序列

创建表

插入数据

查看数据

查看序列的当前值

Currval返回序列的当前值，也就是上次引用NEXTVAL时返回的值

测试曲线

重启实例后，再次写入数据，发现是从40开始的，因为按照创建序列的要求，每次都会往缓存中放入30个序号。 实例重启后，缓存中的序列会消失

3.改变顺序

Alter sequence 命令用于修改序列的定义。 如果执行以下操作，则会修改序列。

设置或删除 MINVALUE 或 MAXVALUE

修改增量值

修改缓存中序列号的个数

不能修改序列的 START WITH 参数

4.删除序列

10.同义词

同义词是对象的别名，不占用任何实际存储空间，只在oracle数据字典中保存其定义描述。 当使用同义词时，oracle会把它翻译成对应对象的名称。

1。目的：

创建数据库链接的语法：

CREATE DATABASE LINK 数据库链接名 CONNECT TO 用户名 IDENTIFIED BY 密码 USING 'Oracle connection string';

2.近义词分类

(1) 私有同义词

私有同义词只能被当前模式的用户访问，私有同义词的名称不能与当前模式的对象名称相同。 要在其自己的架构中创建私有同义词，用户必须具有创建同义词系统权限。 要在其他用户模式下创建私有同义词，用户必须具有 create any synonym 系统权限。

语法：

CREATE [OR REPLACE] SYNONYM  [schema.]synonym_name  FOR  [schema.]object_name ;

参数说明：

(2) 共享同义词

所有数据库都可以访问公共同义词。 公共同义词可以隐藏基表的身份，降低 SQL 语句的复杂度。 要创建公共公共同义词，用户必须具有创建公共 SYNOYM 系统权限。

语法：

CREATE   [OR REPLACE]     PUBLIC    SYNONYM    [schema.]synonym_name    FOR    [schema.]object_name ;

(3) 删除同义词

DROP [PUBLIC] SYNONYM  [schema.]synonym_name;

十一、分区表

1、含义：

Oracle 允许将一个表的所有行分成若干部分，并将它们存储在不同的表空间中。 分区的每个部分成为一个分区，分区表成为分区表。

分区对于包含大量数据的表很有用。 优点如下：

1）提高表的查询性能。 对表进行分区后，用户在执行SQL查询时，只能访问表中的特定分区，不能访问整个表。

2) 表格更易于管理。 由于分区表的数据存储在多个部分，因此按分区加载和删除数据比在表内更容易。

3）易于备份和恢复。 每个分区都可以独立备份和恢复。

4) 提高数据安全性。 将不同的分区分布在不同的磁盘上，可以减少所有分区同时出现数据损坏的可能性。