------------------------------------------------------------------------
-- create tablespace
create smallfile tablespace atwlam_data
datafile '/u01/app/oracle/oradata/orcl/atwlam_data_1.dbf'
size 100m
autoextend on
next 10m
maxsize unlimited
logging
extent management local
segment space management auto;
-- resize a datafile
alter database datafile '/u01/app/oracle/oradata/orcl/atwlam_data_1.dbf' resize 200m
-- check tablespace status
select file_name, tablespace_name, (bytes/1024) size
from dba_data_files ;
------------------------------------------------------------------------
-- remove existing user and roles
drop user atwlam cascade;
drop user atwlam_user cascade;
drop role atwlam_rw;
drop role atwlam_ro;
-- create schema owner
create user atwlam identified by password
default tablespace atwlam_data
temporary tablespace temp
quota unlimited on atwlam_data
quota unlimited on atwlam_index;
grant connect, resource to atwlam;
alter user atwlam default role all
-- create application user.
create user atwlam_user identified by password
default tablespace atwlam_data
temporary tablespace temp;
grant connect to atwlam_user;
grant create table to atwlam_user;
grant create view to atwlam_user;
grant create any trigger to atwlam_user;
grant create any procedure to atwlam_user;
grant create sequence to atwlam_user;
grant create synonym to atwlam_user;
------------------------------------------------------------------------
-- create schema roles
create role atwlam_rw;
create role atwlam_ro;
grant atwlam_rw to atwlam_user;
-- create table
conn atwlam/password
create table test_tab (
id number,
description varchar2(50),
constraint test_tab_pk primary key (id)
);
-- grant table access to roles
grant select on test_tab to atwlam_ro;
grant select, insert, update, delete on test_tab to atwlam_rw;
-- create synonym from application user
sql> conn atwlam_user/password
create synonym test_tab for atwlam.test_tab;
------------------------------------------------------------------------
-- change default profile
alter profile default
limit
password_life_time unlimited
password_grace_time unlimited
password_lock_time unlimited
failed_login_attempts unlimited;
