存放数据的仓库

  • 内存: int num = 10 ; 问题:临时
  • 文件: 解析(XML)/操作起来麻烦
  • 数据库: 永久存放,方便解析/管理

相关概念

数据库是一组文件,位于磁盘上,存储数据。这些文件可以独立于数据库实例而存在。
实例是一组管理数据库文件的内存结构。实例由共享内存区域(称为系统全局区域(SGA))和一组后台进程组成。实例可以独立于数据库文件而存在。


关系/非关系型数据库

关系型的数据库(RDBMS):关系型->二维表
Oracle:产品免费,服务收费,强大稳定性 安全性
MySQL:MySQL AB开源, 2008被SUN收购, 2009年被Oracle收购; 版本:社区Community免费,企业版收费 ;产品免费,服务收费
SQL Server:微软开发,强大的图形化工具,方便使用(大学学习)
DB2:IBM,多个操作系统、多个硬件
非关系型:基于key-value结构(eg:person.name)
NoQSL:Not only SQL
Redis
MongoDB


Oracle版本

  • Oracle8i/9i:internet ,开始走向网络
  • Oracle10g/11g:grid,网格计算,提高访问速度,避免舍近取远的问题
  • Oracle12c:cloud,云计算
  • Oracle11g:目前主流
  • Oracle19c:本次学习

各个版本对于学习、研发 没有区别,只是在 最终的部署、运维时差异较大


Oracle结构

数据库硬盘文件和实例为一对多的关系,多数据库实例称之为集群(Real Application Cluster)

存储结构:

  • 物理存储结构:数据文件(存储表和索引数据,以及排序和散列等操作的中间结果。),控制文件(Oracle为管理数据库的状态而维护的一个文件,它记录数据库的物理存储结构和其他控制信息,如数据库名称、创建数据库的时间戳、组成数据库的各个数据文件和重做日志文件的存储路径及名称、系统的检查点信息等 ;),重做日志文件组成,还有其它辅助文件,如:参数文件、口令文件、警告日志文件、跟踪文件等。
  • 逻辑存储结构:逻辑存储结构使Oracle数据库能够对磁盘进行细粒度控制,主要由数据块(Oracle数据库的I/O单位)、(Oracle数据库内存储空间的最小分配单位)、(占用存储空间的数据库对象,段分为表段、索引段、回滚段和临时段)、表空间(用于存储数据库中的所有数据)构成。

组成结构:

Architecture
database_instance
实例分为两阶段(PGA+SGA),前者专门供每个会话使用,后者由所有Oracle进程共享。

  • PGA(program global area,程序全局区):一个PGA对应于一个客户端,主要为了某个用户进程所服务的,PGA主要包含排序区、会话区、堆栈区和游标区四个部分的内容,他们各司其职,完成用户进程与数据库之间的会话。当用户进程连接到数据库并创建一个对应的会话时,Oracle服务进程会为这个用户专门设置一个PGA区,用来存储这个用户会话的相关内容。当这个用户会话终止时,数据库系统会自动释放这个PAG区所占用的内存。这个PGA区对于数据库的性能有比较大的影响,特别是对于排序操作的性能。
  • SGA(system global area, 系统全局区):主要由三部分构成:(SQL)共享池、数据缓冲区、日志缓冲区。SGA对系统内的所有进程共享,主要为不同用户之间的进程与服务进程提供一个交流的平台。另外,各种数据库的操作主要就是在这个SGA区内完成。

两阶段提交:

  • pga->sga
  • sga->数据库(此阶段,会将一些重复/冗余的工作 进行合并,从而减少数据访问次数)
    PGA-SGA

口令管理

超级管理员:sys/change_on_install
普通管理员:system/manager
普通用户:scott/tiger 需要解锁,12c后没有了需要手动添加
@%oracle_home%\rdbms\admin\scott.sql
数据库统一管理帐号:orcl/安装时设密

采用操作系统验证的方式登陆:sqlplus / as sysdba
sys/system底下修改用户口令:alter user 用户名 identified by 新口令;
解锁:alter user 用户名 account unlock;


