查询Oracle表空间占用情况及表空间操作

1、查询表空间占用情况：

SELECT a.tablespace_name as '表空间名'
        ,total as '表空间大小'
        ,free '表空间剩余大小',
        ,(total - free) '表空间使用大小'
        ,total / (1024 * 1024 * 1024) '表空间大小(G)'
        ,free / (1024 * 1024 * 1024) '表空间剩余大小(G)'
        ,(total - free) / (1024 * 1024 * 1024) '表空间使用大小(G)'
        ,round((total - free) / total, 4) * 100 '使用率 %'
   FROM (SELECT tablespace_name, SUM(bytes) free
           FROM dba_free_space
          GROUP BY tablespace_name) a,
        (SELECT tablespace_name, SUM(bytes) total
           FROM dba_data_files
          GROUP BY tablespace_name) b
  WHERE a.tablespace_name = b.tablespace_name   order by a.tablespace_name

2、查看表空间及数据文件的SQL：

1、查看表空间的名称及大小
   SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
     FROM dba_tablespaces t, dba_data_files d
    WHERE t.tablespace_name = d.tablespace_name
    GROUP BY t.tablespace_name;
    
2、查看表空间数据文件的名称及大小
 SELECT tablespace_name,
        file_id,
        file_name,
        round(bytes / (1024 * 1024), 0) total_space
   FROM dba_data_files
  ORDER BY tablespace_name;

3、查询默认临时表空间的信息：

select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'; --查询默认表空间的信息
  select t.tablespace_name,t.file_name,t.bytes/1024/1024 file_size,t.autoextensible from dba_data_files t; --查询表空间及数据文件的大小

4、扩展表空间：
（1）方法一：

  alter database tempfile '/oracle/oratest/temp2.dbf' resize 1G;
  alter database DATAFILE '/u01/app/oracle/oradata/amldb/users01.dbf' resize 2G;

（2）方法二：

追加物理文件：
 （1）非临时表空间：ALTER TABLESPACE tsodsdat add DATAFILE '/u01/app/oracle/oradata/amldb/tsodsdat_02.dbf' SIZE 4096M;
 （2）临时表空间： ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ora_data/temp02.dbf' size 512M reuse  autoextend on next 640K maxsize unlimited;

5、创建表空间：

1、自增表空间
 CREATE TABLESPACE tsdat01 LOGGING DATAFILE '/u01/app/oracle/oradata/amldb/tsdat01.dbf' SIZE 5119M 
 AUTOEXTEND ON NEXT 40M MAXSIZE UNLIMITED DEFAULT STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 5);
2、非自增表空间
 CREATE SMALLFILE TABLESPACE TSDAT01 LOGGING DATAFILE  '/data/oradata/amldb/TSDAT01.dbf' SIZE 6G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

6、删除物理文件及表空间：

1、删除物理文件：
ALTER TABLESPACE temp drop TEMPFILE '/oradata/ora_data/temp02.dbf';
2、删除表空间
drop TABLESPACE test_space including contents and datafiles;

7、查看回滚段、控制文件及日志文件：

1、查看回滚段名称及大小
 SELECT segment_name,
        tablespace_name,
        r.status,
        (initial_extent / 1024) initialextent,
        (next_extent / 1024) nextextent,
        max_extents,
        v.curext curextent
   FROM dba_rollback_segs r, v$rollstat v
  WHERE r.segment_id = v.usn(+)
  ORDER BY segment_name;

2、查看控制文件
 SELECT NAME FROM v$controlfile;

3、查看日志文件
 SELECT MEMBER FROM v$logfile;