Oracle 常用的十一大操作指令

avatar
作者
筋斗云
阅读量:17

一、启动和关闭Oracle数据库

1. 数据库启动

以SYSDBA身份登录

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

数据库启动三个阶段:

oracle修改用户的表空间_oracle修改用户表空间_oracle修改表空间状态

oracle修改用户的表空间_oracle修改表空间状态_oracle修改用户表空间

2. 数据库的关闭

以SYSDBA身份登录

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

数据库关闭三个阶段:

oracle修改用户表空间_oracle修改用户的表空间_oracle修改表空间状态

oracle修改用户表空间_oracle修改用户的表空间_oracle修改表空间状态

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

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

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

1. 启动监听

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

2.关闭监听

注意:先启动监听,后启动数据库。

三、表空间

1. 创建表空间

oracle修改用户的表空间_oracle修改用户表空间_oracle修改表空间状态

参数解释:

tablespacename:表空间名称

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

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

SIZE:指定文件的大小,用K指定千字节大小,用M指定兆字节大小

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

举例:

oracle修改用户表空间_oracle修改表空间状态_oracle修改用户的表空间

2. 表空间的管理

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

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

oracle修改用户表空间_oracle修改表空间状态_oracle修改用户的表空间

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

oracle修改用户表空间_oracle修改表空间状态_oracle修改用户的表空间

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

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

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

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

DROP TABLESPACE 表空间名 [INCLUDING CONTENTS] ;

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

四、用户管理

1. 创建用户

oracle修改表空间状态_oracle修改用户表空间_oracle修改用户的表空间

举例:

oracle修改用户的表空间_oracle修改表空间状态_oracle修改用户表空间

2. 修改用户的密码

ALTER USER 用户名 IDENTIFIED BY 密码 ;

3. 删除用户

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

DROP USER 用户名 CASCADE;

创建用户前的准备工作:

1) 选择用户名和密码

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

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

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

5) 创建用户

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

五、数据库权限管理

1. 系统权限

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

oracle修改表空间状态_oracle修改用户表空间_oracle修改用户的表空间

常用的系统权限如下:

CREATE SESSION:连接到数据库

CREATE TABLE:创建表

CREATE VIEW:创建视图

CREATE SEQUENCE:创建序列

2. 对象权限

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

oracle修改用户的表空间_oracle修改表空间状态_oracle修改用户表空间

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

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

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

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

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

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

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

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

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

授予权限语法格式如下:

GRANT 权限 | 角色 TO 用户名;

oracle修改表空间状态_oracle修改用户的表空间_oracle修改用户表空间

撤销权限语法格式如下:

REVOKE 权限 | 角色 FROM 用户名;

六、事务控制

oracle修改用户的表空间_oracle修改表空间状态_oracle修改用户表空间

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

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

自动提交:set autocommit on

关闭自动提交:set autocommit off

七、索引

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

1、索引的特点

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

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

建立索引的数量没有限制

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

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

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

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

2.索引的分类

oracle修改用户的表空间_oracle修改用户表空间_oracle修改表空间状态

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

参数解释:

index_name:创建索引的名称

tablename:为之创建索引的表名

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

tablespace:为索引指定表空间

(2)创建唯一索引

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

(3)创建反向键索引

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

(4)创建位图索引

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

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

oracle修改表空间状态_oracle修改用户的表空间_oracle修改用户表空间

(5)其他索引

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

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

oracle修改表空间状态_oracle修改用户表空间_oracle修改用户的表空间

3. 创建索引的原则

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

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

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

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

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

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

4. 查看索引

和索引有关的数据字典:

USER_INDEXS:用户创建的索引信息

USER_IND_COLUMNS:与索引相关的表列信息

5. 维护索引

(1)重建索引

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

(2)合并索引碎片

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

(3)重命名索引

(4)删除索引

八、视图

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

1. 视图的优点:

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

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

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

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

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

2. 创建视图

oracle修改用户表空间_oracle修改表空间状态_oracle修改用户的表空间

