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
/ |
