Database storage

Files that are not in object storage are kept in SecureFile LOBs, and full-text search keeps an Oracle Text index next to them. This page is for the DBA who looks after that space: what to prepare before installing, how to put documents in tablespaces of their own, and how to get space back once files have left the database.

In short:

• Give the ADM schema an ASSM default tablespace, and use ASSM for any tablespace you set aside for documents.
• If you want documents in their own tablespaces, set that up right after the install. Later it needs a maintenance window.
• Moving files to object storage does not shrink the database by itself. Run the reclaim script afterwards.

Before you install

ADM’s document tables need automatic segment space management (ASSM). That applies to the ADM schema’s default tablespace and to any tablespace you set aside for documents later. Locally managed tablespaces use ASSM unless they were created otherwise, so this usually holds. It matters on older databases, where a tablespace may still be on manual segment space management.

select tablespace_name
     , segment_space_management -- must be AUTO
  from dba_tablespaces
 where tablespace_name = (select default_tablespace
                            from dba_users
                           where username = 'ADM');

If it is not, the install cannot create adm_document_versions and the log shows ORA-43853. Give the schema a different default tablespace and install again.

What takes up the space

Segment	Holds	Grows with
adm_document_versions.file_content	The file of every version stored in the database.	Every upload and every new version. Emptied when a version moves to object storage.
adm_document_versions.index_content	The text extracted from each file, for full-text search and the AI Pack.	Every version whose text was extracted. Plain text, typically a small fraction of the file.
adm_indexing_queue.file_blob	A short-lived copy of an object-storage file waiting for text extraction. Files stored in the database are not copied.	Removed once the file is indexed.
adm_upload_staging.content	Uploads in progress.	Removed once the upload completes, or after FS_UPLOAD_STAGING_TTL_HOURS (24 by default).
DR$ADM_DOCUMENT_VERSIONS_OS_CONTENT_CTX$*	The full-text index, on index_content.	The latest version of each document.
ADM_AI_RAG_VEC_*	AI Pack only: the embeddings of each RAG collection, created at runtime with default storage.	The number of chunks in the collection.

Everything else ADM stores is ordinary rows, and small next to these.

For sizing:

• ADM keeps every version until the whole document is purged. There is no version limit, trashed documents stay until the trash is emptied (CLEAN_TRASH_DAYS, 30 by default), and archived documents stay for good. file_content grows with the number of versions, not documents.
• The full-text index covers only the latest version of each document. A superseded version keeps its extracted text in index_content, but leaves the index.
• The index does not shrink straight away when versions are superseded or purged. The daily job optimizes it, which removes those entries.

LOB settings

ADM creates its LOBs with these settings:

LOB	Settings
file_content, index_content	securefile (nocache logging retention auto)
adm_indexing_queue.file_blob	securefile (nocache nologging retention none)
adm_upload_staging.content	securefile (nocache nologging retention none)

• NOCACHE: downloading documents does not push the rest of the database out of the buffer cache.
• LOGGING on everything that cannot be recreated. The two short-lived LOBs are NOLOGGING; a database in FORCE LOGGING mode, which a Data Guard primary should be, logs them anyway.
• RETENTION NONE on the two short-lived LOBs, so their space is reusable as soon as a row is gone.
• No compression and no deduplication. Both are part of the Advanced Compression option, which is licensed separately. If you hold that licence, you can turn them on yourself, for example alter table adm_document_versions modify lob (file_content) (compress low). file_content is the only LOB where that pays off, and even there most documents (PDF, Office) are compressed already.
• No tablespace is named: every LOB goes to the schema’s default tablespace until you move it (see below). Any of these settings can be changed with alter table ... modify lob (...).

If you restore from a backup, or switch over to a standby that was not in FORCE LOGGING mode, rows in the two NOLOGGING LOBs may be unreadable:

• Uploads in progress fail. They are removed after FS_UPLOAD_STAGING_TTL_HOURS; the user uploads the file again.
• Files waiting for text extraction fail until they are marked permanently unindexable. They do not come back on their own: requeue them with adm_storage_index_api.requeue_failed_index, which reads the file from storage again.

Upgraded from 0.1.4 or earlier?

