论坛 › oracle › q_non_partition_tab count

admin 发表于 2023-12-12 11:15:08

q_non_partition_tab count

SELECT c.name database
        ,count(CASE
                        WHEN round(a.bytes / 1024 / 1024 , 0) <= 2048
                                THEN 1
                        END) AS "0-2GB"
        ,count(CASE
                        WHEN round(a.bytes / 1024 / 1024 , 0) > 2048
                                AND round(a.bytes / 1024 / 1024 , 0) <= 10240
                                THEN 1
                        END) AS "2-10GB"
        ,count(CASE
                        WHEN round(a.bytes / 1024 / 1024 , 0) > 100240
                                THEN 1
                        END) AS ">10GB"
FROM cdb_segments a
        ,v$containers c
WHERE a.con_id = c.con_id
        AND a.OWNER NOT IN (
                'sys'
                ,'system'
                )
        AND a.segment_type = 'TABLE'
        AND a.segment_name NOT IN (
                SELECT table_name
                FROM cdb_tab_partitions
                )
GROUP BY c.name
/

admin 发表于 2024-1-9 21:17:22

SELECT OWNER,
        SUM(CASE
                        WHEN bytes < 1024 * 1024 * 1024
                                THEN 1
                        ELSE 0
                        END) AS "0-1GB count",
        round(SUM(CASE
                                WHEN bytes < 1024 * 1024 * 1024
                                        THEN bytes
                                ELSE 0
                                END) / (1024 * 1024 * 1024), 2) AS "0-1GB bytes",
        SUM(CASE
                        WHEN bytes >= 1024 * 1024 * 1024
                                AND bytes < 10 * 1024 * 1024 * 1024
                                THEN 1
                        ELSE 0
                        END) AS "1-10GB count",
        round(SUM(CASE
                                WHEN bytes >= 1024 * 1024 * 1024
                                        AND bytes < 10 * 1024 * 1024 * 1024
                                        THEN bytes
                                ELSE 0
                                END) / (1024 * 1024 * 1024), 2) AS "1-10GB bytes"
FROM dba_segments
WHERE segment_type = 'TABLE'
        AND partition_name IS NULL
GROUP BY OWNER;

admin 发表于 2024-1-17 09:07:41

--个数
SELECT '0-2GB' AS size_range,
    SUM(CASE WHEN bytes <= 2 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '2-5GB' AS size_range,
    SUM(CASE WHEN bytes > 2 * 1024 * 1024 * 1024 AND bytes <= 5 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '5-10GB' AS size_range,
    SUM(CASE WHEN bytes > 5 * 1024 * 1024 * 1024 AND bytes <= 10 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '10-15GB' AS size_range,
    SUM(CASE WHEN bytes > 10 * 1024 * 1024 * 1024 AND bytes <= 15 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '15-20GB' AS size_range,
    SUM(CASE WHEN bytes > 15 * 1024 * 1024 * 1024 AND bytes <= 20 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '20-25GB' AS size_range,
    SUM(CASE WHEN bytes > 20 * 1024 * 1024 * 1024 AND bytes <= 25 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '25-30GB' AS size_range,
    SUM(CASE WHEN bytes > 25 * 1024 * 1024 * 1024 AND bytes <= 30 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '30-35GB' AS size_range,
    SUM(CASE WHEN bytes > 30 * 1024 * 1024 * 1024 AND bytes <= 35 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '35-40GB' AS size_range,
    SUM(CASE WHEN bytes > 35 * 1024 * 1024 * 1024 AND bytes <= 40 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '>40GB' AS size_range,
    SUM(CASE WHEN bytes > 40 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID;

--大小
SELECT '0-2GB' AS size_range,
    round(SUM(CASE WHEN bytes <= 2 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '2-5GB' AS size_range,
    round(SUM(CASE WHEN bytes > 2 * 1024 * 1024 * 1024 AND bytes <= 5 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '5-10GB' AS size_range,
    round(SUM(CASE WHEN bytes > 5 * 1024 * 1024 * 1024 AND bytes <= 10 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '10-15GB' AS size_range,
    round(SUM(CASE WHEN bytes > 10 * 1024 * 1024 * 1024 AND bytes <= 15 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '15-20GB' AS size_range,
    round(SUM(CASE WHEN bytes > 15 * 1024 * 1024 * 1024 AND bytes <= 20 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '20-25GB' AS size_range,
    round(SUM(CASE WHEN bytes > 20 * 1024 * 1024 * 1024 AND bytes <= 25 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '25-30GB' AS size_range,
    round(SUM(CASE WHEN bytes > 25 * 1024 * 1024 * 1024 AND bytes <= 30 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '30-35GB' AS size_range,
    round(SUM(CASE WHEN bytes > 30 * 1024 * 1024 * 1024 AND bytes <= 35 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '35-40GB' AS size_range,
    round(SUM(CASE WHEN bytes > 35 * 1024 * 1024 * 1024 AND bytes <= 40 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
UNION ALL
SELECT '>40GB' AS size_range,
    round(SUM(CASE WHEN bytes > 40 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
FROM space_dba_segments
WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID;

查看完整版本: q_non_partition_tab count