DBCA创建数据库过程中生成的脚本文件分析

DBCA创建数据库过程中生成的初始化参数文件:E:\oracle\admin\orcl\scripts

查看初始化参数设置:
方法一、打开初始化参数文件;
方法二、查询动态性能视图。v$parameter显示当前用户会话中生效的初始化参数信息(443h),v$spparameter显示spfile中的初始化参数信息(447h),v$system_parameter显示实例中当前生效的初始化参数信息(443h);
方法三、SQL*Plus命令show parameter

  • init.ora
    初始化参数文件、实例属性文件

  • orcl.bat
    DBCA的总控程序。它创建所需目录、设置环境变量SID PATH、创建实例服务OracleServiceSID(实例服务只是实例的一个引导程序),编辑实例服务启动项(OS:实力服务srvcstart、实例startmode),并启动SQL*Plus,执行orcl.sql脚本文件
  • orcl.sql
    接受用户输入的用户口令,执行orapwd.exe创建口令文件,并依次执行下面各个 SQL 脚本文件。它相当于 SQL 脚本文 件总控程序
  • CreateDB.sql
    启动实例,创建数据库
    命令:spool E:\oracle\admin\orcl\scripts\CreateDB.log append把输入命令和输出写过写入日志
    命令:spool off刷新缓存区,将以上写入日志
  • CreateDBFiles.sql
    为数据库创建 USERS 表空间,并将它设置为数据库的默认表空间
  • CreateDBCatalog.sql
    创建数据字典
  • lockAccount.sql
    锁定 sys 和 system 之外的其他所有数据库帐户
  • postDBCreation.sql
    重新编译数据库中的所有无效对象,创建 spfile,并重新启动数据库

控制文件

  • v$database:显示数据库的相关信息;
  • v$tablespace:显示数据库的表空间信息;
  • v$datafilev$tempfile:显示数据库的数据文件和临时文件信息;
  • v$log:显示数据库的重做日志文件组信息;
  • v$logfile:显示数据库的重做日志文件信息;
  • v$archived_log:显示归档日志文件信息。

查看控制文件参数设置:
方法一、用SQL*Plus命令显示control_files初始化参数:show parameter control_files;
方法二、检索动态性能视图v$controlfile:select name from v$controlfile;;
方法三、检索动态性能视图v$parameter(443h)。

相关操作

  1. 查看pfile/spfile位置:show parameter spfile
  2. 查看控制文件位置:同上
    E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
    E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
  3. 从spfile创建pfile
    CREATE PFILE [= 'pfile_name'] FROM SPFILE [= 'spfile_name'];
  4. 从pfile创建spfile
    CREATE SPFILE [= 'spfile_name'] FROM PFILE [= 'pfile_name'];
  5. 修改spfile
    alter system set parameter_name = parameter_value
  6. 修改pfile
    用文件编辑器,文件名一般为init.ora
  7. 修改open_cursors
    alter system set open_cursors=30000 scope=spfile;
    alter system set open_cursors=1000 scope=memory
  8. 备份
    alter database backup controlfile tp 'RUL\control.bkp'
    alter database backup controlfile to trace;
    查看备份位置:alert_orcl.log

注册表相关

注册表相关配置:计算机\HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraDB19Home:
ORA_ORCL_AUTOSTART 服务器启动时候是否启动实例,建议FALSE
ORA_ORCL_SHUTDOWN 服务器关闭时候是否关闭实例
ORA_ORCL_SHUTDOWNTYPE 服务器关闭类型
ORA_ORCL_SHUTDOWN_TIMEOUT 90秒后关闭异常放回异常信息
ORACLE_SID 指定默认连接实例SID
ORACLE_HOME Home位置


数据库启动与关闭

启动数据库

打开数据库可使用的工具包括SQL*Plus、Recovery Manager、Oracle Enterprise Manager等。在SQL*Plus下,使用SQL*Plus的startup命令可以把数据库从close状态启动到其他三种状态,如:
SQL> startup nomount
SQL> startup mount
SQL> startup open

