ermp项目oracle脚本

create tablespace ERMP datafile 'D:\oracle\product\10.2.0\oradata\orcl\ERMP.DBF' size 500M autoextend on next 10M maxsize unlimited;
create user ermp identified by ermp default tablespace ERMP;
grant connect to ermp;
grant resource to ermp;
grant dba to ermp;
grant unlimited tablespace to ermp;
grant select any table to ermp;
grant select any dictionary to ermp;
create temporary tablespace termp tempfile 'D:\oracle\product\10.2.0\oradata\orcl\TERMP.DBF' size 100M;
alter user ermp temporary tablespace termp;
alter database tempfile 'D:\oracle\product\10.2.0\oradata\orcl\TERMP.DBF' resize 500M;
alter database tempfile 'D:\oracle\product\10.2.0\oradata\orcl\TERMP.DBF' autoextend on next 10M maxsize unlimited;


create table org_info
(
  ORG_CODE                   VARCHAR2(20) primary key,
  ORG_NAME                   VARCHAR2(200),
  ORG_TYPE                   VARCHAR2(2),
  ORG_LEVEL                  VARCHAR2(1) not null,
  INTERNAL_CODE              VARCHAR2(20),
  STATUS                     VARCHAR2(2) not null,
  SUPER_CODE                 VARCHAR2(20) references org_info(org_code),
  POST_CODE                  VARCHAR2(10),
  ADDRESS                    VARCHAR2(256),
  CREATE_DATE                VARCHAR2(10),
  LEADER                     VARCHAR2(100),
  PHONE                      VARCHAR2(20),
  REMARK                     VARCHAR2(256)
);
create table user_info
(
  USER_CODE                  VARCHAR2(20) primary key,
  PASSWORD                   VARCHAR2(32) not null,
  STATUS                     VARCHAR2(2) not null,
  INTERNAL_CODE              VARCHAR2(20),
  USER_NAME                  VARCHAR2(20),
  ORG_CODE                   VARCHAR2(20) references org_info(org_code),
  ID_CARD                    VARCHAR2(20),
  PHONE                      VARCHAR2(20),
  MOBILE                     VARCHAR2(20),
  EMAIL                      VARCHAR2(40),
  ADDRESS                    VARCHAR2(20),
  REMARK                     VARCHAR2(256)
);
create table role_info
(
  ROLE_CODE                  VARCHAR2(20) primary key,
  ROLE_NAME                  VARCHAR2(40),
  SYSTEM              VARCHAR2(20),
  STATUS                     VARCHAR2(2) not null,
  REMARK                     VARCHAR2(256)
);
create table user_role
(
  USER_CODE                  VARCHAR2(20) not null references user_info(user_code),
  ROLE_CODE                  VARCHAR2(20) not null references role_info(role_code),
  STATUS                     VARCHAR2(2) not null
);


 
--系统上建立数据库服务名hrCenterDB指向hr系统
--将ermp用户赋予DBA权限执行以下语句
drop public database link SYN_CENTER;
create public database link SYN_CENTER
  connect to hr_syn
  identified by hr_syn
  using 'hrCenterDB';


exp备份shell

# !/bin/bash
# whoami
# root
# description: backup oracle ermp automatic shell

export ORACLE_HOME=/oracle/app/product/10g;
export ORACLE_SID=ermp;

rq=`date '+%y%m%d'`;
su - oracle -c "export ORACLE_SID=ermp;export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK;/oracle/app/product/10g/bin/exp ermp/ermp file=/oraback/fullbackup_dmp/ermp$rq.dmp log=/oraback/fullbackup_dmp/ermp$rq.log";