(一) 、创建业务数据库
注释:Oracle配置监听文件 D:\app\zhishui\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
创建 szmz_gos database--》01_create_tablespace_szmz_gos.sql
创建用户 szmz_gos user--》02_create_user_szmz_gos.sql
【
--创建临时库
create temporary tablespace szmz_gos_temp
tempfile 'd:\oracledata\szmz_gos_temp.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local;
--创建库
create tablespace szmz_gos
logging
datafile 'd:\oracledata\szmz_gos_data.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local;
--创建用户和密码
create user szmz_gosidentified by szmz_gos
default tablespace szmz_gos
temporary tablespace szmz_gos_temp;
】
//赋权限
grantconnecttoszmz_flsy;
grantresourcetoszmz_flsy;
grantcreatetabletoszmz_flsy;
grantcreateviewtoszmz_flsy;
grantdbatoszmz_flsy;
grantselectanytabletoszmz_flsy;
导入表结构文件:szmz_gos.sql(tools->import tables->SQL Inserts 导入.sql文件)
导入数据文件:szmz_gos.dmp(tools->import talbes->Oracle Import然后再导入dmp文件。)
或:
DOS下执行该语句
impzdcz/zdcz@127.0.0.1:1521/ORCL FILE=D:\exportFile.dmp IGNORE=Y;
(二)、创建数据库
创建zdcz database--》01_create_tablespace_zdcz.sql
创建用户 zdcz user--》02_create_user_zdcz.sql【具体同上】
导入表结构文件:zdcz.sql(tools->import tables->SQL Inserts 导入.sql文件)
导入数据文件:zdcz.dmp(tools->import talbes->Oracle Import然后再导入dmp文件。)
导入表结构文件:05-create-table-GOS_APPLOG.sql(tools->importtables->SQL Inserts 导入.sql文件)
(三)、添加序列
--创建序列语句【gos_applog_seq、gos_dept_seq等5个】
-- Createsequence
createsequencegos_applog_seq
minvalue0
maxvalue9999999999999999999999999999
--start with:【起始数据为表中最大id加1】
startwith1
incrementby1
cache20;
(四)、授权【需在最大权限dba下执行】
-- 表授权
grant select, insert, update,delete on szmz_gos.gos_applog to zdcz;
grant select, insert, update,delete on szmz_gos.gos_dept to zdcz ;
grant select, insert, update,delete on szmz_gos.gos_region to zdcz ;
grant select, insert, update,delete on szmz_gos.gos_role to zdcz;
grant select, insert, update,delete on szmz_gos.gos_user to zdcz;
grant select, insert, update,delete on szmz_gos.jz_dictionary to zdcz;
-- 序列授权
grant select, alter on szmz_gos.gos_applog_seq to zdcz;
grant select, alter on szmz_gos.gos_dept_seq to zdcz;
grant select, alter on szmz_gos.gos_region_seq to zdcz;
grant select, alter on szmz_gos.gos_role_seq to zdcz;
grant select, alter on szmz_gos.gos_user_seq to zdcz;