Oracle入门基础

1.Oracle数据库简介

  (1)是一种对象关系型数据库管理系统

  (2)在管理信息系统、企业数据处理、因特网和电子商务等领域使用非常广泛

  (3)在数据库安全性与数据完整性控制方面性能优越

  (4)具有很好的跨操作系统、跨硬件平台的数据互操作能力

  

2.Oracle数据库的主要特点

  (1)支持多用户、大事务量的事务处理 

    一个事务包含一个或多个SQL语句，是逻辑管理的工作单元（原子单元）。

    一个事务开始于第一次执行的SQL语句，结束于Commit 或 Rollback 或 DDL语句。

    注意：其中Commit, Rollback是显示的提交事务，而DDL语句是隐式的提交事务的。DDL语句的操作是没有办法回滚的

    

  (2)数据安全性和完整性控制(锁机制)

    为了确保并发用户在存取同一数据库对象时的正确性（即无丢失修改、可重复读、不读“脏”数据），数据库中引入了锁机制。(sql需要手动设置)

  (3)支持分布式数据处理

  (4)可移植性

  

3.Oracle数据库简介2

   Oracle数据库使用的是基于客服端与服务器技术

   数据库服务器对数据库表进行最佳管理，处理多个客户端对统一数据的并发访问。全面地保持数据完整性，并控制数据库访问权限等安全性要求

   

4.Oracle的体系结构

   Oracle的体系结构是指数据库的组成、工作过程与原理，以及数据在数据库中组织与管理机制

   Oracle服务器提供开发、全面和综合的信息管理，它由Oracle数据库和Oracle实例组成

(1)数据库：物理操作系统文件或磁盘的集合.

(2)实例：管理数据库的后台进程和内存结构的集合。由一组Oracle后台进程/线程以及一个共享内存区组成。

 

5.Oracle的主要组件

 

用户进程->服务器进程(PGA：程序全局区。服务器进程使用的内存区域，包括特定服务器进程的数据和控制信息，例如使用的操作系统资源等.)

 

实例(UGA:用户全局区，特定Session使用的内存区域。session会话：是实例中存在的一个逻辑实体，就是表示特定的一组内存中的数据结构)

实例：内存结构和后台进程

实例有三种类型的进程：

用户进程：代表后台进程和服务器进程来完成一些额外的工作。

服务器进程：这些进程根据客户的请求来完成工作。

后台进程：随数据库的启动而启动，用于完成各种维护任务，如写入磁盘、重做日志、清理异常终止的进程等。

后台进程是Oracle数据库为了保持最佳系统性能和协调多个用户请求而设置的。 Oracle 实例启动时即创建一系列后台进程 

 

 

内存结构(SGA:系统全局区):大小可以在安装路径 E:\Oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA 文件中看到

数据缓冲(Buffer Cache)、重做日志缓冲(Redo Log Buffer)、共享池(Shared Pool)、Java池(Java Pool)、

数据字典缓存(Data Dictionary Cache)、大池(Large Pool)、流池(Streams Pool --- 10g以后才有)、其他信息(如数据库和实例的状态信息)

SGA：是一组为系统分配的共享的内存结构，可以包含一个数据库实例的数据或控制信息。如果多个用户连接到同一个数据库实例，

在实例的SGA中，数据可以被多个用户共享。当数据库实例启动时，SGA的内存被自动分配；当数据库实例关闭时，SGA内存被回收。

SGA是占用内存最大的一个区域，同时也是影响数据库性能的重要因素。

 

补充：

解锁scott用户：alter user scott account unlock;

修改帐号密码：alter user scott identified by a;

 

(1)查看内存结构的方法：

在Oracle SQL*Plus中输入: show sga

Total System Global Area  612368384 bytes

Fixed Size 固定大小                 1292036 bytes

Variable Size   可变大小         234883324 bytes

Database Buffers 数据区缓存区         369098752 bytes

Redo Buffers   重做日志缓存区             7094272 bytes

 

 

(2)Oracle如何调整SGA的大小？

Oracle 10G开始支持使用SGA_TARGET参数进行SGA自动管理，Oracle可自动调节共享池和数据缓冲池的大小。

先查看现在已有的SGA设置：

在Oracle SQL*Plus中输入: show parameter sga;

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 2G

sga_target(目标)                           big integer 2G

当lock_sga参数设置为TRUE时（默认值是FALSE），可以保证整个SGA被锁定在中，这样可以防止SGA被换出到虚拟内存。

只要设置lock_sga为"TRUE"便可保证SGA被锁定在物理内存中，

这里之所以顺便将pre_page_sga参数也设置为"TRUE"，是因为这样可以保证在启动时把整个SGA读入到物理内存中，以便提高系统的效率（虽然会增加系统的启动时间）。

 

修改其状态的语法：

 SQL> alter system set lock_sga=true scope=spfile;

 SQL> alter system set pre_page_sga=true scope=spfile;

  

scope=spfile:就是动态参数文件，里面设置了Oracle 的各种参数。

所谓的动态，就是说你可以在不关闭数据库的情况下，更改数据库参数，记录在spfile里面。更改参数的时候，有4种scope选项。

scope就是范围:

scope=spfile 仅仅更改spfile里面的记载，不更改内存，也就是不立即生效，而是等下次数据库启动生效。有一些参数只允许用这种方法更改

scope=memory 仅仅更改内存，不改spfile。也就是下次启动就失效了

scope=both 内存和spfile都更改

   不指定scope参数，等同于scope=both

   

更改SGA大小：

SQL> alter system set sga_max_size=4096M scope=spfile;

SQL> alter system set sga_target=4096M scope=spfile;

 

重新启动使之生效：

切换到系统管理员登录：

SQL> conn system/a as sysdba

SQL> shutdown immediate;

SQL> startup

 

内存分多了，导致无法启动数据库解决办法

a.修改安装目录下的参数文件，如 E:\Oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA

 

b.既然配置参数出错导至无法启动，就先来了解一下oracle 启动过程，

oracle 数据库启动分为三个部分： 

a.nomount   启动实例 

b.mount     加载数据库 

c.open       打开数据库 

在nomount时，要读取spfile.ora,init<sid>.ora,init.ora 并且是先读取spfile<sid>.ora。spfile内容是无法看到的。不过可以看pfile 文件。

看到pfile sga_target 已修改。一般spfile 和pfile 是一样的内容，spfile 可以动态修改，而pfile 不能动态修改。

默认情况下，oralce 启动时，先取读spfile ,所以我们完全可以通过pfile 来代替spfile 来启动。

如果通过pfile 启动成功，就表明spfile 文件出了问题。能确定spfile 出了问题就好办了。我们只需要重建一个。

 

另一途径启动数据库(pfile)

SQL> startup pfile='D:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.102320099652';

 

好了。启动成功。说明spfile 出了问题。知道了问题根源，就好办了。我们只要通过pfile 来创建spfile。

SQL> create spfile from pfile='D:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.102320099652';

 

SQL>  shutdown immediate;

SQL>  startup;

完工

 

后台进程

PMON(Process Monitor Process)：进程监视进程。负责监视数据库的处理情况,并负责清除死掉的进程。PMON还负责重启失败的调度进。

SMON(System Monitor Process)：系统监视进程。进程负责在实例启动时恢复实例，包括清除临时段以及恢复因系统崩溃而中断的事物。

DBWR(Database Writer Process)：数据库写入进程。负责将脏数据块从数据缓存（database block buffer)写回磁盘。

当一个事务修改数据块中的数据以后，不需要立即将数据块写回磁盘。由于Oracle所采用的先进机制，修改后的数据可以不用立即写回，

并且即使出现故障也不会丢失。因此，DBWR可以采取更有效写回方式，而不用在事务提交完之后立即写回。DBWR通常定时写回数据，除非数据缓冲区。

数据写回时，采用了最近最少使用原则（least-recently-used).对于支持异步I/O的系统，只需使用一个BWR进程即可，

对不支持异步IO的可以通过增加DBWR的个数来提升效率。

LGWR(log writer process): 日志写入进程。负责将日志缓冲区中的数据写入重做日志。

由于COMMIT操作依赖于LGWR写回日志（commit操作触发LGWR将日志缓冲区数据写入UNDO日志)，因此系统性能容易受到LGWR的影响。

