ORACLE培训——HR

（一）、学习目标

 

（二）、理念

ORACLE的相关名词

1、PL/SQL
PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。
2、NLSRTL:全球化支持
Oracle NLS Runtime Library(NLSRTL)：与oracle的字符集文件以及字符支持有关。
Oracle的全球支持是通过Oracle NLS Runtime Library(NLSRTL)来实施的， NLS运行库通过独立的函数来完成运行时与语言相关的转换及控制。
3、 VSS (Volume Shadow copy Service, 卷映射拷贝服务)，它可以在多卷、或者单个卷上创建映射拷贝，同时不会影响到系统性能。
A、 快速数据备份和恢复；
B、 针对制定文件的恢复；
C、 磁带备份、测试，以及数据挖掘方面；
VSS整体框架包含了VSS核心模块、请求者(Requestor)、写入者(Writer)，以及提供者(Provider)。

4、RAC
Oracle RAC是Oracle Real Application Cluster的简写，官方中文文档一般翻译为“真正应用集群”，它一般有两台或者两台以上同构计算机及共享存储设备构成，可提供强大的数据库处理能力，现在是Oracle 10g Grid应用的重要组成部分。

5、SGA PGA
SGA:系统全局区，用于存储数据库信息的内存区，该信息为数据库所有进程所共享，包括oracle服务器的数据和控制信息。
PGA:程序全局区，包括单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA刚好相反，PGA只能被一个进程所使用，在进程创建时分配，进程结束是收回。

 

TNS 透明网络底层

 1.Oracle中TNS的完整定义：Transparence Network Substrate，透明网络底层。监听服务是它重要的一部分，不是全部，不要把TNS当作只是监听器。
2.ORACLE当中，如果想访问某个服务器，必须要设置TNS，它不像SQL SERVER那样在客户端自动列举出在局域网内所有的在线服务器，只需在客户端选择需要的服务器，然后使用帐号与密码登录即可。而ORCAL不能自动列举出网内的服务器，需要通过读取TNS配置文件才能列出经过配置的服务器名。
3.配置文件名一般为：tnsnames.ora
4.存取路径： $ORACLE_HOME\network\admin\tnsnames.ora 

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora81\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#sample
database_name=orcl
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = servicename)
)
)
斜体红色处是需要修改的地方，但是一般我们只修改三处位置，连接别名database_name、连接地址127.0.0.1、连接的数据库名servicename。配置好了就可以使用客户端软件（如PL/SQL Developer、TOAD）来连接数据库了。

 

TNSPING命令
1.Oracle Net 工具（命令）tnsping，是一个OSI会话层的工具，它用来：
1）可以检查tcp协议是否可用，即：我们平时说的网络通不通；能验证一台服务器的名字解析（name resolution，当然是oracle自己的网络服务名）;
2）tnsping命令监听服务器上的listener监听是否已开启;
3）tnsping命令不检测服务器上的监听的服务名，所以tnsping通，不代表ORACLE DB就能连通。

2.tnsping命令：如果能够ping通，则说明客户端能解析listener的机器名，而且lister也已经启动，但是并不能说明数据库已经打开，而且tnsping的过程与真正客户端连接的过程也不一致。但是如果不能用tnsping通，则肯定连接不到数据库。

3.查看服务配置的ora文件路径：tnsnames.ora
4.格式：  tnsping  主机名或IP地址
(如:tnsping localhost )
(再如：tnsping 192.168.1.200)

LISTENER监听命令LSNRCTL
1.lsnrctl start  开启监听
2.lsnrctl stop   关闭监听
3.lsnrctl status  监听状态
4.通过在lsnrctl中输入set display verbose，然后再通过命令service查看，服务状态为READY表示PMON自动注册的服务名，而UNKNOWN则表示该服务是手工在LISTENER.ORA（监听服务配置）中配置的数据库服务！

TOAD

