q_non_partition_tab count

admin

1. SELECT c.name database
   
2.         ,count(CASE
   
3.                         WHEN round(a.bytes / 1024 / 1024 , 0) <= 2048
   
4.                                 THEN 1
   
5.                         END) AS "0-2GB"
   
6.         ,count(CASE
   
7.                         WHEN round(a.bytes / 1024 / 1024 , 0) > 2048
   
8.                                 AND round(a.bytes / 1024 / 1024 , 0) <= 10240
   
9.                                 THEN 1
   
10.                         END) AS "2-10GB"
    
11.         ,count(CASE
    
12.                         WHEN round(a.bytes / 1024 / 1024 , 0) > 100240
    
13.                                 THEN 1
    
14.                         END) AS ">10GB"
    
15. FROM cdb_segments a
    
16.         ,v$containers c
    
17. WHERE a.con_id = c.con_id
    
18.         AND a.OWNER NOT IN (
    
19.                 'sys'
    
20.                 ,'system'
    
21.                 )
    
22.         AND a.segment_type = 'TABLE'
    
23.         AND a.segment_name NOT IN (
    
24.                 SELECT table_name
    
25.                 FROM cdb_tab_partitions
    
26.                 )
    
27. GROUP BY c.name
    
28. /

复制代码

admin

1. SELECT OWNER,
   
2.         SUM(CASE
   
3.                         WHEN bytes < 1024 * 1024 * 1024
   
4.                                 THEN 1
   
5.                         ELSE 0
   
6.                         END) AS "0-1GB count",
   
7.         round(SUM(CASE
   
8.                                 WHEN bytes < 1024 * 1024 * 1024
   
9.                                         THEN bytes
   
10.                                 ELSE 0
    
11.                                 END) / (1024 * 1024 * 1024), 2) AS "0-1GB bytes",
    
12.         SUM(CASE
    
13.                         WHEN bytes >= 1024 * 1024 * 1024
    
14.                                 AND bytes < 10 * 1024 * 1024 * 1024
    
15.                                 THEN 1
    
16.                         ELSE 0
    
17.                         END) AS "1-10GB count",
    
18.         round(SUM(CASE
    
19.                                 WHEN bytes >= 1024 * 1024 * 1024
    
20.                                         AND bytes < 10 * 1024 * 1024 * 1024
    
21.                                         THEN bytes
    
22.                                 ELSE 0
    
23.                                 END) / (1024 * 1024 * 1024), 2) AS "1-10GB bytes"
    
24. FROM dba_segments
    
25. WHERE segment_type = 'TABLE'
    
26.         AND partition_name IS NULL
    
27. GROUP BY OWNER;

复制代码

admin

1. --个数
   
2. SELECT '0-2GB' AS size_range,
   
3.     SUM(CASE WHEN bytes <= 2 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
   
4. FROM space_dba_segments
   
5. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
   
6. UNION ALL
   
7. SELECT '2-5GB' AS size_range,
   
8.     SUM(CASE WHEN bytes > 2 * 1024 * 1024 * 1024 AND bytes <= 5 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
   
9. FROM space_dba_segments
   
10. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
11. UNION ALL
    
12. SELECT '5-10GB' AS size_range,
    
13.     SUM(CASE WHEN bytes > 5 * 1024 * 1024 * 1024 AND bytes <= 10 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
14. FROM space_dba_segments
    
15. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
16. UNION ALL
    
17. SELECT '10-15GB' AS size_range,
    
18.     SUM(CASE WHEN bytes > 10 * 1024 * 1024 * 1024 AND bytes <= 15 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
19. FROM space_dba_segments
    
20. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
21. UNION ALL
    
22. SELECT '15-20GB' AS size_range,
    
23.     SUM(CASE WHEN bytes > 15 * 1024 * 1024 * 1024 AND bytes <= 20 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
24. FROM space_dba_segments
    
25. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
26. UNION ALL
    
27. SELECT '20-25GB' AS size_range,
    
28.     SUM(CASE WHEN bytes > 20 * 1024 * 1024 * 1024 AND bytes <= 25 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
29. FROM space_dba_segments
    
30. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
31. UNION ALL
    
32. SELECT '25-30GB' AS size_range,
    
33.     SUM(CASE WHEN bytes > 25 * 1024 * 1024 * 1024 AND bytes <= 30 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
34. FROM space_dba_segments
    
35. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
36. UNION ALL
    
37. SELECT '30-35GB' AS size_range,
    
38.     SUM(CASE WHEN bytes > 30 * 1024 * 1024 * 1024 AND bytes <= 35 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
39. FROM space_dba_segments
    
40. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
41. UNION ALL
    
42. SELECT '35-40GB' AS size_range,
    
43.     SUM(CASE WHEN bytes > 35 * 1024 * 1024 * 1024 AND bytes <= 40 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
44. FROM space_dba_segments
    
45. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
46. UNION ALL
    
47. SELECT '>40GB' AS size_range,
    
48.     SUM(CASE WHEN bytes > 40 * 1024 * 1024 * 1024 THEN 1 ELSE 0 END) AS table_count
    
49. FROM space_dba_segments
    
50. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID;
    
51. 
    
52. --大小
    
53. SELECT '0-2GB' AS size_range,
    
54.     round(SUM(CASE WHEN bytes <= 2 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
    
55. FROM space_dba_segments
    
56. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
57. UNION ALL
    
58. SELECT '2-5GB' AS size_range,
    
59.     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
    
60. FROM space_dba_segments
    
61. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
62. UNION ALL
    
63. SELECT '5-10GB' AS size_range,
    
64.     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
    
65. FROM space_dba_segments
    
66. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
67. UNION ALL
    
68. SELECT '10-15GB' AS size_range,
    
69.     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
    
70. FROM space_dba_segments
    
71. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
72. UNION ALL
    
73. SELECT '15-20GB' AS size_range,
    
74.     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
    
75. FROM space_dba_segments
    
76. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
77. UNION ALL
    
78. SELECT '20-25GB' AS size_range,
    
79.     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
    
80. FROM space_dba_segments
    
81. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
82. UNION ALL
    
83. SELECT '25-30GB' AS size_range,
    
84.     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
    
85. FROM space_dba_segments
    
86. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
87. UNION ALL
    
88. SELECT '30-35GB' AS size_range,
    
89.     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
    
90. FROM space_dba_segments
    
91. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
92. UNION ALL
    
93. SELECT '35-40GB' AS size_range,
    
94.     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
    
95. FROM space_dba_segments
    
96. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID
    
97. UNION ALL
    
98. SELECT '>40GB' AS size_range,
    
99.     round(SUM(CASE WHEN bytes > 40 * 1024 * 1024 * 1024 THEN bytes ELSE 0 END) / (1024*1024*1024),2) AS sum_gb
    
100. FROM space_dba_segments
     
101. WHERE partition_name IS NULL AND segment_type = 'TABLE' and dbid=:P20_DBID;

复制代码