oracle——基本语句参考02

迈不过友情╰ 2023-10-02 08:14 18阅读 0赞

网址：[https://mp.weixin.qq.com/s?\_\_biz=MzI5MzIwNDI1MQ==&mid=2650131616&idx=2&sn=68fc895fe8846901de4369fcbcc91835&chksm=f474d4d4c3035dc2be66b9032fef0878fa6101373087884844d95d626e50525dd26b4f2e25cb&scene=0&xtrack=1\#rd][https_mp.weixin.qq.com_s_biz_MzI5MzIwNDI1MQ_mid_2650131616_idx_2_sn_68fc895fe8846901de4369fcbcc91835_chksm_f474d4d4c3035dc2be66b9032fef0878fa6101373087884844d95d626e50525dd26b4f2e25cb_scene_0_xtrack_1_rd]

DBA们每天会用到大大小小很多指令，本文对这些指令做了归纳总结，希望可以帮助大家查阅翻看，喜欢记得转发分享+“在看”哦。

**一、启动和关闭Oracle数据库**

--------------------

**1. 数据库启动**

以SYSDBA身份登录

数据库启动命令：STARTUP 【启动选项】

数据库启动三个阶段：

*  启动Oracle实例(非安装阶段)
 *  由实例安装数据库(安装阶段)
 *  打开数据库(打开阶段)

![4d82b806d07b07ca63704218940c9e41.png][]

![e18a932c52e419f63043fdd7df0406c4.png][]

**2. 数据库的关闭**

以SYSDBA身份登录

数据库关闭命令：SHUTDOWN 【启动选项】

数据库关闭三个阶段：

*  关闭数据库
 *  卸载数据库
 *  关闭Oravle实例

![0dfc0a8d6d203bb77d0e0e32ab4884f4.png][]

![66d42cc252514bc7b9ca3d520920b016.png][]

**二、启动、关闭Oracle监听进程**

--------------------

监听器lsnrctl：提供数据库访问，默认端口1521

为了使客户端用户能连接到Oracle实例，要启动监听

**1.启动监听**

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

![366980070e7c7c7caf887ef6f388640c.png][]

**2.关闭监听**

**![b993a280eb2515215142afcb777ddcc1.png][]**

注意：先启动监听，后启动数据库。

**三、表空间**

--------------------

**1. 创建表空间**

![d98109e01a7d199be56b68881eea35a7.png][]

参数解释：

tablespacename：表空间名称

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

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

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

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

举例：

![7684ad55c5ec5b98889c425133c7e9e2.png][]

**2. 表空间的管理**

(1)调整表空间的大小。当表空间已满的情况下，可以通过ALTER语句来调整表空间的大小。

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

![aa4725845324284f3246ad09a808abdb.png][]

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

![279e79a6e77ed82d362ff66b90b1081b.png][]

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

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

(3)删除表空间，可以通过DROP语句来删除表空间，再加上表空间的名字即可。

DROP TABLESPACE 表空间名 \[INCLUDING CONTENTS\] ;

其中INCLUDING CONTENTS是可选项。如果删除仍包含数据的表空间，需要加上该选项。

**四、用户管理**

--------------------

**1. 创建用户**

![42245d2a8f8d5fcf408c6fdcbf2303a6.png][]

举例：

![36d4c08ad0e84dca61e6bc90aa7ead38.png][]

**2. 修改用户的密码**

ALTER USER 用户名 IDENTIFIED BY 密码 ；

**3. 删除用户。使用DROP USER命令可以删除用户，当用户拥有模式对象时则无法删除用户，而必须使用CASCADE选项以删除用户及用户模式对象。**

DROP USER 用户名 CASCADE；

创建用户前的准备工作：

1) 选择用户名和密码

2) 识别用户需用于存储对象的表空间

3) 决定每个表空间的限额

4) 分配缺省表空间和临时表空间

5) 创建用户

6) 向用户授予权限和角色

**五、数据库权限管理**

--------------------

**1. 系统权限**

系统权限是指在数据库中执行某种系统级别的操作，或者针对某一类对象执行某种操作的权利。

![13b8ad111aab1407596ef9ae1e9ae45a.png][]

常用的系统权限如下：

CREATE SESSION：连接到数据库

CREATE TABLE：创建表

CREATE VIEW：创建视图

CREATE SEQUENCE：创建序列

**2. 对象权限**

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

![a01f46dd725750a2e928972d87c11142.png][]

Oracle数据用户有两种途径获得权限：

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

(2)将权限授予给角色，再将角色授予给一个或多个用户。使用角色能够更加方便和高效地对权限进行管理，所以数据库管理员通常使用角色向用户授予权限，而不是直接向用户授予权限。

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

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

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

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

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

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

**授予权限语法**格式如下：

