Best DEBUG for WNA in OAS 10.1.2

In my experience the best way to trace what’s going on if configuring SSO with WNA is to enable JAZN debug. In OAS 10.1.2 it can be enabled with -Djazn.debug.log.enable=true as a Java startup parameter for OC4J_SECURITY

Syncronize and optimize Portal text indexes

To improve performance with Oracle Text based searches in Oracle Portal, you should on a regular basis make sure to syncronize and optimize Portal text indexes (if this is not allready set up during installation).

sqlplus portal/<password>

exec wwv_context.sync
exec wwv_context.optimize
exit

Finding grants from data dictionary

Unless you make an full export with grants you won’t get all grants when doing a export/import with Oracle. So if you don’t have a list of all grants across those schemas you imported, you will have a huge job of compiling and establishing which grants are missing.

The solution to this is to find those grants through the data dictionary on the source system. For instance, if you want to find all grants made by the PORTAL schema, the query would be like this:


SET HEADING OFF
SET PAGES 999
SPOOL grants.sql
SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||grantee||DECODE(grantable,'YES',' WITH GRANT OPTION;',';')
FROM DBA_TAB_PRIVS
WHERE grantor = 'PORTAL';
SPOOL OFF
EXIT


Now you have everything you need in the grants.sql and this script can now be run on the target system.

Which languages are installed in my Portal ?

select title from portal.WWNLS_SYS_LANGUAGE$
where installed <> 0;

Oracle Q-Quote

To avoid quoting quotes in string, Oracle 10g offers the Q-Quote technique.

Let’s say yoy want to select the following from dual: I’m into rock’n’roll

This would normally mean you should quote the three quotes, but with the Q-Quote technique it’s much simpler:

SQL> SELECT q'[I'm into rock'n'roll]' FROM dual;

Tracing JInitiator 1.3.22

In the Windows Control Panel open the Jinitiator Control Panel.
Enter the following in the “Java Run Time parameters” textfield:

-Djavaplugin.trace=true
-Djavaplugin.trace.option=basic|net|security|ext|liveconnect

The traceoptions are cumulative, so if you use security traceoption it will
also record basic and net traces.

These trace messages are shown both on the Jinitiator Console, and in the file
C:\Documents and Settings\<username>\jinitiator1322.trace

Schedule a job using DBMS_SCHEDULER

In this article I show how you can setup automatic rebuild of Portal indexes every day at 04.00 am.

Create your PL/SQL procedure

sqlplus portal/<password>

CREATE OR REPLACE PROCEDURE my_analyze_portal IS
BEGIN
wwsbr_stats.delete_stats;
wwsbr_stats.gather_stats;
wwsbr_stats.enable_monitoring;
wwsbr_stats.gather_stale;
commit;
END;
/

Submit the job

sqlplus portal/<password>

BEGIN
	DBMS_SCHEDULER.create_job (
    job_name        => 'job_my_analyze_portal',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'my_analyze_portal',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Refresh Portal indexes.');
END;
/

COMMIT;

EXIT

View the job

You can view the current status of this job at any time.

SELECT job_name, enabled, last_start_date,next_run_date,comments
FROM user_scheduler_jobs
WHERE job_action='my_analyze_portal';

Put DBMS_SCHEDULER to the test

Imagine you have a job that should run every third minute, monday to friday and sunday, but only between 18.00 and midnight. How would you do that with DBMS_JOB ? The short answer is: You wouldn’t! The longer answer would be to have a chain of jobs enabling, disabling and redefining eachothers.

With DBMS_SCHEDULER however, this is just a walk in the park, and can be achieved with one single statement in a block:

BEGIN
dbms_scheduler.create_schedule (
schedule_name => ‘WOW_SCHEDULE’,
repeat_interval => ‘FREQ=DAILY; BYDAY=1,2,3,5,7; BYHOUR=18,19,20,21,22,23; BYMINUTE=0,3,6,9,12,15,18,21,24,27,30,33,36,39,42,45,48,51,54,57; BYSECOND=0′,
comments => ‘Every third minut between 18.00 and midnight monday to friday and sundays’);
END;
/

Find an attribute’s value dynamically

Imagine you have a custom attribute on a lot of Portal pages and you want to read it’s value programatically in order to use it in some other context. This is one way you could do it:


SELECT ta.value
FROM portal.wwv_things t, portal.wwv_thingattributes ta
WHERE t.id = ta.masterthingid
AND ta.attributeid IN (
SELECT id FROM portal.wwsbr_attribute$ WHERE name = '&attributeName'
AND siteid IN (SELECT id FROM portal.wwsbr_sites$ WHERE name = '&siteName')
)
AND t.siteid IN (SELECT id FROM portal.wwsbr_sites$ WHERE name = '&siteName')
AND t.cornerid=&pageId;