Oracle 查询与维护操作

根据网络整理，完善中

1.数据库名(DB_NAME)、实例名(Instance_name)、以及操作系统环境变量(ORACLE_SID)

通常情况下:

1. 一个实例对应一个数据库
2. RAC集群:一个数据库对应多个实例
3. Oracle 12C: 一个实例可以对应多个数据库

在ORACLE 7、8数据库中只有数据库名(db_name)和数据库实例名(instance_name)。在ORACLE 8i、9i中出现了新的参数，即数据库域名(db_domain)、服务名(service_name)、以及操作系统环境变量(ORACLE_SID)。这些都存在于同一个数据库中的标识，用于区分不同数据库的参数。

1.1 什么是数据库名(db_name)

数据库名是用于区分数据的内部标识，是以二进制方式存储于数据库控制文件中的参数，在数据安装或创建之后将不得修改。数据库安装完成后，该参数被写入数据库参数文件pfile中，格式如下：

db_name="orcl" #(不允许修改) 
db_domain=dbcenter.toys.com 
instance_name=orcl 
service_names=orcl.dbcenter.toys.com 
control_file=(............... 
.........

在每一个运行的ORACLE 8i数据库中都有一个数据库名(db_name),如果一个服务器程序中创建了两个数据库，则有两个数据库名。其控制参数据分属在不同的pfile中控制着相关的数据库。

1.2 什么是数据库实例名(instance_name)

• 数据库实例名则用于和本地主机操作系统之间的联系 (注意是与本机操作系统的联系，不是像监听器是与远程主机的联系 ，监听器见监听部分 )，在操作系统中要取得与数据库之间的交互，必须使用数据库实例名。例如，要和某一个数据库server连接，就必须知道其数据库实例名，只知道数据库名是没有用的，与数据库名不同，在数据安装或创建数据库之后，实例名可以被修改。数据库安装完成后，该实例名被写入数据库参数文件pfile中，格式如下：

db_name="orcl" #(不允许修改) 
db_domain=dbcenter.toys.com 
instance_name=orcl #(可以修改,可以与db_name相同也可不同) 
service_names=orcl.dbcenter.toys.com 
control_file=(............... 
.........

• 数据库名与实例名之间的关系

  非RAC中，数据库名与实例名之间的关系一般是一一对应关系，有一个数据库名就有一个实例名，如果在一个服务器中创建两个数据库，则有两个数据库名，两个数据库实例名，用两个标识确定一个数据库，用户和实例相连接。

  RAC中，数据库与实例之间不存在一一对应关系，而是一对多关系(一个数据库对应多个实例），同一时间内用户只一个实例相联系，当某一实例出现故障，其它实例自动服务，以保证数据库安全运行。)

  在12C以后，一个实例可以对应多个数据库了。

1.3 操作系统环境变量(ORACLE_SID)

在实际中，对于数据库实例名的描述有时使用实例名(instance_name)参数，有时使用ORACLE_SID参数。这两个都是数据库实例名，它们有什么区别呢？(经常弄混)

(ORACLE_SID)
OS<----------------> ORACLE 数据库 <--------(Instance_name(实例名))

上图表示实例名instance_name、ORACLE_SID与数据库及操作系统之间的关系，虽然这里列出的两个参数都是数据库实例名，但instance_name参数是ORACLE数据库的参数，此参数可以在参数文件中查询到（select命令），而ORACLE_SID参数则是操作系统环境变量（env命令或echo命令）。
操作系统环境变量ORACLE_SID用于和操作系统交互。也就是说，在操作系统中要想得到实例名，就必须使用ORACLE_SID。此参数与ORACLE_BASE、ORACLE_HOME等用法相同。在数据库安装之后，ORACLE_SID被用于定义数据库参数文件的名称。如：$ORACL{E}_{B}ASE/admin/D{B}_{N}AME/pfile/init$ORACLE_SID.ora。

定义方法:

export ORACLE_SID=orcl

如果在同一服务器中创建了多个数据库，则必然同时存在多个数据库实例,这时可以重复上述定义过程，以选择不同实例。

2.SQLplus的连接方式

1. sqlplus / as sysdba
   操作系统认证，不需要数据库服务器启动listener，也不需要数据库服务器处于可用状态
   比如我们想要启动数据库就可以用这种方式进入sqlplus，然后通过startup命令来启动。

2. sqlplus username/password
   连接本机数据库，不需要数据库服务器的listener进程
   但是由于需要用户名密码的认证，因此需要数据库服务器处于可用状态才行

3. sqlplus username/password@远端ip:端口号/服务名
   用sqlplus远程连接oracle服务器，比如sqlplus system/system@127.0.0.1:1521/orcl

   大家只要把@后面的IP地址换成你们要连接的IP地址就可以了，1521是远程数据库的端口号，orcl是远程数据库的服务名
   如果你们远程的那台oracle数据库的服务名叫其他名字，替换服务名即可

4. sqlplus usernaem/password@主机字符串
   通过网络连接，这是需要数据库服务器的listener处于监听状态。此时建立一个连接的大致步骤如下　
   a. 查询sqlnet.ora，看看名称的解析方式，默认是TNSNAME　　
   b. 查询tnsnames.ora文件，从里边找orcl的记录，并且找到数据库服务器的主机名或者IP，端口和service_name　　
   c. 如果服务器listener进程没有问题的话，建立与listener进程的连接。　　
   d. 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。
   默认是专用服务器模式，没有问题的话客户端就连接上了数据库的server process。
   e. 这时连接已经建立，可以操作数据库了。

3.数据库启动/关闭/状态查询/切换实例/监听

3.1 监听

监听是用于数据库与远程主机访问（IP访问）用的，只在本地操作可以不启用监听

# 切换到oracle用户（oracle替换成真实用户名）
su - oracle
# 启动监听
lsnrctl start
# 查看监听状态
lsnrctl status
# 停止监听
lsnrctl stop

3.2 数据库与实例

3.2.1 查看数据库、实例和切换实例（操作系统法）

通过数据库记录在操作系统的信息或进程进行查询。Windows另行百度

# 查看已经启动的实例,注意ora_smon_前缀部分不是实例名的一部分，剩余部分才是
ps -ef |grep smon
oracle   18281     1  0  2019 ?        21:38:14 ora_smon_eam
oracle   42710     1  0  2019 ?        02:30:45 ora_smon_oradbx5
# 切换实例
export ORACLE_SID=实例名称
# 查看数据库名和当前选中的实例（通过环境变量查看，无论是否运行）
env

3.2.2 查看数据库、实例和切换实例（数据库法）

注意：在多实例的环境中，以下命令都只针对当前选中的实例，其它的实例是看不到的！要看到话就先切换实例。方法：export ORACLE_SID=实例名称

# 查看当前选中实例的数据库名：
select name from v$database;
# 查看当前数据库实例名
select instance_name from v$instance;
# 查看当前实例开启时间
select startup_time from v$instance;
# 查看当前实例状态
select status from v$instance;
# 查看当前数据库状态
select database_status v$instance;
# 当前系统时间戳
SELECT SYSTIMESTAMP FROM  dual;  		
# 查看数据库版本
select * from v$version;

# 也可以通过查看数据库的参数，获取数据库和实例的信息
>show parameter service_names  //查看当前数据库service_name
>show parameter instance_name  //查看当前SID
>show parameter 查看详细信息

3.3.3 实例的启动与关闭

启动的整体过程：启动监听–>选择实例->启动实例

主要的命令是 startup ,shutdown和shutdown immediate。以下是整个过程的示例，结合了其他操作。启动/停止后，使用ps -ef|grep ora_|grep -v grep 查看启动/停止情况

# --- Linux 命令的操作，进入数据库前的准备---
# 切换oracle用户
su - oracle
# 启动监听
lsnrctl start
# 查看监听状态
lsnrctl status
# 停止监听
lsnrctl stop
# 查看系统关于Oracle的环境变量
env|grep ORA
# 查看当前实例
echo $ORACLE_SID
# 切换实例，并启动或关闭（多个实例时不同的切换然后执行相同操作）
export ORACLE_SID=XXXXX

sqlplus / as sysdba

# ---以下是数据库命令的操作---
# 查看当前SID，在Linux系统命令执行
>echo $ORACLE_SID
# 切换实例，并启动或关闭（多个实例时不同的切换然后执行相同操作），也可以作Linux系统命令执行
>startup #启动
>shutdown #关闭
>shutdown immediate #立即关闭
>select instance_name, status from v$instance; -->查看实例状态(SID)
>quit 

# --以下是SQL中常用的查询命令，查询常用的参数--
>show parameter service_names  //查看当前数据库service_name
>show parameter instance_name  //查看当前SID
>select instance_name, status from v$instance; -->查看实例状态(SID)
>show parameter 查看详细信息

4.表空间tablespace与数据文件

• 单个数据文件（dbf文件）中只保存一个表空间的数据

• 单个数据文件（dbf文件）的大小有限制，限制由创建时指定，可以指定是否在达到设定大小时自动增长

• 单个数据文件大小不能超过系统限制（32G），现在允许设置更大的了，但仍有上限

4.1 常用表空间查询操作

# 查看数据库使用了几个表空间
select distinct TABLESPACE_NAME from tabs；

# 查看数据库表空间情况：
select f.tablespace_name,a.total,f.free,round((f.free / a.total) * 100) "% Free"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";

4.2 将表空间的数据文件设置为自动增长/不自动增长

# 不允许自动增长
SQL> alter tablespace  表空间名称 add datafile '新数据文件路径/数据文件名.dbf' size 32G autoextend off;

# 允许数据文件大小自动增长，一个表空间数据库文件最大可以扩展到32G。
SQL> alter tablespace 表空间名 add datafile '数据文件路径/数据文件名.dbf' size 1000m autoextend on next 100m maxsize unlimited; 

# 查看表空间自动增长情况（或数据文件大小自动增长情况）
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS 
FROM DBA_TABLESPACES T,DBA_DATA_FILES D 
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 
ORDER BY TABLESPACE_NAME,FILE_NAME; 

5. 数据库用户查询操作

# 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from dba_users; 
注意：dba_users的含义为database_all_users
# 查看你能管理的所有用户
select * from all_users;
# 查看当前用户信息
select * from user_users;

6.修改数据库用户的密码（包含忘记密码）

# 以超级用户登录
sqlplus / as sysdba
# 解除锁定(必须带“;”号)
alter user 用户名 account unlock;
# 修改密码
alter user 用户名 identified 密码; 
然后用你改好的密码登陆就行

# 忘记Oracle系统管理员sys的密码
sqlplus / as sysdba;
alter user sys identified by newpwd***;

7.设置开机自动启动Oracle

修改/etc/oratab文件，找到文件中这行（orcl:/opt/oracle/app/product/12.1.0/dbhome_1:N）并把N替换成Y即可，该行是这样定义的$ORACLE_SID:$ORACLE_HOME:<N|Y>，然后修改/etc/rc.d/rc.local文件添加如下两行才能开机自启动数据库：
su oracle -lc "/opt/oracle/product/11.2.0/dbhome_1/bin/lsnrctl start"
su oracle -lc /opt/oracle/product/11.2.0/dbhome_1/bin/dbstart
su oracle -lc "/opt/oracle/product/11.2.0/dbhome_1/bin/emctl start dbconsole" 
1234

Oracle创建/授权相关操作

1. 预先提示

1. 在Oracle中，表是归属用户的（表的上一级是用户，或称用户是表的集合），使用 用户名.表名 形式； MySQL中，表是归属库的（表的上一级是库，或称库是表的集合），使用 库名.表名 形式

   比如查表的区别：

   ​ MySQL： select * from 数据库名.表名

   ​ Oracle： select * from 用户名.表名

2. 在传统的Oracle架构中，一个实例只能有一个数据库（RAC是多个实例对应一个库），因此多个库就需要多个实例（一个实例可以理解为一个Oracle进程） 。在12C以后，一个实例已经可以对应多个库了。

3. 开发一个新应用时，MySQL是为该应用新建一个库；而对于Oracle来说，是新建一个用户（即使新建库，也要创建新用户才能使用，本质上还是新建一个用户）

4. 在Oracle中，一个表空间对应一个或多个数据文件，一个数据文件只能属于一个表空间。

5. 新建一个表空间后，该表空间须有用户才能使用。一个表空间可以有多个用户，一个用户只能有一个表空间。（用户数：表空间数=1：n）

2. 新建表空间

3. 新建用户

4. 授权

5.常用工具（实际上是Oracle自带的脚本）

1. sqlplus 连接工具，不用多说了，上面篇幅一直在用
2. netca network configuration assistant，网络配置工具，配置监听等
3. dbca database configuration assistant，用于创建数据库等
4. emca
5. rman 备份工具，热备，生成备份数据文件而非脚本？

新建用户：
create  user 用户名 identified by 密码 default tablespace 默认表空间 temporary 临时表空间;
赋权限:
grant connect,resource,create any view,create any synonym,create database link,select any table,create table,update any table,insert any table,delete any table,alter any table to 用户名;

更多的权限加进去就可以了

赋予DBA权限：
grant dba to username;
撤销DBA权限：
REVOKE dba,exp-full-database,imp-full-datsabase from 用户名;

---

oracle数据库的权限系统分为系统权限与对象权限。

• 系统权限( database system privilege )
  可以让用户执行特定的命令集。例如，create table权限允许用户创建表，grant any privilege 权限允许用户授予任何系统权限。
• 对象权限( database object privilege )
  可以让用户能够对各个对象进行某些操作。例如delete权限允许用户删除表或视图的行，select权限允许用户通过select从表、视图、序列(sequences)或快照(snapshots)中查询信息。

一、创建\删除用户

• 登录到system以创建其他用户
  打开cmd:

sqlplus /nolog

SQL> connect / as sysdba

创建用户以及设置密码：

create user username identified by password;

如：

create user user1 identified by 123456;

修改用户：

alter user user1 identified by 234556;

撤销用户user1：

drop user user1;

二、为用户授权角色\撤销授权

oracle提供三种标准角色（role）:connect/resource和dba.

1. connect role(连接角色)

临时用户，特指不需要建表的用户，通常只赋予他们connect role.

connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。
拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link)。

2. resource role(资源角色)

更可靠和正式的数据库用户可以授予resource role。

resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

3. dba role(数据库管理员角色)

dba role拥有所有的系统权限

包括无限制的空间限额和给其他用户授予各种权限的能力。

• 为用户user1授权：

grant connect, resource to user1;

• 撤销对user1赋予的权限：

revoke connect, resource from user;

—-创建表空间：
create tablespace 表空间名
logging
datafile ‘D:\oracle\product\10.2.0\oradata\orcl\TSP_BBS.DBF’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

将表空间分配给用户：
alter user test default tablespace TSP_BBS;

给用户授权：
为用户赋予connect和resource权限：
grant connect,resource to test;
赋予这些权限后就可以

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

grant connect, resource to test;
grant connect to test;
grant create indextype to test;
grant create job to test;
grant create materialized view to test;
grant create procedure to test;
grant create public synonym to test;
grant create sequence to test;
grant create session to test;
grant create table to test;
grant create trigger to test;
grant create type to test;
grant create view to test;
grant unlimited tablespace to test;
alter user test quota unlimited on TSP_BBS;