GRANT 权限 | 角色 TO 用户名；

![e64c6c473ca277a4492f320d4f7867c1.png][]

**撤销权限语法**格式如下：

REVOKE 权限 | 角色 FROM 用户名；

![36b8c140b79708afd12af13b566b1424.png][]

**六、事务控制**

--------------------

![dcf4dc4a2ba06c53be474c81bbe2d38f.png][]

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

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

自动提交：set autocommit on

关闭自动提交：set autocommit off

**七、索引**

--------------------

索引是oracle的一个对象，是与表关联的可选结构，提供了一种快速访问数据的途径，提高了数据库检索性能。索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需要的数据。就像书的目录，可以通过目录快速查找所需信息，无需阅读整本书。

**1、索引的特点**

适当地使用索引可以提高查询速度

可以对表的一列或多列建立索引

建立索引的数量没有限制

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

索引对用户透明，检索时是否使用索引由oracle自身决定

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

*  全表扫描
 *  通过ROWID(行地址，快速访问表的一行)
 *  使用索引

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

**2.索引的分类**

![2e1ac373286e296b463c87432458c1b9.png][]

**(1)创建普通索引(B树索引)**

![ec4b6bb265e29987dc261f9fdf6edb58.png][]

参数解释：

index\_name：创建索引的名称

tablename：为之创建索引的表名

columnname:在其上创建索引的列名列表，可以基于多列创建索引，列之间用逗号分隔

tablespace:为索引指定表空间

**(2)创建唯一索引**

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

![28c1f0b0b204e50cdcf5edaba9c9ce11.png][]

**(3)创建反向键索引**

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

![8e6ccef1d2f88dba402cade527606ad3.png][]

**(4)创建位图索引**

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

相比其他索引技术，位图索引占用空间明显减少。

![86dc736df6f23a10966fe5b1925ad0ea.png][]

![e8b3de1db8d14a07df4c8a8e45e0a814.png][]

**(5)其他索引**

组合索引：在表内多列上创建。索引中的列不必与表中的列顺序一致，也不必相互邻接。

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

![c314704d3a56444ab0b154290776ec7c.png][]

**3. 创建索引的原则**

频繁搜索的列可以作为索引列

经常排序，分组的列可以作为索引

经常用作连接的列(主键/外键)可以作为索引

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

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

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

**4. 查看索引**

和索引有关的数据字典：

USER\_INDEXS：用户创建的索引信息

USER\_IND\_COLUMNS：与索引相关的表列信息

![9d0ac71f29efd5d11979bfb32b8dbe50.png][]

**5. 维护索引**

**(1)重建索引**

索引需要维护，如果建立了索引的表中有大量的删除和插入操作，会使得索引很大，因为删除操作后，删除值的索引空间不能被自动重新使用，对于大表和DML操作很频繁的表，索引的维护是很重要的。Oracle提供了rebuild指令来重建索引。使索引空间可以重用删除值所占用的空间，使索引更加紧凑。

![8153ddff1147163d31768d07319d6bea.png][]

(2)合并索引碎片

合并索引碎片可以释放部分磁盘空间，是索引维护的一种重要方式，也是维护磁盘空间的方式，类似于磁盘碎片整理，把不用的空间释放出来再利用。

![91d8204624f9e6d8faf5f8304d73ca41.png][]

(3)重命名索引

![3236d630d512595ab50a9ec2e2659afa.png][]

(4)删除索引

![794290e1f2e2398ce0dab8824b0522f5.png][]

**八、视图**

--------------------

视图是一个虚表，不占用物理空间，因为视图本身的定义语句存储在数据字典里，视图中的数据是一个或多个实际表中获得的。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

**1. 视图的优点：**

1) 提供了另外一种级别的表安全性

2) 隐藏的数据的复杂性：一个视图可能是用多表连接定义的，但用户不需要知道多表连接的语句也可以查询数据。

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

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

5)通过重命名列，从另一个角度提供数据：例如在销售系统中，每日下班前要对当日数据进行汇总，在销售人员眼中，该汇总表成为日销售统计表，在财务人眼中，该销售表成为销售日报表

**2. 创建视图**

![86bec730b69fc40d69c69a2bdb28e6a9.png][]

参数解释：

OR REPLACE:如果视图已存在，此选项将重新创建该视图。

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

NOFORCE：这是默认值，如果使用此关键字，则仅当基表存在时才创建视图。

VIEW\_NAME：要创建视图的名称

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

Select\_statement:select语句

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

WITH READ ONLY：此选项保证不能在此视图上执行任何修改操作。

**3. DML语句和复杂视图**

DML语句是指用于修改数据的insert、delete和update语句。因为视图是一个虚拟的表，所以这些语句也可以与视图一同使用。一般情况下不通过视图修改数据，而是直接修改基表，因为这样条例更清晰。

