Completed the Installing Oracle Database 10g on Windows Using Real Application Clusters (RAC) and Automated Storage Management (ASM) tutorial

OR

Completed the Installing Oracle Database 10g on Linux tutorial

Open a browser window, and enter the following URL:

http://<hostname>:5500/em

Log in as system/<password>, and click Login.

Click the Administration tab.

Under Schema, click Tables.

Enter SH for the schema and SALES in the Object Name field. Click Go.

Make sure SALES is selected and click Edit.

Click the Partitions tab.

The data has been classified by date using the TIME_ID column, and the data has been grouped by years or quarters depending on how old it is. All the data for each date range is held in a partition. Classifying by time is a very common method and in this case is the most suitable method. Click the Cluster Database: O10G locator link.

Click Tablespaces under Storage.

Click Create.

Enter HIGH_PERFORMANCE in the Name field, and scroll down.

In the Datafiles section, click Add. Note that for the High_Performance tier the data files would typically be placed on a high performance storage device or by using ASM. Because those devices are not unavailable, the data files will have to be placed on a single device in order to complete this step.

Enter high_perf in the File Name field and specify 3 MB for the file size. Click Continue.

Click OK.

Now you can create the next tablespace. Click Create. The process is now repeated to create the Low Cost Storage Tier.

Enter LOW_COST in the Name field, and scroll down.

In the Datafiles section, click Add. Note that for the LOW_COST tier the data files would typically be placed on a low costs ATA storage devices. Because those devices are not unavailable, the data files will have to be placed on a single device to complete this step.

Enter low_cost in the File Name field and specify 10 MB for the file size. In the Storage section, select the Automatically extend datafile when full check box, and enter 10 KB for the increment. Click Continue.

Click OK.

Click Create.

Enter HISTORICAL in the Name field, and scroll down. The final tier is the Historical tier where all the orders before 2000 are held.

In the Datafiles section, click Add. Note that for the Historical tier the data files would typically be placed on a low cost ATA storage devices. Because those devices are not unavailable, the data files will have to be placed on a single device in order to complete this step.

Enter historical in the File Name field, and specify 10 MB for the file size. Click Continue.

Click OK.

Scroll down to see the list of tablespaces you just created.

Note that the tablespaces are currently empty. Scroll up to the top. Now you see the three tablespaces that correspond to each storage tier. Note that in a real ILM implementation, the tablespaces would not have to be named to correspond to the required storage tiers. The tablespace approach has been used in this tutorial to illustrate the process, because three types of actual storage devices are unavailable.

Click the Cluster Database: O10G locator link.

Under Schema, select Tables.

Enter SH in the Schema field and SALES in the Object Name field. Click Go.

Click SALES.

Click the Partitions tab.

Assign the HIGH_PERFORMANCE partitions first. Select the last set of pages in the drop-down list. The High Performance storage tier should only contain orders for Q3 to Q4 for 2003.

Select the SALES_Q3_2003 partition, and select Move in the Actions drop-down list. Click Go.

Click the searchlight icon for the Tablespaces field.

Select the HIGH_PERFORMANCE tablespace, and click Select.

Click OK to assign the partition to the HIGH_PERFORMANCE tablespace.

Select the SALES_Q4_2003 partition, and select Move in the Actions drop-down list. Click Go.

Enter HIGH_PERFORMANCE in the tablespace field, and click OK.

Click Apply to commit your changes.

Now you can assign the LOW_COST tablespace to the partitions from Q4 2000 to Q2 2003. Select any of the partitions within this timeframe (SALES Q1 2002). Select Move in the Actions drop-down list, and click Go.

Enter LOW_COST in the Tablespace field, and click OK.

Perform the previous steps for the following partitions, and assign the LOW COST tablespace. When done, click Apply.

SALES_Q1_2000
SALES_Q2_2000
SALES_Q3_2000
SALES_Q4_2000
SALES_Q1_2001
SALES_Q2_2001
SALES_Q3_2001
SALES_Q4_2001
SALES_Q1_2002
SALES_Q2_2002
SALES_Q3_2002
SALES_Q4_2002
SALES_Q1_2003
SALES_Q2_2003

Perform the previous steps for the following partitions and assign the HISTORICAL tablespace. When done, click Apply.

SALES_1995
SALES_1996
SALES_H1_1997
SALES_H2_1997
SALES_Q1_1998
SALES_Q2_1998
SALES_Q3_1998
SALES_Q4_1998
SALES_Q1_1999
SALES_Q2_1999
SALES_Q3_1999
SALES_Q4_1999

All your partitions have been assigned to the various storage tiers. Click the Cluster Database: O10G locator link.

