Oracle表空间满了如何解决？_oracle 扩展表空间

Oracle表空间满了如何解决？

1、表空间满了如何解决？

当 Oracle 数据库中的表空间满了时，一般需要采取以下措施：

《1》扩展数据文件

可以通过增加数据文件的大小来扩展表空间。具体步骤是使用 ALTER TABLESPACE 命令加上关键字 ADD DATAFILE 来添加一个或多个数据文件。

例如：

ALTER TABLESPACE tablespace_name ADD DATAFILE '/path/to/datafile.dbf' SIZE 50M;
1

这个命令会向指定的表空间中添加一个新的数据文件，其大小为 50MB。需要注意的是，在执行此操作前必须保证磁盘空间足够。

《2》压缩数据表

当表空间中存在不必要的数据时，可以考虑对数据表进行压缩，从而释放一些空间。一种常见的做法是删除一些不再使用的数据行，比如历史数据或者备份数据等。

《3》移动数据对象

可以将一些不常用到的数据对象移动到其他的表空间中。例如，将一些历史数据对象或者稀少使用的辅助表（如日志表）移动到硬盘速度较慢但容量较大的表空间中，从而腾出更多的存储空间。

《4》压缩重建索引

重建索引可以消除分裂的块并缩小存储区的碎片，从而释放一些空间。一般情况下，重建完索引后，表空间大小会被自动收缩。

注意

在执行上述操作时，请务必备份好数据以防止意外丢失。同时，要根据实际情况选择对应的措施，并充分评估可能产生的风险和影响。

2、表空间限额

创建一个用户,分配了400M的表空间,结果在用到13.3M时报错: ORA-01536: 超出表空间 ‘***’ 的空间限额 。

在这种情况下，出现了 “ORA-01536: 超出表空间 ‘***’ 的空间限额” 错误，原因可能是：

①. 分配的表空间大小不够：通过具体的错误信息 ORA-01536 可以看出，表空间 ‘***’ 已经超出了分配的限额。因此，可能是在创建用户时分配未能考虑到数据增长所需的空间。

②. 数据集中存储在某个表/分区：数据量可能远远超过预期，或者存在数据密集型表，导致该表所占用的存储空间已达到表空间上限。可以通过查看使用了最多空间的对象来确认此问题是否存在。例如:

SELECT owner, segment_name, segment_type, bytes/1024/1024 MB 
FROM dba_segments
WHERE tablespace_name = '***'
ORDER BY bytes DESC;
1
2
3
4

其中 “***” 要替换为实际的表空间名称。

要解决ORR-01536错误，可执行以下操作：

①. 增加表空间容量：根据实际需要，增加表空间中数据文件的数量或大小。

②. 检查表空间使用情况：对表空间及其包含的对象进行彻底检查，弄清楚哪些对象占用了大量空间，并采取相应有效措施来释放空间或重分配它们的存储位置。

注意： 无论采用哪种方法，都要进行充分的风险评估，并确认执行前已经备份了相关数据以防止意外丢失。

经过查询后我发现表空间跟表空间限额两个值是不一样的，推测按默认的话oracle应该会给每个用户分配一个默认的表空间限额，具体比例待查，但这比例肯定远小于100%，所以说分配了400M的表空间未必能存储400M的数据。

解决办法：

查看下用户表空间的限额 ：

select * from user_ts_quotas;
1

max_bytes字段就是了 ，-1是代表没有限制,其它值多少就是多少了。

不对用户做表空间限额控制:

GRANT UNLIMITED TABLESPACE TO ***(用户);
1

这种方式是全局性的，或者：

alter user ***(用户名) quota unlimited on ***(表空间);
1

这种方式是针对特定的表空间的，可以分配自然也可以回收了；

revoke unlimited tablespace from ***(用户)
1

或者

alter user *** quota 0 on ***
1

3、Oracle压缩表

在Oracle中实现表的压缩

在数据库存储管理中，压缩技术是一种重要的优化手段，可以减少数据库存储空间，降低存储成本。 Oracle数据库提供了表的压缩功能，可以对表进行压缩，减少表的存储空间，提高数据访问性能。本节将介绍如何在Oracle中实现表的压缩。

《1》压缩类型

在Oracle中，表的压缩分为行压缩和列压缩两种类型。

《2》行压缩

行压缩是指对表的行进行压缩，可以减少每行数据的存储空间，节省存储空间。 行压缩可以通过ALTER TABLE语句来实现。例如，以下SQL语句可以对表进行行压缩：

ALTER TABLE table_name COMPRESS FOR OLTP;
1

《3》列压缩

列压缩是指对表的列进行压缩，可以减少每列数据的存储空间，节省存储空间。 列压缩可以通过定义表的列时，指定压缩类型来实现。例如，以下SQL语句可以定义一列进行列压缩：

CREATE TABLE table_name (
    column_name data_type COMPRESS FOR QUERY HIGH
);
1
2
3

《4》原理

1. 压缩原理

Oracle的表压缩是通过使用一种称为Advanced Row Compression(ARC)的算法来实现的。这个算法可以在不影响表数据访问性能的情况下，压缩表的数据存储空间。ARC算法的基本原理是对表的数据进行扫描，找出重复的数据块，并使用一个指针来指向该数据块。当需要访问该数据块时，指针会指向该数据块，而不是存储该数据块的实际位置。这种方法可以减少存储空间，提高数据访问性能。

