Oracle Database - Script to create tablespace and schema and provide common grants

_readme.md

Script to create Oracle Database tablespace & schema

Instructions

Run the script as SYS/SYSDBA. Script will create a new tablespace and schema and provide common grants to create user objects.

creation.sql

accept DEFAULT_TABLESPACE CHAR prompt 'Enter Default tablespace name. [USERS] ' default 'USERS'
accept TEMP_TABLESPACE CHAR prompt 'Enter Temporary tablespace name. [TEMP] ' default 'TEMP'
accept SCHEMA_NAME CHAR prompt 'Enter schema Name [] '
accept SCHEMA_PASSWORD CHAR prompt 'Enter schema password [] '

column dcol new_value spool_date noprint
select to_char(sysdate,'RRRRMMDD_HH24MISS') dcol from dual;

spool creation_&spool_date..log
set verify off

-- Create default tablespace if it doesnt exist
declare
  l_count number;
begin
  select count(*)
    into l_count
  from dba_tablespaces
  where tablespace_name ='&DEFAULT_TABLESPACE';
  
  if l_count = 0
  then
    execute immediate 'create tablespace &DEFAULT_TABLESPACE datafile ''&DEFAULT_TABLESPACE.dbf'' size 250M autoextend on maxsize unlimited';
  end if;
end;
/

-- Create temp tablespace if it doesnt exist
declare
  l_count number;
begin
  select count(*)
    into l_count
  from dba_tablespaces
  where tablespace_name ='&TEMP_TABLESPACE';
  
  if l_count = 0
  then
    execute immediate 'create tablespace &TEMP_TABLESPACE datafile ''&TEMP_TABLESPACE.dbf'' size 250M autoextend on maxsize unlimited';
  end if;
end;
/


create user &SCHEMA_NAME identified by &SCHEMA_PASSWORD default tablespace &DEFAULT_TABLESPACE temporary tablespace &TEMP_TABLESPACE;
alter user &SCHEMA_NAME quota unlimited on &DEFAULT_TABLESPACE;
GRANT CREATE CLUSTER TO &SCHEMA_NAME;
GRANT CREATE DIMENSION TO &SCHEMA_NAME;
GRANT CREATE INDEXTYPE TO &SCHEMA_NAME;
GRANT CREATE JOB TO &SCHEMA_NAME;
GRANT CREATE MATERIALIZED VIEW TO &SCHEMA_NAME;
GRANT CREATE OPERATOR TO &SCHEMA_NAME;
GRANT CREATE PROCEDURE TO &SCHEMA_NAME;
GRANT CREATE SEQUENCE TO &SCHEMA_NAME;
GRANT CREATE SESSION TO &SCHEMA_NAME;
GRANT CREATE SYNONYM TO &SCHEMA_NAME;
GRANT CREATE TABLE TO &SCHEMA_NAME;
GRANT CREATE TRIGGER TO &SCHEMA_NAME;
GRANT CREATE TYPE TO &SCHEMA_NAME;
GRANT CREATE VIEW TO &SCHEMA_NAME;
GRANT CREATE SESSION TO &SCHEMA_NAME;
GRANT IMP_FULL_DATABASE TO &SCHEMA_NAME;

spool off;