oracle表空间临时表空间信息查询教程

这两天由于工作原因，一直跟oracle数据库打交道，踩了很多坑，现在写一些自己的感想，与大家分享，有不正确的地方，欢迎指正！

1、查询表空间信息

select   *   from   dba_tablespaces

2、查看用户和表空间的关系

select username,default_tablespace from user_users;

3、查询表空间大小，剩余量，利用率，包含临时表空间

SELECT
    *
FROM
    (
        SELECT
            A .tablespace_name,
            TO_CHAR (
                A .bytes / 1024 / 1024,
                '99,999.999'
            ) total_bytes,
            TO_CHAR (
                b.bytes / 1024 / 1024,
                '99,999.999'
            ) free_bytes,
            TO_CHAR (
                A .bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
                '99,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
    )

4、扩展临时表空间容量

alter database tempfile 'tempFilePath' resize 2048M;

5、扩展表空间容量

alter database datafile 'filePath' resize 4000m

6、查询表空间文件路径，包含临时表空间

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)"
  FROM DBA_DATA_FILES

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE(M)"
  FROM DBA_TEMP_FILES

7、查询表空间所在表大小情况

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

oracle表空间临时表空间信息查询教程

相关推荐