Loading... ## 存放数据的仓库 * 内存： 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数据库内存储空间的最小分配单位）、**段**（占用存储空间的数据库对象,段分为表段、索引段、回滚段和临时段）、**表空间**（用于存储数据库中的所有数据）构成。  ### 组成结构：   实例分为两阶段（PGA+SGA），前者专门供每个会话使用，后者由所有Oracle进程共享。 * PGA（program global area，程序全局区）：一个PGA对应于一个客户端，主要为了某个用户进程所服务的，PGA主要包含**排序区**、会话区、堆栈区和游标区四个部分的内容，他们各司其职，完成用户进程与数据库之间的会话。当用户进程连接到数据库并创建一个对应的会话时，Oracle服务进程会为这个用户专门设置一个PGA区，用来存储这个用户会话的相关内容。当这个用户会话终止时，数据库系统会自动释放这个PAG区所占用的内存。这个PGA区对于数据库的性能有比较大的影响，特别是对于排序操作的性能。 * SGA（system global area, 系统全局区）：主要由三部分构成：(SQL)共享池、数据缓冲区、日志缓冲区。SGA对系统内的所有进程共享，主要为不同用户之间的进程与服务进程提供一个交流的平台。另外，各种数据库的操作主要就是在这个SGA区内完成。 **两阶段提交：** * pga->sga * 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** 启动实例，创建数据库<br>命令：`spool E:\oracle\admin\orcl\scripts\CreateDB.log append`把输入命令和输出写过写入日志 <br>命令：`spool off`刷新缓存区，将以上写入日志 * **CreateDBFiles.sql** 为数据库创建 USERS 表空间，并将它设置为数据库的默认表空间 * **CreateDBCatalog.sql** 创建数据字典 * **lockAccount.sql** 锁定 sys 和 system 之外的其他所有数据库帐户 * **postDBCreation.sql** 重新编译数据库中的所有无效对象，创建 spfile，并重新启动数据库 ### 控制文件 * `v$database`：显示数据库的相关信息； * `v$tablespace`：显示数据库的表空间信息; * `v$datafile`、`v$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. 查看控制文件位置：同上<br>`E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL`<br>`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;`<br>`alter system set open_cursors=1000 scope=memory` 8. 备份 `alter database backup controlfile tp 'RUL\control.bkp'`<br>`alter database backup controlfile to trace;`<br>查看备份位置:`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 环境中的监听器之间进行沟通通信。 ### 操作 ```Oracle host lsnrctl//启动监听 help//查看帮助 status//查看当前监听器的状态，对应操作日志信息和服务注册信息等内容。 ``` ### 监听分析 **监听文件**位置 **E:\\oracle19c\\network\\admin底下的listener.ora** ,从中可得: 1.监听信息 ```Oracle SID_LIST_LISTENER1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl.jmu) (ORACLE_HOME = E:\oracle19c) (SID_NAME = ORCL) ) ) ``` **监听命名方法文件**位置 **E:\\oracle19c\\network\\admin底下的sqlnet.ora**,从中可得 ```Oracle 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 查看连接情况 --- ## 常用指令 ```SQL 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;`(手动归档,非正在填写的所有未归档文件) 添加重做日志文件组 ```Oracle 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` 移动/重命名重做日志成员 ```Oracle 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实例数据恢复的过程查看 ```Oracle 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; ``` 归档状态转换 ```Oracle 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`(表空间+数据文件信息) 查询永久+临时表空间 ```SQL 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;`(为非标准块设置缓存) 创建非标准块的表空间 ```SQL 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用户的默认临时表空间。 ```SQL 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表空间 ```SQL 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增加数据文件(源数据文件只能和表空间一块删除) ```SQL 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.创建表并规定分区(分区表)，脱机并还原，并且删除表空间 ```SQL 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;//(修改表解构为只读) ``` 建议在归档模式下进行脱机 ```SQL 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; ``` ```SQL 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] --- ## 用户管理 **1.密码一次有效性(zhangsan/oracle连接后需要改密)** `create user zhangsan identified by oracle password expire;` `grant create session to zhangsan;`//创建会话授权 ```SQL 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;` [1]: http://www.tangsong.fun/usr/uploads/2020/04/287277082.doc Last modification：August 11, 2022 © Allow specification reprint Like 0 喵ฅฅ