Oracle19c的表空间

全栈精英 2021-08-02

5640

什么是表空间

表空间属于Oracle中的存储结构，是一种用于存储数据库对象(如：数据文件)的逻辑空间

表空间分为：

永久表空间：存储数据库中需要永久化存储的对象，比如二维表、视图、存储过程、索引
临时表空间：存储数据库的中间执行过程，如：保存 order by 数据库排序，分组时产生的临时数据。操作完成后存储的内容会被自动释放。临时表空间是通用的，所有的用户都使用
TEMP 临时表空间：一般只有temp一个临时表空间，如果还需要别的临时表空间时，可以自己创建
UNDO表空间：保存数据修改前的副本。存储事务所修改的旧值，即被修改之前的数据。当我们对一张表中的数据进行修改的同时会对修改之前的信息进行保存，为了对数据执行回滚、恢复、撤销的操作

如何查看表空间

查看用户的表空间

 -- 管理员使用的表空间
 select tablespace_name from dba_tablespaces; 
 -- 普通用户使用的表空间
 select tablespace_name from user_tablespaces;

说明：

SYSTEM 系统表空间，是永久系统表空间，用于存储SYS用户的表、视图、存储过程对象。
UNDOTBS1 存储撤销信息的undo表空间
SYSAUX 作为EXAMPLE的辅助表空间
TEMP 临时表空间，用户存储SQL语句处理的表示索引信息
USERS 永久表空间，存储数据库用户创建的数据库对象
EXAMPLE 安装Oracle数据库示例的表空间

查询指定用户的表空间：

 -- 查看SYSTEM的表空间 （用户名要大写）
 select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';

注意：如果要查看"本地用户"的表空间，必须先切换到PDB

表空间使用情况查询

SELECT A.TABLESPACE_NAME                     AS TABLESPACE_NAME, 
         ROUND(A.BYTES/(1024*1024*1024),2)    AS "总共(G)", 
         ROUND(B.BYTES/(1024*1024*1024),2)    AS "已使用(G)", 
         ROUND(C.BYTES/(1024*1024*1024),2)    AS "空闲(G)", 
         ROUND((B.BYTES * 100)  A.BYTES,2)   AS "% 使用占比", 
         ROUND((C.BYTES * 100)  A.BYTES,2)   AS "% 空闲占比" 
 FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C 
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME 
 AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

如何查看数据文件

oracle有几种常见的数据文件格式：控制文件，数据文件，日志文件

其中表空间里面有数据文件

控制文件：.ctl

 select name from v$controlfile;

数据文件：.dbf

  -- 查询从控制文件中获取的数据文件的基本信息，包括：表空间名称、编号等
  select name from v$datafile;
  -- 查询所有临时数据文件的基本信息
  select name from v$tempfile;

日志文件：.log

 select member from v$logfile;

注意：登录不同的PDB看到不同的数据文件                   PDB和CDB公用相同的控制文件

查询当前账户的表空间数据文件：

 -- 查询永久表空间的数据文件：
 select tablespace_name ,file_name from dba_data_files;
 -- 临时表空间的数据文件：
 select tablesapce_name,file_name from dba_temp_files;
 
 -- 查看永久表空间数据文件和当前状态：
 select tablespace_name,file#, file_name,v.status,v.enabled from dba_data_files d,v$datafile v where d.file_id=v.file#;

注意：如果要查看PDB数据库的表空间，需要先切换到指定的PDB

如何新建表空间

如果没有为用户指定表空间，那么数据就会存储到默认表空间

 -- 创建school_data永久表空间及数据文件：
 create tablespace school_data datafile 'C:\oracledata\school_data.dbf' size 5m autoextend on next 5m;
 -- 创建school_temp临时表空间及数据文件：
 create temporary tablespace school_temp tempfile 'C:\oracledata\school_temp.dbf' size 5m;
 
 -- 删除school_data永久表空间及数据文件：
 drop tablespace school_data including contents and datafiles;
 -- 删除school_temp临时表空间及数据文件：
 drop tablespace school_temp including contents and datafiles;

说明：数据文件创建到了服务器的C:\oracledata，默认大小5m，永久表空间数据文件每次自动增长5m

如何设置表空间

创建新的PDB本地用户并分配表空间

 -- 创建用户test02，密码123，永久表空间oracle_data，临时表空间oracle_temp
 create user test02 identified by 123
 default tablespace oracle_data
 temporary tablespace oracle_temp;
 
 -- 赋权用户test02
 grant connect,resource,dba,CTXAPP,create view to test02;
 
 -- 回收test02的unlimited tablespace权限 （防止在其它表空间里随意建表）
 revoke unlimited tablespace from test02;
 
 -- 修改test02在users表空间的限额为0
 -- 注意：如果test02用户已经被授予了unlimited tablespace权限，那么alter user quota 0 是不起作用的
 alter user test02 quota 0 on users;
 
 -- 修改test02在oracle_data表空间不受限额
 alter user test02 quota unlimited on oracle_data;
 
 -- 查询用户表空间配额 (max_bytes为-1，即不受限制)
 select tablespace_name,username, max_bytes from  dba_ts_quotas;

注意：
UNLIMITED TABLESPACE系统权限：此系统权限会覆盖所有的单个表空间限额，并向用户提供所有表空间（包括SYSTEM和SYSAUX）的无限制限额（注：授予resource角色的时候也会授予此权限）
一定不要为用户提供system或sysaux表空间的限额。通常，只有sys和system用户才能在system或sysaux表空间中创建对象。
对于分配的临时表空间或临时还原表空间则不需要限额

修改用户的表空间

 -- 修改test账号的默认表空间为 school_data，临时表空间为school_temp
 alter user test default tablespace  school_data temporary tablespace school_temp;

修改表空间状态

 -- 修改school_data表空间离线
 alter tablespace school_data offline;
 -- 修改school_data表空间在线
 alter tablespace school_data online;
 
 -- 修改school_data表空间为只读
 alter tablespace school_data read only;
 -- 修改school_data表空间为可读写
 alter tablespace school_data read write;
 
 -- 查看school_data表空间的状态（名称要大写）
 select STATUS from dba_tablespaces where tablespace_name='SCHOOL_DATA';

修改数据文件

 -- 设置数据文件大小
 alter database datafile 'C:\oracledata\school_data.dbf' RESIZE 500M;
 
 -- 增加school_data表空间新的数据文件
 ALTER TABLESPACE school_data
 ADD DATAFILE 'C:\oracledata\school_data02.dbf'    -- 新文件名
 SIZE 100M                                         -- 默认大小100m
 AUTOEXTEND ON                                     -- 允许自动增加大小
 NEXT 10M                                          -- 每次自动增加10m大小
 MAXSIZE 20480M;                                   -- 文件最大20480m

oracle

文章转载自全栈精英，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle19c的表空间

什么是表空间

如何查看表空间

如何新建表空间

如何设置表空间

评论