7
Oracle 9iFS Schema SQL Views

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

Viewing Your Schema with SQL Views

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

Note:

The Oracle 9iFS views should be used exclusively by system administrators. The data in the views bypasses 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 Oracle9i platform date format, based on Greenwich Mean Time.

The Schema Views

These eight schema 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 column.

To create additional views, see the Oracle Internet File System Developer Reference.

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 9iFS.

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	N`UMBER(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

7 Oracle 9iFS Schema SQL Views