Oracle 表空间操作

空间回收

purge recyclebin;

使用delete删除数据回收空间方法

-- 开启行迁移功能
ALTER TABLE T_FTP_ARREARS_RESULT ENABLE ROW MOVEMENT;
-- 收缩表并降低hwm，并且回收相应的索引
ALTER TABLE T_FTP_ARREARS_RESULT SHRINK SPACE CASCADE;

修改表空间

-- 修改表空间
SELECT 'ALTER TABLE WWTEST.' || T.TABLE_NAME || ' MOVE TABLESPACE USERS;'
  FROM ALL_TABLES T
 WHERE T.OWNER = 'WX_KBT_TEST';
---- 如果有分区，使用这条语句
SELECT 'alter table ' || TABLE_NAME || ' move partition ' || PARTITION_NAME ||
       ' tablespace USERS;'
  FROM USER_TAB_PARTITIONS
 WHERE TABLE_NAME IN ('IM_WX_QX_TEMPLATE_RESULT', 'IM_WX_QX_TEMPLATE');

-- 修改索引的表空间
SELECT 'alter index ' || INDEX_NAME || ' rebuild tablespace USERS;'
  FROM ALL_INDEXES I
 WHERE I.OWNER = 'WX_KBT_TEST'
   AND I.PARTITIONED = 'YES';
---- 如果有分区，使用这条语句
SELECT 'alter index ' || P.INDEX_NAME || ' rebuild PARTITION ' ||
       P.PARTITION_NAME || ' tablespace USERS;'
  FROM USER_IND_PARTITIONS P
 WHERE P.INDEX_NAME IN ('I_IM_WX_LOG_PROCESSTYPE', 'I_IM_WX_LOG_USERNO');

大小扩展

数据文件大小

SELECT A.TABLESPACE_NAME,
       ROUND(A.BYTES / 1024 / 1024 / 1024, 2) "sum GB",
       ROUND(A.BYTES / 1024 / 1024, 2) "sum MB",
       ROUND((A.BYTES - B.BYTES) / 1024 / 1024 / 1024, 2) "used GB",
       ROUND((A.BYTES - B.BYTES) / 1024 / 1024, 2) "used MB",
       ROUND(B.BYTES / 1024 / 1024, 2) "free MB",
       ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) "used%"
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, MAX(BYTES) LARGEST
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 ORDER BY ((A.BYTES - B.BYTES) / A.BYTES) DESC;

SELECT T.TABLESPACE_NAME,
       T.FILE_NAME,
       T.BYTES / 1024 / 1024 || 'M' AS TOTAL,
       T.AUTOEXTENSIBLE,
       T.MAXBYTES / 1024 / 1024 || 'M' AS MAXSIZE
  FROM DBA_DATA_FILES T
 WHERE T.TABLESPACE_NAME = 'USERS';

表空间追加文件（大小）

-- 固定大小（会马上分配 30GB 硬盘空间）
alter tablespace users add datafile '/data/app/oracle/oradata/orcl/users12.dbf' size 30G;

-- 自动扩展大小（初始大小 500MB，最大 30GB），推荐这种配置方式
alter tablespace users  add datafile '/data/app/oracle/oradata/orcl/im_data07.dbf' size 500M autoextend on next 5M maxsize 30G;

SELECT T.bytes/1024/1024,T.* FROM Dba_Segments t  WHERE t.tablespace_name='users' ORDER BY T.bytes DESC;

重设大小

SELECT 'alter database datafile ' || FILE_NAME || ' resize 500M;'
  FROM DBA_DATA_FILES F
 WHERE F.TABLESPACE_NAME = 'USERS';

SELECT 'ALTER database datafile ' || FILE_NAME || ' autoextend on next 10M maxsize 30G;'
  FROM DBA_DATA_FILES F
 WHERE F.TABLESPACE_NAME = 'USERS';

ALTER database datafile '/data/app/oracle/oradata/orcl/users55.dbf' resize 500M;
ALTER database datafile '/data/app/oracle/oradata/orcl/users55.dbf' autoextend on next 10M maxsize 30G;

删除表空间

-- 删除空的表空间，不包含物理文件
drop tablespace tablespace_name;
-- 删除非空表空间，不包含物理文件
drop tablespace tablespace_name including contents;
-- 删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
-- 删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
-- 如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
drop tablespace <tablespace_name> including contents and datafiles;