oracle表空间管理

-- 查看表空间总大小和使用率

SELECT 
    a.tablespace_name,
    total_mb,
    free_mb,
    (total_mb - free_mb) used_mb,
    round((1 - free_mb/total_mb)*100, 2) pct_used
FROM (
    SELECT tablespace_name, 
           sum(bytes)/1024/1024 total_mb
    FROM dba_data_files
    GROUP BY tablespace_name
) a,
(
    SELECT tablespace_name, 
           sum(bytes)/1024/1024 free_mb
    FROM dba_free_space
    GROUP BY tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name;


TABLESPACE   TOTAL_MB    FREE_MB    USED_MB   PCT_USED
---------- ---------- ---------- ---------- ----------
SYSTEM           1040     3.0625  1036.9375      99.71
SYSAUX            660    36.1875   623.8125      94.52
UNDOTBS1           25      4.375     20.625       82.5
USERS               7      .9375     6.0625      86.61

-- 查看表空间使用情况

col file_name for a30
col tablespace_name for A10

SELECT 
    tablespace_name,
    file_name,
    bytes/1024/1024 MB,
    maxbytes/1024/1024/1024 max_GB,
    autoextensible,
    status
FROM dba_data_files
ORDER BY tablespace_name;

TABLESPACE FILE_NAME                              MB     MAX_GB AUT STATUS
---------- ------------------------------ ---------- ---------- --- ---------
SYSAUX     /opt/oracle/oradata/FREE/sysau        660      32768 YES AVAILABLE
           x01.dbf

SYSTEM     /opt/oracle/oradata/FREE/syste       1040      32768 YES AVAILABLE
           m01.dbf

UNDOTBS1   /opt/oracle/oradata/FREE/undot         25      32768 YES AVAILABLE
           bs01.dbf

USERS      /opt/oracle/oradata/FREE/users          7      32768 YES AVAILABLE
           01.dbf

 二、-- 查看表空间类型

SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES;

TABLESPACE CONTENTS              EXTENT_MAN ALLOCATIO
---------- --------------------- ---------- ---------
SYSTEM     PERMANENT             LOCAL      SYSTEM
SYSAUX     PERMANENT             LOCAL      SYSTEM
UNDOTBS1   UNDO                  LOCAL      SYSTEM
TEMP       TEMPORARY             LOCAL      UNIFORM
USERS      PERMANENT             LOCAL      SYSTEM