参数解释:

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. 物化视图

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

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

oracle修改表空间状态_oracle修改用户表空间_oracle修改用户的表空间

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

(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用户来演示

oracle修改用户的表空间_oracle修改表空间状态_oracle修改用户表空间

(2)创建物化视图日志

oracle修改用户表空间_oracle修改用户的表空间_oracle修改表空间状态

(3)创建物化视图语句

oracle修改表空间状态_oracle修改用户表空间_oracle修改用户的表空间

其中:

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

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

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

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

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

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

(4)删除物化视图

drop materialized view view_name;

九、序列

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

1. 创建序列

oracle修改表空间状态_oracle修改用户的表空间_oracle修改用户表空间

参数解释:

2. 访问序列

创建了序列之后,可以通过NEXTVAL和CURRVAL伪列来访问该序列的值。可以从伪列中选择值。但是不能操纵他们的值。

NETXVAL:创建序列后第一次使用NEXTVAL时,将返回该序列的初始值。以后再引用NETXVAL时,将使用INCREMENT BY子句的值来增加序列值,并返回这个新值。

CURRVAL:返回序列的当前值,即最后一次引用NEXTVAL时返回的值

举例:

创建序列

oracle修改表空间状态_oracle修改用户的表空间_oracle修改用户表空间

创建表

oracle修改用户表空间_oracle修改用户的表空间_oracle修改表空间状态

插入数据

oracle修改用户表空间_oracle修改表空间状态_oracle修改用户的表空间

查看数据

oracle修改表空间状态_oracle修改用户的表空间_oracle修改用户表空间

查看序列的当前值

oracle修改用户的表空间_oracle修改用户表空间_oracle修改表空间状态

Currval返回序列的当前值,即最后一次引用NEXTVAL时返回的值

测试currval

oracle修改用户表空间_oracle修改用户的表空间_oracle修改表空间状态

重启实例之后再次写入数据发现从40开始,因为按创建序列的要求,每次会拿30个序列号放到缓存中,实例重启后,缓存中的序列就会消失

oracle修改用户表空间_oracle修改表空间状态_oracle修改用户的表空间

3. 更改序列

Alter sequence命令用于修改序列的定义。如果要进行下列操作,则会修改序列。

设置或删除MINVALUE或MAXVALUE

修改增量值

修改缓存中的序列号的数目

不能修改序列的START WITH参数

4. 删除序列

十、同义词

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

1. 用途:

创建数据库链接的语法:

CREATE DATABASE LINK 数据库链接名 CONNECT TO user名 IDENTIFIED BY 口令 USING ‘Oracle链接串’;

2. 同义词的分类

(1)私有同义词

私有同义词只能被当前模式的用户访问,私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词,用户必须拥有create synonym系统权限。要在其他用户模式创建私有同义词,用户必须拥有create any synonym系统权限。

语法:

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

参数解释:

oracle修改用户的表空间_oracle修改表空间状态_oracle修改用户表空间

(2)共有同义词

公有同义词被所有的数据库访问。公有同义词可以隐藏基表的身份,并降低sql语句的复杂性。要创建公有公有同义词,用户必须拥有create public SYNOYM的系统权限。

语法:

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

oracle修改用户表空间_oracle修改表空间状态_oracle修改用户的表空间

(3)删除同义词

DROP [PUBLIC] SYNONYM [schema.]synonym_name;

oracle修改表空间状态_oracle修改用户的表空间_oracle修改用户表空间

十一、分区表

1. 含义:

Oracle允许把一个表重的所有行分成几个部分,并将它们存储在不通的表空间,分成的每一部分成为一个分区,被分区的表成为分区表。

对于包含大量数据的表来说,分区很有用,优点有以下几点:

1)改善表的查询性能。在对表进行分区后,用户执行sql查询时可以只访问表中的特定分区而非整个表。

2)表更容易管理。因为分区表的数据存储在多个部分中,按分区加载和删除数据比在表中加载和删除更容易。

3)便于备份和恢复。可以独立地备份和恢复每个分区。

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

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!