On quite a number of occasions, it becomes necessary to query the WebCenter Content schema directly to get details for particular use cases. This article provides examples and the corresponding WebCenter SQL queries, which can come in handy for multiple administrative scenarios.
1. How to get the total size of content items that belong to a particular content type (or any other metadata)
This case is relevant for planning Archiver exports and imports, wherein the whole set of content items need to be moved from source server to target. For this case, the administrator would need to query on the dFileSize column in the DOCUMENTS table. This table alone will not give the detail for specific content types. It needs to be achieved by creating a join with the REVISIONS table.
Scenario 1: Get the total file size for specific content Type ‘abc’
select sum(dFileSize)/1024/1024/1024 as TotalSizeinGB from prod_ocs.documents doc, prod_ocs.revisions rev where doc.did=rev.did and rev.ddoctype=’abc’;
Scenario 2: Get the total file size based on specific content file extension
select sum(dFileSize)/1024/1024/1024 as TotalSizeinGB from prod_ocs.documents doc, prod_ocs.revisions rev where doc.did=rev.did and doc.dextension=’pdf’;
2. List Content items that are not associated with any Framework folders
During the development phase of a content taxonomy, hierarchy, etc., there are occasions when the initial set of content items are not associated with any folders.
Once the design is completed and in place, it becomes necessary to associate all the items to specific folders. The following SQL query will give the results for all those items belonging to a specific content type that are not yet part of any folders.
The logic here uses the FolderFiles table, which holds the details for content items that are associated with folders.
select distinct ddocname as ContentID from prod_ocs.revisions rev,prod_ocs.docmeta doc where rev.did=doc.did and rev.ddoctype=’abc’ and rev.ddocname NOT IN (select fold.ddocname as ContentID FROM prod_ocs.folderfiles fold,prod_ocs.revisions rev,prod_ocs.documents docum where fold.ddocname=rev.ddocname and rev.ddoctype=’abc’);
3. List all the content items that have duplicate values for a specific metadata
Like Number 2 above, during the initial phase, there are a number of content items that will be checked in with the same values set for some of the metadata fields. To list such content items, the following query is to be used:
select * from( select distinct dDocName as contentid,xmetadata as DupMetadat, count(*) over (partition by xmetadata) ct from Prod_ocs.docmeta doc,prod_ocs.revisions rev where doc.did=rev.did and rev.ddoctype=’abc’ group by xmetadata,ddocname ) where ct > 1;
The above query is finding all the content IDs for which the values in the xmetadata field are duplicates.
4. List of all content items which have specific metadata fields starting with numbers
At times, there might be a need to keep a list of all content items for which specific metadata fields would start with a number. To achieve this requirement, the DBA would need to leverage regular expression functionality available with Oracle 11g and above.
select /*csv*/ dDocName as ContentID,xmetadata as FieldWithNumber FROM Prod_ocs.docmeta doc,prod_ocs.revisions rev where doc.did=rev.did and rev.ddoctype=’abc’ and regexp_like(doc.xmetadata,’^[0-9]+$’);
The above query will give the results in a CSV format.
If you need any more assistance with administering WebCenter Content, or if you’d like help upgrading your Oracle WebCenter Content environment from 11g to 12c, contact Inspired ECM to let our experienced consultants get your environment running smoothly and successfully today.
