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

Login as system/<password> and click Login.

Click Administration tab.

Under Schema, click Tables.

Enter SH for the schema and SALES for the Object Name and click Go.

Make sure SALES is selected and click Edit.

Select Partitions tab.

The data has been classified by Date using the column TIME_ID 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 Database breadcrumb.

Under Storage, select Tablespaces.

Click Create.

Enter HIGH_PERFORMANCE as the Name. 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 devices or using ASM. Since those devices are not unavailable, the datafiles will have to be placed on a single device in order to complete this step.

Enter high_perf for the File Name and specify 3 MB for the File Size. Then 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 as the Name. 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. Since those devices are not unavailable, the datafiles will have to be placed on a single device in order to complete this step.

Enter low_cost for the File Name and specify 10 MB for the File Size. Under Storage, click Automatically extend datafile when full (AUTOEXTEND) and enter 10KB for the increment. Then click Continue.

Click OK.

Click Create. The final tier is the Historical tier where all of the orders prior to 2000 are held.

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

Enter historical for the File Name and specify 10 MB for the File Size. Then click Continue.

Click OK.

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

You see the three tablespaces which correspond to each Storage Tier. Notice that tablespaces are currently empty. 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 different types of actual storage devices are unavailable.

Scroll up to the top. Click Database breadcrumb.

Under Schema, select Tables.

Enter SH for Schema and SALES for Object Name. Then click Go.

Select SALES.

Select Partitions tab.

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

Select the SALES_Q3_2003 partition, select Move for the Action and click Go.

Select the search light 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, select Move for the Action and 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 (i.e. SALES Q1 2002), select the Action Move and click Go.

Enter LOW_COST in the tablespace field and click OK.

Perform the prior 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 prior 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 Database breadcrumb.

Click Tablespaces.

Notice that your tablespaces are now full with data.

Scroll up to the top and click Database breadcrumb.

From the Administration tab, under Schema, click Views.

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

Enter SALES_HISTORICAL as the Name, SH as the Schema and enter the following Query Text, then click OK.

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

Now create a second view SALES_CURRENT which allows access to all the current data which is from the 1st Jan 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'

Notice the Views you just created. You can see that you have 2 views created, SALES_CURRENT for the recent data and SALES_HISTORICAL for all of the old orders. Now you need to create the user SH_USER that will use them. Scroll up and click Database breadcrumb.

To illustrate the view SALES_HISTORICAL is only showing data prior to 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 contains the following:

select max(time_id)from sh.sales_historical;

Since this view only contains data 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;

First 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 sqlplus 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 1-Jan-2000. From your sqlplus 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 the 1st Jan 2000 is available to this user. From your sqlplus session, run the following SQL script:

@ilm_count_sales

The @ilm_count_sales.sql contains the following:

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

From the Administration tab, 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 a Default Tablespace of EXAMPLE and a Temporary Tablespace of TEMP, then click Role tab.

Since 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 explictly granted the SELECT privilege. Click Object Privileges tab.

Select the Object Type Table and click Add.

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

Click OK to create the user.

Select the Database breadcrumb.

Execute the following SQL commands:

connect sh_user/sh_user
@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. From the Administration tab, under Storage, click Tablespaces.

Select the tablespace HISTORICAL, select the Action Make Readonly and click Go.

Notice that the tablespace is now set to read only.