CKPT(Checkpoint Process)：检查点进程。 负责向DBWR进程发送信号，要求执行一次检查点，并更新数据库的所有数据和控制文件。

CheckPoint(检查点)，是指有DBWR进程将所有修改过的数据缓冲区写回数据文件。CKPT是可选的，如果没有CKPT进程，则有LGWR代行这些任务。

其它：

ARCHn(Archiver Process )：归档进程。负责将再现重做日志复制到归档存储器。进档RDBMS运行在ARCHIVELOG模式时ARCH才有效，

如果系统没有运行在ARCHIVELOG模式，则系统失效后可能无法恢复。最后让系统运行在ARCHIVELOG模式下，此时归档进程可能有多个。

 

SQL>archive log list          //查看是否为归档模式

可以在数据库Mount 模式下开启归档

SQL>alter database archivelog; //开启

SQL>alter database noarchivelog;//关闭

RECO(Recovery Process)：恢复进程。用于清除分布式数据库中的未决(pending)事务，它负责分布式事务中本地部分的提交与回滚

Dnnn(Dispatcher Process)：调度进程。负责将用户进程的请求转发到一个可用的共享服务器进程,

并负责将用户进程的请求转发到一个可用的共享服务器进程,并负责回送响应消息。

Snnn(Shared Servers Process)：共享服务器进程。当使用共享服务器配置或MTS时，会用到共享服务器进程这些进程如同专用服务器进程。可以被多个用户共享。

队列管理进程等

 

数据库：(安装目录下可看到，如：E:\Oracle\product\10.2.0\oradata\orcl，就有这三种文件)

物理组件：数据文件(.dbf)、日志文件(.log日志文件记录对数据库的所有修改信息，用于故障恢复)、控制文件(.ctl)

日志文件分为：联机日志文件、联机日志文件

联机日志文件(重做日志文件、非归档方式)：目前我们所看到的列资源管理器中的如：REDO01.LOG、REDO02.LOG、REDO03.LOG等

特点：第一个写满了写入第二个，第二个写满了写入第三个，第三个写满了，以覆盖的形式写入

 

归档日志文件：进行热备份的时候一定要选择的。

 

这三个文件中由控制文件管理和控制数据文件和日志文件，启动数据库时，实例启动后首先启动控制文件，接着由控制文件打开数据文件。

直观的看一下

运行->cmd->sqlplus "sys/a as sysdba" 或者直接使用SQL*Plus

 

查看数据文件：select name,status from v$datafile;

查看日志文件：select name from v$logfile;

查看控制文件：select name from v$controlfile

 

 

查看参数文件：(E:\Oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA)

参数文件并不实际参与工作，但是控制文件是由参数文件来寻找的。我们在此文件中可以看到所有的控制文件的路径。

 

 

逻辑组件(有图片说明)：数据库(database)->表空间(tablespace)->段(segment)->区(extent)—>数据快(block)     (模式)  

表空间(PPT-14)：是数据库中最大的逻辑单位，一个Oracle数据库至少包含一个表空间，就是名为system的表空间

每个表空间有一个和多个数据文件组成，一个数据文件只能与一个表空间相关联

表空间的大小等于构成该空间的所有数据文件的大小之和

创建表空间的语法：

create [undo] tablespace tablespacename     undo:说明系统将创建一个回滚表空间   

 

filename1:数据文件的全路径名    reuse:表示文件是否被重用