在视图上使用DML语句有如下限制(相对于表)。

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

2)如果过记录的修改违反了基表的约束条件，则将无法更新视图。

3)如果创建的视图包含连接运算符，DISTINCT运算符、集合运算符、聚合函数和groupby子句，则将无法更新视图。

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

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

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

**4. 查询视图**

select view\_name from user\_views;

**5. 删除视图**

drop view view\_name;

**6. 物化视图**

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

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

![ef0b166b5c85b184a5a82cc776e53217.png][]

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

(1)**查询重写**：对sql语句进行重写，当用户使用sql语句对基表进行查询时，如果已经建立了基于这些表的物化视图，oracle将自动计算和使用物化视图来完成查询，在某些情况下可以节约查询时间，减少系统i/o。Oracle将这种查询优化技术成为查询重写。参数QUERY\_REWRITE\_ENABLED决定是否使用重写查询，该参数为布尔型。在创建物化视图需要用ENABLE\_QUERY REWRITE来启动查询重写功能。通过SHOW指令可以查看该参数的值。

(2)**物化视图的同步：**

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

ON COMMIT方式：指物化视图在对基表的DML操作事务提交的同时进行刷新。

ON DEMAND方式：指物化视图在用户需要的时候进行更新，可以手工通过DBMS\_MVIEW.REFRESH等方式来进行刷新，也可以通过JOB定时进行刷新。

选择刷新方式之后，还需要选择一种刷新类型，刷新类型指定刷新时基表与物化视图如何实现数据的同步，oracle提供了以下4种刷新类型。

COMPLETE:对整个物化视图进行完全的刷新。

FAST：采用增量刷新，只刷新自上次刷新后进行的修改。

FORCE:oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE方式。

NEVER:物化视图不进行任何刷新。

默认值是FORCE刷新类型。

**创建物化视图**

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

通过SCOTT用户来演示

![28bd212971a3b5a9ff60de1e7a8ae0e3.png][]

(2)创建物化视图日志

![aab16a46514c45e67d1f4d8dcb14e3a9.png][]

(3)创建物化视图语句

![c3bbf2c9a24fa256a7ed3ab7edf3404e.png][]

其中：

bulid immediate：该参数的含义是立即创建物化视图，也可以选择build deffered，该参数说明在物化视图定以后不会立即执行，而是延迟执行，在使用该视图时再创建。

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

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

ENABLE QUERY REWRITE:启动查询重写功能。在创建物化视图时明确说明启用查询重写功能。

As：定义后面的查询语句。

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

**(4)删除物化视图**

drop materialized view view\_name;

