Oracle Materialized View

Each night, in OID database I have the following job running (I don’t want to change anything related to built-in table orasso.wwsso_audit_log_table_t, not even creating materialized view/log directly on it):

begin
INSERT INTO SSO_AUDIT_LOG SELECT * FROM orasso.wwsso_audit_log_table_t where LOG_DATE>=(sysdate-1) and LOG_DATE<sysdate;
commit;
end;

For Oracle Portal Last Login Time information:

> CREATE MATERIALIZED VIEW LOG ON SSO_AUDIT_LOG tablespace users with primary key, rowid(user_name) including new values;

Materialized view log created.

> alter materialized view log on “LOGUSER”.”SSO_AUDIT_LOG” add(LOG_DATE);

Materialized view log altered.

> create materialized view mv_last_login
2  refresh fast
3  enable query rewrite
4  as
5  select user_name, max(log_date) from SSO_AUDIT_LOG group by user_name;

Materialized view created.

> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews where mview_name=’MV_LAST_LOGIN’;

> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews where mview_name=’MV_LAST_LOGIN’;

LAST_REF LAST_REFR
——– ———
COMPLETE 13-OCT-08

> desc mlog$_sso_audit_log
Name                                      Null?    Type
—————————————– ——– —————————-
SUBSCRIBER_ID                                      NUMBER
LOG_ID                                             NUMBER
USER_NAME                                          VARCHAR2(256)
M_ROW$$                                            VARCHAR2(255)
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
LOG_DATE                                           DATE

> alter materialized view MV_LAST_LOGIN refresh fast on commit;
alter materialized view MV_LAST_LOGIN refresh fast on commit
*
ERROR at line 1:
ORA-32337: cannot alter materialized view with pending changes refresh on
commit

> exec dbms_mview.refresh(‘MV_LAST_LOGIN’);

PL/SQL procedure successfully completed.

> alter materialized view MV_LAST_LOGIN refresh fast on commit;

Materialized view altered.

( If you want fast refresh to occur whenever the database commits a transaction, ON COMMIT clause during creation of the materialized view should be specified )

Selenium Monitoring

I finally get Selenium Remote Control to work with Oracle Portal login today. It is monitoring our live site every 10 minutes, logging into the portal site and place an ad. If anything went wrong in the process, it should send out a page. I am using Perl / Test::WWW::Selenium this time.

Portal Encoding CSS URL

Not sure why the following does not work now:

pageContext.setAttribute("cssurl", response.encodeURL("CSSURL"));
...
var link = document.createElement('link');
link.setAttribute('href', '<c:out value="${cssurl}"/>');
The following still works:
link.setAttribute('href', '<%=response.encodeURL("CSSURL")%>');