2. 压缩效果

表的压缩效果取决于多个因素，例如表的大小、表的数据类型、数据的重复性等。通常情况下，表的压缩可以将表的存储空间减少30％至50％。对于大型表而言，表的压缩可以显著减少存储空间，并提高数据访问性能。

3. 压缩使用注意事项

在对表进行压缩时，需要注意以下几点：

• 压缩对性能的影响

虽然表的压缩可以减少表的存储空间，但在压缩的过程中会增加CPU和I/O负载。因此，在对表进行压缩时，需要考虑到可能对性能造成的影响。

• 压缩类型的选择

在选择压缩类型时，需要根据表的特点进行选择。例如，如果表的主要操作是插入和更新操作，那么行压缩是最好的选择。如果表的主要操作是查询操作，那么列压缩是最好的选择。

• 压缩的优先级

在对表进行压缩时，需要考虑到表的使用频率和重要性。对于经常使用的和重要的表，可以优先对其进行压缩，以减少存储空间。

《5》压缩案例

以下是对一个包含10万条记录的表进行压缩前后的存储空间对比实例：

--创建表
CREATE TABLE test_table (
    id NUMBER,
    name VARCHAR2(50),
    address VARCHAR2(200),
    phone_number VARCHAR2(20)
);
--插入100000条随机数据
INSERT INTO test_table
SELECT rownum,dbms_random.string('A', 50), dbms_random.string('A', 200), dbms_random.string('N', 10)
FROM dual
CONNECT BY rownum <= 100000;

commit;

--查看表的大小
SELECT segment_name, sum(bytes) / 1024 /1024 AS size_mb
FROM user_segments
WHERE segment_name = 'TEST_TABLE'
GROUP BY segment_name;

--行压缩
ALTER TABLE test_table COMPRESS FOR OLTP;
--查看表的大小
SELECT segment_name, sum(bytes) / 1024 /1024 AS size_mb
FROM user_segments
WHERE segment_name = 'TEST_TABLE'
GROUP BY segment_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

压缩前的表大小为271MB，压缩后的表大小为153MB，压缩取得了较好的效果。

综上所述，Oracle中的表压缩可以将表的存储空间减少30％至50％，同时提高数据访问性能。在进行表的压缩时需要注意压缩类型、压缩对性能的影响、优先级等。

备注：

上述 ALTER TABLE 语句不生效时，可以采用如下语句：

ALTER TABLE test_table MOVE COMPRESS;
1

然后重建索引即可。

《6》 压缩类型说明

1. ALTER TABLE … COMPRESS FOR OLTP
   此方法对现有数据不压缩，对以后的DML语句相关数据进行OLTP压缩。

2. Online Redefinition (DBMS_REDEFINITION)
   对现有和以后的数据均压缩。使用DBMS_REDEFINITION可以在线对表进行操作，可以使用并行操作。分区表的global index是个例外，需要在线重定义之后重建索引。

3. ALTER TABLE … MOVE COMPRESS FOR OLTP
   对现有和以后的数据均压缩。在move过程中，会对表加排它（X）锁，DML操作会被阻塞，可以使用并行提高性能。move操作会导致索引失效，因此move之后需要重建索引。move操作可以改变segment的表空间。

4、Oracle索引重建

《1》索引重建指南

索引是数据库重要的组成部分，它可以提高查询的速度和效率。但是，随着数据量的增加和数据库使用时间的推移，索引也会出现问题，比如索引失效、性能下降等。此时，重建索引就是一个不错的解决方案。

Oracle有多种方法可以重建索引，如在线重建、离线重建等。下面我们将详细介绍这些方法，以及如何选择最适合您的重建方式。

《2》在线重建索引

在线重建可以在不停止数据库的情况下进行索引重建。Oracle提供了两种在线重建索引的方法：在线重建（Online Rebuild）和在线重构（Online Reorganize）。

• 在线重建：

在重建过程中，索引是可以被访问和使用的。

示例：

ALTER INDEX test_index REBUILD ONLINE;
1

• 在线重构：

索引被禁用，并创建新的索引，旧索引在重构过程中删除。此过程中，索引不可被使用。

示例：

ALTER INDEX test_index REORGANIZE ONLINE;
1

• 离线重建：

离线重建是在数据库停机期间进行的索引重建，相对于在线重建，离线重建更加彻底，可以更好地优化索引的性能。

在开始离线重建索引之前，建议先备份原始索引，以便于在出现问题时进行回滚。

示例：

DROP INDEX test_index;

CREATE INDEX test_index ON test_table (test_column);
1
2
3

《3》选择重建方式建议

不同的场景和需求，需要选择不同的重建方式。在大多数情况下，我们优先选择在线重建，因为它可以在不干扰数据库运行的情况下进行索引重建。

但是，在以下几种情况下，我们建议选择离线重建：

1. 索引数据非常大，而且需要完全优化。
2. 索引数据的修改频率很高。
3. 系统磁盘空间不够。

总结：

索引是数据库性能优化的关键之一，它的作用不容忽视。为了保障数据库的高效运行，我们需要定期检查索引情况并进行重建。在选择重建方式时，需要根据不同的情况和需求，选择最适合的方式进行索引重建。