Oracle表大小掌握小技巧（oracle中表部分大小）

Oracle表大小掌握小技巧

Oracle表大小是一个重要的指标，在优化数据库性能、估算存储需求、规划备份恢复策略等方面起着关键作用。本文将介绍一些小技巧，帮助管理员和开发人员掌握Oracle表大小。

1. 查询表大小

查询表大小的最简单方式是使用Oracle自带的用户界面工具——SQL Developer。打开SQL Developer，连接需要查询表大小的数据库，点击左侧导航栏中的“Tables”选项卡，在表列表中右键点击目标表，选择“Quick DDL”→“Custom DDL”，在弹出界面中可以看到完整的DDL语句，其中包括表大小信息，如下图所示：


上图中的“TABLESPACE USERS”表示该表所属的表空间名称，“PCTFREE 10”表示空闲空间比例，即每个块中预留10%的空间供未来使用，“PCTUSED 40”表示块被占用的比例，即块中的数据占用40%的空间，“INITRANS 1”表示每个块的事务槽数量。

如果在工具栏中选择“SQL”编辑器，可以使用以下SQL查询语句查询需要的表的大小：

“`sql

SELECT SUM(bytes)/1024/1024 AS MB

FROM user_segments

WHERE segment_type = ‘TABLE’

AND segment_name = ‘表名’;

其中，“user_segments”表示当前用户的数据库片段，“segment_type = 'TABLE'”用于过滤只查询表，“segment_name = '表名'”用于指定需要查询的表名，查询结果以MB为单位。

2. 规划表分区

在数据量庞大的情况下，将表划分为若干个分区可以提高查询性能，同时也有利于备份恢复和维护。Oracle提供了多种分区方式，如按时间、按范围、按哈希等，根据不同业务需求可以灵活应用。例如，以下SQL语句将一个按时间分区的表创建在用户“TEST”下，将按每个月分为一个区：

```sql
CREATE TABLE TEST.TABLE_NAME (
  ID NUMBER(10) NOT NULL,
  CREATE_DATE TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,
  NAME VARCHAR2(50 CHAR),
  CONSTRNT PK_TABLE_NAME PRIMARY KEY (ID,CREATE_DATE)
)
PARTITION BY RANGE (CREATE_DATE) 
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION p0 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD')));

上述语句中，“PARTITION BY RANGE (CREATE_DATE)”表示按时间范围进行分区，“INTERVAL (NUMTOYMINTERVAL(1,’MONTH’))”表示每个分区为每个月的时间段，“PARTITION p0 VALUES LESS THAN (TO_DATE(‘2019-01-01′,’YYYY-MM-DD’))”表示第一个分区小于2019年1月1日。使用分区后，查询特定时间范围内的数据时只需要访问特定的分区，大大提高了查询速度。

3. 使用压缩技术

Oracle提供多种表空间压缩技术，包括基于行的压缩和基于列的压缩。基于行的压缩适用于重复数据较多的表，能够将数据压缩至原大小的1/4左右；而基于列的压缩则是将相同列的数据放在一起，从而提高压缩率。使用表空间压缩技术可以减少存储需求，降低备份恢复时间。

以下是使用Oracle的基于行压缩技术的示例：

“`sql

CREATE TABLE TEST.TABLE_NAME_PLUS

AS SELECT /*+ COMPRESS */ *

FROM TEST.TABLE_NAME;

“/*+ COMPRESS */”是SQL的提示语句，表示对于查询结果进行基于行的压缩。

4. 定期清理无用数据

有些表数据的过期时间比较短、增长速度又较快，如果不及时进行清理，可能会导致表空间占满。为了解决这个问题，可以定期清理无用数据。常见的清理方式包括手动DELETE语句和自动任务（如定期清理7天前的数据）。以下是一个自动清理脚本的示例：

```sql
BEGIN
  DBMS_SCHEDULER.create_job (
   job_name           => 'CLEANUP_JOB',
   job_type           => 'PLSQL_BLOCK',
   job_action         => 'BEGIN DELETE FROM TEST.TABLE_NAME WHERE CREATE_DATE 
   start_date         => SYSTIMESTAMP,
   repeat_interval    => 'FREQ=DLY; BYHOUR=1',
   enabled            => TRUE,
   comments           => 'Cleanup data of table after 7 days automatically.'
 );
END;

以上示例中，“DBMS_SCHEDULER.create_job”用于创建一个定期运行的作业，“repeat_interval => ‘FREQ=DLY; BYHOUR=1’”表示每天1点执行一次。

5. 合理规划表空间

规划表空间是Oracle数据库管理的关键之一，有效的表空间规划可以提高性能、减少备份恢复时间、避免数据损坏等问题。在规划表空间时，需要评估数据量、并发查询量、备份恢复策略等多个因素，并根据业务需求进行有针对性的设置。例如，对于I/O密集型的应用，可以将数据和索引分别放在不同的表空间中，以提高查询效率。以下示例中演示了如何创建一个数据表和索引表各自的表空间：

“`sql

CREATE TABLESPACE DATA_TS

DATAFILE ‘/u01/oradata/ORCL/data01.dbf’

SIZE 50M

AUTOEXTEND ON NEXT 10M

MAXSIZE 200M;

CREATE TABLESPACE INDEX_TS

DATAFILE ‘/u01/oradata/ORCL/index01.dbf’

SIZE 20M

AUTOEXTEND ON NEXT 5M

MAXSIZE 100M;

CREATE TABLE TEST.TABLE_NAME (

ID NUMBER(10) NOT NULL,

CREATE_DATE TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL,

NAME VARCHAR2(50 CHAR),

CONSTRNT PK_TABLE_NAME PRIMARY KEY (ID,CREATE_DATE)

)

TABLESPACE DATA_TS;

CREATE INDEX TEST.TABLE_NAME_NAME_IDX

ON TEST.TABLE_NAME(NAME)

TABLESPACE INDEX_TS;

以上示例中，“DATA_TS”和“INDEX_TS”分别为数据表和索引表的表空间名字，“SIZE”表示初始大小，“AUTOEXTEND”表示自动增长，“MAXSIZE”表示最大容量。在创建数据表和索引表时，可以通过“TABLESPACE”参数指定对应的表空间。

总结

表大小是Oracle数据库管理中的一个关键问题，本文介绍了一些掌握Oracle表大小的小技巧，包括查询表大小、规划表分区、使用压缩技术、定期清理无用数据、合理规划表空间等。这些技巧不仅可以提高性能，同时也有利于减少备份恢复时间和避免数据损坏。