11
Oracle iFS Repository Views

Using the Oracle iFS repository views, experienced database administrators can see the effects of changes they make to the Oracle iFS repository in a familiar format. This chapter provides information on the SQL views provided with Oracle iFS and an example of each. The following topics are included:

• Using the Oracle iFS Repository Views for System Maintenance

• Oracle iFS Has Eight Different Oracle iFS Repository Views

Using the Oracle iFS Repository Views for System Maintenance

Repository views are created when Oracle iFS is installed. The Oracle iFS repository views are created when Oracle iFS is installed. The views are owned by the repository owner: ifssys. The Oracle iFS SQL read-only views are created with the Oracle iFS schema and are for informational purposes only. Grant the SELECT permission to users who need access to these views.

Note: The Oracle iFS views should be used exclusively by system administrators. The data in the views bypass all security; therefore, caution should be taken in granting users access to these views.

The names of objects in the view may not be unique. Therefore, IDs are provided. Any column of type date has two columns in the view, for example:

• CREATE_DATE--An internal representation of the create date.

CREATE_DATE_GMT--The standard Oracle8 date format, based on Greenwich Mean Time.

Oracle iFS Has Eight Different Oracle iFS Repository Views

These eight repository views are provided out-of-the-box. Each view is described in a separate table which provides the name of the column, whether the column can be null, the type of column, and the length for each view.

• ifs_versioned_documents

• ifs_all_documents

• ifs_folders

• ifs_folder_items

• ifs_users

• ifs_groups

• ifs_group_members

• ifs_acls

View: ifs_versioned_documents

This view shows a list of all documents which are versioned.

Column Name	Null?	Type of Column and Length
Modified_By	Yes	VARCHAR2 (700)
Modifier_ID	Yes	NUMBER (20)
Creator	Yes	VARCHAR (700)
Creator_ID	Yes	NUMBER (20)
Owner	Yes	VARCHAR (700)
Owner_ID	Yes	NUMBER (20)
FAMILY	Yes	VARCHAR2(700)
FAMILY_ID	Yes	NUMBER(20)
FORMAT	Yes	VARCHAR2(700)
FORMAT_ID	Yes	NUMBER(20)
DOCUMENT_SIZE	Yes	NUMBER(20)
CHARACTERSET	Yes	VARCHAR2(40)
LANGUAGE	Yes	VARCHAR2(40)
MEDIA	Yes	VARCHAR2(700)
MEDIA_ID	Yes	NUMBER(20)
ACL	Yes	VARCHAR2(700)
ACL_ID	Yes	NUMBER(20)
NAME	Yes	VARCHAR2(700)
DOCUMENT_ID	Yes	NUMBER(20)
DESCRIPTION	Yes	VARCHAR2(2000)
CREATE_DATE	Yes	NUMBER(20)
CREATE_DATE_GMT	Yes	DATE
MODIFIED_DATE	Yes	NUMBER(20)
MODIFIED_DATE_GMT	Yes	DATE
EXPIRATION_DATE	Yes	NUMBER(20)
EXPIRATION_DATE_GMT	Yes	DATE
LOCKED	Yes	NUMBER(1)
READ_BY_OWNER	Yes	NUMBER(1)

View: ifs_all_documents

This view shows a list of all documents.

Column Name	Null?	Type of Column and Length
MODIFIED_BY	Yes	VARCHAR2 (700)
MODIFIER_ID	Yes	NUMBER (20)
CREATOR	Yes	VARCHAR (700)
CREATOR_ID	Yes	NUMBER (20)
OWNER	Yes	VARCHAR (700)
OWNER_ID	Yes	NUMBER (20)
FORMAT	Yes	VARCHAR2(700)
FORMAT_ID	Yes	NUMBER(20)
DOCUMENT_SIZE	Yes	NUMBER(20)
CHARACTERSET	Yes	VARCHAR2(40)
LANGUAGE	Yes	VARCHAR2(40)
MEDIA	Yes	VARCHAR2(700)
MEDIA_ID	Yes	NUMBER(20)
ACL	Yes	VARCHAR2(700)
ACL_ID	Yes	NUMBER(20)
NAME	Yes	VARCHAR2(700)
DOCUMENT_ID	Yes	NUMBER(20)
DESCRIPTION	Yes	VARCHAR2(2000)
CREATE_DATE	Yes	NUMBER(20)
CREATE_DATE_GMT	Yes	DATE
MODIFIED_DATE	Yes	NUMBER(20)
MODIFIED_DATE_GMT	Yes	DATE
EXPIRATION_DATE	Yes	NUMBER(20)
EXPIRATION_DATE_GMT	Yes	DATE
LOCKED	Yes	NUMBER(1)
READ_BY_OWNER	Yes	NUMBER(1)

View: ifs_folders

This view shows all folders and their attributes.

