ORACLE-表空间维护

 日常维护数据库和统计数据信息时，可能涉及到一些表空间相关的查询和操作；表空间的维护，表空间和临时表空间使用情况；表或索引占用空间大小等。表空间的大小，根据实际情况进行配置。

 

1.创建表空间

create tablespace tablespace_name datafile 'file_dir' size 20480m autoextend on next 500m maxsize 25000m extent management local segment space management auto

    指定表空见名称：tablespace_name，数据文件地址：file_dir。初始大小：20G，增长长度：500M，最大大小约：25G。

2.表空间扩容

Alter tablespace tablespace_name add datafile 'file_dir' size 20480m autoextend on next 500m maxsize 25000m

    扩容指定表空间：tablespace_name，扩容文件位置file_dir，初始大小：20G，增长长度：500M，最大大小约：25G。

    文件位置，大小可以根据实际情况进行设置。

3.临时表空间扩容

Alter tablespace temp_name add tempfile 'temp_file' size 10240m

    扩容临时表空间：temp_name，临时表控件文件：temp_file，大小10G

4.创建临时表空间

create temporary tablespace TEMP TEMPFILE 'temp_file' SIZE 10240M ;

    创建临时表空间：TEMP，临时表控件文件：temp_file，大小10G

5.删除临时表空间

drop tablespace temp including contents and datafiles

6.修改临时表空间

alter database default temporary tablespace temp;

7.回缩临时表空间

ALTER TABLESPACE TEMP SHRINK SPACE; 

8.查看表空间和临时表空间使用情况

SELECT *
  FROM (SELECT A.TABLESPACE_NAME,
               TO_CHAR(A.BYTES / 1024 / 1024, '9,999,999.999') TOTAL_BYTES,
               TO_CHAR(B.BYTES / 1024 / 1024, '999,999.999') FREE_BYTES,
               TO_CHAR(A.BYTES / 1024 / 1024 - B.BYTES / 1024 / 1024,
                       '999,999.999') USE_BYTES,
               TO_CHAR((1 - B.BYTES / A.BYTES) * 100, '99.99') || '%' USE
          FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) B
         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
        UNION ALL
        SELECT C.TABLESPACE_NAME,
               TO_CHAR(C.BYTES / 1024 / 1024, '99,999.999') TOTAL_BYTES,
               TO_CHAR((C.BYTES - D.BYTES_USED) / 1024 / 1024, '99,999.999') FREE_BYTES,
               TO_CHAR(D.BYTES_USED / 1024 / 1024, '99,999.999') USE_BYTES,
               TO_CHAR(D.BYTES_USED * 100 / C.BYTES, '99.99') || '%' USE
          FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
                  FROM DBA_TEMP_FILES
                 GROUP BY TABLESPACE_NAME) C,
               (SELECT TABLESPACE_NAME, SUM(BYTES_CACHED) BYTES_USED
                  FROM V$TEMP_EXTENT_POOL
                 GROUP BY TABLESPACE_NAME) D
         WHERE C.TABLESPACE_NAME = D.TABLESPACE_NAME)

9.消耗临时表空间SQL

SELECT SE.USERNAME,
       SE.SID,
       SU.EXTENTS,
       SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)) AS SPACE,
       TABLESPACE,
       SEGTYPE,
       SQL_TEXT
  FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE, V$SQL S
 WHERE P.NAME = 'db_block_size'
   AND SU.SESSION_ADDR = SE.SADDR
   AND S.HASH_VALUE = SU.SQLHASH
   AND S.ADDRESS = SU.SQLADDR
 ORDER BY SE.USERNAME, SE.SID

10.表占文件大小

SELECT TABLESPACE_NAME,
       TO_CHAR(SUM(BYTES) / (1024 * 1024), '999G999D999') CNT_MB
  FROM DBA_EXTENTS
 WHERE OWNER = '用户'
   AND SEGMENT_NAME = '表或索引等对象名'
   AND SEGMENT_TYPE LIKE 'TABLE%' 
 GROUP BY TABLESPACE_NAME;

    OWNER 用户；SEGMENT_NAME 表明；SEGMENT_TYPE 类型 表 或 索引 等

11.数据文件使用

SELECT A.FILE_ID "FileNo",
       A.TABLESPACE_NAME "Tablespace_name",
       A.BYTES "Bytes",
       A.BYTES - SUM(NVL(B.BYTES, 0)) "Used",
       SUM(NVL(B.BYTES, 0)) "Free",
       SUM(NVL(B.BYTES, 0)) / A.BYTES * 100 "%free"
  FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
 WHERE A.FILE_ID = B.FILE_ID(+)
 GROUP BY A.TABLESPACE_NAME, A.FILE_ID, A.BYTES
 ORDER BY A.TABLESPACE_NAME;