查看临时表空间使用情况,如何扩展表空间-教育资源网

查看临时表空间使用情况,如何扩展表空间

SELECT D.TABLESPACE_NAME,SPACE \”SUM_SPACE(M)\”,BLOCKS SUM_BLOCKS, USED_SPACE \”USED_SPACE(M)\”,ROUND(NVL(USED_SPACE,0)/SPACE*100,2) \”USED_RATE(%)\”,NVL(FREE_SPACE,0) \”FREE_SPACE(M)\”FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)当通过ORACLE中的create table … as select 语句创建一张新表时，新表的数据量为比较大，如10亿，这时SQL*Plus很可能就会提示“ORA-01653: …”错误信息。这个错误信息暗示表空间大小不够，需要为表空间增加数据文件。科普生活

如何修改临时表空间大小

查看所有用户的临时表空间及相应的数据文件：select d.username, t.file_name, d.temporary_tablespace from DBA_TEMP_FILES t,dba_users d where t.tablespace_name = d.temporary_tablespace;修改临时表空间tmp的大小：alter database tempfile \’/home/oracle/oradata/trade/temp01.dbf\’ resize 4096m;将系统的默认临时表空间设为tmp：alter database default temporary tablespace tmp;修改用户aa的默认表空间为tmp：alter user aa temporary tablespace tmp;删除用户aa以前的临时表空间ex_aa：drop tablespace ex_aa including contents and datafiles;

如何查看数据库的默认表空间，与临时表空间

sel

ORACLE 临时表空间TEMP 满了怎么办？

打印出SQL放到PL/SQL Devoloper 执行，报“无法通过8（在表空间XXX中）扩展 temp 段”，还有一个页面，可以查询出记录，但无法统计数据！经过分析产生原因可能是：ORACLE临时段表空间不足，因为ORACLE总是尽量分配连续空间，一但没有足够的可分配空间或者分配不连续就会出现上述的现象。解决方法：知道由于ORACLE将表空间作为逻辑结构-单元，而表空间的物理结构是数据文件，数据文件在磁盘上物理地创建，表空间的所有对象也存在于磁盘上，为了给表空间增加空间，就必须增加数据文件。

先查看一下指定表空间的可用空间，使用视图SYS.DBA_FREE_SPACE，视图中每条记录代表可用空间的碎片大小：SQL>Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name=‘XXX’;返回的信息可初步确定可用空间的**块，看一下它是否小于错误信息中提到的尺寸，再查看一下缺省的表空间参数：SQL>SELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME=\’XXX\’;通过下面的SQL命令修改临时段表空间的缺省存储值：SQL>ALTER TABLESPACE name DEFAULT STORAGE (INITIAL 64K NEXT 64K);适当增大缺省值的大小有可能解决出现的错误问题，也可以通过修改用户的临时表空间大小来解决这个问题：SQL>好文分享;ALTER USER username TEMPORARY TABLESPACE new_tablespace_name;使用ALTER TABLESPACE命令，一但完成，所增加的空间就可使用，无需退出数据库或使表空间脱机，但要注重，一旦添加了数据文件，就不能再删除它，若要删除，就要删除表空间。

MYSQL存储引擎InnoDB（三十五）：临时表空间

InnoDB使用会话临时表空间和全局临时表空间。在InnoDB配置为磁盘内部临时表的存储引擎时，会话临时表空间存储用户创建的临时表和优化器创建的内部临时表。

从 MySQL 8.0.16 开始，用于磁盘内部临时表的存储引擎固定为InnoDB。

（之前，存储引擎由internal_tmp_disk_storage_engine的值决定）在**次请求创建磁盘临时表时会话临时表空间从临时表空间池中被分配给会话。一个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。分配给会话的临时表空间用于会话创建的所有磁盘临时表。当会话断开连接时，其临时表空间将被截断并释放回池中。

服务器启动时会创建一个包含 10 个临时表空间的池。池的大小永远不会缩小，并且表空间会根据需要自动添加到池中。临时表空间池在正常关闭或中止初始化时被删除。

会话临时表空间文件在创建时大小为 5 页，并且具有.ibt文件扩展名。 InnoDB为会话临时表空间保留了40 万个空间 ID。因为每次启动服务器时都会重新创建会话临时表空间池，所以会话临时表空间的空间 ID 在服务器关闭时不会保留，并且可以重复使用。