而当实例已启动之后,就不能再使用STARTUP命令改变数据库的状态,而只能使用SQL语句ALTER DATABASE把数据库改变到下一个状态。 如:
SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE OPEN;

关闭数据库

在SQL*Plus中使用SHUTDOWN命令关闭数据库,该命令有以下四个选项,它们分别对应于四种关闭方式 :

  • NORMAL:正常关闭 ;
  1. 禁止新建连接;
  2. 等待当前所有已连接用户主动断开之后再关闭数据库。如果当前用户不主动断开连接,他们仍可继续执行数据库操作,但这会导致shutdown命令因超时而执行失败。
  • TRANSACTIONAL:事务关闭 ;
  1. 禁止新建连接;
  2. 禁止已连接用户启动新的事务,但会等待已启动事务执行完成,然后断开用户连接,关闭数据库。所以,只要所有连接用户结束他们的当前事务,该命令即可成功关闭数据库。
  • IMMEDIATE:立即关闭 ;
    执行SHUTDOWN IMMEDIATE命令后,Oracle实例将:
  1. 禁止新建连接;
  2. 禁止已连接用户启动新的事务,当前未完成的事务被立即回滚,然后断开用户连接,关闭数据库。所以,该命令关闭数据库的速度比前两种更快 。

以上三种关闭方式中,用户事务都能完成(要么提交,要么回滚),在关闭数据库之前还会执行检查点,所以可以确保所关闭的数据库处于一致状态,下次启动时不需要做实例恢复。因此,这三种关闭方式也被称作一致性关闭。

  • ABORT:异常关闭 ;
    执行SHUTDOWN ABORT命令后,将立即中止数据库实例的运行来关闭数据库,所以它将:
  1. 禁止新建连接;
  2. 禁止已连接用户启动新的事务,当前已连接用户正在执行的SQL语句被立即中止,不回滚也不提交用户尚未提交的事务,也不执行检查点。

以这种方式关闭数据库就像系统停电一样,立即中止Oracle数据库的运行。因此,这种方式关闭的数据库处于不一致状态,下次启动时需要做实例恢复


Oracle监听

作用

监听的作用就是给客户端电脑和中心数据库电脑建立数据的连接。一旦建立连接就不对两者造成任何影响,连接上的客户端上的用户可以做任何操作和查询,哪怕监听已经关闭或者被破坏。

注意:在数据库本机电脑连接数据库不需要用监听。哪怕没有监听都可以建立链接。

  1. 举例说明假设有一台电脑A中安装了一个数据库,其中只有一个实例peng,然后有一台客户端电脑B相连接到A中的数据库实例peng,那A中就必须对监听进行设置,否则B无法连接到A中的数据库实例peng。
  2. 如果已经配置好了监听,那马上B中的数据库客户端就可以通过监听建立到A的连接,而且没有断开连接。此时A中的监听已经完成使命,如果此时将A中的监听服务停止或者破坏或者删除,B还是可以继续访问A的数据库,不会受到任何影响。但假如B连接上后又与A的数据库断开了连接。那么B再想连接上A的话,就得A上的监听启动并配置好。

功能

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

操作

host lsnrctl//启动监听
help//查看帮助
status//查看当前监听器的状态,对应操作日志信息和服务注册信息等内容。

监听分析

监听文件位置 E:\oracle19c\network\admin底下的listener.ora ,从中可得:
1.监听信息

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.jmu)
      (ORACLE_HOME = E:\oracle19c)
      (SID_NAME = ORCL)
    )
  )

监听命名方法文件位置 E:\oracle19c\network\admin底下的sqlnet.ora,从中可得

SQLNET.AUTHENTICATION_SERVICES= (NTS)//是否支持Windows操作系统认证
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT(简易连接))//选用的连接/命名方法

网络服务名文件位置 E:\oracle19c\network\admin底下的tnsnames.ora
2.监听日志位置 E:\oracle19c\log