Column Name	Null?	Type of Column and Length
MODIFIED_BY	Yes	VARCHAR2 (700)
MODIFIER_ID	Yes	NUMBER (20)
CREATOR	Yes	VARCHAR (700)
CREATOR_ID	Yes	NUMBER (20)
OWNER	Yes	VARCHAR (700)
OWNER_ID	Yes	NUMBER (20)
ACL	Yes	VARCHAR2(700)
ACL_ID	Yes	NUMBER(20)
NAME	Yes	VARCHAR2(700)
FOLDER_ID	Yes	NUMBER(20)
DESCRIPTION	Yes	VARCHAR2(2000)
CREATE_DATE	Yes	NUMBER(20)
CREATE_DATE_GMT	Yes	DATE
MODIFIED_DATE	Yes	NUMBER(20)
MODIFIED_DATE_GMT	Yes	DATE
EXPIRATION_DATE	Yes	NUMBER(20)
EXPIRATION_DATE_GMT	Yes	DATE
LOCKED	Yes	NUMBER(1)

View: ifs_folder_items

This view lists all folders and the top level items.

Column Name	Null?	Type of Column and Length
CREATOR	Yes	VARCHAR (700)
CREATOR_ID	Yes	NUMBER (20)
OWNER	Yes	VARCHAR (700)
OWNER_ID	Yes	NUMBER (20)
TYPE	No	VARCHAR2(700)
NAME	Yes	VARCHAR2(700)
DESCRIPTION	Yes	VARCHAR2(2000)
CREATE_DATE	Yes	NUMBER(20)
CREATE_DATE_GMT	Yes	DATE
OBJECT_ID	Yes	VARCHAR2(700)
ACL	Yes	VARCHAR2(700)
ACL_ID	Yes	NUMBER(20)
FOLDER_NAME	Yes	VARCHAR2 (700)
FOLDER_ID	Yes	NUMBER(20)

View: ifs_users

This view lists all users.

Column Name	Null?	Type of Column and Length
MODIFIED_BY	Yes	VARCHAR2 (700)
MODIFIER_ID	Yes	NUMBER (20)
CREATOR	Yes	VARCHAR (700)
CREATOR_ID	Yes	NUMBER (20)
OWNER	Yes	VARCHAR (700)
OWNER_ID	Yes	NUMBER (20)
ACL	Yes	VARCHAR2(700)
ACL_ID	Yes	NUMBER(20)
NAME	Yes	VARCHAR2(700)
USER_ID	Yes	NUMBER(20)
DESCRIPTION	Yes	VARCHAR2(2000)
CREATE_DATE	Yes	NUMBER(20)
CREATE_DATE_GMT	Yes	DATE
DISTINGUISHED_NAME	Yes	VARCHAR2(700)
ADMIN_ENABLED	Yes	NUMBER(1)
CREDENTIAL_MANAGER	Yes	VARCHAR2(700)
UNIQUE_NAME	Yes	VARCHAR2(700)
HOME_FOLDER_NAME	Yes	VARCHAR2(700)
HOME_FOLDER_ID	Yes	NUMBER(20)

View: ifs_groups

This view lists all groups.

Column Name	Null?	Type of Column and Length
MODIFIED_BY	Yes	VARCHAR2 (700)
MODIFIER_ID	Yes	NUMBER (20)
CREATOR	Yes	VARCHAR (700)
CREATOR_ID	Yes	NUMBER (20)
OWNER	Yes	VARCHAR (700)
OWNER_ID	Yes	NUMBER (20)
NAME	Yes	VARCHAR2(700)
GROUP_ID	Yes	NUMBER(20)
DESCRIPTION	Yes	VARCHAR2(2000)
CREATE_DATE	Yes	NUMBER(20)
CREATE_DATE_GMT	Yes	DATE
ACL	Yes	VARCHAR2(700)
ACL_ID	Yes	NUMBER(20)

View: ifs_group_members

This view lists groups and their direct members.

Column Name	Null?	Type of Column and Length
MEMBER_NAME	Yes	VARCHAR2 (700)
MEMBER_ID	Yes	NUMBER (20)
MEMBER_DESCRIPTION	Yes	VARCHAR (700)
MEMBER_CREATE_DATE	Yes	NUMBER (20)
MEMBER_CREATE_DATE_GMT	Yes	DATE
GROUP_ID	Yes	NUMBER(20)
TYPE	No	VARCHAR2(2000)

View: ifs_acls

This view lists all ACLs in Oracle iFS.

Column Name	Null?	Type of Column and Length
CONTROLLING_ACL_NAME	Yes	VARCHAR2 (700)
CONTROLLING_ACL_ID	Yes	NUMBER (20)
NAME	Yes	VARCHAR2(700)
ACL_ID	Yes	NUMBER(20)
DESCRIPTION	Yes	VARCHAR2(2000)
CREATE_DATE	Yes	NUMBER(20)
CREATE_DATE_GMT	Yes	DATE
MODIFIED_BY	Yes	VARCHAR (700)
MODIFIER_ID	Yes	NUMBER (20)
CREATOR	Yes	VARCHAR (700)
CREATOR_ID	Yes	NUMBER (20)
OWNER	Yes	VARCHAR2(700)
OWNER_ID	Yes	NUMBER(20)

Example: Selecting Attributes of a Document

This example uses ifs_versioned_documents to select attributes. To accomplish this, the following command is run:

SQL> select name, format, document_size, media, document_id, acl, owner from ifs_versioned_documents

The following table displays the output.

Name	Format	Document_Size	Media	ACL	Document_ID	Owner
Business Plan	MS Word	11954	IndexedBlob	Published	7902	jsmith
June Report	Postscript	121	NonIndexedBlob	Private	7936	lfrench
Oracle logo	JPEG	121	NonIndexedBlob	Published	7926	pbrown