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;

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;