Nov 29, 2014

Create a new schema (user, tablespace) in Oracle

------------------------------------------------------------------------
-- 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;


No comments: