| Other way to get the Schema Size other than dba_segments view [message #689065] |
Wed, 06 September 2023 17:44  |
wtolentino
Messages: 420
Registered: March 2005
|
Senior Member |
|
|
Basically, whenever there is a request from our application development group to get the size of the schema. I always use this query:
select ds.owner, sum(ds.bytes)/1024/1024/1024 as "Size in GB"
from dba_segments ds
group by ds.owner
order by ds.owner;
Thank you,
Warren
|
|
|
|
|
|
|
|
|
|
| Re: Other way to get the Schema Size other than dba_segments view [message #689071 is a reply to message #689070] |
Thu, 07 September 2023 18:45  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SYS@XE_21.3.0.0.0> CREATE OR REPLACE FUNCTION get_total_clob_size
2 (p_owner IN VARCHAR2)
3 RETURN NUMBER
4 AS
5 v_size NUMBER := 0;
6 v_total_size NUMBER := 0;
7 e_not_exist EXCEPTION;
8 e_no_privs EXCEPTION;
9 PRAGMA EXCEPTION_INIT (e_not_exist, -00942);
10 PRAGMA EXCEPTION_INIT (e_no_privs, -01031);
11 BEGIN
12 FOR i IN
13 (SELECT column_name, owner, table_name
14 FROM dba_tab_columns
15 WHERE UPPER (owner) = UPPER (p_owner)
16 AND data_type = 'CLOB'
17 AND owner NOT IN ('SYS', 'SYSTEM')
18 -- AND any other criteria you can use to limit it to xml or pdf
19 )
20 LOOP
21 BEGIN
22 EXECUTE IMMEDIATE
23 'SELECT SUM(DBMS_LOB.GETLENGTH("' || i.column_name || '"))/1024/1024/1024
24 FROM "' || i.owner || '"."' || i.table_name || '"'
25 INTO v_size;
26 EXCEPTION
27 WHEN e_not_exist THEN v_size := 0;
28 DBMS_OUTPUT.PUT_LINE (i.owner || '.' || i.table_name || ' does not exist');
29 WHEN e_no_privs THEN v_size := 0;
30 DBMS_OUTPUT.PUT_LINE (i.owner || '.' || i.table_name || ' fsinsufficient privileges');
31 END;
32 v_total_size := v_total_size + NVL(v_size,0);
33 END LOOP;
34 RETURN v_total_size;
35 END get_total_clob_size;
36 /
Function created.
SYS@XE_21.3.0.0.0> SHOW ERRORS
No errors.
SYS@XE_21.3.0.0.0> column owner format a30
SYS@XE_21.3.0.0.0> select t1.owner, t1."Size in GB", nvl (t2."Clobs in GB", 0) as "Clobs in GB",
2 t1."Size in GB" - nvl (t2."Clobs in GB", 0) as "without clobs"
3 from (select ds.owner,
4 sum(ds.bytes)/1024/1024/1024 as "Size in GB"
5 from dba_segments ds
6 group by ds.owner) t1,
7 (select owner, get_total_clob_size (owner) as "Clobs in GB"
8 from (select distinct owner
9 from dba_tab_columns
10 where data_type = 'CLOB')) t2
11 where t1.owner = t2.owner(+)
12 order by t1.owner
13 /
OWNER Size in GB Clobs in GB without clobs
------------------------------ ---------- ----------- -------------
AUDSYS .028381348 .000062657 .028318691
C##SCOTT .082275391 .000011489 .082263902
CTXSYS .002990723 0 .002990723
DBSNMP .000183105 0 .000183105
DVSYS .004455566 0 .004455566
GSMADMIN_INTERNAL .001098633 0 .001098633
LBACSYS .000305176 0 .000305176
MDSYS .167236328 .030518234 .136718094
OJVMSYS .000366211 0 .000366211
ORDDATA .001281738 0 .001281738
ORDSYS .000366211 0 .000366211
OUTLN .000549316 0 .000549316
SYS 1.75708008 0 1.75708008
SYSTEM .018310547 0 .018310547
WMSYS .007141113 0 .007141113
XDB .099609375 0 .099609375
16 rows selected.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