[datafile 'filename1 [size integer [k|m] reuse [autoextend off|on|next integer [k|m]] [maxesize integer [k|m]]' [,'filename2'……]

 

mininum extent:指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。

指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。

[{mininum extent integer [K|M]

 

|blocksize integet [k]

 

logging:这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），

包括表，索引，分区，物化视图，物化视图上的索引，分区。

|logging clause

|force logging

 

online|offline:改变表空间的状态。online使表空间创建后立即有效.这是缺省值.

offline使表空间创建后无效.这个值，可以从dba_tablespace中得到。

|[online|offline]

 

permanent|temporary:指出表空间的属性，是永久表空间还是临时表空间。

|[permanent|temporary]

 

extent_manager_clause:这是最重要的子句，说明了表空间如何管理范围。一旦你声明了这个子句，只能通过移植的方式改变这些参数。

|extent_manager_clause

|segment_manager_clause

}]

 

案例：

create tablespace ycspace  --表空间名

datafile 'D:/ycspace01.dbf'   --表空间对应的数据文件

size 100M --数据文件大小

autoextend on next 10M  --数据文件不够用自动扩展，每次扩展大小10M

maxsize 1000M   --数据文件最大文件大小

logging --启动重做日志

permanent  --指定表空间为永久性的表空间

extend management local autoalllocal  --指定新建表空间为本地管理方式的表空间

blocksize 16k  --块大小16k

segment space management auto  --指定本地管理表空间中段的存储管理方式，AUTO自动方式，MANUAL手工方式。

 

a.修改表空间的状态：

alter tablespace ycspace offline|online|readonly|readwrite

 

b.修改表空间数据文件的大小，添加数据文件

alter databasespace datafile 'D:\ycspace01.dbf' resize 200M;

alter databasespace ycspace add datafile 'D:/ycspace02.dbf' size 10M; 

 

c.修改表空间数据文件的自动扩张性

alter tablespace datafile 'D:/ycspace01.dbf' autoextend on next 5M maxsize 100M;

 

d.移动表空间数据文件 

1).设置数据文件状态为离线OFFLINE: alter tablespace ycspace offline; 

2).复制数据文件 

3).重新指定表空间路径 alter tablespace ycspace rename datafile 'E:\table2.dbf' to 'D:\table.dbf'; 

 

e.删除表空间

drop tablespace ycspace;

drop tablespace ycspace including contents and datafiles; //同时删除其数据库对象和数据文件 

 

段(PPT-15)：段是构成表空间的逻辑存储结构，段由一组区组成。按照段所存储数据的特征，将段分为四种类型，即数据段、索引段、回退段和临时段。

区：区为段分配空间，它由连续的数据块组成。当段中的所有空间已完全使用时，系统自动为该段分配一个新区。区不能跨数据文件存在，只能存在于一个数据文件中。

数据块：数据块是Oracle服务器所能分配、读取或写入的最小存储单元。Oracle服务器以数据块为单位管理数据文件的存储空间。 

 

模式：模式是对用户所创建的数据库对象的总称。模式对象包括表、视图、索引、同义词、序列、过程和程序包等。

 

 

会话

会话是用户与 Oracle 服务器的单个连接

当用户与服务器建立连接时创建会话

当用户与服务器断开连接时关闭会话

 

Oracle启动过程：启动Oracle实例(startup nomount)->加载数据库(mount)->打开数据库(open)->使用SQL*Plus连接到数据库->创建用户进程->创建服务器进程->提交SQL查询

 

6.Oracle网络配置(PPT-16)

Oracle产品安装完成后，服务器和客户端都需要进行网络配置才能实现网络连接。

服务器端配置监听器，客户端配置网络服务名。

 

服务器端监听器配置信息包括监听协议、地址及其他相关信息。 配置信息保存在名为listener.ora的文件中。在安装服务器软件时自动配置一个监听器

客户端的网络服务名配置信息包括服务器地址、监听端口号和数据库SID等，与服务器的监听器建立连接。配置信息保存在名为tnsnames.ora的文件中

Oracle中的 Net Configuration Assistant和Net Manager工具都能用来配置监听器和网络服务名

 

7.Oracle的查询工具

Oracle的查询工具包括：SQL*Plus  iSQL*Plus  PL/SQL

PL/SQL 是 SQL 的扩展。PL/SQL 结合了SQL语言的数据操纵能力和过程语言的流程控制能力

 

8.Oracle企业管理器

Oracle 10g后通过网络访问，方法为：

需要启动DBConsoleorcl

em(Enterprise Manager):

一般要输入http://ip地址:1158/em

em服务是否启动，通过emctl status dbconsole查一下，如没启动执行emctl start dbconsole，然后注意观察em启动时的端口号，一般默认端口为1158

 

9.Oracle的默认用户

只有合法的用户帐号才能访问Oracle数据库，Oracle有几个默认的数据库用户：sys、system、scott

