Setting up Oracle Schema

Submitted by code_admin on Wed, 07/25/2018 - 10:27

Code used to setup an oracle schema and tablespace etc. for playing about with.

  1. --run as system
  2. --whenever sqlerror exit;
  3.  
  4. drop tablespace RJM_USER INCLUDING CONTENTS AND DATAFILES;
  5. create
  6. tablespace RJM_USER
  7. DATAFILE '/u01/app/oracle/oradata/ORCL/rjm_users.dbf' size 40M;
  8.  
  9.  
  10. drop USER ODI_SOURCE CASCADE;
  11. create USER ODI_SOURCE
  12. IDENTIFIED BY ODI_SOURCE
  13. DEFAULT TABLESPACE RJM_USER
  14. ;
  15.  
  16. ALTER USER ODI_SOURCE QUOTA 40M ON RJM_USER;
  17.  
  18. GRANT create session TO ODI_SOURCE;
  19. GRANT create table TO ODI_SOURCE;
  20. GRANT create view TO ODI_SOURCE;
  21. GRANT create any trigger TO ODI_SOURCE;
  22. GRANT create any procedure TO ODI_SOURCE;
  23. GRANT create sequence TO ODI_SOURCE;
  24. GRANT create synonym TO ODI_SOURCE;

Adjustment for 12c - Multitenant

Log in as system as normal
View pdb's:

  1. SELECT name, pdb
  2. FROM   v$services
  3. ORDER BY name;
  4.  
  5. show con_name;

PDBORCL is a default pdb to use:

  1. alter session set container=PDBORCL;

Tags

RJM Article Type
Quick Reference