Oracle 表空间操作

空间回收

1
purge recyclebin;

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

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

修改表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 修改表空间
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');

重设大小

1
2
3
4
5
6
7
8
9
10
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;

大小扩展

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’;

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

表空间追加文件(大小)

alter tablespace users add datafile ‘/data/app/oracle/oradata/orcl/users12.dbf’ size 30G;

create tablespace IM_DATA datafile ‘/data/app/oracle/oradata/orcl/im_data01.dbf’ size 500M autoextend on next 5M maxsize 30G;

alter tablespace IM_DATA 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=’IM_DATA’ ORDER BY T.bytes DESC;

删除表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
-删除空的表空间,但是不包含物理文件
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;
  • 本文作者: forever杨
  • 本文链接: https://blog.yl-online.top/posts/2fa1c46f.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。如果文章内容对你有用,请记录到你的笔记中。本博客站点随时会停止服务,请不要收藏、转载!