监听的静态/动态注册

默认监听必须为:listener(名称) TCP/IP(协议) 1521(端口)
默认监听listener为动态监听,其他为静态监听。只能存在一个动态监听。

实例静态注册转动态注册

  1. 复制listener1监听地址 (ADDRESS=(PROTOCOL=TCP)(HOST=CTX)(PORT=1530))
  2. 初始化参数 local_listener(本机)/remote_listener
    alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=CTX)(PORT=1530))';
  3. (Oracle11前)步骤2.失效则:alter system register;(强制注册)
  4. 只能动态注册一个地址

总结

Oracle 监听器 Listener 是一个重要的数据库服务器组件,在整个 Oracle 体系结构中,扮演着重要的作用。它负责管理 Oracle 数据库和客户端之间的通讯,它在一个特定的网卡端口(默认是TCP 1521端口)上监听连接请求,并将连接转发给数据库


简易连接

建立连接—简易连接(前提:开启共享服务器模式,否则只能专用访问)
注:可在Database Configuration Assistant中的配置现有数据库修改服务器模式

conn username/pwd@[//]host[:port][/[service_name][:server_type][/instance_name]]
conn username/pwd@网络服务名(不能加server_type,因为已设定模式)
共享:conn system/oracle@//CTX:1530/orcl.jmu:shared
专用:conn system/oracle@//CTX:1530/orcl.jmu:dedicated

lsnrctl: service 查看连接情况


常用指令

select * from tab;//查看所有表

desc xxx;//查看表结构
desc dba_data_files;//查看物理表 结构
desc dba_tablespaces;//查看逻辑表 结构
desc dba_segments;//查看段结构
desc dba_extents;//查看块结构

host cls;//清屏

SQL> select * form emp;
select * form emp
         *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字
方案一:
SQL> c /form(错误字段)/from(修正字段)    //修改语句错误
  1* select * from emp                 //预览
SQL> /                                 //确定修改
  (结果)
方案二:
SQL> ed                                //弹出txt修改
已写入 file afiedt.buf                  //修改后保存,关闭
  1* select * from emp
SQL> /
  (结果)

a order by xxx desc;// a 追加上条命令

数据字典和性能视图的使用

查看数据字典表结构:desc dict;
查看DBA相关的数据字典:selecr * from dict where table_name like'DBA%';
查看表空间相关的动态性能视图(V开头):select * from dict where table_name like 'V$TABLESPACE%';

数据字典查看逻辑存储结构:
desc dba_tablespaces//表空间
desc dba_segments//段
desc dba_extents//区

数据字典查看表空间的表名称,块大小:select TABLESPACE_NAME,block_size from dba_tablespaces;
动态视图查看表空间的表名称:select NAME from v$tablespace;
查看SCOTT用户的(四张)表:select table_name from dab_tables where owner = 'SCOTT';
数据字典查看段的所有者(用户),段类型,段名称:select owner,segment_type, segment_name from dba_segments where owner='SCOTT';
数据字典查看SCOTT用户DEPT段下的区编号:select EXTENT_ID from dba_extents where owner='SCOTT' and SEGMENT_NAME='DEPT';

数据字典查看物理存储结构的数据文件:
desc dba_data_files//永久数据文件
desc dba_temp_files//临时表空间数据文件
select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files union select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_temp_files;//所有数据文件
动态视图查看数据文件的路径:select name from v$datafile;
动态视图查看物理存储结构的控制文件(无数据字典):
desc v$controlfile
查看控制文件的路径:select name from v$controlfile;
动态视图查看物理存储结构的重做日志文件:
desc v$log//日志文件
desc v$logfile//成员信息
查看重做日志文件组信息:select group#,member from v$logfile;
同上:select group#,members from v$log;

动态性能视图查询Oracle实例当前状态:select status from v$instance;
查看用户会话情况:desc v$session

查询当前用户对象:show user
SYS查询数据库对象SCOTT类型,名称(总对象有6w多条):select object_type,object_name from dba_objects where owner='SCOTT';
SCOTT查询该用户所具有的数据库对象:select object_type,object_name from user_objects;


重做日志管理

数据库:1 缓存;2 undo data(undo表空间);3 redo log ->联机重做日志

用户修改时产生重做日志项-->服务器进程复制到日志缓冲区-->LGWR写入联机重做日志文件-->日志切换后,ARCn把填充过的连接重做日志文件归档到归档日志文件

相关命令

archive log list(查看归档模式,三组9、10、11)
alter system switch logfile;(强制日志文件切换到下一组12,三组10、11、12,12覆盖/归档9)
desc v$log
select GROUP#,SEQUENCE#,MEMBERS,STATUS from v$log;(查看日志序列号、数量、状态)
select dbid,name,log_mode from v$database;(查看当前数据库实例的id、名称、日志归档模式)
show parameter arc(归档进程的数量)

ALTER SYSTEM ARCHIVE LOG NEXT;(手动归档,非正在填写的最早未归档文件)
ALTER SYSTEM ARCHIVE LOG ALL;(手动归档,非正在填写的所有未归档文件)

添加重做日志文件组

alter database add logfile group 4 
('E:\redo0401.log','E:\redo0402.log') size 10m reuse;

select GROUP#,SEQUENCE#,MEMBERS,STATUS from v$log;
ALTER DATABASE ADD LOGFILE MEMBER 'E:\redo0302.log' to group 3(为第3组添加重做日志成员)
select group#,member,status from v$logfile

移动/重命名重做日志成员

shutdown immediate
startup mount
host move E:\REDO0403.log D:\REDO0403.log;(操作系统层面移动文件)
alter database rename file 'E:\REDO0403.log' to 'D:\REDO0403.log';(修改物理存储结构)
alter database open;
select group#,menbers,status from v$logfile
select group#,menbers,status from v$log

select group#,status from v$log;(查看下状态)
ALTER DATABASE DROP LOGFILE MEMBER 'E:\redo0103.log';(删除重做日志文件成员,无法删除最后一个/CURRENT的成员)

alter system set log_archive_dest_1 = 'LOCATION=e:\oracle\archive';(设置归档位置)
show parameter LOG_ARCHIVE_MAX_PROCESSES(查看归档服务器进程数)

重做redo实例数据恢复的过程查看

create table t (c int);

 begin
   for i in 1..100000 loop
     insert into t values(i);
   end loop;
 end;
 /

shutdown abort

startup mount

select  ((UNDOBLOCKSDONE *100) / UNDOBLOCKSTOTAL) as "done(%)",USN  from v$fast_start_transactions

alter database open;

归档状态转换

archive log list(sqlplus查看归档模式)
select name,log_mode from v$database;(动态性能视图查看归档模式)
---
(设置初始化参数)/show parameter log_archive(查看初始化参数)
shutdown immediate
startup mount
alter database noarchivelog(改成非归档模式)/alter database archivelog;(改成归档模式)
archive log list
---
shutdown immediate
startup mount
alter database archivelog manual(改成手动归档)
alter database open;
archive log list/select log_mode from v$database;(查看修改)
---
alter system switch logfile;(强制切换归档状态)
select group#,menbers,status from v$log
alter system archive log next;(手动归档增加,不执行会导致switch挂起)

表空间与数据文件

从逻辑上来讲,Oracle把数据存放在表空间里,而从物理上来讲,这些数据实际存放在数据文件内。每个Oracle数据库由一个或多个表空间组成,但每个表空间只能属于一个数据库。

分类

  • 永久表空间:存储数据字典和用户数据,如SYSTEM、SYSAUX、USERS表空间均属于永久表空间;
  • 临时表空间:用于存储会话的中间排序结果、临时表和索引等。创建orcl数据库时创建的TEMP表空间就是临时表空间;
  • 还原表空间:这是一种特殊类型的表空间,其中存储的数据专门用于回滚或还原操作,为数据库提供读一致性支持。创建orcl数据库时创建的UNDOTBS1表空间就是一个还原表空间。

操作

1.查询Oracle数据库内现有表空间信息,以及各个表空间使用的数据文件信息。

desc dba_tablespaces(只有表空间信息)
desc dba_data_files(表空间+数据文件信息)
查询永久+临时表空间

select TABLESPACE_NAME,FILE_ID,FILE_NAME,RELATIVE_FNO from dba_data_files
union
select TABLESPACE_NAME,FILE_ID,FILE_NAME,RELATIVE_FNO from dba_temp_files;
TABLESPACE    FILE_ID FILE_NAME                                          RELATIVE_FNO
---------- ---------- -------------------------------------------------- ------------
SYSAUX              2 E:\ORACLE\APP\CTX\ORADATA\ORCL\SYSAUX01.DBF                   2
SYSTEM              1 E:\ORACLE\APP\CTX\ORADATA\ORCL\SYSTEM01.DBF                   1
TEMP                1 E:\ORACLE\APP\CTX\ORADATA\ORCL\TEMP01.DBF                     1
UNDOTBS1            3 E:\ORACLE\APP\CTX\ORADATA\ORCL\UNDOTBS01.DBF                  3
USERS               4 E:\ORACLE\APP\CTX\ORADATA\ORCL\USERS01.DBF                    4

2.创建一个非标准数据块永久表空间,它由两个数据文件组成,数据文件可自动扩展,区采用本地管理方式,该表空间的其它属性自定。

show parameter cache_size(查看非标准块缓存)
alter system set db_16k_cache_size = 16M;(为非标准块设置缓存)
创建非标准块的表空间

create tablespace ts1
datafile 'E:\Oracle\app\CTX\oradata\ORCL\ts01.dbf' size 5M
autoextend on next 5M,
'E:\Oracle\app\CTX\oradata\ORCL\ts02.dbf' size 5M
autoextend on next 5M
extent management local AUTOALLOCATE;

select TABLESPACE_NAME,CONTENTS,STATUS,LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces;(查看表空间名称、(三种)类型、状态(联/脱机)、LOG(永久打开,临时关闭)、区管理方式、分配类型)

3.创建一个临时表空间,并把它指定为scott用户的默认临时表空间。

create TEMPORARY tablespace temp2
tempfile 'E:\Oracle\app\CTX\oradata\ORCL\temp2.dbf' size 5M
autoextend on next 5M
extent management local uniform size 1M;

alter user scott TEMPORARY tablespace temp2;

4.查询scott默认表空间位置,并建立数据库对象查看位置

select username ,DEFAULT_TABLESPACE from dba_users where username='SCOTT';(默认在USERS上)
create table t1(a char) tablespace ts1;(默认在USERS上,手动指定为ts1)

5.创建一个还原表空间,并把它指定为数据库 的当前还原表空间。

select tablespace_name,file_id,file_name from dba_data_files;//查询表空间的名称,ID和文件位置
创建UNDO表空间

create undo tablespace UNDOTBS2
datafile 'E:\ORACLE\APP\CTX\ORADATA\BOOK\UNDOTBS02.DBF'
size 50M reuse
Autoextend on next 10M maxsize unlimited
Retention guarantee;

show parameter undo_tablespace//查看当前使用的表空间
alter system set undo_tablespace = UNDOTBS2;//切换使用的表空间
给UNDOTBS2增加数据文件(源数据文件只能和表空间一块删除)

alter tablespace UNDOTBS2
add datafile 'E:\ORACLE\CTX\ORADATA\ORCL\UNDOTBS0201.DBF' size 5M;

alter database datafile 6 resize 10//根据文件ID更改数据文件大小
alter tablespace UNDOTBS2 drop datafile 6;//根据编号删除表空间数据文件

6.再次查询数据库内的各个表空间及其对应的数据文件信息。

select tablespace_name,file_id,file_name from dba_data_files;//查询表空间的名称,ID和文件位置

7.创建表并规定分区(分区表),脱机并还原,并且删除表空间

create table student(
startingYear NUMBER(4,0) not null;
name varchar2(10)
)
partition by range(staringYear)(
partition part01 values less than (2018) tablespace ts1,
partition part02 values less than (2019) tablespace ts2,
)

insert into student values(2017,'zs');
insert into student values(2018,'ls');
insert into student values(2019,'ww');

select * from student;
select * from student partition(part01);
select * from student partition(part02);

alter tablespace ts1 read only;//(修改表解构为只读)

建议在归档模式下进行脱机

alter tablespace ts1 offline normal;
select TABLESPACE_NAME,CONTENTS,STATUS,LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces;
alter tablespace ts1 read write;//无法运行,已脱机
alter tablespace ts1 online;
alter tablespace ts1 read write;

alter tablespace ts1 offline immediate;
alter tablespace ts1 online;//失败,需要先介质恢复:数据文件5(ts01)
recover datafile 5;
alter tablespace ts1 online;//失败,需要先介质恢复:数据文件6(ts02)
recover datafile 6;
alter tablespace ts1 online;
select TABLESPACE_NAME,CONTENTS,STATUS,LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces;
drop table student;
drop tablespace ts1 including contents and datafiles;
drop tablespace ts2 including contents and datafiles;

扩展:LogMiner工具

使用LogMiner工具对日志文件进行分析可以得到以下主要信息:

  • 对数据库执行过的数据操作,例如,INSERT、UPDATE、DELETE和DDL语句;
  • 提交事务时确认的SCN;
  • 数据改变时的SCN;
  • 数据修改操作所隶属的事务;
  • 执行DDL或DML语句的用户;
  • 被修改的模式对象的名称;
  • 重现所执行的SQL语句

开启补充日志
1.查看补充日志功能状态,默认关闭
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
2.开启补充日志功能;要使LogMiner能够返回所有日志信息,数据库必须启用补充日志功能(否则可能只能分析出DDL语句对应的日志,而看不到DML语句日志)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
建立LogMiner字典文件
LogMiner在返回重做数据时需要用字典把对象ID翻译为对象名称。可以采用以下三种方法提供字典:

  • 【使用联机目录】可以访问创建日志的源数据库,并且没有删除表和改变表中列定义时,建议采用这种方法;
  • 【将字典提取到重做日志文件】当无法访问创建日志的源数据库,或者已删除表或改变表中列的定义时,建议采用这种方法;
  • 【将字典提取到文本格式文件】这种方法无法确保事务的一致性,为保持与前期版本兼容而保留。

具体查看文档:logminer.doc


用户管理

1.密码一次有效性(zhangsan/oracle连接后需要改密)
create user zhangsan identified by oracle password expire;
grant create session to zhangsan;//创建会话授权

C:\Users\CTX>sqlplus /nolog
SQL> conn zhangsan/oracle
ERROR:
ORA-28001: 口令已经失效
更改 zhangsan 的口令
新口令:
重新键入新口令:
口令已更改
已连接。
SQL>

2.创建操作系统认证用户
(1)在Oracle数据库内创建外部用户。外部用户与操作系统当前登录用户同名,或在其上加一定的前缀,这由初始化参数OS_AUTHENT_PREFIX决定,默认是加前缀“OPS$”;这里设置为空。
alter system set os_authent_prefix = '' scope = spfile;
startup force//重启
show parameter os_//查看是否修改成功
(2)在计算机\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB19Home1底下增加OSAUTH_PREFIX_DOMAIN注册项值为FALSE
(3)确认Oracle NET配置文件E:\Oracle\db_home\network\admin\SQLNET.ORA中的以下参数值设置为SQLNET.AUTHENTICATION_SERVICES= (NTS),即允许采用操作系统认证(NONE为禁止)。默认为NTS。
(4)创建用户(用户名为当前计算机用户名)
create user CTX identified externally;
grant create session to CTX;//创建会话授权
(5)连接测试
sqlplus /nolog
conn /
(6)sys口令授权(sysdba/sysoper/sysasm)
grant sysdba to zhangsan;

3.数据库管理员身份验证
(1)select * from v$pwfile_users;可查询(SYSDBA、SYSOPER、SYSASM、SYSBACKUP、SYSDG、 SYSKM)用户具有口令文件认证权限
注:默认SQLNET.ORA中开启操作系统验证(NTS)下,conn scott/tiger as sysdba会被简化为conn / as sysdba
(2)操作系统用户组授权(ora_dba/ora_oper/ora_asm)
默认在创建时自动添加到ora_dba用户组

4.删除用户
(1)具有模式对象不能直接删除(CASCADE选项说明在删除用户时先自动删除该用户模式下的所有对象,如果存在该用户的模式对象而又未指定该选项,将导致语句执行失败。)
drop user 用户名 cascade
(2)已连接用户不能删除,但是管理员可以强行(ALTER SYSTEM KILL SESSION)将普通用户进程杀死并删除用户

5.授权
grant create table to zhangsan;//在自己拥有得模式下建立表
grant create any table tozhangsan;//在任何模式下建立表,即允许它在任何表空间里创建表,包括 system表空间;
(GRANT语句内的WITH GRANT OPTION子句要求把对象权限的执行权力授予用户的同时,还授予对象权限的管理权。但在向角色授权时,不能使用WITH GRANT OPTION子句。
如果使用WITH GRANT OPTION子句向用户A授予了某项对象权限,A又将该项权限授予了用户B,当收回用户A的这项对象权限时,用户B的该对象权限也被一并收回。)
grant create table to zhangsan with admin option;授予创建表并管理表权限的权限
revoke select on scott.dept from zhangsan//回收用户权限
select grantor,grantee,table_name,privilege,granttable from dba_tab_privs where grantee='zhangsan'//查看用户权限

6.角色管理
(1)创建角色:
CREATE ROLE sr_admin IDENTIFIED BY admin;
CREATE ROLE sr_query IDENTIFIED BY query;
(2)为角色授权:
GRANT CREATE SESSION TO sr_admin;
GRANT INSERT,UPDATE,DELETE ON scott.dept TO sr_admin;
GRANT SELECT ON scott.dept TO sr_query;
GRANT sr_query TO sr_admin;//版本问题19c中失效
GRANT CONNECT TO sr_query;
(3)管理用户角色:
GRANT sr_query TO zhang( WITH ADMIN OPTION);//向用户授予角色
撤销用户角色对已经启用该角色的用户会话没有影响,所以这些用户会话仍可使用被撤销角色的权限进行操作,但该用户之后不能再启用该角色。
REVOKE sr_query FROM zhang;//撤销用户角色
角色授予用户之后,其默认为禁用状态,这时用户连接还不会获得角色所具有的权限。用户要想获得授权给角色的权限,需要调用SET ROLE语句启用角色。该语句同时还可以禁用角色.
SET ROLE sr_query IDENTIFIED BY query;//给sr_query角色query权限
ALTER USER zhang DEFAULT ROLE sr_admin;//设置用户默认角色
SELECT granted_role,default_role FROM user_role_privs;//查看发现default为no,需要安全验证导致没有授权,默认角色不允许口令认证
set role sr_query//手动启用角色
set role sr_admin identified by admin;
SET ROLE NONE;//禁用隐含激活的默认角色
(4)查询角色信息
SELECT * FROM role_role_privs WHERE ROLE='SR_ADMIN';//列出授予其他角色的角色信息
SELECT * FROM role_sys_privs WHERE ROLE='SR_ADMIN';//列出授予角色的系统权限信息
SELECT * FROM role_tab_privs WHERE ROLE='SR_ADMIN';//列出授予角色的表权限信息
SELECT * FROM dba_role_privs WHERE grantee='ZHANG';//列出数据库内授予所有用户和角色的角色
(5)修改角色,只影响之后所建立的用户会话。
ALTER ROLE sr_query NOT IDENTIFIED;
(6)删除角色
drop role sr_query;

Last modification:May 19th, 2020 at 05:24 pm
喵ฅฅ