CentOS7.6上Oracle 11g表空间管理 作者: sysit 分类: d 发表于 2019-07-09 84人围观 接上文，我们在CentOS7.6上完成了数据库软件的安装，完成了网络监听和数据库实例的创建，接下来我们需要为Oracle创建表空间，创建用户，并为用户授权。 # 1. Oracle的一些概念 * ORACLE服务器：由实例和数据库组成。 * 数据库：Oracle数据库是数据的物理存储。就是一堆文件（包括数据文件ORA或者DBF、控制文件、联机日志、参数文件等）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。 * 实例： 是访问数据库文件的一个手段。 一个Oracle实例（Oracle Instance）由一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有多个实例来访问，但一个实例只能访问一个数据库。 * 连接：互相访问的进程。如：客户端和服务器之间的TCP/IP访问进程，服务器和数据库之间的ODBC进程。 * 会话：客户端和服务器通过进程互相确认身份、传输通信的过程。 * 用户：用户是在实例下建立的。不同实例可以建相同名字的用户。 * 表空间：表空间是一个用来管理数据存储逻辑概念，表空间只是和数据文件（ORA或者DBF文件）发生关系，数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。 * 数据文件（dbf、ora）：数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真 正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。 注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！ * 关系示意图：  来源：https://www.jianshu.com/p/4dc47beb4d8a # 2. 理解 Oracle数据库可以创建多个实例，实例下有用户和表空间，用户经授权访问表空间，在表空间中创建表，表随机存储到一个或多个数据文件中。如下图所示：  # 3. 表空间 Oracle的表空间属于Oracle中的存储结构，是一种用于存储数据库对象(如：数据文件)的逻辑空间，是Oracle中信息存储的最大逻辑单元，其下还包含有段、区、数据块等逻辑数据类型。表空间是在数据库中开辟的一个空间，用于存放数据库的对象，一个数据库可以由多个表空间组成。可以通过表空间来实现对Oracle的调优。（Oracle数据库独特的高级应用） * 表空间的分类 * 永久表空间：存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引。 * 临时表空间：存储数据库的中间执行过程，如：保存order by数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间，如果还需要别的临时表空间时，可以自己创建。 * UNDO表空间：保存数据修改前的副本。存储事务所修改的旧址，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作。 ## 3.1 查看表空间 * 查看system用户的默认表空间和临时表空间 ``` [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 9 14:03:39 2019 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 3273641984 bytes Fixed Size 2217792 bytes Variable Size 1795164352 bytes Database Buffers 1459617792 bytes Redo Buffers 16642048 bytes Database mounted. Database opened. SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEMP ``` * 查看默认的永久表空间和TEMP表空间 用户在登录后创建数据库对象时，如果没有指定表空间，那么这些数据就会存储到默认表空间。 注意：如果创建用户时，不指定其永久表空间，则会使用默认的表空间。 ``` SQL> select property_name,property_value from database_properties; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------------------------- DICT.BASE 2 DEFAULT_TEMP_TABLESPACE TEMP DEFAULT_PERMANENT_TABLESPACE USERS DEFAULT_EDITION ORA$BASE Flashback Timestamp TimeZone GMT TDE_MASTER_KEY_ID DST_UPGRADE_STATE NONE DST_PRIMARY_TT_VERSION 11 DST_SECONDARY_TT_VERSION 0 DEFAULT_TBS_TYPE SMALLFILE NLS_LANGUAGE AMERICAN PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------------------------- NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------------------------- NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.2.0.1.0 GLOBAL_DB_NAME ORCL EXPORT_VIEWS_VERSION 8 WORKLOAD_CAPTURE_MODE PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------------------------- WORKLOAD_REPLAY_MODE NO_USERID_VERIFIER_SALT 8BF56230FC49D6ECFE53EB96A5922DD5 DBTIMEZONE 00:00 36 rows selected. ``` DEFAULT_PERMANENT_TABLESPACE 默认永久表空间 DEFAULT_TEMP_TABLESPACE 默认TEMP表空间 DEFAULT_TBS_TYPE 默认表空间类型 * 查看数据库拥有的表空间 ``` #1.包含数据库中所有表空间的描述信息 SELECT * FROM DBA_TABLESPACES; #2.包含当前用户的表空间的描叙信息 SELECT * FROM USER_TABLESPACES; #3.包含从控制文件中获取的表空间名称和编号信息 SELECT * FROM V$TABLESPACE; ``` * 查看表空间中的数据文件 ``` #1.包含数据文件以及所属的表空间的描述信息(永久表空间/UNDO表空间) SELECT * FROM DBA_DATA_FILES; #2.包含临时数据文件以及所属的表空间的描述信息 SELECT * FROM DBA_TEMP_FILES; SELECT * FROM V$TEMPFILE; #3.包含从控制文件中获取的数据文件的基本信息，包括它所属的表空间名称、编号等 SELECT * FROM V$DATAFILE; #4.包含所有临时数据文件的基本信息 SELECT * FROM V$TEMPFILE; ``` * 查看用户拥有的表空间 系统管理员 ``` SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS ``` > SYSTEM 系统表空间，是永久系统表空间，用于存储SYS用户的表、视图、存储过程对象。 > UNDOTBS1 存储撤销信息的undo表空间 > SYSAUX 作为EXAMPLE的辅助表空间 > TEMP 临时表空间，用户存储SQL语句处理的表示索引信息 > USERS 永久表空间，存储数据库用户创建的数据库对象 普通用户 ``` SQL> conn scott/tiger Connected. SQL> select tablespace_name from user_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS ``` ## 3.2 创建表空间的语法 ``` CREATE [UNDO] TABLESPACE tablespace_name [DATAFILE datefile_spec1 [,datefile_spec2] ...... [ { MININUM EXTENT integer [k|m] | BLOCKSIZE integer [k] |logging clause |FORCE LOGGING |DEFAULT {data_segment_compression} storage_clause |[online|offline] |[PERMANENT|TEMPORARY] |extent_manager_clause |segment_manager_clause}] ``` * undo 说明系统将创建一个回滚表空间。只有建立了undo表空间，系统就会自动管理回滚段的分配，回收的工作。当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。 * tablespace 指出表空间的名称。 * datafile datefile_spec1 指出表空间包含什么空间文件。 datefile_spec1 是形如 ['filename'] [SIZE integer [K |M ]] [REUSE] [autoextend_clause]。 其中filename是数据文件的全路径名。 size是文件的大小。 REUSE表示文件是否被重用。 [autoextend_clause]是形如： AUTOEXTEND {OFF |ON [NEXT integer [K |M ] ] [maxsize_clause] }。 AUTOEXTEND表明是否自动扩展。 OFF |ON 表示自动扩展是否被关闭。 NEXT 表示数据文件满了以后，扩展的大小。 maxsize_clause表示数据文件的最大大小,形如MAXSIZE {UNLIMITED | integer [K |M ] }。 UNLIMITED 表示无限的表空间。 integer是数据文件的最大大小。 > 举个例子： DATAFILE 'D:\oracle\oradata\MAGEDATA01.dbf' SIZE 2000M reuse autoextend on next 1024M maxsize 10G * MININUM EXTENT integer [k|m] 指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。 * BLOCKSIZE integer [k] 这个参数可以设定一个不标准的块的大小。如果要设置这个参数，必须设置db_block_size，至少一个db_nk_block_size，并且声明的integer的值必须等于db_nk_block_size。 注意：在临时表空间不能设置这个参数。 * logging clause 这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），包括表，索引，分区，物化视图，物化视图上的索引，分区。 * FORCE LOGGING 使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。这个参数高于对象的nologging选项。 注意：设置这个参数数据库必须open并且处于读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。 * DEFAULT storage_clause 声明缺省的存储子句。 * online|offline 改变表空间的状态。online使表空间创建后立即有效，这是缺省值。offline使表空间创建后无效。这个值，可以从dba_tablespace中得到。 * PERMANENT|TEMPORARY 指出表空间的属性，是永久表空间还是临时表空间。永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。这个参数 生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。如果要创建本地管理表空间，必须使用create temporary tablespace 注意，声明了这个参数后，不能声明block size。 * extent_management_clause 这个子句说明了表空间如何管理范围。一旦你声明了这个子句，只能通过移植的方式改变这些参数。 ``` 如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。 autoallocate说明表空间自动分配范围，用户不能指定范围的大小。只有9.0以上的版本具有这个功能。 uniform说明表空间的范围的固定大小，缺省是1m。 不能将本地管理的数据库的system表空间设置成字典管理。 ``` 推荐使用本地管理表空间。`EXTENT MANAGEMENT LOCAL` 如果没有设置extent_management_clause，oracle会给他设置一个默认值。如果初始化参数compatible小于9.0.0,那么系统创建字典管理表空间。如果大于9.0.0,那么按照如下设置： 如果没有指定default storage_clause,oracle创建一个自动分配的本地管理表空间。 否则，如果指定了mininum extent,那么oracle判断mininum extent 、initial、next是否相等,以及pctincrease是否=0.如果满足以上的条件，oracle创建一个本地管理表空间，extent size是initial.如果不满足以上条件，那么oracle将创建一个自动分配的本地管理表空间。 如果没有指定mininum extent。initial、那么oracle判断next是否相等,以及pctincrease是否=0。如果满足oracle创建一个本地管理表空间并指定uniform。否则oracle将创建一个自动分配的本地管理表空间。 注意：本地管理表空间只能存储永久对象。如果你声明了local,将不能声明default storage_clause,mininum extent、temporary. * segment_management_clause ``` SEGMENT SPACE MANAGEMENT AUTO 自动段管理 segment space management manual 手动段管理 ``` ## 3.3 表空间操作 ### 3.3.1 普通表空间操作 * 创建表空间 ``` create tablespace user1 datafile '/u01/app/oracle/oradata/orcl/user1.dbf' size 10m autoextend on next 1m ``` * 添加数据文件 ``` alter tablespace user1 Add datafile '/u01/app/oracle/oradata/orcl/user1_1.dbf' size 20M autoextnend off ``` * 更改数据文件大小 ``` alter database datafile '/u01/app/oracle/oradata/orcl/user1_1.dbf' resize 50M ``` * 更改数据文件自增 ``` alter database datafile '/u01/app/oracle/oradata/orcl/user1_1.dbf' autoextend on ``` * 查询表空间 ``` * 查询所有表空间名字：user_tablespaces * 查询默认表空间、临时表空间：user_users * 查询表空间空闲情况：dba_free_space * 查询数据文件：dba_data_files * 查询表空间状态：dba_tablespaces ``` * 移动数据文件 ``` # 先offline alter tablespace user1 offline # 物理硬盘上移动数据文件 alter tablespace user1 rename datafile '/u01/app/oracle/oradata/orcl/user1.dbf' to '/u01/app/oracle/oradata/orcl/user1_0.dbf' ``` * 删除表空间 ``` drop tablespace user1 including contents and datafiles cascade constraints # including contents and datafiles 删除内容和文件 # cascade constraints 删除约束 ``` ### 3.3.2 临时表空间操作 * 创建临时表空间 ``` create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp1.dbf' size 10m autoextend on next 1m ``` * 添加数据文件 ``` alter tablespace temp1 Add tempfile '/u01/app/oracle/oradata/orcl/temp1_1.dbf' size 20M autoextnend off ``` * 更改数据文件大小 ``` alter database tempfile '/u01/app/oracle/oradata/orcl/temp1_1.dbf' resize 50M ``` * 更改数据文件自增 ``` alter database tempfile '/u01/app/oracle/oradata/orcl/temp1_1.dbf' autoextend on ``` * 删除表空间 ``` drop tablespace temp1 including contents and datafiles cascade constraints # including contents and datafiles 删除内容和文件 # cascade constraints 删除约束 ``` * 更改系统的默认临时表空间 ``` # 所有用户的默认临时表空间都将切换为新的临时表空间： alter database default temporary tablespace temp1; # 更改某一用户的临时表空间 alter user scott temporary tablespace temp; ``` 如果觉得我的文章对您有用，请随意赞赏。您的支持将鼓励我继续创作！ 赞赏支持