Click Tablespaces.

Scroll down to see your tablespaces are now full with data.

Notice that your tablespaces are now full with data. Scroll up to the top, and click the Cluster Database: O10G locator link.

In the Administration page, click Views under Schema.

Create a view called SALES_HISTORICAL that allows access only to the historical data, that is, up to December 31, 1999. Click Create.

Enter SALES_HISTORICAL in the Name field and SH in the Schema field. Enter the following Query Text, and then click OK.

SELECT * FROM sales WHERE time_id < '1-Jan-2000'

Now create a second view SALES_CURRENT that allows access to all the current data, that is, from January 1, 2000. Click Create.

Enter SALES_CURRENT as the Name, SH as the Schema, and enter the following Query Text. Then click OK.

SELECT * from sales where time_id > '31-Dec-1999'

Scroll down to see the list of views. You can see that you have two views created, SALES_CURRENT for the recent data and SALES_HISTORICAL for all the old orders.

Notice the Views you just created. Now you need to create the user SH_USER that will use them. Scroll up and click the Cluster Database: O10G locator link.

To illustrate, the SALES_HISTORICAL view is only showing data before the year 2000. Query the SALES_HISTORICAL view to report the most recent data it holds. This can be found by querying the time_id column. Open a terminal window and enter the following commands:

cd /home/oracle/wkdir
sqlplus sh/sh
@ilm_query01 

The @ilm_query01.sql command contains the following:

select max(time_id)from sh.sales_historical;

Because this view contains data only up to the end of 1999, you can now report which Customers purchased the most goods up to the year 2000. Execute the following command:

@ilm_query02 

The @ilm_query02.sql contains the following:

SELECT cust_last_name, sum(amount_sold) 
  FROM sh.sales_historical h, sh.customers c 
  WHERE c.cust_id=h.cust_id 
  GROUP BY cust_last_name 
  ORDER BY sum(amount_sold) desc;

As user SH, count the number of rows visible in the Sales table. Make a note of this number. At the moment, you have access to all the data in the Sales table. From your SQL*Plus session, run the following SQL script:

@ilm_count_sales

The @ilm_count_sales.sql contains the following:

connect sh/sh
select count(*) from sales;

Next as user SYSTEM, add the following VPD Policy which states that a user only see data from January 1, 2000. From your SQL*Plus session, run the following SQL script:

@ilm_add_policy

The @ilm_add_policy.sql contains the following:

connect system/oracle
CREATE OR REPLACE FUNCTION ilm_pf1 (oowner IN VARCHAR2, ojname IN VARCHAR2) 
RETURN VARCHAR2 AS
con VARCHAR2(200);
BEGIN
  con :='time_id > ''31-DEC-1999''';
RETURN (con);
END ilm_pf1;
/
BEGIN
  DBMS_RLS.ADD_POLICY (object_schema=>'SH',object_name=>'Sales',
   policy_name=>'ilm_current_data',function_schema=>'system',
   policy_function=>'ilm_pf1',
   sec_relevant_cols=>'time_id');
END;
/

Now repeat the query of the Sales table as user SH and fewer rows should be visible because only data from January 1, 2000 is available to this user. From your SQL*Plus session, run the following SQL script:

@ilm_count_sales

The @ilm_count_sales.sql contains the following:

connect sh/sh
select count(*) from sales;

In the Administration page, under Security, click Users.

The first step is to create a new user SH_USER. Click Create.

Enter SH_USER for the Name and Password. Enter EXAMPLE in the Default Tablespace field, and TEMP in the Temporary Tablespace field. Click the Roles tab.

Because this is not a privileged user, only limited resources are granted to this user. Click Modify.

Select RESOURCE from the list of Available Roles, and click Move. Then click OK.

To ensure that this user can only read the sales and customer data, they must be explicitly granted the SELECT privilege. Click the Object Privileges tab.

Select Table in the Select Object Type drop-down list, and click Add.

In the Select Table Objects field, enter SH.SALES, SH.SALES_HISTORICAL, SH.CUSTOMERS. Select Select from the Available Privileges list, and click Move. Then click OK.

Click OK to create the user.

Select the Cluster Database: O10G locator link.

Execute the following SQL script:

@ilm_update01

The @ilm_update01.sql contains the following:

update sh.sales set prod_id=12 where prod_id=13 and time_id='10-Jan-98';

Switch back to Enterprise Manager Database Control. In the Administration page, click Tablespaces under Storage.

In the Search field, enter HISTORICAL, and click Go.

Then select Make Readonly from the Actions drop-down list, and click Go.

Note that the tablespace is now set to read only. Click the Cluster Database: O10G locator link.