目录
1、SQL * Plus的使用详解
1.1 SQL语法
1.1.1 查询记录:
select colummn_name from tablename
where
group by
having
order by
where:可使用运算符:=、<>、!=、>=、<=、in、between、like(%_)、is null 或is not null、not、or、and。
order by:asc(升序)或desc(降序),缺省升序。
group by:将查询结果分组,在包含group by 子句的查询中,select 子句的列表中的所有个体值(除聚组函数avg、count等外)必须是groub by子句中的表达式或常量。
having:如果需要对各个组返回的结果进行筛选,查询满足一定条件的分组值,可以使用having。
例如:
select deptno,sum(sal)
from test
group by deptno
having sum(sal)>9000;
1.1.2 插入记录
语法1:
insert into tablename(l1,l2)
values(v1,v2);
语法2:
insert into tablename(列表)
select 语句;
1.1.3 更新记录
语法:
update tablename
set 列名1=,列名2=
where
1.1.4 删除记录
语法:
delete from tablename where
1.2 PL/SQL
PL/SQL语句种类
DDL(数据定义语言) | create(创建)、alter(修改结构)、drop(删除)、truncate(截 断)、(其他:rename)。 |
DML(数据操纵语言) | insert(增)、delete(删)、select(查询)、update(改)。 |
DCL(数据控制语言) | grant(授权)、revoke(回收权限)、set role(角色)。 |
commit、rollback、savepoint(其他:lock table、set constraint(s)、set transaction)。 | |
audit、noaudit。 | |
alter system。 | |
alter session。 | |
comment(添加注释)、explain plan、analyze(收集统计)、validate、call。 |
PL/SQL常见的类型转换函数
函数 | 说明 | 转换可使用的类型 |
TO_CHAR | 按照可选的格式将参数转换为字符类型 | 数字型、日期型 |
TO_DATE | 按照可选的格式将参数转换为日期类型 | 字符型 |
TO_NUMBER | 按照可选的格式将参数转换为NUMBER类型 | 字符型 |
例如:
To_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)
To_date(‘20020908','yyyymmdd')
To_number(‘20020911’)
to_number('123.4')
2、ORACLE用户及权限管理
合理的用户和权限管理对于数据库系统的高效、安全、可靠是很关键的。ORACLE在用户及权限管理上有许多新的概念和特性。
对用户及权限的管理需要进入SQL*Plus交互工具。每一个SQL语句后要以分号“;”结束。退出交互工具命令为:quit
SQL命令语句及可选项不区分大小写,本文中出现大写的地方是强调作用。
2.1 ORACLE的用户管理
每个ORACLE数据库都有许多合法用户,这些用户可以根据用户名和口令登录数据库,并使用SQL语言存取数据。
2.1.1 创建用户
创建用户命令格式 | 说 明 |
create user 用户名; | 创建用户的操作必须由DBA来做,一般用户无权创建用户。用户名必须是唯一的,即同一数据库中不能有两个相同的用户。 |
Identified by 口令; | 为用户设置口令 |
default tablespace 表空间名; | 表示该用户存放数据的缺省表空间 |
TTITLEorary tablespace 表空间名; | 表明用户使用的缺省临时表空间名 |
quota 大小 on 表空间名; | quota 可以限制用户在某个表空间上最多可使用多少字节 |
profile 资源文件; | profile 为用户指定各种资源的使用 |
下面是一个创建用户的完整例子:
create user test;
identified by tiger;
default tablespace data_ts;
tTITLEorary tablespace tTITLE_ts;
quota 500K on data_ts;
profile newprofile;
2.1.2 修改用户
对用户的修改包括:口令字、缺省表空间、临时表空间、表空间限量、profile、缺省角色。角色是ORACLE7的一个新概念,我们在“权限管理”里再讨论。在这里可把角色看成具有某些权限的一个特殊用户。修改用户的缺省角色也就是为用户指明另一个权限的集合。下面举例说明修改用户的操作:
任务 | 命令 |
将test的口令改为test123 | ALTER USER test IDENTIFIED BY test123; |
将test的缺省表空间改为data2_ts | ALTER USER test DEFAULT TABLESPACE data2_ts; |
将test的临时表空间修改为tTITLE2_ts | ALTER USER test TTITLEORARY TABLESPACE tTITLE2_ts; |
将test的资源文件改为otherprofile | ALTER USER test PROFILE otherprofile; |
将test的缺省角色改为DEVELOPER | ALTER USER test DEFAULT ROLE DEVELOPER; |
将当前系统所有角色都授予test,除Payroll外 | ALTER USER test DEFAULT ROLE ALL EXCEPT Payroll; |
2.1.3 删除用户
删除用户的命令为:
DROP USER 用户名 [CASCADE];
若不使用CASCADE选项,则必须在该用户的所有实体都删除之后,才能删除该用户。使用CASCADE后,则不论用户实体有多大,都一并删除。
2.2 ORACLE的权限管理
ORACLE的安全机制,是由系统权限、实体权限和角色权限这三级体系结构组成的。
权限类型 | 说明 |
系统权限 | 是指对数据库系统及数据结构的操作权,例如创建/删除用户、表、同义词、索引等等 |
实体权限 | 是指用户对数据的操作权,如查询、更新、插入、删除、完整性约束等等 |
角色权限 | 是把几个相关的权限组成角色,角色之间可以进一步组合而成为一棵层次树,以对应于现实世界中的行政职位。角色权限除了限制操作权、控制权外,还能限制执行某些应用程序的权限。 |
这样的安全控制体系,使得整个系统的管理人员及程序开发人员能控制系统命令的运行、数据的操作及应用程序的执行。
2.2.1 系统权限
(1)系统权限的授予命令为GRANT,例如把创建任何表视图的权限授予test用户:
GRANT create any view TO test;
(2)系统权限的回收命令为REVOKE,例如将create any view 权限从test用户手中收回:
REVOKE create any view FROM test;
2.2.2 实体权限
每种类型的实体有与之相关的实体权限。
(1)授予实体权限的命令举例(将TITLE表上的Select和Insert权限授给test):
GRANT select,insert ON TITLE TO test;
(2)回收实体权限的命令举例(将TITLE表上的Select权限从test手中回收):
REVOKE select ON TITLE FROM test;
2.2.3 管理角色
角色是许多权限和角色的组合,它极大地方便了ORACLE的权限管理。
(1)创建角色:如创建一个名为dept1的角色,口令字为test123:
CREATE ROLE dept1 IDENTIFIED BY test123;
(2)使用角色:可以通过修改用户的缺省角色来使用角色,或通过授权的方法来将角色授予其它角色或用户。
示例:如将test用户的缺省角色修改为DEVELOPER:
ALTER USER test DEFAULT ROLE DEVELOPER;
示例:将角色dept1授予manager角色:
GRANT manager TO test;
(3)使角色生效或失效,DBA可以通过控制角色的生效或失效,来暂时回收用户的一部分权限。如使dept1角色失效:
SET ROLE dept1 DISABLE;
(4)删除角色,这将会影响到拥有该角色的用户和其它角色的权限。用DROP ROLE命令删除角色,如:
DROP ROLE dept1;
3、ORACLE数据库的备份与恢复
ORACLE系统提供的Export/导出(备份)、Import/导入(恢复)使用程序实现备份与恢复功能。
Export是在数据库打开并能使用的情况下备份数据库数据的实用程序。用Export将数据库中的数据写到以二进制形式表示的操作系统文件中(ORACLE),该文件叫导出文件,它可以是磁盘文件。这些文件独立于数据库存在。导出文件在需要时能被再导入到ORACLE数据库中,也可导入到另一个CPU上的ORACLE数据库上,还可把它导入到不同ORACLE版本的数据库上。用Export可实现应用程序失败时的恢复,例如可把某个表或某些表恢复到执行该Export时的状态。
由于导出文件的特殊格式,所以只能用Import实用程序将其读入数据库中。
3.1 Export 导出
ORACLE数据库逻辑备份方法,逻辑备份又分为三种模式。
表模式(T):这种模式可以导出当前用户数据库模式下的表,甚至是所有的表。具有特权的用户可根据所指定的数据库模式来(限制表)导出他们所包含的表。缺省情况是导出属于当前正在进行导出的用户的所有表。
用户模式(U):这种模式可以导出当前用户数据库模式下的所有实体(表、数据和索引)。
全数据库模式(F):只有具有EXP_FULL_DATABASE角色的用户才可能以这种模式导出。以这种模式进行导出的用户,除SYS模式下的内容之外,数据库中所有实体都可以导出。 下面列出给用户赋予EXP_FULL_DATABASE角色的方法。
# su – ORACLE | 假设当前为超级用户身份 |
$ sqlplus sys/change_on_install | 以sys用户登录并进入Sql*Plus |
SQL> GRANT "EXP_FULL_DATABASE" TO 用户名 | 在Sql*Plus下执行此命令 |
要选择表、用户或全数据库方式,可相应指定TABLES=tablelist、OWNER=userlist或FULL=y。
3.1.1 表模式
EXP MAIN/MMC BUFFER=8192(或64000)
FILE=EXP_MAIN_SERVICEINFO.DMP
TABLES=MAIN.SERVICEINFO
ROWS=Y COMPRESS=N
LOG= EXP_MAIN_SERVICEINFO.LOG
参数说明:
BUFFER | 缓冲区大小 |
FILE | 由Export创建的输出文件的名字 |
TABLES | 将要导出的表名列表 |
ROWS | 指明是否导出表中数据的行数,缺省为“Y”。 |
COMPRESS | 指明在导入期间是否将表中数据压缩到一个区域中。如果在导出数据时,指定参数COMPRESS=Y,那么导入时,就会将数据压缩到一个初始区域中。这种选择可以保持初始化区域的原始大小。缺省为“Y”。 |
LOG | 指定一个接收有用信息和错误信息的文件 |
3.1.2 用户模式
EXP MAIN/MMC OWNER=MAIN BUFFER=8192(或64000)
FILE=EXP_MAINDB.DMP
ROWS=Y
COMPRESS=N
LOG= EXP_MAINDB.LOG
参数说明:
OWNER | 将要导出的用户名列表 |
BUFFER、FILE、ROWS、COMPRESS、LOG | 同上 |
3.1.3 全数据库模式
EXP MAIN/MMC BUFFER=8192(或64000)
FILE=EXP_MAIN_DB.DMP
FULL=Y ROWS=Y COMPRESS=N
LOG= EXP_MAIN_DB.LOG
对于数据库备份,建议采用增量备份,即只备份上一次备份以来更改的数据。
增量备份命令:
EXP MAIN/MMC BUFFER=8192(或64000)
FILE=EXP_MAIN_DB.DMP
FULL=Y INCTYPE=INCREMENTAL ROWS=Y COMPRESS=N
LOG=EXP_MAIN_DB.LOG
参数说明:
BUFFER、FILE、ROWS、COMPRESS、LOG | 同上 |
FULL | 指明是否导出完整的数据库。如果FULL=Y,将以全数据库模式进行导出。 |
INCTYPE | 增加导出的类型,有效值有complete(完全)、comulative(固定)和incremental(增量)。 |
complete | 输出所有表 |
comulative | 将输入第一次完全输出后修改过的表 |
incremental | 将输出前一次输出后修改过的表 |
说明:关于增量备份必须满足下列条件
(1)只对数据库备份有效,且第一次需要FULL=Y参数,以后需要INCTYPE=INCREMENTAL参数。
(2)用户必须有EXP_FULL_DATABASE。
(3)数据量较小时方可采用数据库备份。
3.2 Import 导入
Import和Export是两个相配套的实用程序,Export把数据库中的数据导出到操作系统文件中,而Import实用程序则把Export导出的数据恢复到数据库中。按备份方案确定恢复方案,例如:采用表逻辑备份方案,则恢复方案也采用恢复到表的方式(不应恢复到用户)。
要使用Import,必须具有CREATE SESSION特权,以便能注册到ORACLE RDBMS中去。这一特权属于在数据库创建时所建立的CONNECT角色。
如果导出文件是由某用户利用EXP_FULL_DATABASE角色创建的全数据库导出,那么只有具有IMP_FULL_DATABASE角色的用户才能导入这样的文件。下面给用户赋予IMP_FULL_DATABASE角色的方法。
# su – ORACLE | 假设当前为超级用户身份 |
$ sqlplus sys/change_on_install | 以sys用户登录并进入Sql*Plus |
SQL> GRANT "IMP_FULL_DATABASE" TO 用户名 | 在Sql*Plus下执行此命令 |
3.2.1 表模式
恢复方法为:
IMP MAIN/MMC FILE=文件名 LOG=LOG文件名
ROWS=Y COMMIT=Y BUFFER=Y IGNORE=Y
TABLES=(表名1,表名2,表名3,表名4,.......)
参数说明:
BUFFER | 缓冲区大小 |
FILE | 用于导入的导出文件名字 |
TABLES | 将要导入的表名列表 |
ROWS | 指明是否导入表数据的行数,缺省为“Y”。 |
IGNORE | 指明如何处理实体创建错误。指定IGNORE=Y,当试图创建数据库实体时,忽略实体存在错误。对除了表之外的其他实体,指定IGNORE=Y,Import不报告错误,继续执行。而指定IGNORE=N时,Import在继续执行前报告实体创建错误。 |
COMMIT | 指明在每个矩阵插入之后是否提交。缺省时,Import在导入每个实体之后提交。指定COMMIT=N时,如有错误产生,Import在记录导入下一个实体之前,完成一个回退。指定COMMIT=Y时,可以抑制回滚字段无限制增大,并改善大量导入时的性能,表具有唯一约束时,这种选择比较好。如果再次开始导入,将拒绝导入已经导入的任何行,原因是非致命性错误。表具有非唯一约束时,指定COMMIT=N可能是比较好的选择。因为重新导入可能会产生重复行。 |
LOG | 指定一个接收有用信息和错误信息的文件 |
3.2.2 用户模式
如果备份方式为用户模式,采用下列恢复方法:
IMP SYSTEM/MANAGER FROMUSER=MMCMIAN TOUSER=MAIN
FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y
BUFFER=Y IGNORE=Y
3.2.3 数据库模式
如果备份方式为数据库模式,采用下列恢复方法:
IMP SYSTEM/MANAGER FULL=Y
FILE=文件名 LOG=LOG文件名 ROWS=Y COMMIT=Y
BUFFER=Y IGNORE=Y
3.2.4 字符集转换对于单字节字符集(例如US7ASCII),恢复时,数据库自动转换为该会话的字符集(NLA_LANG参数);对于多字节字符集(例如ZHS168CGB),恢复时,应尽量使字符集相同(避免转换),如果要转换,目标数据库的字符集应是输出数据库字符集的超集。
3.3 增量导出/导入
下面介绍利用Export/Import实用程序对ORACLE数据库进行备份、恢复的方法:增量导出/导入。增量导出是一种常用的数据备份方法,包括3个子类:
“完全”增量导出
就是对整个ORACLE数据库进行完全导出。如:
$ exp system/口令 inctype=complete full=y file=today.dmp
“增量型”增量导出
即从ORACLE数据库中导出上次导出操作之后所有数据库的变化信息。如:
$ exp system/口令 inctype=incremental file=today.dmp
增量型导出文件的大小,可能只是完全导出文件大小的1%,具体要看“新信息或更新过的信息”的总量而定。
4、配置客户机/服务器
listener.ora和tnsnames.ora:放在“/ORACLE_HOME/ product/.../network/admin”子目录下,是SQL*Net V2正常工作所需的配置文件及整个网络的配置文件。listener.ora是服务器监听进程网络配置文件,tnsnames.ora是客户机与服务器联络所需的网络配置文件。
使用ORACLE的SQL*Net V.2连接客户机和服务器,需配置每个客户机和服务器。在服务器上需配置的文件有:listener.ora、/etc/services,在客户机上需配置tnsnames.ora文件。
4.1 配置listener.ora
listener.ora文件中存放了客户机与服务器连接所需要的监听地址,以及服务器启动监听进程时的信息。listener.ora文件缺省放置在/ORACLE_HOME/product/…/network/admin之下。一个完整的listener.ora文件样式如下:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ORACLE/product/...)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = oracledb.test.com)
(ORACLE_HOME = /opt/ORACLE/product/...)
(SID_NAME = oracledb)
)
)
其中,LISTENER是服务器中监听进程的名称;PROTOCOL表明所使用的网络协议,若是TCP/IP协议,则该值必须为“TCP”;HOST表示服务器的网络地址;PORT指TCP/IP协议在主机中所占用端口号,ORACLE缺省使用1521,是在安装ORACLE数据库时定义的,在安装时可以改变,安装完成后不能改变此值;SID_NAME指服务器上运行的ORACLE数据库名称,该值应与.bash_profile文件中的环境变量ORACLE_SID相同(一般情况下此文件为缺省值“PLSExtProc”);ORACLE_HOME是指ORACLE数据库的主目录,该值也应与. bash_profile文件中的环境变量ORACLE_HOME相同。
4.2 配置tnsnames.ora文件
客户机为了和服务器连接,必须先和服务器上的监听进程联络。ORACLE通过tnsnames.ora文件中的连接描述符来说明连接信息。一般tnsnames.ora 是建立在客户机上的。如果是客户机/服务器结构,整个网络上只有一台机器安装了ORACLE数据库服务器,那么只需在每个要访问ORACLE服务器的客户机上定义该文件,在服务器上无需定义。但是,如果网络上有多台机器均安装了ORACLE数据库服务器,并且服务器之间有数据共享的要求,那么在每台服务器上都必须定义该文件。tnsnames.ora文件缺省放在/ORACLE_HOME/ product/…/network/admin目录下。下面是tnsnames.ora的文件样式:
ORACLEDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracledb. test.com)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = oracledb. test.com)
(PRESENTATION = http://admin)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
字段含义如下:
ADDRESS_LIST | 表示该客户机要经由多种协议与一台或多台服务器连接。在该样式文件中就表示该客户机要用TCP/IP协议来和服务器相连。 |
PROTOCOL | 指明要连接使用的协议。 |
SERVICE_NAME | “SERVICE_NAME”就是“Global Database Name”,ORACLE8i数据库使用“Global Database Name”来唯一标识自己,通常的格式为“name.domain”,此处的值为“oracledb.test.com”。 |
HOST | 是TCP/IP协议使用的服务器IP地址。 |
PORT | 是TCP/IP使用的端口地址。 |
SID | 指定要连接的服务器上ORACLE数据库的ORACLE_SID。 |
SERVER=DEDICATED | 表示用专用服务器连接ORACLE数据库。 |
5、常用问题解决办法
5.1 如何恢复被误删的数据文件
- Svrmgrl> alter database datafile 文件名 offline;或重启ORACLE;
- Svrmgrl>alter database create datafile 原文件名 as 新文件名;
- Svrmgrl>recover datafile 新文件名
- Svrmgrl>alter database datafile 新文件名 online;
- Svrmgrl>alter database open;
5.2 如何杀掉吊死session
Select sid,serial#,program,machine,lockwait from v$session;
Svrmgrl>alter system kill session 'init1,init2';
说明:其中init1为sid,init2为serial#。
5.3 如何修改字符集
以sys用户执行如下命令
Update props$ set value$='新字符集'
Where ltrim(name)='NLS_CHARACTERSET';
commit;
注意:如果有数据,不要修改数据集。
5.4 如何追加表空间
以sys用户:
Alter tablespace 表空间 add datafile 文件名(带路径)size Xm;
5.5 如何加大表的maxextents值
Alter table 表名 storage(maxextents 新值)
5.6 如何查询无效对象
Select substr(object_name,1,30) object_name,object_type
from user_objects
where status ='INVALID'
对于存储过程,如果存储过程或函数脚本中某个表或所调用的存储过程被drop或重新编译,则此存储过程可能变为invalid,正常情况下,再次调用此存储过程时,系统会自动编译使其变为valid,也可以手工编译:Alter procedure 名称 compile;
5.7 怎样分析SQL语句是否用到索引
- ORACLE提供的策略分析器Explain plan能很好地分析SQL语句使用索引情况,分析步骤如下
- 检查当前用户下是否存在策略分析表plan_table
- 检查表结构是否正确(建立数据库时只有sys用户下存在此表),若没有,需要在当前用户下建此表:
CREATE TABLE PLAN_TABLE
(
STATEMENT_ID VARCHAR2(30),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANC NUMBER(38),
OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255),
SEARCH_COLUMNS NUMBER(38),
ID NUMBER(38),
PARENT_ID NUMBER(38),
POSITION NUMBER(38),
COST NUMBER(38),
CARDINALITY NUMBER(38),
BYTES NUMBER(38),
OTHER_TAG VARCHAR2(255),
OTHER LONG
);
sql>delete from plan_table;
sql>explain plan for
sql>select * from tab where tname like 'T%';
sql>select object_name,options,operation from plan_table;
执行完上述三步,可查看“select * from tab where tname like 'T%'”语句where条件是否用到索引。
如果你觉得此文章对你有一定的帮助,记得点赞、收藏一下,记得顺便点击如下链接关注一下小编的微信公众号吧,后续会不断有干货输出^.^~O(∩_∩)O