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:
Oracle Database Reference for information about the data dictionary views described in this chapter
The queries in the following sections provide examples for monitoring a file group repository:
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.
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
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.
The queries in the following sections provide examples for monitoring a tablespace repository:
Displaying Information About the Tablespaces in a Tablespace Repository
Displaying Information About the Tables in a Tablespace Repository
Displaying Export Information About Versions 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
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
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