O'Reilly logo

Oracle Distributed Systems by Charles Dye

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

An Example

Since data dictionary tables cannot be replicated, the DBA must perform various administrative tasks in multiple sites. One particular task that I have found to be a needless nuisance is user administration, so I have created the package USERADMIN to replicate calls to create and drop users and to grant and revoke roles. The scripts to create the package are:

cr_seq_audit_admin.sql

Creates the sequence SEQ_AUDIT_ADMIN which is used to populate the ID field in the table AUDIT_ADMIN.

cr_audit_admin.sql

Creates the table AUDIT_ADMIN, which logs usage of the USERADMIN package.

pl_useradmin.sql

Calls the scripts cr_seq_audit_admin.sql and cr_audit_admin.sql and creates the package USERADMIN. This script should be run by user SYSTEM.

These scripts are included here .

cr_seq_audit_admin.sql

---------------------------------------------------------------------------
-- Filename:    cr_seq_audit_admin.sql
-- Purpose:     Creates SEQ_AUDIT_ADMIN used by USER_ADMIN procedure
-- Author:      Chas. Dye (cdye@excitecorp.com)
-- Date:        5-Mar-1998
--------------------------------------------------------------------------- 

set echo on
set termout on
spool seq_audit_admin.log

DROP PUBLIC SYNONYM seq_audit_admin
/
DROP SEQUENCE seq_audit_admin
/
CREATE SEQUENCE seq_audit_admin
START WITH 1
/
CREATE PUBLIC SYNONYM seq_audit_admin FOR seq_audit_admin
/

spool off

cr_audit_admin.sql

--------------------------------------------------------------------------- -- Filename: cr_audit_admin.sql -- Purpose: Creates table ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required