scott用户是Oracle数据库的一个示范账户，在数据库安装时创建，默认密码为tiger

 

要连接到数据库就必须有一个用户帐号，每一个用户都有一个默认的表空间和一个临时表空间。

创建用户的语法如下：

created user <username> identified by <password> default tablespace users temporary tablespace temp;

 

10.授权管理

权限指的是执行特定命令或访问数据库对象的权利

权限有两种类型：系统权限和对象权限

系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限，使用dba_sys_privs视图查看

select * from dba_sys_privs;  (需要系统管理员权限)

对象权限允许用户对数据库对象，如：表、视图、序列等，执行特定的操作

 

角色

角色是一组相关权限的组合，可以将权限授予角色，再把角色授予用户，以简化权限管理

一组权限相同的客户可以通过角色来统一管理，还可以把该角色授予其他用户或角色，默认角色有：

dba：执行所有操作

connect:数据库链接操作

resource:使用DB资源

select_catalog_role:执行select操作    catalog:目录、登记

OLAP_DBA:执行与表、视图相关的操作

scheduler_admin:作业管理   scheduler：调度、制表人、策划人

使用dba_roles,dba_role_privs,dba_sys_privs和role_sys_privs视图查看有个角色的信息  (注意：需要系统管理员权限)

 

 

11.登录用户的创建

(1)以system身份登录oracle

 

(2)查看dba_tablespaces数据字典视图,了解当前数据库中包括哪里些数据字典, 可知当前库包括了六个表空间。

select * from dba_tablespaces;

 

system：系统表空间，存储数据字典数据，数据库要打开不可缺少的的表空间，SYSTEM表空间离线状态，数据库不能打开。

undotbs1：撤销表空间，存储被修改过的数据，用于数据恢复。可以手动删除，创建和指定。

sysaux：系统辅助表空间，数据库组件的数据，如EM的数据。

users：默认的用户数据表空间。 

temp：临时表空间，存储用于排序等用的临时数据，临时表空间的数据库不会被备份。可以手动删除，创建和指定。

example：存储sample方案的数据。如scott、hr等方案的数据。

 

(3)创建一个用户yc,口令为window,默认表空间是users,临时表空间是temp

create user yc identified by window default tablespace users temporary tablespace temp;

 

注意：创建此用户后，在没有为这个用户授予相应的连接数据库的权限的情况下，它依然不能连接到数据库.

测试 yc 登录，   ora-01045错误

 

(4)为 yc 授予create session权限，再测试

grant create session to yc;

 

(5)修改用户口令

语法:  alter user 用户名  identified by 新密码

练习: 使用alter user 语句将 yc 口令修改为door

alter user yc identified by door;

修改完后测试登录

 

    (6)锁定和解除锁定用户:  临时禁止某个用户访问oracle

语法: alter user 用户名 account [lock|unlock]

练习: 对 yc  用户进行锁定和解除锁定操作         注意：应以system身份登录操作

alter user yc account lock;

alter user yc account unlock;

 

(7)查看用户信息 : 采用dba_users视图来实现

select * from dba_users;

 

(8)删除用户

语法:   drop user 用户名[cascade]

注意: 如果当前删除的用户拥有对象，那么必须使用cascade关键字才可以删除该用户

drop user hillary cascade;

 

12.为用户授予系统权限

语法:  grant 系统权限1[, 系统权限2] to 用户 [with admin option] with admin option: 表示该用户可以将这种系统权限转授予其他用户.

练习: 

以system登录,创建两个用户hillary/window和jackson/tree,

create user hillary identified by window;

create user jackson identified by tree;

 

为hillary 用户授予create session和create user系统权限， 可连接系统和创建用户

grant create session,create user to hillary;

 

为hillary用户授予create any table 和execute any procedure系统权限，并使用with admin option选项，这样它可以将授予自己的权限转授别人.

grant create any table,execute any procedure to hillary with admin option;

 

以hillary身份登录系统, 为jackson用户授予create any table和execute any procedure系统权限.

 

grant create any table,execute any procedure to jackson;

以system身份登录系统，为public用户授予create session权限。因为public 是公共角色，所有的用户都是其成员，所以，系统中所有的用户都拥有联接系统权限. 

