oracle 表空间talbespace 学习专题

陈晓龙 · 发表于 2020-6-27 21:52:59

表空间专题

一、Oracle中的表空间查询方法

提到表空间大家可能都不会陌生，它是数据库中最大的逻辑单位与存储空间单位，数据库系统通过表空间为数据库对象分配空间。表空间在物理上体现为磁盘数据文件，每一个表空间由一个或多个数据文件组成，一个数据文件只可与一个表空间相联系，这是逻辑与物理的统一。了解表空间和数据文件的的属性及使用率，是数据库管理员的一项重要职责。在本文中笔者将以oracle为例，详细介绍查询Oracle数据库表空间信息和数据文件信息的方法。希望能帮助大家更深入了解表空间的知识和应用。

一、如何查看Oracle数据库中表空间信息的方法

1、从Oracle数据库中工具入手：

使用oracle enterprise manager console工具，这是oracle的客户端工具，当安装oracle服务器或客户端时会自动安装此工具，在windows操作系统上完成oracle安装后，通过下面的方法登录该工具：开始菜单——程序——Oracle-OraHome92——Enterprise Manager Console(单击)——oracle enterprise manager console登录——选择‘独立启动’单选框——‘确定’ —— ‘oracle enterprise manager console，独立’ ——选择要登录的‘实例名’ ——弹出‘数据库连接信息’ ——输入’用户名/口令’ (一般使用sys用户)，’连接身份’选择选择SYSDBA——‘确定’，这时已经成功登录该工具，选择‘存储’ ——表空间，会看到如下的界面，该界面显示了表空间名称，表空间类型，区管理类型，以”兆”为单位的表空间大小，已使用的表空间大小及表空间利用率。

2、从Oracle数据库中命令方法入手：

通过查询数据库系统中的数据字典表(data dictionary tables)获取表空间的相关信息，首先使用客户端工具连接到数据库,这些工具可以是SQLPLUS字符工具、TOAD、PL/SQL等,连接到数据库后执行如下的查询语句：

select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from
dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from
dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;

该语句通过查询dba_free_space，dba_data_files，dba_tablespaces这三个数据字典表，得到了表空间名称，表空间类型，区管理类型，以”兆”为单位的表空间大小，已使用的表空间大小及表空间利用率。dba_free_space表描述了表空间的空闲大小，dba_data_files表描述了数据库中的数据文件，dba_tablespaces表描述了数据库中的表空间。

上面语句中from子句后有三个select语句，每个select语句相当于一个视图，视图的名称分别为a、b、c，通过它们之间的关联关系，我们得到了表空间的相关信息。

语句执行结果如下：

上面描述中分别介绍了查看Oracle数据库中表空间信息的工具方法和命令方法。

二、查询Oracle数据库中数据文件信息的方法

1、查看Oracle数据库中数据文件信息的工具方法：

使用上面介绍过的方法登录oracle enterprise manager console工具，选择‘存储’ ——数据文件，会看到如下的界面，该界面显示了数据文件名称，表空间名称，以”兆”为单位的数据文件大小，已使用的数据文件大小及数据文件利用率。

2、查看Oracle数据库中数据文件信息的命令方法：

通过查询数据库系统中的数据字典表(data dictionary tables)获取数据文件的相关信息，首先使用客户端工具连接到数据库,这些工具可以是SQLPLUS字符工具、TOAD、PL/SQL等,连接到数据库后执行如下的查询语句：

select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

上面描述中分别介绍了查看Oracle数据库中数据文件信息的工具方法和命令方法。

三、查看临时表空间和数据库文件的方法

在oracle数据库中，临时表空间主要用于用户在使用order by 、group by语句进行排序和汇总时所需的临时工作空间。要查询数据库中临时表空间的名称，大小及数据文件，可以查询数据字典dba_tablespaces及dba_data_files。命令如下：

select
a.tablespace_name 表空间名称,
b.bytes 大小bytes,
b.file_name 数据文件名
from dba_tablespaces a, dba_data_files b
Where a.tablespace_name=b.Tablespace_Name and a.contents='TEMPORARY';

查询结果如下：

从oracle 9i开始，可以创建Temporary tablespace类表空间，即“临时“表空间，这类表空间使用临时文件。临时文件的信息被存储在数据字典V$tempfile中。命令如下：

Select file#，status，name from V$tempfile;

查询数据字典V$tempfile结果如下：

在上面介绍的方法中，建议掌握命令方法,因为你的环境可能没有图形工具，而SQLPLUS一般情况下都是可以使用的，有了命令脚本，很容易得到表空间和数据文件的相关信息。另外，数据库管理员应该多整理命令脚本，在需要时直接执行脚本以提高工作效率。

在数据库管理员的日常工作中，应该经常查询表空间的利用率，按照数据库系统的具体情况估算表空间的增长量，当表空间的利用率超过90%时，要及时采取措施，如清理历史表、历史数据以释放空间，向表空间中添加新的数据文件，扩展现有数据文件大小等方法来降低表空间的利用率，避免表空间利用率接近100%时，将产生空间不够的错误。

二