[https_mp.weixin.qq.com_s_biz_MzI5MzIwNDI1MQ_mid_2650131616_idx_2_sn_68fc895fe8846901de4369fcbcc91835_chksm_f474d4d4c3035dc2be66b9032fef0878fa6101373087884844d95d626e50525dd26b4f2e25cb_scene_0_xtrack_1_rd]: https://mp.weixin.qq.com/s?__biz=MzI5MzIwNDI1MQ==&mid=2650131616&idx=2&sn=68fc895fe8846901de4369fcbcc91835&chksm=f474d4d4c3035dc2be66b9032fef0878fa6101373087884844d95d626e50525dd26b4f2e25cb&scene=0&xtrack=1#rd
[4d82b806d07b07ca63704218940c9e41.png]: https://img-blog.csdnimg.cn/img_convert/4d82b806d07b07ca63704218940c9e41.png
[e18a932c52e419f63043fdd7df0406c4.png]: https://img-blog.csdnimg.cn/img_convert/e18a932c52e419f63043fdd7df0406c4.png
[0dfc0a8d6d203bb77d0e0e32ab4884f4.png]: https://img-blog.csdnimg.cn/img_convert/0dfc0a8d6d203bb77d0e0e32ab4884f4.png
[66d42cc252514bc7b9ca3d520920b016.png]: https://img-blog.csdnimg.cn/img_convert/66d42cc252514bc7b9ca3d520920b016.png
[366980070e7c7c7caf887ef6f388640c.png]: https://img-blog.csdnimg.cn/img_convert/366980070e7c7c7caf887ef6f388640c.png
[b993a280eb2515215142afcb777ddcc1.png]: https://img-blog.csdnimg.cn/img_convert/b993a280eb2515215142afcb777ddcc1.png
[d98109e01a7d199be56b68881eea35a7.png]: https://img-blog.csdnimg.cn/img_convert/d98109e01a7d199be56b68881eea35a7.png
[7684ad55c5ec5b98889c425133c7e9e2.png]: https://img-blog.csdnimg.cn/img_convert/7684ad55c5ec5b98889c425133c7e9e2.png
[aa4725845324284f3246ad09a808abdb.png]: https://img-blog.csdnimg.cn/img_convert/aa4725845324284f3246ad09a808abdb.png
[279e79a6e77ed82d362ff66b90b1081b.png]: https://img-blog.csdnimg.cn/img_convert/279e79a6e77ed82d362ff66b90b1081b.png
[42245d2a8f8d5fcf408c6fdcbf2303a6.png]: https://img-blog.csdnimg.cn/img_convert/42245d2a8f8d5fcf408c6fdcbf2303a6.png
[36d4c08ad0e84dca61e6bc90aa7ead38.png]: https://img-blog.csdnimg.cn/img_convert/36d4c08ad0e84dca61e6bc90aa7ead38.png
[13b8ad111aab1407596ef9ae1e9ae45a.png]: https://img-blog.csdnimg.cn/img_convert/13b8ad111aab1407596ef9ae1e9ae45a.png
[a01f46dd725750a2e928972d87c11142.png]: https://img-blog.csdnimg.cn/img_convert/a01f46dd725750a2e928972d87c11142.png
[e64c6c473ca277a4492f320d4f7867c1.png]: https://img-blog.csdnimg.cn/img_convert/e64c6c473ca277a4492f320d4f7867c1.png
[36b8c140b79708afd12af13b566b1424.png]: https://img-blog.csdnimg.cn/img_convert/36b8c140b79708afd12af13b566b1424.png
[dcf4dc4a2ba06c53be474c81bbe2d38f.png]: https://img-blog.csdnimg.cn/img_convert/dcf4dc4a2ba06c53be474c81bbe2d38f.png
[2e1ac373286e296b463c87432458c1b9.png]: https://img-blog.csdnimg.cn/img_convert/2e1ac373286e296b463c87432458c1b9.png
[ec4b6bb265e29987dc261f9fdf6edb58.png]: https://img-blog.csdnimg.cn/img_convert/ec4b6bb265e29987dc261f9fdf6edb58.png
[28c1f0b0b204e50cdcf5edaba9c9ce11.png]: https://img-blog.csdnimg.cn/img_convert/28c1f0b0b204e50cdcf5edaba9c9ce11.png
[8e6ccef1d2f88dba402cade527606ad3.png]: https://img-blog.csdnimg.cn/img_convert/8e6ccef1d2f88dba402cade527606ad3.png
[86dc736df6f23a10966fe5b1925ad0ea.png]: https://img-blog.csdnimg.cn/img_convert/86dc736df6f23a10966fe5b1925ad0ea.png
[e8b3de1db8d14a07df4c8a8e45e0a814.png]: https://img-blog.csdnimg.cn/img_convert/e8b3de1db8d14a07df4c8a8e45e0a814.png
[c314704d3a56444ab0b154290776ec7c.png]: https://img-blog.csdnimg.cn/img_convert/c314704d3a56444ab0b154290776ec7c.png
[9d0ac71f29efd5d11979bfb32b8dbe50.png]: https://img-blog.csdnimg.cn/img_convert/9d0ac71f29efd5d11979bfb32b8dbe50.png
[8153ddff1147163d31768d07319d6bea.png]: https://img-blog.csdnimg.cn/img_convert/8153ddff1147163d31768d07319d6bea.png
[91d8204624f9e6d8faf5f8304d73ca41.png]: https://img-blog.csdnimg.cn/img_convert/91d8204624f9e6d8faf5f8304d73ca41.png
[3236d630d512595ab50a9ec2e2659afa.png]: https://img-blog.csdnimg.cn/img_convert/3236d630d512595ab50a9ec2e2659afa.png
[794290e1f2e2398ce0dab8824b0522f5.png]: https://img-blog.csdnimg.cn/img_convert/794290e1f2e2398ce0dab8824b0522f5.png
[86bec730b69fc40d69c69a2bdb28e6a9.png]: https://img-blog.csdnimg.cn/img_convert/86bec730b69fc40d69c69a2bdb28e6a9.png
[ef0b166b5c85b184a5a82cc776e53217.png]: https://img-blog.csdnimg.cn/img_convert/ef0b166b5c85b184a5a82cc776e53217.png
[28bd212971a3b5a9ff60de1e7a8ae0e3.png]: https://img-blog.csdnimg.cn/img_convert/28bd212971a3b5a9ff60de1e7a8ae0e3.png
[aab16a46514c45e67d1f4d8dcb14e3a9.png]: https://img-blog.csdnimg.cn/img_convert/aab16a46514c45e67d1f4d8dcb14e3a9.png
[c3bbf2c9a24fa256a7ed3ab7edf3404e.png]: https://img-blog.csdnimg.cn/img_convert/c3bbf2c9a24fa256a7ed3ab7edf3404e.png