oracle 创建表空间和用户流程

--用oralce sys用户执行以下语句
 

--create tablespace
--确保路径文件夹存在，不存在需创建且用chmod语句授权777
CREATE TABLESPACE FMSS
    LOGGING
    DATAFILE
    '/u01/app/oracle/oradata/orcl/FMSS01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;

alter database datafile '/u01/app/oracle/oradata/orcl/FMSS01.dbf' autoextend on next 64M maxsize unlimited;


CREATE TABLESPACE DLT
    LOGGING
    DATAFILE
    '/u01/app/oracle/oradata/orcl/DLT01.dbf' SIZE 100M  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;



alter database datafile '/u01/app/oracle/oradata/orcl/DLT01.dbf' autoextend on next 64M maxsize unlimited;

CREATE TABLESPACE WORK
    LOGGING
    DATAFILE
    '/u01/app/oracle/oradata/orcl/WORK01.dbf' SIZE 100M  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;



alter database datafile '/u01/app/oracle/oradata/orcl/WORK01.dbf' autoextend on next 64M maxsize unlimited;

CREATE TABLESPACE METABASE
    LOGGING
    DATAFILE
    '/u01/app/oracle/oradata/orcl/METABASE01.dbf' SIZE 100M  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;



alter database datafile '/u01/app/oracle/oradata/orcl/METABASE01.dbf' autoextend on next 64M maxsize unlimited;

CREATE TABLESPACE REPORT
    LOGGING
    DATAFILE
    '/u01/app/oracle/oradata/orcl/REPORT01.dbf' SIZE 100M  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;



alter database datafile '/u01/app/oracle/oradata/orcl/REPORT01.dbf' autoextend on next 64M maxsize unlimited;



--create user FMSS
CREATE USER FMSS IDENTIFIED BY FMSS
  DEFAULT TABLESPACE FMSS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT "CONNECT" TO FMSS;
GRANT "RESOURCE" TO FMSS;
GRANT UNLIMITED TABLESPACE TO FMSS;
GRANT CREATE ANY VIEW TO FMSS;
GRANT DROP ANY VIEW TO FMSS;
ALTER USER FMSS DEFAULT ROLE ALL;
grant create any table to FMSS;
grant debug connect session to FMSS;
grant create public synonym to FMSS;
grant drop public synonym to FMSS;

--create user DLT
CREATE USER DLT IDENTIFIED BY DLT
  DEFAULT TABLESPACE DLT
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT "CONNECT" TO DLT;
GRANT "RESOURCE" TO DLT;
GRANT UNLIMITED TABLESPACE TO DLT;
GRANT CREATE ANY VIEW TO DLT;
GRANT DROP ANY VIEW TO DLT;
ALTER USER DLT DEFAULT ROLE ALL;
grant create any table to DLT;
grant debug connect session to DLT;
grant create public synonym to DLT;
grant drop public synonym to DLT;

--create user WORK
CREATE USER WORK IDENTIFIED BY WORK
  DEFAULT TABLESPACE WORK
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT "CONNECT" TO WORK;
GRANT "RESOURCE" TO WORK;
GRANT UNLIMITED TABLESPACE TO WORK;
GRANT CREATE ANY VIEW TO WORK;
GRANT DROP ANY VIEW TO WORK;
ALTER USER WORK DEFAULT ROLE ALL;
grant create any table to WORK;
grant debug connect session to WORK;
grant create public synonym to WORK;
grant drop public synonym to WORK;

--create user METABASE
CREATE USER METABASE IDENTIFIED BY METABASE
  DEFAULT TABLESPACE METABASE
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT "CONNECT" TO METABASE;
GRANT "RESOURCE" TO METABASE;
GRANT UNLIMITED TABLESPACE TO METABASE;
GRANT CREATE ANY VIEW TO METABASE;
GRANT DROP ANY VIEW TO METABASE;
ALTER USER METABASE DEFAULT ROLE ALL;
grant create any table to METABASE;
grant debug connect session to METABASE;
grant create public synonym to METABASE;
grant drop public synonym to METABASE;

--create user REPORT
CREATE USER REPORT IDENTIFIED BY REPORT
  DEFAULT TABLESPACE REPORT
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT "CONNECT" TO REPORT;
GRANT "RESOURCE" TO REPORT;
GRANT UNLIMITED TABLESPACE TO REPORT;
GRANT CREATE ANY VIEW TO REPORT;
GRANT DROP ANY VIEW TO REPORT;
ALTER USER REPORT DEFAULT ROLE ALL;
grant create any table to REPORT;
grant debug connect session to REPORT;
grant create public synonym to REPORT;
grant drop public synonym to REPORT;



--创建数据泵文件目录
create or replace directory dir_dmp as '/dir_dmp/dump';
grant read,write on directory dir_dmp to FMSS,DLT,WORK,METABASE,REPORT;

上传文件到/dir_dmp/dump目录下，然后解压文件
tar -xvzf fmss_dlt_20160526.tar.gz

然后使用数据泵导入dmp
impdp FMSS/FMSS directory=dir_dmp dumpfile=fmss_201607141650.dump log=fmss_201607141650.log;
impdp DLT/DLT directory=dir_dmp dumpfile=dlt_201607141650.dump log=dlt_201607141650.log
impdp WORK/WORK directory=dir_dmp dumpfile=work_201607141650.dump log=work_201607141650.log
impdp REPORT/REPORT directory=dir_dmp dumpfile=report_201607141650.dump log=report_201607141650.log
impdp METABASE/METABASE directory=dir_dmp dumpfile=metabase_201607141650.dump log=metabase_201607141650.log

在导入数据的过程中，如果出现警告或者由于用户不存在而提示的错误，可以忽略；


删除 用户和表空间：
drop user FMSS cascade;
drop user DLT cascade;
drop user METABASE cascade;
drop user WORK cascade;
drop user REPORT cascade;

DROP TABLESPACE FMSS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE DLT INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE METABASE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE WORK INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE REPORT INCLUDING CONTENTS AND DATAFILES;

 

 

 

 

如果windows客户端