表空间相关知识

1. sql查询各表空间使用情况：

Select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
SELECT df.tablespace_name "tablespace_name",totalspace "totalspace_M",(totalspace-freespace) "Usedspace_M",
freespace "leftspace_M",ROUND((1-freespace/totalspace)*100,2) "Use_rate_%" FROM
(SELECT tablespace_name,ROUND(SUM(bytes)/1024/1024) totalspace FROM dba_data_files
GROUP BY tablespace_name) df,(SELECT tablespace_name,ROUND(SUM(bytes)/1024/1024) freespace
FROM dba_free_space GROUP BY tablespace_name) fs where df.tablespace_name=fs.tablespace_name(+);

2. 表空间不足的解决办法：（表空间尽量让free百分比保持在10%以上，低于10%就增加datafile或者resizedatafile，一般数据文件不要超过2G）

• 第一种，增加/增大数据文件，扩大表空间

　　---增大数据文件：alter database datafile '全路径数据文件名称' resize ***M;

alter database datafile '/oracle/oradata/ora01/TBS_SYSDATA01' resize 10M;

　　---增加数据文件：alter tablespace 表空间名称 add datafile '全路径数据文件名称' size ***M;

alter tablespace TBS_LJB add datafile 'E:\ORADATA\ORA10\DATAFILE\TBS_LJB_02.DBF' size 100M ;

• 第二种，设置表空间为自动扩展方式

　　---alter database datafile '全路径数据文件名称' autoextend on;

alter database datafile 'E:\ORADATA\ORA10\DATAFILE\TBS_LJB_02.DBF' autoextend on; --如果自动扩展关闭，则为‘off’

3. 查看临时表空间使用情况

select d.tablespace_name tableSpaceName,
       nvl (a.BYTES / 1024 / 1024, 0) totalSize,
       nvl (a.BYTES / 1024 / 1024, 0) - nvl (t.BYTES, 0) / 1024 / 1024 freeSize,
       nvl (t.BYTES, 0) / 1024 / 1024 usedSize,
       round(NVL(t.BYTES / a.BYTES * 100, 0), 2) usedPercent
  from SYS.dba_tablespaces d,
       (select tablespace_name, sum(BYTES) BYTES
          from dba_temp_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes_cached) BYTES
          from v$temp_extent_pool
         group by tablespace_name) t
 where d.tablespace_name = a.tablespace_name(+)
   and d.tablespace_name = t.tablespace_name(+)
   and d.extent_management like 'LOCAL'
   and d.CONTENTS like 'TEMPORARY';

4. 查看临时表空间大小（原始表空间大小）

BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，这一部分大小是128个block，如下图

SELECT tablespace_name,bytes,blocks,user_bytes,user_blocks,blocks-user_blocks AS system_userd FROM dba_temp_files;

5. 创建新的表空间

CREATE TABLESPACE TBS_SYSDATA DATAFILE '/home/DB/oracle/oradata/orcl/TBS_SYSDATA.dbf' SIZE 50M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

6. 查询当前存在哪些表空间

Select * from v$tablespace;

7. 查看数据库文件

SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 FROM DBA_DATA_FILES;

8.通过数据库文件查看表空间及表空间是否开启自动扩展，扩展了多少

SELECT TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024,STATUS,AUTOEXTENSIBLE,INCREMENT_BY FROM dba_data_files;

9. 删除表空间（包括数据文件）

DROP TABLESPACE TBS_SYS_DATA  including  contents and datafiles;

10. 查询用户使用的表空间

--查询imuse01用户所使用的缺省表空间
select USERNAME,DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username=upper('imuse01');--用户名要大写
select distinct TABLESPACE_NAME from dba_segments where owner='SYS22';--查询用户使用过的所有的表空间
--查询imuse01用户所使用的临时表空间
select temporary_tablespace from dba_users where username=’imuse01’;

11. 删除数据文件

Alter tablespace name drop datafile ‘/telllin/orale/app/oradata/oracle/data01.bdf’;

...待持续更新