--***********************注意：要先创建存放数据库的文件夹***********************--        
--connect sys/sys;
CREATE TABLESPACE ts_mz LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_mz0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_zy LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_zy0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_yk LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_yk0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_sf LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_sf0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_yj LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_yj0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_yf LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_yf0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_bq LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_bq0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_zs LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_zs0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_gy LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_gy0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_tj LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_tj0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_sb LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_sb0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;


CREATE TABLESPACE ts_yy LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_yy0001.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;


CREATE TABLESPACE ts_mz_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_mzindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_zy_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_zyindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
 
CREATE TABLESPACE ts_yk_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_ykindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
 
CREATE TABLESPACE ts_sf_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_sfindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_bq_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_bqindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
 
CREATE TABLESPACE ts_yj_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_yjindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
 
CREATE TABLESPACE ts_yf_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_yfindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_gy_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_gyindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_zs_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_zsindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_tj_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_tjindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE ts_sb_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_sbindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;
 
CREATE TABLESPACE ts_yy_index LOGGING 
DATAFILE 'd:\oracle\Datafile\qzfe\ts_yyindex01.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO ;

--CREATE  temporary TABLESPACE ts_yy_temp  TEMPFILE 
--'d:\oracle\Datafile\qzfe\ts_yytemp01.dat' SIZE 50M REUSE AUTOEXTEND ON NEXT 10M 
--DEFAULT STORAGE ( INITIAL 10M NEXT 10M MAXEXTENTS 10000 ) MINIMUM EXTENT 10M;

CREATE TEMPORARY TABLESPACE TS_YY_TEMP 
TEMPFILE 'd:\oracle\Datafile\qzfe\ts_yy_temp.dat' SIZE 50M AUTOEXTEND ON NEXT 10M 
EXTENT  MANAGEMENT LOCAL UNIFORM SIZE 10M ;
    
drop user sd_hospital cascade;
create user sd_hospital identified by sd_hospital
            default tablespace ts_yy temporary tablespace ts_yy_temp;
GRANT CONNECT, RESOURCE , DBA TO sd_hospital;

GRANT CREATE ANY SEQUENCE TO SD_HOSPITAL;
GRANT EXECUTE ON SYS.DBMS_SQL TO PUBLIC;
GRANT EXECUTE ON SYS.DBMS_OUTPUT TO PUBLIC;
GRANT SELECT ON SYS.DBA_OBJECTS TO SD_HOSPITAL;

--创建用户sd_yk
drop user sd_yk cascade;
create user sd_yk identified by sd_yk
	default tablespace ts_yk
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_yk;

--创建用户sd_mz
drop user sd_mz cascade;
create user sd_mz identified by sd_mz
	default tablespace ts_mz
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_mz;

--创建用户sd_sf
drop user sd_sf cascade;
create user sd_sf identified by sd_sf
	default tablespace ts_sf
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_sf;

--创建用户sd_yf
drop user sd_yf cascade;
create user sd_yf identified by sd_yf
	default tablespace ts_yf
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_yf;

--创建用户sd_yj
drop user sd_yj cascade;
create user sd_yj identified by sd_yj
	default tablespace ts_yj
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_yj;

--创建用户sd_zy
drop user sd_zy cascade;
create user sd_zy identified by sd_zy
	default tablespace ts_zy
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_zy;

--创建用户sd_bq
drop user sd_bq cascade;
create user sd_bq identified by sd_bq
	default tablespace ts_bq
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_bq;
--创建用户sd_zs
drop user sd_zs cascade;
create user sd_zs identified by sd_zs
	default tablespace ts_zs
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_zs;

--创建用户sd_gy
drop user sd_gy cascade;
create user sd_gy identified by sd_gy
	default tablespace ts_gy
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_gy;

--创建用户sd_tj
drop user sd_tj cascade;
create user sd_tj identified by sd_tj
	default tablespace ts_tj
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_tj;

--创建用户sd_sb
drop user sd_sb cascade;
create user sd_sb identified by sd_sb
	default tablespace ts_sb
	temporary tablespace ts_yy_temp;
GRANT RESOURCE,CONNECT TO sd_sb;

--创建用户scott
DROP USER SCOTT CASCADE;
CREATE USER SCOTT IDENTIFIED BY tiger 
        default tablespace ts_yy 
        temporary tablespace ts_yy_temp;
GRANT CREATE SESSION TO SCOTT;
commit;

--IMP system/oracle FILE=c:\temp\cwyy.dmp fromuser=(sd_yk,sd_mz,sd_zy,sd_sf,sd_bq,sd_yj,sd_yf,sd_hospital) touser=(sd_yk,sd_mz,sd_zy,sd_sf,sd_bq,sd_yj,sd_yf,sd_hospital) LOG=c:\temp\import.txt

--grant select on sd_hospital.xt_dbms00 to scott;
--create synonym scott.xt_dbms00 for sd_hospital.xt_dbms00;