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