Oracle Autonomous Database - Over-counting storage used?

Question

I was trying out the oracle's autonomous DB. Wanted to understand what is the fuss about oracle APEX services - which enables one to directly build apps on a db.

The DB comes with a bunch of system tables (oracle_maintained flag = y in all_tables).
It also contains the SSB sample dataset.
According to docs, sample dataset should not count towards usage.
However, I see 2.7 G usage for the fresh DB.
Further, after adding and deleting a sample APEX app - the usage is at 4.8 G.

Note: I have created a simple schema and one table with < 1000 rows.

Output of:

SELECT
    OWNER,
    SUM(BYTES) / 1024.0 / 1024.0 as SIZE_MB
FROM
    DBA_EXTENTS
GROUP BY
    ROLLUP(OWNER)
ORDER BY
    SIZE_MB DESC

"OWNER",            "SIZE_MB",          
"",                 168400.6875,   <- Total
"SSB",              166316.9375,   <- Sample (should not be counted)
"SYS",              1059.875,
"APEX_190200",      888.4375,
"MDSYS",            72.75,
"AUDSYS",           26.6875,
"APEX_200100",      13.4375,
"SH",               5.75,
... 
A few more rows with size < 5 MB

user_1156452 · Answer

When installing a sample app, APEX offers to create a workspace for you. Said workspaces lives in a SCHEMA, an Oracle USER account, as well. It will create that new for you if necessary.

When you remove your sample app, everything is 'dropped' - however, if you created a new USER/SCHEMA to hold your app, that is NOT dropped.

Additionally, the tables when dropped, are NOT purged.

If you go peak into the Recycle Bin, you can see the 'remnants' of your sample application.

You can purge that schema's recycle bin, or even better, just DROP the user entirely.

The data available to you, e.g. SSB, that's stored in an Oracle Managed schema, will show in any database report, but you won't be charged for it.

The amount of data you're observing seems more than a Sample APEX app would ever account for.

If you query this, you'll see all of the SEGMENTS (tables, partitions, LOBs) in your DB, sorted by size DESC.

select OWNER                "Owner",
       TABLESPACE_NAME      "Tablespace",
       SEGMENT_NAME         "Segment",
       EXTENTS              "Extents",
       BYTES / 1024 / 1024      "Megabytes"
  from SYS.DBA_SEGMENTS
 where UPPER(
              SUBSTR(
                     SEGMENT_NAME,
                     1,
                     4
              )
       ) != 'BIN$' and
       UPPER(
              SUBSTR(
                     SEGMENT_NAME,
                     1,
                     3
              )
       ) != 'DR$' and
       ( :TABLESPACE_NAME is null or
         INSTR(
                LOWER(TABLESPACE_NAME),
                LOWER(
                        :TABLESPACE_NAME
                 )
         ) > 0 )
 order by OWNER,
          EXTENTS desc,
          TABLESPACE_NAME,
          SEGMENT_NAME

Drop and purge as necessary.

Oracle Autonomous Database - Over-counting storage used?

1 Answers