37 Monitoring File Group and Tablespace Repositories

A file group repository can contain multiple file groups and multiple versions of a particular file group. A tablespace repository is a collection of tablespace sets in a file group repository. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. This chapter provides sample queries that you can use to monitor file group repositories and tablespace repositories.

The following topics describe monitoring file group and tablespace repositories:

Note:

The Oracle Streams tool in Oracle Enterprise Manager Cloud Control is also an excellent way to monitor an Oracle Streams environment. See the online Help for the Oracle Streams tool for more information.

See Also:

Monitoring a File Group Repository

The queries in the following sections provide examples for monitoring a file group repository:

Displaying General Information About the File Groups in a Database

The query in this section displays the following information for each file group in the local database:

  • The file group owner

  • The file group name

  • Whether the files in a version of the file group are kept on disk if the version is purged

  • The minimum number of versions of the file group allowed

  • The maximum number of versions of the file group allowed

  • The number of days to retain a file group version after it is created

Run the following query to display this information for the local database:

COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
COLUMN KEEP_FILES HEADING 'Keep|Files?' FORMAT A10
COLUMN MIN_VERSIONS HEADING 'Minimum|Number|of Versions' FORMAT 9999
COLUMN MAX_VERSIONS HEADING 'Maximum|Number|of Versions' FORMAT 9999999999
COLUMN RETENTION_DAYS HEADING 'Days to|Retain|a Version' FORMAT 9999999999.99

SELECT FILE_GROUP_OWNER,
       FILE_GROUP_NAME,
       KEEP_FILES,
       MIN_VERSIONS,
       MAX_VERSIONS,
       RETENTION_DAYS
  FROM DBA_FILE_GROUPS;

Your output looks similar to the following:

                                     Minimum     Maximum        Days to
File Group File Group Keep            Number      Number         Retain
Owner      Name       Files?     of Versions of Versions      a Version
---------- ---------- ---------- ----------- ----------- --------------
STRMADMIN  REPORTS    Y                    2  4294967295  4294967295.00

This output shows that the database has one file group with the following characteristics:

  • The file group owner is strmadmin.

  • The file group name is reports.

  • The files in a version are kept on disk if a version is purged because the "Keep Files?" is "Y" for the file group.

  • The minimum number of versions allowed is 2. If the file group automatically purges versions, then it will not purge a version if the purge would cause the total number of versions to drop below 2.

  • The file group allows an infinite number of versions. The number 4294967295 means an infinite number of versions.

  • The file group retains a version of an infinite number of days. The number 4294967295 means an infinite number of days.

Displaying Information About File Group Versions

The query in this section displays the following information for each file group version in the local database:

  • The owner of the file group that contains the version

  • The name of the file group that contains the version

  • The version name

  • The version number

  • The name of the user who created the version

  • Comments for the version

Run the following query to display this information for the local database:

COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A20
COLUMN VERSION HEADING 'Version|Number' FORMAT 99999999
COLUMN CREATOR HEADING 'Creator' FORMAT A10
COLUMN COMMENTS HEADING 'Comments' FORMAT A14

SELECT FILE_GROUP_OWNER,
       FILE_GROUP_NAME,
       VERSION_NAME,
       VERSION,
       CREATOR,
       COMMENTS
  FROM DBA_FILE_GROUP_VERSIONS;

Your output looks similar to the following:

File Group File Group                        Version
Owner      Name       Version Name            Number Creator    Comments
---------- ---------- -------------------- --------- ---------- --------------
STRMADMIN  REPORTS    SALES_REPORTS_V1             1 STRMADMIN  Sales reports
                                                                for week of 06
                                                                -FEB-2005
 
STRMADMIN  REPORTS    SALES_REPORTS_V2             2 STRMADMIN  Sales reports
                                                                for week of 13
                                                                -FEB-2005

Displaying Information About File Group Files

The query in this section displays the following information about each file in a file group version in the local database:

  • The owner of the file group that contains the file

  • The name of the file group that contains the file

  • The name of the version in the file group that contains the file

  • The file name

  • The directory object that contains the file

COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A20
COLUMN FILE_NAME HEADING 'File Name' FORMAT A15
COLUMN FILE_DIRECTORY HEADING 'File Directory|Object' FORMAT A15

SELECT FILE_GROUP_OWNER,
       FILE_GROUP_NAME,
       VERSION_NAME,
       FILE_NAME,
       FILE_DIRECTORY
  FROM DBA_FILE_GROUP_FILES;

Your output looks similar to the following:

File Group File Group                                      File Directory
Owner      Name       Version Name         File Name       Object
---------- ---------- -------------------- --------------- ---------------
STRMADMIN  REPORTS    SALES_REPORTS_V1     book_sales.htm  SALES_REPORTS1
STRMADMIN  REPORTS    SALES_REPORTS_V1     music_sales.htm SALES_REPORTS1
STRMADMIN  REPORTS    SALES_REPORTS_V2     book_sales.htm  SALES_REPORTS2
STRMADMIN  REPORTS    SALES_REPORTS_V2     music_sales.htm SALES_REPORTS2

Query the DBA_DIRECTORIES data dictionary view to determine the corresponding file system directory for a directory object.

Monitoring a Tablespace Repository

The queries in the following sections provide examples for monitoring a tablespace repository:

Displaying Information About the Tablespaces in a Tablespace Repository

The query in this section displays the following information about each tablespace in the tablespace repository in the local database:

  • The owner of the file group that contains the tablespace in the tablespace repository

  • The name of the file group that contains the tablespace in the tablespace repository

  • The name of the version that contains the tablespace

  • The tablespace name

COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A15
COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A15
COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A15
COLUMN VERSION HEADING 'Version|Number' FORMAT 99999999
COLUMN TABLESPACE_NAME HEADING 'Tablespace Name' FORMAT A15