TOAD for Oracle的使用
1.TOAD英文含义是：青蛙 蟾蜍。 Tools of Oracle Application Developers (甲骨文应用开发工具)
2.四个版本：标准版、专业版、专家版（开发套装版）、管理套装版。
3.官方网站：http://www.quest.com/
4.TOAD比PL/SQL DEVELOPER 贵100-200倍，价格一般在2-4万人民币。
5.在Oracle应用程序的开发过程中，访问数据库对象和编写SQL程序是一件乏味且耗费时间的工作，对数据库进行日常管理也是需要很多SQL脚本才能完成的。Quest Software为此提供了高效的Oracle应用开发工具-Toad（Tools of Oracle Application Developers）。在Toad的新版本中，还加入了DBA模块，可以帮助DBA完成许多日常管理工作。它最大的特点就是简单易用，访问速度快。使用Toad，我们可以通过一个图形化的用户界面快速访问数据库，完成复杂的SQL和PL/SQL代码编辑和测试工作。Toad由Oracle开发专家专门为开发人员而设计，是一个功能强大、结构紧凑的专业化PL/SQL开发环境。

TOAD 监听程序配置（服务器端）
1.通过开始---程序---OracleDB11g（服务器端）---配置和移植工具---Net Configuration Assistant (NCA网络配置助手)，建立监听程序配置。（如果存在监听程序，可以更新配置，没有监听程序，就添加一个）
2.注意：
2.1要不断下一步，可以根据需要取一个个性化的名称，默认监听程序为：LISTENER（可保持不变）;
2.2网络协议选择:TCP;
2.3端口配置时，使用甲骨文默认的标准端口：1521；

 

TOAD监听程序配置（客户端）
1.通过开始---程序---OracleClient11g（客户端）---配置和移植工具---Net Configuration Assistant (NCA网络配置助手)，建立与服务器端相同的监听程序（例如：均配置为LISTENER)。
2.注意：
2.1如果存在监听程序，可以更新配置，如果没有监听程序，就添加一个新配置;
2.2要不断下一步，可以根据实际需要取一个个性化的名称，默认监听程序为：LISTENER（可保持不变）;
2.3网络协议选择:TCP;
2.4端口配置时，使用甲骨文默认的标准端口：1521；
2.5view---Toad Opptions---Oracle---Transactios--勾选Commit after every statement.就可以写完sql语句后自动提交。
2.6在Toad中想同时选中多条Insert语句插入数据时会报无效字符,可以Excute as script.

（三）、准备工作

1.ORACLE的启动与关闭
启动：startup open
关闭：shutdown immediate  (请勿随意关闭)
ORACLE主要服务有：
（1）Oracle Service<SID>：数据库服务（*）
（2）Oracle<HOME_NAME>TNSListener：监听器服务（*）
（3）Oracle<Home_Name>Agent：代理服务
（4）OracleDBConsoleorcl 企业管理器（浏览器版）
（5）Oracle<Home_Name>HTTPServer
（6）Oracle<Home_Name>ManagementServer
Oracle两个主要的组成部分是：数据库和实例
Oracle数据库用于存储和检索信息，是数据的集合。包括逻辑结构和物理结构。
Oracle实例是指数据库服务器的内存及相关处理程序。

2.正确使用SQL*PLUS
2.1开始---运行---CMD---SQLPLUS
2.2开始---运行---SQLPLUS
2.3退出：EXIT / QUIT
2.4清屏：CLEAR SCREEN ------相当于DOS中的CLS
2.5操作流程：
实例---表空间---表---用户---授权
Instance---TableSpace---Table---User---Grant
每个实例是庞大数据库中的一个具体的分站点，Database Configuration Assistant （数据库配置助手）可建立新实例。

3.登录
请登录数据库再进行相关操作！
管理员登录：sys as sysdba  或system  as sysdba
操作员登录：scott as sysoper  或system  as sysoper
查看登录者身份：show user;

 

（四）、结构设计：如何使用SQLPLUS进行库、表、列操作?
进入SQLPLUS,确定你现在是以管理员sysdba身份登录的：
1.首先，创建（新）用户：   
create user username identified by password;   
username：新用户名的用户名   
password: 新用户的密码也可以不创建新用户，而仍然用以前的用户，如：继续利用scott用户
用户默认是权限锁定的，即account lock,解锁请用在上面的命令后面加上：
account unlock.只有解锁后的用户才可进行各种操作。

以SYSDBA身份登陆时可以修改其他用户的密码，比如：
SQL> alter user user01 identified by user10;
用户已更改。这个是把USER01用户密码修改为USER10

2.创建表空间：   

create tablespace tablespacename datafile ‘d:\data.dbf’ size xxxm;    