grant create session to public;

 

以hillary登录系统, 查看当前用户hillary的权限.     user_sys_privs

select * from user_sys_privs;

 

13.收回授予的系统权限 

语法: revoke 系统权限1[,系统权限2] from 用户名

练习: 

以system登录，从hillary处收回create any table权限

revoke create any table from hillary;

 

以hillary登录，查看它的权限

select * from user_sys_privs;

 

 

 

14.对象权限管理.

语法: grant 对象权限1[(列名)] on 对象名 to 用户名 [with grant option]

练习: 

以scott身份登录系统, 为hillary用户授予对emp表的select,insert和delete对象权限 

grant select,insert,delete on scott.emp to hillary;

 

为hillary 用户授予对emp表中的ename列的更新权限.

grant update(ename) on scott.emp to hillary;

 

练习:查看用户的对象权限:    

user_tab_privs_made, 某个用户对另外一个用户授予的权限

user_col_privs_made, 有关列级的权限

user_tab_privs_read  , 有关某个用户接受的权限

user_col_privs_read    有关列级对象的权限.

 

以scott登录，查看

select * from user_tab_privs_made;

 

收回对象权限

语法:  revoke 对象权限  on 对象名  from 用户名

revoke update on scott.emp from hillary;

 

 

15.Oracle服务(PPT-30)

(1)OracleHOME_NAMETNSListener

该服务启动数据库服务器的监听器，监听器接受来自客户端应用程序的连接请求。若监听器未启动，则客户端将无法连接到数据库服务器。

 

(2)OracleServiceSID

该服务启动系统标识符为SID的数据库实例，其中 SID 是在安装 Oracle 9i 时输入的数据库名称。

 

(3)OracleHOME_NAMEAgent 

Oracle企业管理器组件智能代理服务，此智能代理用于执行作业和监控 Oracle 服务或对象的性能。在使用Oracle管理服务器网络的环境中必须启动此服务。

 

(4)OracleHOME_NAMEHTTPServer

该服务用于启动Oracle HTTP服务器，它提供文档信息、基于Web的企业管理器和iSQL*Plus等服务。

 

(5)OracleHOME_NAMEManagementServer

该服务启动Oracle管理服务器，它用于维护管理控制台对各个被管理服务器节点之间的集中的、智能的和分布式的控制。

该服务必须经过企业管理器配置向导配置完成后才创建。

 

 

16.SQL*Plus基本操作

conn|connect sys/a as sysdbd;  --连接到数据库

 

startup;     --启动数据库

可细分为三步

nomount   启动实例 

mount     加载数据库 

open       打开数据库 

 

 

show user          --显示当前用户名

conn  用户名/密码 as 角色    --联接用户

disc      --断开联接

 

如果控制文件丢失而不能打开时，则使用startup nomount;启动实例，让其重建控制文件

 

把数据库从非归档模式改为归档模式

archive log list;  --显示归档日志列表

startup mount;  --加载数据库，但不打开数据库

 

alter database archivelog;

alter database open;

 

shutdown;  --经常关闭数据库，只有当所有用户断开链接时，数据库才会关闭。如果有用户正在访问数据库时，则会造成数据库长时间不能关闭。

shutdown immediate;   --Oracle建议使用的，通常情况下使用的。迫使每个用户执行完当前的sql语句后，立即断开链接。

shutdown transactional; --执行事务处理。迫使用户在当前操作执行完成后立刻断开链接，终止实例，恢复破坏的数据文件。

shutdown abort; --终止数据库。是一个非常严厉的手段，它是强行关闭数据库，关闭实例，很有可能造成文件的破坏。

所以，不到万不得已的时候一般不用。除非遇到一个特殊情况，如地震、火灾等，来不及正常关闭。

 

 

启动isqlplus

在浏览器的地址栏输入：http://机器名:5560/isqlplus

连接字符串就是数据库实例名，不要用sys登录，因为sys只能以sysdba的身份登录，可以用scott

 

 

查看sqlplus中所支持的命令

help index

 

使用？命令名 可以查看其相关属性

如：查询set的相关操作，可以用 ？set;

 