SELECT FILE_GROUP_OWNER,
       FILE_GROUP_NAME,
       VERSION_NAME,
       VERSION,
       TABLESPACE_NAME
  FROM DBA_FILE_GROUP_TABLESPACES;

Your output looks similar to the following:

File Group      File Group                        Version
Owner           Name            Version Name       Number Tablespace Name
--------------- --------------- --------------- --------- ---------------
STRMADMIN       SALES           V_Q1FY2005              1 SALES_TBS1
STRMADMIN       SALES           V_Q1FY2005              1 SALES_TBS2
STRMADMIN       SALES           V_Q2FY2005              3 SALES_TBS1
STRMADMIN       SALES           V_Q2FY2005              3 SALES_TBS2
STRMADMIN       SALES           V_Q1FY2005_R            4 SALES_TBS1
STRMADMIN       SALES           V_Q1FY2005_R            4 SALES_TBS2
STRMADMIN       SALES           V_Q2FY2005_R            5 SALES_TBS1
STRMADMIN       SALES           V_Q2FY2005_R            5 SALES_TBS2

Displaying Information About the Tables in a Tablespace Repository

The query in this section displays the following information about each table in the tablespace repository in the local database:

  • The owner of the file group that contains the table in the tablespace repository

  • The name of the file group that contains the table in the tablespace repository

  • The name of the version that contains the table

  • The table owner

  • The table name

  • The tablespace that contains the table

COLUMN FILE_GROUP_OWNER HEADING 'File Group|Owner' FORMAT A10
COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A15
COLUMN OWNER HEADING 'Table|Owner' FORMAT A10
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN TABLESPACE_NAME HEADING 'Tablespace Name' FORMAT A15

SELECT FILE_GROUP_OWNER,
       FILE_GROUP_NAME,
       VERSION_NAME,
       OWNER,
       TABLE_NAME,
       TABLESPACE_NAME
  FROM DBA_FILE_GROUP_TABLES;

Your output looks similar to the following:

File Group File Group                 Table
Owner      Name       Version Name    Owner      Table Name      Tablespace Name
---------- ---------- --------------- ---------- --------------- ---------------
STRMADMIN  SALES      V_Q1FY2005      SL         ORDERS          SALES_TBS1
STRMADMIN  SALES      V_Q1FY2005      SL         ORDER_ITEMS     SALES_TBS1
STRMADMIN  SALES      V_Q1FY2005      SL         CUSTOMERS       SALES_TBS2
STRMADMIN  SALES      V_Q2FY2005      SL         ORDERS          SALES_TBS1
STRMADMIN  SALES      V_Q2FY2005      SL         ORDER_ITEMS     SALES_TBS1
STRMADMIN  SALES      V_Q2FY2005      SL         CUSTOMERS       SALES_TBS2
STRMADMIN  SALES      V_Q1FY2005_R    SL         ORDERS          SALES_TBS1
STRMADMIN  SALES      V_Q1FY2005_R    SL         ORDER_ITEMS     SALES_TBS1
STRMADMIN  SALES      V_Q1FY2005_R    SL         CUSTOMERS       SALES_TBS2
STRMADMIN  SALES      V_Q2FY2005_R    SL         ORDERS          SALES_TBS1
STRMADMIN  SALES      V_Q2FY2005_R    SL         ORDER_ITEMS     SALES_TBS1
STRMADMIN  SALES      V_Q2FY2005_R    SL         CUSTOMERS       SALES_TBS2

Displaying Export Information About Versions in a Tablespace Repository

To display export information about the versions in the tablespace repository in the local database, query the DBA_FILE_GROUP_EXPORT_INFO data dictionary view. This view only displays information for versions that contain a valid Data Pump export dump file. The query in this section displays the following export information about each version in the local database:

  • The name of the file group that contains the version

  • The name of the version

  • The export version of the export dump file. The export version corresponds to the version of Data Pump that performed the export.

  • The platform on which the export was performed

  • The date and time of the export

  • The global name of the exporting database

COLUMN FILE_GROUP_NAME HEADING 'File Group|Name' FORMAT A10
COLUMN VERSION_NAME HEADING 'Version Name' FORMAT A13
COLUMN EXPORT_VERSION HEADING 'Export|Version' FORMAT A7
COLUMN PLATFORM_NAME HEADING 'Export Platform' FORMAT A17
COLUMN EXPORT_TIME HEADING 'Export Time' FORMAT A17
COLUMN SOURCE_GLOBAL_NAME HEADING 'Export|Database' FORMAT A10

SELECT FILE_GROUP_NAME,
       VERSION_NAME,
       EXPORT_VERSION,
       PLATFORM_NAME,
       TO_CHAR(EXPORT_TIME, 'HH24:MI:SS MM/DD/YY') EXPORT_TIME,
       SOURCE_GLOBAL_NAME
  FROM DBA_FILE_GROUP_EXPORT_INFO;

Your output looks similar to the following:

File Group               Export                                      Export
Name       Version Name  Version Export Platform   Export Time       Database
---------- ------------- ------- ----------------- ----------------- ----------
SALES      V_Q1FY2005    10.2.0  Linux IA (32-bit) 12:23:52 03/08/05 INST1.EXAM
                                                                     PLE.COM   
SALES      V_Q2FY2005    10.2.0  Linux IA (32-bit) 12:27:37 03/08/05 INST1.EXAM
                                                                     PLE.COM   
SALES      V_Q1FY2005_R  10.2.0  Linux IA (32-bit) 12:39:50 03/08/05 INST2.EXAM
                                                                     PLE.COM
SALES      V_Q2FY2005_R  10.2.0  Linux IA (32-bit) 12:46:04 03/08/05 INST2.EXAM
                                                                     PLE.COM