一个查看Oracle表和表空间存储分配的示例

-- 查询Oracle表和表空间的存储分配情况
SELECT
    a.tablespace_name AS "表空间名",
    ROUND(SUM(bytes) / 1024 / 1024, 2) AS "总大小(MB)",
    ROUND(SUM(maxbytes) / 1024 / 1024, 2) AS "最大大小(MB)"
FROM
    dba_data_files a
GROUP BY
    a.tablespace_name;
 
-- 查询Oracle表空间中各个数据文件的存储分配情况
SELECT
    a.tablespace_name AS "表空间名",
    file_id AS "文件ID",
    file_name AS "文件名",
    round(bytes / 1024 / 1024, 2) AS "大小(MB)",
    round(maxbytes / 1024 / 1024, 2) AS "最大大小(MB)"
FROM
    dba_data_files a
ORDER BY
    a.tablespace_name,
    file_id;
 
-- 查询Oracle表空间的使用情况
SELECT
    df.tablespace_name AS "表空间名",
    ROUND(SUM(df.bytes) / 1024 / 1024, 2) AS "已分配大小(MB)",
    ROUND(SUM(free.bytes) / 1024 / 1024, 2) AS "已使用大小(MB)",
    ROUND(SUM(free.bytes) / SUM(df.bytes) * 100, 2) AS "使用率%"
FROM
    dba_free_space free
JOIN
    dba_data_files df ON free.tablespace_name = df.tablespace_name
GROUP BY
    df.tablespace_name;

这个示例展示了如何查询Oracle数据库中表空间的总体分配、数据文件分配以及使用情况。这些查询可以帮助数据库管理员监控和管理Oracle数据库的存储资源。