Home » RDBMS Server » Server Administration » Other way to get the Schema Size other than dba_segments view (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Other way to get the Schema Size other than dba_segments view [message #689065] Wed, 06 September 2023 17:44 Go to next message
wtolentino
Messages: 388
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;
There was a recent request that needs to exclude the PDF/XML from the tables. Are there any other ways other than the dba_segments? Please advise.

Thank you,
Warren
Re: Other way to get the Schema Size other than dba_segments view [message #689066 is a reply to message #689065] Thu, 07 September 2023 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How are stored these data?

Re: Other way to get the Schema Size other than dba_segments view [message #689069 is a reply to message #689066] Thu, 07 September 2023 12:09 Go to previous messageGo to next message
wtolentino
Messages: 388
Registered: March 2005
Senior Member
I think it's thru the clob if I am correct.
Re: Other way to get the Schema Size other than dba_segments view [message #689070 is a reply to message #689069] Thu, 07 September 2023 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can then exclude the CLOB segments from the query.

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: Other way to get the Schema Size other than dba_segments view [message #689072 is a reply to message #689071] Fri, 08 September 2023 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not consistent, one side (dba_segments) you count segment size and the other side (get_total_clob_size) you count data size.

Maybe just something like:
SQL> select owner, sum(bytes)/1024/1024/1024 as "Size in GB"
  2  from dba_segments
  3  where (owner,segment_name) not in
  4        (select owner, segment_name from dba_lobs)
  5  group by owner
  6  order by owner
  7  /
OWNER                          Size in GB
------------------------------ ----------
APEX_030200                    .071166992
APPQOSSYS                       .00012207
CTXSYS                         .003723145
DBSNMP                         .000549316
DMUSER                         .009216309
EXFSYS                         .003662109
FIXXXER                        .000244141
HR                             .001525879
IX                             .001525879
MC_RMAN                        .000915527
MDSYS                          .022705078
MICHEL                         5.70141602
MICHEL2                        .000061035
OE                             .007873535
OLAPSYS                        .008605957
OPS$MICHEL                     .000366211
ORDDATA                        .010498047
ORDSYS                         .000427246
OUTLN                          .000488281
PERFSTAT                       .108154297
PM                             .001464844
SCOTT                          .002197266
SH                             .180603027
SYS                            1.25653076
SYSMAN                         .049682617
SYSTEM                          .08001709
TEST                           .000244141
WATCHER                        .000427246
WMSYS                          .002929688
XDB                            .047485352
Re: Other way to get the Schema Size other than dba_segments view [message #689073 is a reply to message #689072] Fri, 08 September 2023 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, DBMS_LOB.GETLENGTH give you the number of characters not the number of bytes.

Re: Other way to get the Schema Size other than dba_segments view [message #689526 is a reply to message #689073] Mon, 05 February 2024 09:43 Go to previous message
wtolentino
Messages: 388
Registered: March 2005
Senior Member
Thank you all.
Previous Topic: DBMS_SCHEDULER - External Destination Jobs
Next Topic: Grants of Select Privilege Thru Stored Procedure
Goto Forum:
  


Current Time: Tue Feb 20 15:14:07 CST 2024