Those installations have COMPRESS MEDIUM on index_content and adm_indexing_queue.file_blob, which uses Advanced Compression. The 26.2 upgrade reports this and leaves it as it is. If you do not hold that licence, run storage/adm_lob_compression_off.sql (CHECK, then APPLY). It locks adm_document_versions while it rewrites the text, and the segment grows by the space compression was saving, so pick a quiet time and allow for the extra space.

Documents in their own tablespaces

The install puts everything in the ADM schema’s default tablespace. To move documents and the full-text index into tablespaces of their own, create the tablespaces (ASSM), give the ADM schema quota on them, and run storage/adm_storage_tablespaces.sql as the ADM schema owner:

sql adm/password@db @storage/adm_storage_tablespaces.sql ADM_LOBS ADM_TEXT CHECK
sql adm/password@db @storage/adm_storage_tablespaces.sql ADM_LOBS ADM_TEXT APPLY

Argument	Meaning
1	Tablespace for file content (the four LOBs above), or - to leave them where they are
2	Tablespace for the full-text index, or - to leave it
3	CHECK shows what would be moved and what it will cost; APPLY does it

The table rows stay in the default tablespace; only the LOBs and the full-text index move. The script checks the tablespaces before changing anything. You can run it again at any time, and whatever is already in place is skipped. Full-text objects Oracle Text creates later also go to the text tablespace.

When to do it

Right after the install. The tables are empty and it takes seconds. This is by far the cheapest moment.

On an installation already in use, run CHECK first and plan a maintenance window:

• Uploads are blocked while file content moves. adm_document_versions cannot be moved online, so new files and new versions wait until it is done.
• The full-text index is rebuilt. It covers extracted text only, so no file is filtered again, but on a large installation it still takes a while. CHECK shows how many documents that covers.
• Moving only the full-text index (- as the first argument) is much faster, because nothing is re-indexed. Content search is unavailable while it runs.

Getting space back after a migration

Moving files to object storage, or purging documents, does not make the database smaller. The freed space stays in the LOB segment and is only reused for new files. A database that moved 500 GB to object storage still has a 500 GB segment until it is shrunk.

storage/adm_reclaim_lob_space.sql shows, for each LOB, the space in use, the space that can be reclaimed now, and the space that can be reclaimed later. APPLY shrinks the LOBs that have space to give back:

sql adm/password@db @storage/adm_reclaim_lob_space.sql CHECK
sql adm/password@db @storage/adm_reclaim_lob_space.sql APPLY

What to expect:

• Run it some time after the migration, not straight away. Freed space can only be reclaimed once UNDO_RETENTION has passed, often 15 minutes. If CHECK shows most of the space as “later”, wait and run it again.
• No maintenance window needed. Uploads, downloads and search carry on, and nothing is re-indexed. Content search can come back empty for a few seconds after a LOB with a full-text index is shrunk, so pick a quiet time.
• Some space always stays. Space comes back in whole extents, and the last one is kept, so each LOB keeps up to about 64 MB whatever it contains.
• Needs a release that can shrink SecureFiles, such as Oracle Database 23ai. Where the release cannot, the script says so for each LOB and changes nothing. The remaining option is to move the file content to another tablespace (see above), which has the maintenance-window cost described there.
• The space goes back to the tablespace, not to the disk. Resize the data files afterwards if you want the storage back.

What we advise against

• Leaving the document tablespace out of clones and restores. The document table and its LOBs only work together. Transportable tablespaces, RMAN DUPLICATE ... SKIP TABLESPACE and Data Pump by tablespace either refuse, or produce a copy whose files cannot be read. For a smaller test copy, keep files in object storage, or empty them in the copy after the refresh.
• Read-only tablespaces or partitions for old documents. ADM still writes to old versions: it moves them to object storage, and trash cleanup and retention delete them. All of that fails on read-only storage. Partitioning is also an extra-cost Enterprise Edition option.
• NOLOGGING on file_content or index_content. After a media recovery, or on a Data Guard standby, those documents could not be read.

Also refer to

• Object storage setup: moving file content out of the database entirely
• Storage and versioning
• Search and taxonomy: how content becomes searchable