oracle表空间管理

--查看修改ORACLE表空间
--1、查看所有表空间信息
select a.tablespace_name
      ,round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc
      ,round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free
      ,round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used
      ,round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) Pct_Free
      ,100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) Pct_used
      ,round(maxbytes / 1048576, 2) Max
  from (select f.tablespace_name
              ,sum(f.bytes) bytes_alloc
              ,sum(decode(f.autoextensible,
                          'YES',
                          f.maxbytes,
                          'NO',
                          f.bytes)) maxbytes
          from dba_data_files f
         group by tablespace_name) a
      ,(select f.tablespace_name
              ,sum(f.bytes) bytes_free
          from dba_free_space f
         group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name
      ,round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc
      ,round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             1048576,
             2) megs_free
      ,round(sum(nvl(p.bytes_used, 0)) / 1048576, 2) megs_used
      ,round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100,
             2) Pct_Free
      ,100 -
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100,
             2) pct_used
      ,round(sum(f.maxbytes) / 1048576, 2) max
  from sys.v_$TEMP_SPACE_HEADER h
      ,sys.v_$Temp_extent_pool  p
      ,dba_temp_files           f
where p.file_id(+) = h.file_id
   and p.tablespace_name(+) = h.tablespace_name
   and f.file_id = h.file_id
   and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1;

--2、查看指定表空间详细信息：
Select file_name
      ,BYTES
      ,blocks
      ,autoextensible
      ,nvl(increment_by, 0) increment_by
      ,maxbytes
      ,maxblocks
      ,status
      ,maxblocks maxextend
      ,file_id
  from sys.dba_data_files
where TABLESPACE_NAME = 'USERS';

--３、修改表空间大小：
ALTER DATABASE DATAFILE '/opt/oracle/product/oradata/bocodb/users01.dbf' RESIZE 202M;

--4:开启自动扩展功能
alter database datafile '对应的数据文件路径信息' autoextend on;
--5:关闭自动扩展功能
alter database datafile '对应的数据文件路径信息' autoextend off;

--查看表空间
select * from sys.user_tablespaces;
--查看数据文件
select * from sys.dba_data_files;
--删除表空间及其数据文件
--DROP /*temporary*/ TABLESPACE KSYCMS INCLUDING CONTENTS AND DATAFILES;

--oracle查看表空间使用情况
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查看表空间和用户关系
select b.file_name       物理文件名
      ,b.tablespace_name 表空间
      ,b.AUTOEXTENSIBLE  自动扩展
      ,c.username        用户名称
  from dba_data_files b
      ,sys.dba_users  c
where c.default_tablespace = b.TABLESPACE_NAME
   and c.username in ('EMIS',
                      'EMS',
                      'EMS2',
                      'ESS',
                      'OIS',
                      'KSTEST',
                      'KSYCMS',
                      'KSYWEBACCOUNT')
order by c.username
         ,b.AUTOEXTENSIBLE;

--缩减表空间大小
--ALTER DATABASE TEMPFILE '/oracle/product/11.2.0/db_1/dbs/temp10.ora' RESIZE 3000M;
--ALTER TABLESPACE TEMP1 SHRINK TEMPFILE '/oracle/product/11.2.0/db_1/dbs/temp10.ora';
--ALTER TABLESPACE TEMP1 SHRINK SPACE KEEP 20M;

--11、对临时表空间进行shrink（11g新增的功能）
----将temp表空间收缩为20M
--alter tablespace temp shrink space keep 20M;
----自动将表空间的临时文件缩小到最小可能的大小
--ALTER TABLESPACE temp SHRINK TEMPFILE ’/oracle/product/11.2.0/db_1/dbs/temp10.ora’;
--
--临时表空间作用
--Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
--重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。
--网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。
--也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。
--
--临时表空间的主要作用：
--  索引create或rebuild；
--  Order by 或 group by；
--  Distinct 操作；
--  Union 或 intersect 或 minus；
--  Sort-merge joins；
--  analyze。

--查看临时表空间
select bytes / 1024 / 1024 "file_size(M)"
       ,bytes / 1024 / 1024 / 1024 "file_size(G)"
       ,t.*
  from dba_temp_files t;
--查看数据文件信息
SELECT file_name
      ,tablespace_name
      ,file_id
      ,'datafile' AS TYPE
  FROM DBA_DATA_FILES
UNION ALL
SELECT file_name
      ,tablespace_name
      ,file_id + value
      ,'tempfile'
  FROM DBA_TEMP_FILES
      ,v$parameter p
WHERE p.name = 'db_files';

-- 　查看当前用户的缺省表空间
select * from sys.dba_users;

----------增加数据文件---------
-------------------------------
alter tablespace tablespace_name add datafile '+ASMDISK1/mydbfile.dbf' size 10240M;

--------------------------------
-------手工改变数据文件尺寸------
--------------------------------
alter database datafile '+ASMDISK1/mydbfile.dbf' resize 2048M;

--------------------------------
-------容许数据文件自动扩展------
--------------------------------
alter database datafile '+ASMDISK1/mydbfile.dbf' autoextend on next 10M maxsize 30G;

--------------------------------
----也可以这样数据文件自动扩展----
--------------------------------
alter tablespace tablespace_name add datafile '+ASMDISK1/mydbfile.dbf' size 5M autoextend on next 5M maxsize 50G;