set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* script to spool a listing of all drop and create */
/*    statements required to rebuild accounts.      */
/* Contents: Table name, col definitions and        */
/*    storage parameters.                           */
/*                                                  */
/*         @c:\oracle\sql\bld_role.sql              */ 
/*                                                  */
/* NOTE: will build all non-sys/system tables       */
/*                                                  */
/* ************************************************ */

set termout off;
set echo off;
set feedback off;

col dummy     noprint format a1;
col dummy2    noprint format a1;
col table_name        noprint format a1;
col command   format a70;

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

select 'GRANT '||PRIVILEGE||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs
where GRANTEE = 'SCOPUS_ROLE' and
      table_name = 'YES_NO'
order by 
      OWNER      ASC,
      TABLE_NAME ASC,
      PRIVILEGE  ASC;

spool create_scopus_role.sql
/
spool off;
set heading on;
set pagesize 100;
set termout on;

commit;










