Tailles et nombres d'informations sur les tables d'un schéma

**pcouas** · 27/11/2022, 14h12

Bonjour

J'utilise la requête suivante afin d'obtenir la taille des tables ainsi que le nombre d'enregistrements, mais le COUNT est en PL
Peut t'on faire plus efficace et moins long en temps de traitement ?Merci

PS
Je me suis basé sur celle ci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
taille des tables
 
CREATE OR REPLACE FUNCTION
c_to_f (p_tname in varchar2) RETURN NUMBER IS
l_columnValue number default NULL;
BEGIN
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
END;
/
 
SELECT * FROM (
SELECT
tablespace_name, 
owner, 
object_type, table_name, 
rtrim(to_char(ROUND(bytes)/1024/1024, '99990.00000'), '.') AS Megabytes,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS Tables_in_MB,
c_to_f(table_name) AS COUNT_RECORDS
FROM (
-- We can start this with Table size
SELECT 
tablespace_name,
owner, 
'TABLE' AS object_type,
segment_name AS table_name, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') --e are selecting the table,table partition and table
 
----subpartition
UNION ALL
 
--- We require to check for Indexes
 
 
SELECT 
s.tablespace_name,
i.owner, 
'Index' AS object_type,
i.table_name, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
-- Then for LOB
 
 
UNION ALL
 
 
SELECT 
s.tablespace_name,
l.owner, 
'LOB_COLUMN' AS object_type,
l.table_name, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
-- Get data of LOB INDEXES
UNION ALL
SELECT 
s.tablespace_name,
l.owner, 
'LOB_INDEX' AS object_type,
l.table_name, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('&schema_name')
)
ORDER BY COUNT_RECORDS DESC,Tables_in_MB DESC, OBJECT_TYPE DESC
 
/