Create a tablespace, user, and permission assignment script in Oracle

-- 1. Create tablespace
Create tablespace hmlp datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ hmlp. dbf'
Size 200 m autoextend on next 10 m maxsize unlimited;
Alter database datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ hmlp. dbf' autoextend on;

-- Drop tablespace hmlp including contents and datafiles cascade constraints;

-- 3. create user, associate with temp tablespace and tablespace
Create user hmlpuser identified by beyondsoft
Default tablespace hmlp
Temporary tablespace temp;

-- Alter user hmlpuser identified by beyondsoft
-- Default tablespace hmlp
-- Temporary tablespace temp;

-- Drop user hmlpuser cascade;

-- 4. Grant user to connect, resource and DBA
Grant connect, resource to hmlpuser;
Grant sysdba to hmlpuser;

-- 5. GANT select, update, insert, delete to user on any table
Grant select any table to hmlpuser;
Grant update any table to hmlpuser;
Grant insert any table to hmlpuser;
Grant delete any table to hmlpuser;
Grant all privileges to hmlpuser;

Grant select any dictionary to hmlpuser;

Zookeeper ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Log on to the created table with the user hmlpuser, so that the current user has the permission to access and operate these tables.

Create Table hm_app_error_log
(
Error_date date,
Error_code varchar2 (10 byte ),
Description varchar2 (3000 byte ),
Page_name varchar2 (50 byte ),
Method_name varchar2 (50 byte ),
Line_no varchar2 (4 byte)
) Tablespace hmlp

......

Create unique index hm_user_u01 on hm_user
(Emp_id)
Logging
Tablespace hmlp
Pctfree 10
Initrans 2
Maxtrans 255
Storage (
Initial 1 m
Minextents 1
Maxextents Unlimited
Pctincrease 0
Buffer_pool default
)
Noparallel;

-- Add Constraints

Alter table hm_summ_program_with_cust_ref add (
Constraint hm_prgm_custref_uk
Unique (status, component, program, day_id, cust_ref)
Using index hm_prgm_custref_uk );