Linux系统下操作Oracle数据库

Linux系统下操作Oracle数据库

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。

一、登录oracle数据库：
//切换至Oracle用户:

[root@localhost ~]# su - oracle

//进入sqlplus环境，nolog参数表示不登录:

[oracle@localhost ~]$ sqlplus /nolog

//注：
su 和 su - 命令的区别：
前者只是切换了root身份，但Shell环境仍然是普通用户的Shell；
而后者连用户和Shell环境一起切换成root身份了。只有切换了Shell环境才不会出现PATH环境变量错误。

//以管理员模式登录：

[oracle@localhost ~]$ sqlplus / as sysdba

//注：
sqlplus / as sysdba，是oracle登录三种方式之一
oracle登录身份有三种：
1、normal 普通身份；
2、sysdba 系统管理员身份；
3、sysoper 系统操作员身份；
a.若以 ‘sysdba’ 方式认证，登录用户为 ‘SYS’，为 Oracle ‘最高权限用户’
b.若以 ‘sysoper’ 方式认证，登录用户为 ‘PUBLIC’，仅有 ‘PUBLIC 对象权限’
sysdba可以建数据库，sysoper不能建数据库

//查询sys和system两个管理员的权限个数

SQL> select t.grantee, count(1) from dba_sys_privs t where t.grantee in ('SYS', 'SYSTEM')  group by t.grantee;

注：sys和system都是Oracle ‘内置用户’；sys拥有最高权限，存储 Oracle 的数据字典的基表和视图，这些基表和视图对 Oracle 的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys (‘超级管理员’) 的权限远大于 system (‘一般管理员’)。
//启动数据库

SQL> startup

//停止数据库

SQL> shutdown immediate

//查看数据库所有账号及账号状态

SQL>select username,account_status from dba_users;

//查询SID
在oracle中,sid是“System IDentifier”的缩写,是数据库的唯一标识符,是在建立数据库时系统自动赋予的一个初始ID,是以环境变量的形式出现的,用于将系统上fork的进程与其他实例进行区分。SID是在一些DBA操作以及与操作系统交互，从操作系统的角度访问实例名，必须通过ORACLE_SID(操作系统的环境变量)，且它在注册表中也是存在的。而数据库名是在安装数据库、创建新的数据库、创建数据库控制文件、修改数据结构、备份与恢复数据库时都需要使用到的。

env|grep SID （不用进数据库，只需要su到oracle下就可以执行查询）

查看用户：

SQL> select * from v$instance;
SQL> select instance_name,host_name from v$instance;

设置SID：

 export ORACLE_SID=hhh

//查询数据库的DBID

SQL>select dbid,open_mode from v$database; 

DBID是DataBase IDentifier的缩写，是数据库的唯一识别码（代号），被记录在控制文件和数据文件中，跟学生表中的学号的功能是类似的，ID是系统自动分配的，如数据库名为testdb，dbid 为7。平时很少需要用这个参数。但在RMAN恢复时，若没有使用恢复目录（catalog)，知道被恢复的数据库的DBID可以简化操作。例如要恢复一个已经丢失了控制文件的数据库的控制文件。
//oracle查询数据库密码哪一天过期（其中expiry_date即是过期的时间）

SQL>select username,account_status,expiry_date,profile from dba_users;

//查询数据库密码有效期

SQL>select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

//配置用户密码过期时间

SQL>alter profile "default" limit password_life_time unlimited; 
配置用户密码永不过期
SQL>alter profile "default" limit password_life_time 100; 
配置用户密码100天过期

//修改密码

SQL>alter user 用户名  identified by  密码;

//创建、配置新用户及查看用户属性
//解锁新用户：

SQL>alter user scott account unlock；
SQL>alter user scott identified by tiger;

//删除oracle用户：

SQL>drop user username cascade;

(删除与用户相关的所有对象)
这样该用户包括该用户下的表，试图，同义词，过程，索引，及相关的一切就删除了。

//创建用户并赋权限以及设置默认表空间。
以sysdba用户登陆进行以下设置：
– 创建用户

SQL>create user VHFSM
identified by vhnj1fsm
default tablespace MGRVHFSTBSDEF 此处是设置默认表空间。
temporary tablespace TEMP
profile DEFAULT
quota unlimited on mgrvhfstbs2010 此处是设置可操作的其他表空间
quota unlimited on mgrvhfstbsdef;

–授权

SQL>grant connect to VHFSM;
SQL>grant dba to VHFSM;

– 授予无限表空间权限

SQL>grant unlimited tablespace to VHFSM;

//查看所有用户：

SQL>select * from dba_users;
SQL>select * from all_users;
SQL>select * from user_users;

//查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

SQL>select * from dba_sys_privs;
SQL>select * from user_sys_privs;

//查看角色(只能查看登陆用户拥有的角色)所包含的权限

SQL>select * from role_sys_privs;

//查看用户对象权限：

SQL>select * from dba_tab_privs;
SQL>select * from all_tab_privs;
SQL>select * from user_tab_privs;

//查看所有角色：

SQL>select * from dba_roles;

//查看用户或角色所拥有的角色：

SQL>select * from dba_role_privs;
SQL>select * from user_role_privs;

//查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

SQL>select * from V$PWFILE_USERS

//注意：
//以下语句可以查看Oracle提供的系统权限

SQL>select name from sys.system_privilege_map

//查看一个用户的所有系统权限(包含角色的系统权限)

SQL>SELECT privilege
FROM dba_sys_privs
WHERE grantee = 'DATAUSER'
UNION
SELECT privilege
FROM dba_sys_privs
WHERE grantee IN (SELECT granted_role FROM dba_role_privs WHERE grantee = 'DATAUSER');

