6、各种空间查询

[TOC]
#### 一、Oracle查询表空间占用情况

使用以下SQL语句可以查询数据库中所有表空间的占用情况，包括表空间名、表空间大小、表空间剩余空间、表占用空间大小和已使用空间百分比。

```sql
SELECT a.tablespace_name,
       total,
       free,
       (total - free) AS used,
       ROUND((total - free) / total * 100, 2) || '%' AS usage_pct
FROM   (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free
          FROM dba_free_space
         GROUP BY tablespace_name) b
WHERE  a.tablespace_name = b.tablespace_name
ORDER BY used DESC;

```

#### 二、Oracle查询表占用的空间

使用以下SQL语句可以查询单个表占用的空间大小，包括表名、对象类型和占用空间大小。

```sql
SELECT segment_name "表名",
       segment_type "对象类型",
       SUM(bytes) / 1024 / 1024 "占用空间（MB）"
  FROM dba_extents
 WHERE segment_name = '表名'
 GROUP BY segment_name, segment_type;
```

#### 三、Oracle查询表空间使用情况

使用以下SQL语句可以查询表空间使用情况，包括每个表空间包含的数据文件所占用的文件名、字节数、块数和最大字节数。

```sql
SELECT tablespace_name,
       file_id,
       bytes,
       blocks,
       maxbytes
  FROM dba_data_files;
```

#### 四、Oracle查询每张表占用空间

使用以下SQL语句可以查询每张表占用的空间大小，包括表所有者、表名、对象类型和占用空间大小。

```sql
SELECT owner,
       segment_name,
       segment_type,
       SUM(bytes) / 1024 / 1024 "占用空间（MB）"
  FROM dba_segments
 WHERE owner = '表所有者'
 GROUP BY owner, segment_name, segment_type;
```

#### 五、Oracle表空间大小查询

使用以下SQL语句可以查询所有表和索引所占用的表空间大小信息。

```sql
SELECT tablespace_name,
       SUM(bytes) / 1024 / 1024 "占用空间（MB）"
  FROM dba_segments
 WHERE segment_type IN ('TABLE', 'INDEX')
 GROUP BY tablespace_name;
```

#### 六、Oracle查表空间大小

使用以下SQL语句可以查询单个表空间的大小，包括表空间名和占用空间大小。

```sql
SELECT tablespace_name,
       SUM(bytes) / 1024 / 1024 "占用空间（MB）"
  FROM dba_segments
 WHERE tablespace_name = '表空间名'
 GROUP BY tablespace_name;
```

#### 七、Oracle查询表空间大小

使用以下SQL语句可以查询所有表空间的名字和占用空间大小。

```sql
SELECT tablespace_name,
       SUM(bytes) / 1024 / 1024 "占用空间（MB）"
  FROM dba_data_files
 GROUP BY tablespace_name;
```

#### 八、查看Oracle各个表占空间

使用以下SQL语句可以查看Oracle各个表占用的空间大小，包括表所有者、表名、对象类型、已用空间和最大空间信息。

```sql
SELECT owner,
       segment_name,
       segment_type,
       ROUND(bytes / (1024 * 1024), 2) "已用空间（MB）",
       ROUND(maxbytes / (1024 * 1024), 2) "最大空间（MB）"
  FROM dba_segments
 WHERE owner = '表所有者'
 ORDER BY bytes DESC;
```

查看segment_type为"LOBSEGMENT"类型，所对应的表及字段实现：

```sql
SELECT table_name,
       column_name,
       segment_name 
  FROM dba_lobs 
 WHERE segment_name = 'segment_name名称';
```

#### 九、Oracle查看表空间大小

使用以下SQL语句可以查看表空间大小，包括表空间名、表空间大小、剩余空间、已用空间和最大空间信息。

```sql
SELECT tablespace_name,
       SUM(blocks) * 8192 / 1024 / 1024 "表空间大小（MB）",
       SUM(blocks) * 8192 / 1024 / 1024 - SUM(bytes) / 1024 / 1024 "剩余空间（MB）",
       SUM(bytes) / 1024 / 1024 "已用空间（MB）",
       SUM(maxbytes) / 1024 / 1024 "最大空间（MB）"
  FROM (SELECT tablespace_name,
               file_id,
               MAX(blocks) maxblocks,
               SUM(blocks) blocks
          FROM dba_extents
         GROUP BY tablespace_name, file_id),
       (SELECT tablespace_name, file_id, SUM(bytes) bytes
          FROM dba_free_space
         GROUP BY tablespace_name, file_id)
 WHERE tablespace_name NOT LIKE 'UNDO%'
   AND tablespace_name NOT LIKE 'TEMP%'
   AND tablespace_name NOT LIKE 'SYSAUX%'
   AND tablespace_name NOT LIKE 'SYSTEM%'
   AND tablespace_name NOT LIKE 'RMAN%'
   AND tablespace_name NOT LIKE 'FLASH%'
 GROUP BY tablespace_name;
```

#### 十、查询分区、删除分区

##### 查询所有表大小

```sql
SET linesize 200
SET pagesize 200
SELECT *
  FROM (SELECT segment_name,
               SUM(bytes) / 1024 / 1024 / 1024 total_mb,
               tablespace_name
          FROM dba_segments
         WHERE tablespace_name IN ('WLZY_DN')
         GROUP BY segment_name, tablespace_name
         ORDER BY 2 DESC)
 WHERE ROWNUM <= 20;
```

##### 删除分区

```sql
SELECT 'ALTER TABLE tabaname DROP PARTITION ' || partition_name || ';'
  FROM user_tab_partitions
 WHERE table_name = UPPER('tabaname');
-- AND partition_name LIKE '%20230%';
```

#### 十一、高水位

使用以下SQL语句可以查询表的高水位空间、真实使用空间、预留空间和浪费空间。

```sql
SELECT table_name,
       ROUND((blocks * 8) / 1024 / 1024, 2) AS "高水位空间",
       ROUND((num_rows * avg_row_len / 1024 / 1024 / 1024 ), 2) AS "真实使用空间",
       ROUND((blocks * 10 / 100) * 8 / 1024 / 1024, 2) AS "预留空间(pctfree) K",
       ROUND(
           (blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100) / 1024 / 1024,
           2
       ) AS "浪费空间"
  FROM user_tables
 WHERE temporary = 'N'
 ORDER BY "高水位空间" DESC;
```

#### 十二、查询ASM

使用以下SQL语句可以查询ASM磁盘组的总空间、剩余空间和剩余百分比。

```sql
SELECT group_number,
       name,
       total_mb / 1024 AS "总空间(G)",
       free_mb / 1024 AS "剩余空间(G)",
       ROUND(free_mb / total_mb * 100, 2) AS "剩余百分比"
  FROM v$asm_diskgroup;
```

以上SQL语句可以帮助数据库管理员查询和管理Oracle数据库的表空间和表占用空间情况，及时了解数据库的空间使用状态，并进行相应的维护和优化。

DBLOG

导航1234

最近发表

友情链接