Oracle 建立資料表空間，使用者，許可權分配指令碼

--1. create tablespace
create tablespace hmlp datafile 'D:\oracle\product\10.2.0\oradata\orcl\hmlp.dbf' 
size 200m autoextend on next 10m 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;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

用剛剛建立的使用者hmlpUser 登入建立表，這樣目前使用者就有訪問，操作這些表的許可權了

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          1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

--加約束

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