Content and user information from Content DB or Content Services

1. The metadata for documents is stored in odmv_document.
2. The contentobject column of odmv_document contains the ID of the associated entry in odmv_contentobject.
3. The content column in odmv_contentobject contains the ID of the associated entry in either odmm_nonindexedstore or odmm_contentstore
4. The media column in odmv_contentobject links the contentobject to the odmv_media, where it is indicated in which table/column the document's content is stored.
5. The full pathname of the document is not stored in any table or view. Each document or directory contains a reference to its parent folder in odmv_folderpathrelationship

QUERY 1:

SELECT FO.name AS "Folder Name", DO.name AS "Document name",
to_date('01-JAN-1970','DD-MON-YYYY')+(DO.createdate/86400000) AS
"Create date",
CO.contentsize, CO.content AS "Content ID", ME.tablename,
ME.columnname
FROM odmv_folderpathrelationship FPR, odmv_folder FO, odmv_document DO,
odmv_contentobject CO, odmv_media ME
WHERE FO.name = 'My Library'
AND FO.id = FPR.leftobject
AND DO.id = FPR.rightobject
AND CO.id = DO.contentobject
AND ME.id = CO.media

The query will list all documents stored in a folder named "My Library", and will display the name of the document, the creation date, the size of the document, as well as the table name, and column name where the document content is stored.


QUERY 2:

Getting information about the owner of a document, you need to join odmv_document.owner with the ID column in odmv_directoryuser, then you can retrieve the name of the owner of the document.

Here is a sample query to find this:

select d.name,u.name from odmv_document d, odmv_directoryuser u where
d.owner=u.id;


QUERY 3:

Getting information about the group of a document, here is a sample query to find this:

select dg.name,du.name from odmv_directorygroup dg,
odmv_groupmemberrelationship gm, odmv_directoryuser du
where dg.name is not null
and dg.id=gm.leftobject
and du.id=gm.rightobject
order by dg.name


QUERY 4:

If you need to know the format of the document (e.g. is it an MS Word, Excel, JPEG, etc), then you would need to join the odmv_document table, with odmv_contentobject, since that is the one holding the format IDs, and then look up the actual format name in odmv_format

Try this example:

SELECT FO.name AS "Folder Name", DO.name AS "Document name", FM.name "Format",
to_date('01-JAN-1970','DD-MON-YYYY')+(DO.createdate/86400000) AS "Create date",
CO.contentsize, CO.content AS "Content ID"
FROM odmv_folderpathrelationship FPR, odmv_folder FO, odmv_document DO, odmv_contentobject CO, odmv_format FM
WHERE FO.name = 'public'
AND FO.id = FPR.leftobject
AND DO.id = FPR.rightobject
AND CO.id = DO.contentobject
AND CO.format = FM.id


QUERY 5:

To list all known libraries

select * from odmv_workspace


QUERY 6:
To determine the total number of documents

select count(*) from odmv_document


QUERY 7:

To determine the amount of space that is used by the Content Services / ContentDB database objects

To calculate this, it is sufficient to look at the sum of space consumed by all tablespaces which are used by content services / contentdb, namely:

CONTENT_IFS_MAIN
CONTENT_IFS_CTX_I
CONTENT_IFS_CTX_K
CONTENT_IFS_CTX_X
CONTENT_IFS_LOB_I
CONTENT_IFS_LOB_M
CONTENT_IFS_LOB_N

To find out how much space is used by these tablespaces, you can run the following SQL:

select tablespace_name,sum(bytes)/1024/1024 as "Size in MB"
from dba_data_files
where tablespace_name in ('CONTENT_IFS_MAIN','CONTENT_IFS_CTX_I',
'CONTENT_IFS_CTX_K','CONTENT_IFS_CTX_X','CONTENT_IFS_LOB_I','CONTENT_IFS_LOB_M',
'CONTENT_IFS_LOB_N')
group by tablespace_name