在sqlplus中如果语句还未结束，而不小心输入一个回车，此时语句就会结束，使用如下语句可设置sqlplus中支持空格行，已分号结束。

set sqlblanklines on;

 

使用替代变量实现查询数据

select * from dept where deptno=&dno;

如果我们查询部门为10和20的，用替代变量实现Oracle只安排一个执行计划，而使用

select * from dept where deptno=10;

select * from dept where deptno=20;

Oracle会安排两个执行计划，所以为了提高语句的执行效率，Oracle强力建议我们利用变量替换的方式来输入。

同时我们可以使用list来查询Oracle缓存区中我们曾经写的命令。list可简写为:l。

如果有多行，我们也可以使用list来查看某些行，如查看第2行到第4行，可使用 l2 4,查看第三行可以用：l3

 

如果我们输入一个这样的语句：

select * fron dept;--m写成了n,此时编译器会报错，不过也会写到缓存区，我们可以通过l(list)查看。

l  --查看缓存区中的语句是：select * fron dept;

c/n/m    --然后使用change,简写c来修改，而无需全部重写如：把n改为m,可以用

l  --再查看时已经是：select * from dept;

/--执行缓存区中的数据使用 / 来执行

 

可以使用 ？ change 来查看change的使用

 

删除缓存区中的第4行可以使用：del 4

删除缓存区中的第2行到第4行可以使用:del 2 4

在缓存区的尾部追加信息可以使用: a from dept;  append可简写为:a

 

保存缓存区中的内容可以用：

save D:\database\text.txt

 

执行保存的文件

@D:\database\text.txt或get D:\database\text.txt

 

编辑缓存区中的信息可以使用edit

edit  --默认使用记事本打开。不需要写分号，因为执行缓存区中的数据使用 /

 

给查询的字段设置标签：使用 column简写为 col   ？ col

col deptno heading "编号"

select * from dept; -- deptno列名则会用"编号"代替

 

查看表结构使用：describe 简写为 desc

desc dept

 

格式化显示,如dname占10个字符  (仅在当前的会话期间有效)

col dname format a10 heading "部门名称";   --字符型

 

select bytes from v$datafile; --输出数据文件的字节数

col bytes format 999,999,999 heading "字节数" --数字类型

select bytes from v$datafile; 

 

报表显示即页面、页脚

set linesize 50   linesize:行宽。设置sql plus显示的文本行宽为100个字符

ttitle center "我的标题" skip 1-  --skip 1- 表示空一行

left "测试报表" right "页" -      --中间显示"我的标题"  左边显示"测试报表"  右边显"页 几"

format 999 sql.pno skip 2       --sql.pno：页号 

 

ttitle off  --关闭报表

 

查询时，重复的只显示一次:break on <字段>

 

分组统计：

comp count label "计数" of <显示在哪个字段下> on <字段列名>

 

保存查询的结果集：spool

spool D:\database\text.txt

select * from dept;

spool off;

edit D:\database\text.txt   --编辑保存的数据

 

退出：exit

 

 

补充：初始设定

设置sql plus的默认编辑器

define _editor=vi;

 

默认打开dbms_output,也将默认缓冲区大小设置得尽可能大

set serveroutput on size 1000000;

 

假脱机输出文本时，会去除文本行两端的空格，而且行宽不定。如果设置为off(默认设置),假脱机输出的文本行宽度则等于所设置的linesize

set trimspool on;

 

设置选择long和clob列时显示的默认字节数

set long 5000;

 

 

控制sql plus多久打印一次标题，这里将pagesize设置得比较大，所以每页只有一个标题

set pagesize 9999;

 

设置由autotrace得到的解释计划输出(explain plan output)的默认宽度。a80通常足以放下整个计划

column plan_plus_exp format a80;

column global_name new_value gname;

set termout off;

 

用于建立sql plus提示符

define gname=idle;

 

告诉sql plus取得global_name列中的最后一个值,并将这个值赋给替换变量gname.接下来，从数据库中选出global_name,并与我的登录用户名连接，这样得到的sqlplus提示符这变了.

column global_name new_value gname

select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1) global_name from(select global_name,instr(global_name,'.') dot from global_name);

set sqlprompt '&gname> '

set termout on