This chapter describes how to use information provisioning. This chapter includes an example that creates a tablespace repository, examples that transfer tablespaces between databases, and an example that uses a file group repository to store different versions of files.
This chapter contains these topics:
See Also:
Chapter 8, "Information Provisioning"The following procedures in the DBMS_STREAMS_TABLESPACE_ADM
package can create a tablespace repository, add versioned tablespace sets to a tablespace repository, and copy versioned tablespace sets from a tablespace repository:
ATTACH_TABLESPACES
: This procedure copies a version of a tablespace set from a tablespace repository and attaches the tablespaces to a database.
CLONE_TABLESPACES
: This procedure adds a new version of a tablespace set to a tablespace repository by copying the tablespace set from a database. The tablespaces in the tablespace set remain part of the database from which they were copied.
DETACH_TABLESPACES
: This procedure adds a new version of a tablespace set to a tablespace repository by moving the tablespace set from a database to the repository. The tablespaces in the tablespace set are dropped from the database from which they were copied.
This section illustrates how to use a tablespace repository with an example scenario. In the scenario, the goal is to run quarterly reports on the sales tablespaces (sales_tbs1
and sales_tbs2
). Sales are recorded in these tablespaces in the inst1.net
database. The example clones the tablespaces quarterly and stores a new version of the tablespaces in the tablespace repository. The tablespace repository also resides in the inst1.net
database. When a specific version of the tablespace set is required to run reports at a reporting database, it is copied from the tablespace repository and attached to the reporting database.
In this example scenario, the following databases are the reporting databases:
The reporting database inst2.net
shares a file system with the inst1.net
database. Also, the reports that are run on inst2.net
might make changes to the tablespace. Therefore, the tablespaces are made read/write at inst2.net
, and, when the reports are complete, a new version of the tablespace files is stored in a separate directory from the original version of the tablespace files.
The reporting system inst3.net
does not share a file system with the inst1.net
database. The reports that are run on inst3.net
do not make any changes to the tablespace. Therefore, the tablespaces remain read-only at inst3.net
, and, when the reports are complete, the original version of the tablespace files remains in a single directory.
The following sections describe how to create and populate the tablespace repository and how to use the tablespace repository to run reports at the other databases:
Using a Tablespace Repository for Remote Reporting with a Shared File System
Using a Tablespace Repository for Remote Reporting Without a Shared File System
These examples must be run by an administrative user with the necessary privileges to run the procedures listed previously.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about these procedures and the privileges required to run themThis example creates a tablespaces repository and adds a new version of a tablespace set to the repository after each quarter. The tablespace set consists of the sales tablespaces for a business: sales_tbs1
and sales_tbs2
.
Figure 16-1 provides an overview of the tablespace repository created in this example:
Figure 16-1 Example Tablespace Repository
Table 16-1 shows the tablespace set versions created in this example, their directory objects, and the corresponding file system directory for each directory object.
Table 16-1 Versions in the Tablespace Repository
Version | Directory Object | Corresponding File System Directory |
---|---|---|
|
|
|
|
|
|
This example makes the following assumptions:
The inst1.net
database exists.
The sales_tbs1
and sales_tbs2
tablespaces exist in the inst1.net
database.
The following steps create and populate a tablespace repository:
Connect as an administrative user to the database where the sales tablespaces are modified with new sales data:
CONNECT strmadmin/strmadminpw@inst1.net
The administrative user must have the necessary privileges to run the procedures in the DBMS_STREAMS_TABLESPACE_ADM
package and must have the necessary privileges to create directory objects.
Create a directory object for the first quarter in fiscal year 2005 on inst1.net
:
CREATE OR REPLACE DIRECTORY q1fy2005 AS '/home/sales/q1fy2005';
The specified file system directory must exist when you create the directory object.
Create a directory object that corresponds to the directory that contains the datafiles for the tablespaces in the inst1.net
database. For example, if the datafiles for the tablespaces are in the /orc/inst1/dbs
directory, then create a directory object that corresponds to this directory:
CREATE OR REPLACE DIRECTORY dbfiles_inst1 AS '/orc/inst1/dbs';
Clone the tablespace set and add the first version of the tablespace set to the tablespace repository:
DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN tbs_set(1) := 'sales_tbs1'; tbs_set(2) := 'sales_tbs2'; DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES( tablespace_names => tbs_set, tablespace_directory_object => 'q1fy2005', file_group_name => 'strmadmin.sales', version_name => 'v_q1fy2005'); END; /
The sales
file group is created automatically if it does not exist.
When the second quarter in fiscal year 2005 is complete, create a directory object for the second quarter in fiscal year 2005:
CREATE OR REPLACE DIRECTORY q2fy2005 AS '/home/sales/q2fy2005';
The specified file system directory must exist when you create the directory object.
Clone the tablespace set and add the next version of the tablespace set to the tablespace repository at the inst1.net
database:
DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN tbs_set(1) := 'sales_tbs1'; tbs_set(2) := 'sales_tbs2'; DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES( tablespace_names => tbs_set, tablespace_directory_object => 'q2fy2005', file_group_name => 'strmadmin.sales', version_name => 'v_q2fy2005'); END; /
Steps 5 and 6 can be repeated whenever a quarter ends to store a version of the tablespace set for each quarter. Each time, create a new directory object to store the tablespace files for the quarter, and specify a unique version name for the quarter.
This example runs reports at inst2.net
on specific versions of the sales tablespaces stored in a tablespace repository at inst1.net
. These two databases share a file system, and the reports that are run on inst2.net
might make changes to the tablespace. Therefore, the tablespaces are made read/write at inst2.net
. When the reports are complete, a new version of the tablespace files is stored in a separate directory from the original version of the tablespace files.
Figure 16-2 provides an overview of how tablespaces in a tablespace repository are attached to a different database in this example:
Figure 16-2 Attaching Tablespaces with a Shared File System
Figure 16-3 provides an overview of how tablespaces are detached and placed in a tablespace repository in this example:
Figure 16-3 Detaching Tablespaces with a Shared File System
Table 16-2 shows the tablespace set versions in the tablespace repository when this example is complete. It shows the directory object for each version and the corresponding file system directory for each directory object. The versions that are new are created in this example. The versions that existed prior to this example were created in "Creating and Populating a Tablespace Repository".
Table 16-2 Versions in the Tablespace Repository After inst2.net Reporting
Version | Directory Object | Corresponding File System Directory | New? |
---|---|---|---|
|
|
|
No |
|
|
|
Yes |
|
|
|
No |
|
|
|
Yes |
This example makes the following assumptions:
The inst1.net
and inst2.net
databases exist.
The inst1.net
and inst2.net
databases can access a shared file system.
Networking is configured between the databases so that these databases can communicate with each other.
A tablespace repository that contains a version of the sales tablespaces (sales_tbs1
and sales_tbs2
) for various quarters exists in the inst1.net
database. This tablespace repository was created and populated in the example "Creating and Populating a Tablespace Repository".
Complete the following steps:
Connect to inst1.net
:
CONNECT strmadmin/strmadminpw@inst1.net
The administrative user must have the necessary privileges to create directory objects.
Create a directory object that will store the tablespace files for the first quarter in fiscal year 2005 on inst1.net
after the inst2.net
database has completed reporting on this quarter:
CREATE OR REPLACE DIRECTORY q1fy2005_r AS '/home/sales/q1fy2005_r';
The specified file system directory must exist when you create the directory objects.
Connect as an administrative user to the inst2.net
database:
CONNECT strmadmin/strmadminpw@inst2.net
The administrative user must have the necessary privileges to run the procedures in the DBMS_STREAMS_TABLESPACE_ADM
package, create directory objects, and create database links.
Create two directory objects for the first quarter in fiscal year 2005 on inst2.net
. These directory objects must have the same names and correspond to the same directories on the shared file system as the directory objects used by the tablespace repository in the inst1.net
database for the first quarter:
CREATE OR REPLACE DIRECTORY q1fy2005 AS '/home/sales/q1fy2005'; CREATE OR REPLACE DIRECTORY q1fy2005_r AS '/home/sales/q1fy2005_r';
Create a database link from inst2.net
to the inst1.net
database:
CREATE DATABASE LINK inst1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'inst1.net';
Attach the tablespace set to the inst2.net
database from the strmadmin.sales
file group in the inst1.net
database:
DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES( file_group_name => 'strmadmin.sales', version_name => 'v_q1fy2005', datafiles_directory_object => 'q1fy2005_r', repository_db_link => 'inst1.net', tablespace_names => tbs_set); END; /
Notice that q1fy2005_r
is specified for the datafiles_directory_object
parameter. Therefore, the datafiles for the tablespaces and the export dump file are copied from the /home/sales/q1fy2005
location to the /home/sales/q1fy2005_r
location by the procedure. The attached tablespaces in the inst2.net
database use the datafiles in the /home/sales/q1fy2005_r
location. The Data Pump import log file also is placed in this directory.
The attached tablespaces use the datafiles in the /home/sales/q1fy2005_r
location. However, the v_q1fy2005
version of the tablespaces in the tablespace repository consists of the files in the original /home/sales/q1fy2005
location.
Make the tablespaces read/write at inst2.net
:
ALTER TABLESPACE sales_tbs1 READ WRITE; ALTER TABLESPACE sales_tbs2 READ WRITE;
Run the reports on the data in the sales tablespaces at the inst2.net
database. The reports make changes to the tablespaces.
Detach the version of the tablespace set for the first quarter of 2005 from the inst2.net
database:
DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN tbs_set(1) := 'sales_tbs1'; tbs_set(2) := 'sales_tbs2'; DBMS_STREAMS_TABLESPACE_ADM.DETACH_TABLESPACES( tablespace_names => tbs_set, export_directory_object => 'q1fy2005_r', file_group_name => 'strmadmin.sales', version_name => 'v_q1fy2005_r', repository_db_link => 'inst1.net'); END; /
Only one version of a tablespace set can be attached to a database at a time. Therefore, the version of the sales tablespaces for the first quarter of 2005 must be detached from inst2.net
before the version of this tablespace set for the second quarter of 2005 can be attached.
Also, notice that the specified export_directory_object
is q1fy2005_r
, and that the version_name
is v_q1fy2005_r
. After the detach operation, there are two versions of the tablespace files for the first quarter of 2005 stored in the tablespace repository on inst1.net
: one version of the tablespace prior to reporting and one version after reporting. These two versions have different version names and are stored in different directory objects.
Connect to inst1.net
, and create a directory object that will store the tablespace files for the second quarter in fiscal year 2005 on inst1.net
after the inst2.net
database has completed reporting on this quarter:
CONNECT strmadmin/strmadminpw@inst1.net CREATE OR REPLACE DIRECTORY q2fy2005_r AS '/home/sales/q2fy2005_r';
The specified file system directory must exist when you create the directory object.
Connect to inst2.net
, and create two directory objects for the second quarter in fiscal year 2005 at inst2.net
. These directory objects must have the same names and correspond to the same directories on the shared file system as the directory objects used by the tablespace repository in the inst1.net
database for the second quarter:
CONNECT strmadmin/strmadminpw@inst2.net CREATE OR REPLACE DIRECTORY q2fy2005 AS '/home/sales/q2fy2005'; CREATE OR REPLACE DIRECTORY q2fy2005_r AS '/home/sales/q2fy2005_r';
Attach the tablespace set for the second quarter of 2005 to the inst2.net
database from the sales
file group in the inst1.net
database:
DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES( file_group_name => 'strmadmin.sales', version_name => 'v_q2fy2005', datafiles_directory_object => 'q2fy2005_r', repository_db_link => 'inst1.net', tablespace_names => tbs_set); END; /
Make the tablespaces read/write at inst2.net
:
ALTER TABLESPACE sales_tbs1 READ WRITE; ALTER TABLESPACE sales_tbs2 READ WRITE;
Run the reports on the data in the sales tablespaces at the inst2.net
database. The reports make changes to the tablespace.
Detach the version of the tablespace set for the second quarter of 2005 from inst2.net
:
DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN tbs_set(1) := 'sales_tbs1'; tbs_set(2) := 'sales_tbs2'; DBMS_STREAMS_TABLESPACE_ADM.DETACH_TABLESPACES( tablespace_names => tbs_set, export_directory_object => 'q2fy2005_r', file_group_name => 'strmadmin.sales', version_name => 'v_q2fy2005_r', repository_db_link => 'inst1.net'); END; /
Steps 10-15 can be repeated whenever a quarter ends to run reports on each quarter.
This example runs reports at inst3.net
on specific versions of the sales tablespaces stored in a tablespace repository at inst1.net
. These two databases do not share a file system, and the reports that are run on inst3.net
do not make any changes to the tablespace. Therefore, the tablespaces remain read-only at inst3.net
, and, when the reports are complete, there is no need for a new version of the tablespace files in the tablespace repository on inst1.net
.
Figure 16-4 provides an overview of how tablespaces in a tablespace repository are attached to a different database in this example:
Figure 16-4 Attaching Tablespaces Without a Shared File System
Table 16-3 shows the directory objects used in this example. It shows the existing directory objects that are associated with tablespace repository versions on the inst1.net
database, and it shows the new directory objects created on the inst3.net
database in this example. The directory objects that existed prior to this example were created in "Creating and Populating a Tablespace Repository".
Table 16-3 Directory Objects Used in Example
Directory Object | Database | Version | Corresponding File System Directory | New? |
---|---|---|---|---|
|
|
|
|
No |
|
|
|
|
No |
|
|
Not associated with a tablespace repository version |
|
Yes |
|
|
Not associated with a tablespace repository version |
|
Yes |
This example makes the following assumptions:
The inst1.net
and inst3.net
databases exist.
The inst1.net
and inst3.net
databases do not share a file system.
Networking is configured between the databases so that they can communicate with each other.
The sales tablespaces (sales_tbs1
and sales_tbs2
) exist in the inst1.net
database.
Complete the following steps:
Connect as an administrative user to the inst3.net
database:
CONNECT strmadmin/strmadminpw@inst3.net
The administrative user must have the necessary privileges to run the procedures in the DBMS_STREAMS_TABLESPACE_ADM
package, create directory objects, and create database links.
Create a database link from inst3.net
to the inst1.net
database:
CREATE DATABASE LINK inst1.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'inst1.net';
Create a directory object for the first quarter in fiscal year 2005 on inst3.net
. Although inst3.net
is a remote database that does not share a file system with inst1.net
, the directory object must have the same name as the directory object used by the tablespace repository in the inst1.net
database for the first quarter. However, the directory paths of the directory objects on inst1.net
and inst3.net
do not need to match.
CREATE OR REPLACE DIRECTORY q1fy2005 AS '/usr/sales_data/fy2005q1';
The specified file system directory must exist when you create the directory object.
Connect as an administrative user to the inst1.net
database:
CONNECT strmadmin/strmadminpw@inst1.net
The administrative user must have the necessary privileges to run the procedures in the DBMS_FILE_TRANSFER
package and create database links. This example uses the DBMS_FILE_TRANSFER
package to copy the tablespace files from inst1.net
to inst3.net
. If some other method is used to transfer the files, then the privileges to run the procedures in the DBMS_FILE_TRANSFER
package are not required.
Create a database link from inst1.net
to the inst3.net
database:
CREATE DATABASE LINK inst3.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'inst3.net';
This database link will be used to transfer files to the inst3.net
database in Step 6.
Copy the datafile for each tablespace and the export dump file for the first quarter to the inst3.net
database:
BEGIN DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'q1fy2005', source_file_name => 'sales_tbs1.dbf', destination_directory_object => 'q1fy2005', destination_file_name => 'sales_tbs1.dbf', destination_database => 'inst3.net'); DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'q1fy2005', source_file_name => 'sales_tbs2.dbf', destination_directory_object => 'q1fy2005', destination_file_name => 'sales_tbs2.dbf', destination_database => 'inst3.net'); DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'q1fy2005', source_file_name => 'expdat16.dmp', destination_directory_object => 'q1fy2005', destination_file_name => 'expdat16.dmp', destination_database => 'inst3.net'); END; /
Before you run the PUT_FILE
procedure for the export dump file, you can query the DBA_FILE_GROUP_FILES
data dictionary view to determine the name and directory object of the export dump file. For example, run the following query to list this information for the export dump file in the v_q1fy2005
version:
COLUMN FILE_NAME HEADING 'Export Dump|File Name' FORMAT A35 COLUMN FILE_DIRECTORY HEADING 'Directory Object' FORMAT A35 SELECT FILE_NAME, FILE_DIRECTORY FROM DBA_FILE_GROUP_FILES where FILE_GROUP_NAME = 'SALES' AND VERSION_NAME = 'V_Q1FY2005';
Connect to inst3.net
and attach the tablespace set for the first quarter of 2005 to the inst3.net
database from the sales
file group in the inst1.net
database:
CONNECT strmadmin/strmadminpw@inst3.net DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES( file_group_name => 'strmadmin.sales', version_name => 'v_q1fy2005', datafiles_directory_object => 'q1fy2005', repository_db_link => 'inst1.net', tablespace_names => tbs_set); END; /
The tablespaces are read-only when they are attached. Because the reports on inst3.net
do not change the tablespaces, the tablespaces can remain read-only.
Run the reports on the data in the sales tablespaces at the inst3.net
database.
Drop the tablespaces and their contents at inst3.net
:
DROP TABLESPACE sales_tbs1 INCLUDING CONTENTS; DROP TABLESPACE sales_tbs2 INCLUDING CONTENTS;
The tablespaces are dropped from the inst3.net
database, but the tablespace files remain in the directory object.
Create a directory object for the second quarter in fiscal year 2005 on inst3.net
. The directory object must have the same name as the directory object used by the tablespace repository in the inst1.net
database for the second quarter. However, the directory paths of the directory objects on inst1.net
and inst3.net
do not need to match.
CREATE OR REPLACE DIRECTORY q2fy2005 AS '/usr/sales_data/fy2005q2';
The specified file system directory must exist when you create the directory object.
Connect to the inst1.net
database and copy the datafile and the export dump file for the second quarter to the inst3.net
database:
CONNECT strmadmin/strmadminpw@inst1.net BEGIN DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'q2fy2005', source_file_name => 'sales_tbs1.dbf', destination_directory_object => 'q2fy2005', destination_file_name => 'sales_tbs1.dbf', destination_database => 'inst3.net'); DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'q2fy2005', source_file_name => 'sales_tbs2.dbf', destination_directory_object => 'q2fy2005', destination_file_name => 'sales_tbs2.dbf', destination_database => 'inst3.net'); DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'q2fy2005', source_file_name => 'expdat18.dmp', destination_directory_object => 'q2fy2005', destination_file_name => 'expdat18.dmp', destination_database => 'inst3.net'); END; /
Before you run the PUT_FILE
procedure for the export dump file, you can query the DBA_FILE_GROUP_FILES
data dictionary view to determine the name and directory object of the export dump file. For example, run the following query to list this information for the export dump file in the v_q2fy2005
version:
COLUMN FILE_NAME HEADING 'Export Dump|File Name' FORMAT A35 COLUMN FILE_DIRECTORY HEADING 'Directory Object' FORMAT A35 SELECT FILE_NAME, FILE_DIRECTORY FROM DBA_FILE_GROUP_FILES where FILE_GROUP_NAME = 'SALES' AND VERSION_NAME = 'V_Q2FY2005';
Attach the tablespace set for the second quarter of 2005 to the inst3.net
database from the sales
file group in the inst1.net
database:
CONNECT strmadmin/strmadminpw@inst3.net DECLARE tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; BEGIN DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES( file_group_name => 'strmadmin.sales', version_name => 'v_q2fy2005', datafiles_directory_object => 'q2fy2005', repository_db_link => 'inst1.net', tablespace_names => tbs_set); END; /
The tablespaces are read-only when they are attached. Because the reports on inst3.net
do not change the tablespace, the tablespaces can remain read-only.
Run the reports on the data in the sales tablespaces at the inst3.net
database.
Drop the tablespaces and their contents:
DROP TABLESPACE sales_tbs1 INCLUDING CONTENTS; DROP TABLESPACE sales_tbs2 INCLUDING CONTENTS;
The tablespaces are dropped from the inst3.net
database, but the tablespace files remain in the directory object.
Steps 10-14 can be repeated whenever a quarter ends to run reports on each quarter.
The DBMS_FILE_GROUP
package can create a file group repository, add versioned file groups to the repository, and copy versioned file groups from the repository. This section illustrates how to use a file group repository with a scenario that stores reports in the repository.
In this scenario, a business sells books and music over the internet. The business runs weekly reports on the sales data in the inst1.net
database and stores these reports in two HTML files on a computer file system. The book_sales.htm
file contains the report for book sales, and the music_sales.htm
file contains the report for music sales. The business wants to store these weekly reports in a file group repository at the inst2.net
remote database. Every week, the two reports are generated on the inst1.net
database, transferred to the computer system running the inst2.net
database, and added to the repository as a file group version. The file group repository stores all of the file group versions that contain the reports for each week.
Figure 16-5 provides an overview of the file group repository created in this example:
Figure 16-5 Example File Group Repository
The benefits of the file group repository are that it stores metadata about each file group version in the data dictionary and provides a standard interface for managing the file group versions. For example, when the business needs to view a specific sales report, it can query the data dictionary in the inst2.net
database to determine the location of the report on the computer file system.
Table 16-4 shows the directory objects created in this example. It shows the directory object created on the inst1.net
database to store new reports, and it shows the directory objects that are associated with file group repository versions on the inst2.net
database.
Table 16-4 Directory Objects Created in Example
Directory Object | Database | Version | Corresponding File System Directory |
---|---|---|---|
|
|
Not associated with a file group repository version |
|
|
|
|
|
|
|
|
|
This example makes the following assumptions:
The inst1.net
and inst2.net
databases exist.
The inst1.net
and inst2.net
databases do not share a file system.
Networking is configured between the databases so that they can communicate with each other.
The inst1.net
database runs reports on the books and music sales data in the database and stores the reports as HTML files on the computer file system.
The following steps configure and populate a file group repository at a remote database:
Connect as an administrative user to the remote database that will contain the file group repository:
CONNECT strmadmin/strmadminpw@inst2.net
The administrative user must have the necessary privileges to create directory objects and run the procedures in the DBMS_FILE_GROUP
package.
Create a directory object to hold the first version of the file group:
CREATE OR REPLACE DIRECTORY sales_reports1 AS '/home/sales_reports/fg1';
The specified file system directory must exist when you create the directory object.
Connect as an administrative user to the database that runs the reports:
CONNECT strmadmin/strmadminpw@inst1.net
The administrative user must have the necessary privileges to create directory objects.
Create a directory object to hold the latest reports:
CREATE OR REPLACE DIRECTORY sales_reports AS '/home/sales_reports';
The specified file system directory must exist when you create the directory object.
Create a database link to the inst2.net
database:
CREATE DATABASE LINK inst2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'inst2.net';
Run the reports on the inst1.net
database. Running the reports should place the book_sales.htm
and music_sales.htm
files in the directory specified in Step 4.
Transfer the report files from the computer system running the inst1.net
database to the computer system running the inst2.net
database using file transfer protocol (FTP) or some other method. Make sure the files are copied to the directory that corresponds to the directory object created in Step 2.
Connect in SQL*Plus to inst2.net
:
CONNECT strmadmin/strmadminpw@inst2.net
Create the file group repository that will contain the reports:
BEGIN DBMS_FILE_GROUP.CREATE_FILE_GROUP( file_group_name => 'strmadmin.reports'); END; /
The reports
file group repository is created with the following default properties:
The minimum number of versions in the repository is 2. When the file group is purged, the number of versions cannot drop below 2.
The maximum number of versions is infinite. A file group version is not purged because of the number of versions in the of the file group in the repository.
The retention days is infinite. A file group version is not purged because of the amount of time it has been in the repository.
Create the first version of the file group:
BEGIN DBMS_FILE_GROUP.CREATE_VERSION( file_group_name => 'strmadmin.reports', version_name => 'sales_reports_v1', comments => 'Sales reports for week of 06-FEB-2005'); END; /
Add the report files to the file group version:
BEGIN DBMS_FILE_GROUP.ADD_FILE( file_group_name => 'strmadmin.reports', file_name => 'book_sales.htm', file_type => 'HTML', file_directory => 'sales_reports1', version_name => 'sales_reports_v1'); DBMS_FILE_GROUP.ADD_FILE( file_group_name => 'strmadmin.reports', file_name => 'music_sales.htm', file_type => 'HTML', file_directory => 'sales_reports1', version_name => 'sales_reports_v1'); END; /
Create a directory object on inst2.net
to hold the next version of the file group:
CREATE OR REPLACE DIRECTORY sales_reports2 AS '/home/sales_reports/fg2';
The specified file system directory must exist when you create the directory object.
At the end of the next week, run the reports on the inst1.net
database. Running the reports should place new book_sales.htm
and music_sales.htm
files in the directory specified in Step 4. If necessary, remove the old files from this directory before running the reports.
Transfer the report files from the computer system running the inst1.net
database to the computer system running the inst2.net
database using file transfer protocol (FTP) or some other method. Make sure the files are copied to the directory that corresponds to the directory object created in Step 12.
While connected in SQL*Plus to inst2.net
as an administrative user, create the next version of the file group:
BEGIN DBMS_FILE_GROUP.CREATE_VERSION( file_group_name => 'strmadmin.reports', version_name => 'sales_reports_v2', comments => 'Sales reports for week of 13-FEB-2005'); END; /
Add the report files to the file group version:
BEGIN DBMS_FILE_GROUP.ADD_FILE( file_group_name => 'strmadmin.reports', file_name => 'book_sales.htm', file_type => 'HTML', file_directory => 'sales_reports2', version_name => 'sales_reports_v2'); DBMS_FILE_GROUP.ADD_FILE( file_group_name => 'strmadmin.reports', file_name => 'music_sales.htm', file_type => 'HTML', file_directory => 'sales_reports2', version_name => 'sales_reports_v2'); END; /
The file group repository now contains two versions of the file group that contains the sales report files. Repeat steps 12-16 to add new versions of the file group to the repository.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_FILE_GROUP
package