ORACLE 临时表空间使用率过高的原因及临时解决方案数据库temp临时表空间增大，一般在数据安装时设置临时表空间大小，默认的情况下都是设置为自动增长。这样会引起一个问题：在数据库使用中temp表空间会自动扩展的越来越大，造成磁盘空间使用不足。　　引起临时表空间增大主要使用在以下几种情况：1、order by or group by （disc sort占主要部分）；2、索引的创建和重创建；3、distinct操作；4、union & intersect & minus sort-merge joins；
　　5、Analyze 操作；6、有些异常也会引起TEMP的暴涨。
　　解决temp临时表空间大小问题：
　　一。直接缩小temp表空间大小
　　alter database tempfile 'd：powerdmsdb\dmsdb\temp01.dbf' resize 1024M；
　　此语句会直接修改temp表空间的大小，但可能会执行不成功，因为当temp使用率为100%或当前有会话占用时，temp表空间是无法缩小的。
　　查询当前会话：
　　SELECT se.username，se.sid，se.serial#，se.sql_address，se.machine，se.program，su.tablespace，su.SEGTYPE，su.CONTENTS FROM v$session se，v$sort_usage su WHERE se.saddr=su.session_addr
　　利用此sql语句查询当前会话，然后kill当前会话：
　　Alter system kill session 'sid，serial#'
　　执行此语句后再对temp表空间resize空间大小就可以了。
　　注：此方法只是对temp表空间做临时性的缩小，以后还会继续增大。
　　方法二：
　　对temp临时表空间重建并设置增长上限值，设置这个值时要预先估算设置合理，不然当增大到此值时会出错，在合理的范围内增加到上限值时数据库会自动释放temp临时表空间。
　　重建temp临时表空间：
　　1.——启动数据库
　　startup
　　2.——创建中转临时表空间create TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D：\PowerDmsDB\DMSDB\TEMP02.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M；3.——改变缺省临时表空间为刚刚创建的新临时表空间temp2 alter database default temporary tablespace temp2；4.——删除原来临时表空间drop tablespace temp including contents and datafiles；
　　5.——重新创建临时表空间create TEMPORARY TABLESPACE TEMP TEMPFILE 'D：\PowerDmsDB\DMSDB\TEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M；
　　6.——重置缺省临时表空间为新建的temp表空间alter database default temporary tablespace temp；
　　7.——删除中转用临时表空间drop tablespace temp2 including contents and datafiles；
　　8.——重新指定用户表空间为重建的临时表空间alter user zxd temporary tablespace temp；
　　至此临时表空间增长过大可以更改完成。
　　下面是查询在sort排序区使用的执行耗时的SQL：Select se.username，se.sid，su.extents，su.blocks*to_number（rtrim（p.value））as Space，tablespace，segtype，sql_text from v$sort_usage su，v$parameter p，v$session se，v$sql s where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username，se.sid
　　此语句可以做跟踪查看分析时用。

。。。。。。。。待续

－－－－－－－－－－－－－－－－上面是转载，下面的是平常操作时经验总结(cuker919)－－－－－－－－－－－－－－－－－－－－－－－

三

1、创建表空间和索引空间

my_data为表空间，my_index为索引空间。创建并设置自动增长。

create tablespace my_data datafile '/opt/oracle/app/oracle/oradata/sid/mydata.dbf' size 2048M autoextend on;
create tablespace my_index datafile '/opt/oracle/app/oracle/oradata/sid/myindex.dbf' size 2048M autoextend on;

2、由于现网表都很大，大概100G，那么初始化的表空间由于有些系统限制总文件大小，比如32位的wondow系统一般是4个G，linux估计30G左右。

所以导入大数据时会报下面的错误。

Re: ORA-01659: unable to allocate MINEXTENTS beyond 78 in tablespace my_data[IMP-00003: ORACLE 错误1659出现
ORA-01659: unable to allocate MINEXTENTS beyond 78 in tablespace my_data
IMP-00058: ORACLE 错误1653出现
ORA-01653: unable to extend table test.test_ERRORLOG0326 by 128 in tablespace my_data
IMP-00003: ORACLE 错误1658出现
ORA-01658: unable to create INITIAL extent for segment in tablespace my_data

3、针对上面的问题再增加表空间文件和索引空间文件。导完之后发现没有报错。现网有专家建议（http://www.itpub.net/thread-48198-1-1.html

增加数据文件或将tablespace的minextent,next,intial等参数设小一点，表空间没有足够大的连续的extents），但应该是表空间不够了，才报上面的异常的。

alter tablespace my_data add datafile '/opt/oracle/app/oracle/oradata/sid/mydata1.dbf' size 500M autoextend on;
alter tablespace my_data add datafile '/opt/oracle/app/oracle/oradata/sid/mydata2.dbf' size 500M autoextend on;

alter tablespace my_index add datafile '/opt/oracle/app/oracle/oradata/sid/myindex1.dbf' size 500M autoextend on;
alter tablespace my_index add datafile '/opt/oracle/app/oracle/oradata/sid/myindex2.dbf' size 500M autoextend on;