innodb_temp_tablespaces_dir 变量定义了创建会话临时表空间的位置。默认位置是 #innodb_temp数据目录中的目录。如果无法创建临时表空间池，则会拒绝启动。

在基于语句的** (SBR) 模式下，在副本上创建的临时表驻留在单个会话临时表空间中，该临时表空间仅在 MySQL 服务器关闭时被截断。 INNODB_SESSION_TEMP_TABLESPACES 表提供有关会话临时表空间的元数据。该INFORMATION_SCHEMA.INNODB_TEMP电商_TABLE_INFO表提供有关在InnoDB实例中处于活动状态的用户创建的临时表的元数据。全局临时表空间 ( ibtmp1) 存储对用户创建的临时表所做的更改的回滚段。

innodb_temp_data_file_path 变量定义了全局临时表空间数据文件的相对路径、名称、大小和属性。如果没有为innodb_temp_data_file_path指定值，则默认行为是创建innodb_data_home_dir目录中命名为ibtmp1的单个自动扩展数据文件。初始文件大小略大于 12MB。全局临时表空间在正常关闭或中止初始化时被删除，并在每次服务器启动时重新创建。

全局临时表空间在创建时会收到一个动态生成的空间 ID。如果无法创建全局临时表空间，则拒绝启动。如果服务器意外停止，则不会删除全局临时表空间。在这种情况下，数据库管理员可以手动删除全局临时表空间或重新启动 MySQL 服务器。

重新启动 MySQL 服务器会自动删除并重新创建全局临时表空间。全局临时表空间不能驻留在原始设备上。 INFORMATION_SCHEMA.FILES提供有关全局临时表空间的元数据。

发出与此类似的查询以查看全局临时表空间元数据：默认情况下，全局临时表空间数据文件会自动扩展并根据需要增加大小。要确定全局临时表空间数据文件是否正在自动扩展，请检查以下 innodb_temp_data_file_path 设置：要检查全局临时表空间数据文件的大小，请使用与此类似的查询来查询INFORMATION_SCHEMA.FILES表：TotalSizeBytes显示全局临时表空间数据文件的当前大小。或者，检查操作系统上的全局临时表空间数据文件大小。

全局临时表空间数据文件位于 innodb_temp_data_file_path 变量定义的目录中。要回收全局临时表空间数据文件占用的磁盘空间，请重新启动 MySQL 服务器。重新启动服务器会根据innodb_temp_data_file_path定义的属性删除并重新创建全局临时表空间数据文件。要限制全局临时表空间数据文件的大小，请配置 innodb_temp_data_file_path以指定**文件大小。

例如：配置 innodb_temp_data_file_path 需要重新启动服务器。

怎么修改mysql数据库临时表空间大小

以MySQL 8.0 来说，通过查看 8.0 的**文档得知，8.0 的临时表空间分为会话临时表空间和全局临时表空间，会话临时表空间存储用户创建的临时表和当 InnoDB 配置为磁盘内部临时表的存储引擎时由优化器创建的内部临时表，当会话断开连接时，其临时表空间将被截断并释放回池中;也就是说，在 8.0 中有一个专门的会话临时表空间，当会话被杀掉后，可以回收磁盘空间;而原来的 ibtmp1 是现在的全局临时表空间，存放的是对用户创建的临时表进行更改的回滚段，在 5.7 中 ibtmp1 存放的是用户创建的临时表和磁盘内部临时表;
也就是在 8.0 和 5.7 中 ibtmp1 的用途发生了变化，5.7 版本临时表的数据存放在 ibtmp1 中，在 8.0 版本中临时表的数据存放在会话临时表空间，如果临时表发生更改，更改的 undo 数据存放在 ibtmp1 中;

实验验证：将之前的查询结果保存成临时表，对应会话是 45 号，通过查看对应字典表，可知 45 号会话使用了 temp_8.ibt 这个表空间，通过把查询保存成临时表，可以用到会话临时表空间，如下图：

下一步杀掉 45 号会话，发现 temp_8.ibt 空间释放了，变为了初始大小，状态为非活动的，证明在 mysql8.0 中可以通过杀掉会话来释放临时表空间。

总结：在 mysql5.7 时，杀掉会话，临时表会释放，但是仅仅是在 ibtmp 文件里标记一下，空间是不会释放回操作系统的。

如果要释放空间，需要重启数据库;在 mysql8.0 中可以通过杀掉会话来释放临时表空间。