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.

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;

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;
/