二、数据库监听

  //启动监听服务

  [root@localhost ~]# su - oracle
  [oracle@localhost ~]$ lsnrctl start
 
  //停止监听服务
  [oracle@localhost ~]$ lsnrctl stop
 
  //查看监听状态
  [oracle@localhost ~]$ lsnrctl status

Listener主要负责下面方面功能：
1、 监听客户端请求
监听器运行在数据库服务器之上，与Oracle实例（可为多个）相关关联，是一个专门的进程process，在windows的服务项目或者Linux的运行进程列表中，都会看到对应的运行进程。Windows上名为TNSLSNR，Linux/Unix平台上是lsnrctl。
2、 为客户端请求分配Server Process
监听器只负责接听请求，之后将请求转接给Oracle Server Process。在Oracle的服务模式下，客户端进程是不允许直接操作数据库实例和数据，而是通过一个服务进程Server Process（也称为影子进程）作为代理。
3、 注册实例服务。
本质上讲，listener是建立实例和客户端进程之间联系的桥梁。Listener与实例之间的联系，就是通过注册的过程来实现的。注册的过程就是实例告诉监听器，它的数据库数据库实例名称instance_name和服务名service_names。监听器注册上这样的信息，对客户端请求根据监听注册信息，找到正确的服务实例名称。目前Oracle版本中，提供动态注册和静态注册两种方式。
4、错误转移failover。
Failover是RAC容错的一个重要方面功能，其功能是在数据库实例崩溃的时候，可以自动将请求转移到其他可用实例上的一种功能。可以提供很大程度上的可用性（Availability）功能。这个过程中，发现实例已经崩溃，并且将请求转移到其他实例上，就属于是listener的功能。
5、 负载均衡衡量。
在RAC架构中，Oracle实现了负载均衡。当一个客户请求到来时，Oracle会根据当前RAC集群环境中所有实例的负载情况，避开负载较高的实例，将请求转移到负载较低的实例进行处理。在早期RAC版本中，负载轻重的衡量是根据监听器当前维护连接数目来确定的，而不是实时查看多实例的负载。RAC环境中的监听器之间进行沟通通信。
三、用户权限管理
注：以下命令都需要DBA权限。
//查询哪些用户拥有DBA权限：

SQL>select * from dba_role_privs t where t.granted_role = 'DBA';

//创建用户

SQL>create user 用户名 identified by 123456 ;

//赋予用户的表空间权限

SQL>alter user 用户名default tablespace 用户名;

//或者两条命令合并为

SQL>create user 用户名identified by 123456 default tablespace 用户名;

注：新创建的用户是没有任何权限的，登录的权限都没有。因此需要再继续做授权操作，但必须是具有授权能力的用户，例如：sys、system；角色是指由系统权限集合，通常给某个用户授权时，如果没有角色存在的话，则需要一条条的操作。通常一个角色由多个系统权限组成。常用的角色有三个connect(7种权限)，dba，resource(在任何表空间建表)。

//授予用户管理权限

SQL>grant connect,resource,dba to 用户名;

//删除用户

SQL>drop user“name”cascade;

注：cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数。“name”的引号有无都是一样的。
四、数据表及表空间
=什么是表空间=
表空间是Oracle数据对象和数据存储的容器，它只是一个逻辑概念，若干操作系统文件（文件可以不是很大）可以组成一个表空间。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示：

//创建表空间

create tablespace ittbank datafile '/u01/app/oracle/oradata/ORCL/ittbank.dbf' size 300m autoextend on;

注：末尾带autoextend on参数表示当表空间大小不够用时会自动扩容，of则代表不自动扩容，所以建议加上autoextend on参数。

//查询当前表空间

select * from v$tablespace;

//查询所有表空间

select * from sys.dba_tablespaces;

--查询表空间

//查看用户当前连接数

select count(*) from sys.v_$session；

//查询空闲空间

select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;

//增加Oracle表空间
先查询数据文件名称、大小和路径的信息，语句如下：

select tablespace_name,file_id,bytes,file_name from dba_data_files;

//修改文件大小语句如下

alter database datafile'需要增加的数据文件路径，即上面查询出来的路径'resize 800M;

五、desc常用查询

 desc dba_temp_files; 查询临时表空间
 
 desc v$database; 查看数据库
 
 desc dba_data_files; 查看数据文件
 
 desc user_segments; 查看oracle segment（段）
 
 desc dba_segments; 查看ORACLE segment
 
 desc dba_tables; 查看表
 desc dba_objects 查看对象
 desc dba_users; 查看用户
 desc dba_tablespaces; 查看表空间
 
 desc user_segments; 查看数据段
 
 desc dba_jobs; 查看job
 
 desc dba_role_privs； 查看角色权限
 
 desc dba_constraints 查看约束
 
 desc dba_cons_columns 查看列约束
 
 show parameter log_archive_dest; 查看archive log所在位置
 
 archive log list; 查看归档目录以及log sequence
 
select * from V$FLASH_RECOVERY_AREA_USAGE; 查看flash_recovery_area 使用情况。
 
select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage; 计算flash_recovery_area已经占用的空间。
 
show parameter recover; 查找recovery目录
 
desc v$process 通过v$process视图，我们获的是当前系统中所有进程信息，包括“后台进程”，也包括“服务器进程”。select addr, program from v$process;

desc v$bgprocess 通过v$bgprocess视图,我们获得当前系统中启动的Oracle“后台进程”信息。select paddr, name from v$bgprocess where paddr<>'00';
 
通过v$bgprocess.paddr与v$process.addr关联起来的，关联后查询结果显而易见只会有Oracle“后台进程”的信息
 select a.paddr, a.name, a.description from v$bgprocess a, v$process b where a.paddr=b.addr;