tablespacename：表空间的名字   
‘d:\data.dbf’：表空间的存储位置   
xxx表空间的大小，m单位为兆(M)

3.将表空间分配给用户：  

alert user username default tablespace tablespacename;   

将名字为tablespacename的表空间分配给username

4.给用户授权：  

grant create session,create table,unlimited tablespace to username;

在TOAD中查看用户SQL命令：
desc username（用户名）,然后执行，即可弹出窗口;

5.然后再以自己创建的用户登录，登录之后创建表即可。
conn username/password@###;
###手工指定表空间，将用户与空间建立联系

6、创建角色与角色授权

create role 角色名 identified by 密码;--创建新角色 
grant create session to 角色名；--给角色授予创建会话的权限 
grant 角色名 to 用户名； --把角色授予用户 

7、删除用户

DROP USER test [CASCADE];

8、删除表空间

DROP TABLESPACE test01 INCLUDING CONTENTS AND DATAFILES;

1) 删除表空间内容和数据文件；
2) 一般无效表空间占用磁盘空间,所以这个应该很常用。
9、查看用户角色

select * from user_role_privs;

权限

10、查看用户表权限

select * from user_tab_privs;

11、创建表

create table demo (id 属性,name 属性,pass 属性...);

常用的数据类型有：number  varchar2 date char long等
创建表

create table 表名 
( 
id int not null, 
name varchar2(20) not null 
)tablespace 表空间名 --所属的表空间 
storage 
( 
initial 64K --表的初始值 
minextents 1 --最小扩展值 
maxextents unlimited --最大扩展值 
); 

12、描述表的结构 desc 表名；
13、删除表：drop 表名；
14、为usrs表添加主键和索引

alter table users 
 add constraint pk primary key (ID); 

15、为已经创建users表添加外键

alter table users 
   add constraint fk_roleid foreign key (roleid) 
   references role(role_id) on delete cascad; --下边写主表的列 
   on delete cascad是创建级联 （cascad 层叠、级联 、小瀑布）

 

查看ORACLE中已安装的各种产品组件版本
SQL> select * from v$version;
从系统表中提取系统日期：select sysdate from dual;
显示所有系统参数的状态：show all; 
显示提示信息：prompt  要显示的信息（词或句）; 

--查询所有的表空间：
select * from v$tablespace;
select * from dba_tablespaces；
查询所有用户：    
select * from dba_users;
查询符合某些条件的用户：  
select * from dba_users where username like 'ABC%';
查看所有表：
select table_name from all_tables;
查看所有表对象：
select object_name from all_objects where object_type='TABLE' ；

 

 

表结构::
1、查看表结构
DESC 表名

2、更改表的结构：

ALTER TABLE 表名 ADD  字段名  字段属性  （添加 ）
ALTER TABLE 表名 MODIFY 字段名  字段属性  （修改 ）
ALTER TABLE 表名 DROP COLUMN 字段名（删除）
alter table 表名 rename column旧的字段名 to 新的字段名名;（实现字段名的修改）
show linesize （显示行宽）
set linesize  数值（设置行宽）

 

授权::
grant 权限 to 用户；
一般授权权限有：sysdba   sysoper 、connect 、 resource
语法结构：授权
GRANT角色|权限 TO 用户（角色）
SQL> GRANT CONNECT TO lingzhi; 授权成功。
SQL> GRANT RESOURCE TO lingzhi; 授权成功。

--回收权限 
REVOKE 角色|权限 FROM 用户（角色） 
--修改用户的密码 
ALTER USER 用户名 IDENTIFIED BY 新密码
--修改用户处于锁定（非锁定）状态
 ALTER USER 用户名 ACCOUNT LOCK|UNLOCK

 

查看数据库的字符集，解决乱码::
默认字符集为：ZHS16GBK

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
  WHERE PROPERTY_NAME = 'NLS_CHARACTERSET';

客户端与数据库服务器的字符集一致，方可避免乱码。

  右键＂我的电脑＂，在＂属性＂－－＂设置＂－－＂环境变量＂－－＂系统变量＂中增加一项，其中变量名为NLS_LANG ，变量值为AMERICAN_AMERICA.ZHS16GBK

 重启ＴＯＡＤ后，即可看到原为乱码的内容，显示为正常中文。