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