常用的ORACLE导入导出SQL命令

–建表空间:WYS_WAS、WYS_UM、WYS_WORKFLOW以及相应的用户


create tablespace sxctworkflow logging datafile ‘E:\oracle\product\10.2.0\oradata\orcl\sxctworkflow.dbf’
size 100m autoextend on next 200m maxsize 20480m extent management local;

–采用重命名表空间的方法,可以快速解决这个问题。

SQL> alter tablespace &old_tbsname rename to &new_tbs_name;

–如当前用户默认的表空间为TEST,现在改为需要的表空间YWDBS
SQL> alter tablespace TEST rename to YWDBS;

–删除需要创建的表空间和用户
DROP USER “XS_WAS” CASCADE;
DROP TABLESPACE “WAS” INCLUDING CONTENTS AND DATAFILES;
DROP USER “XS_UM” CASCADE;
DROP TABLESPACE “UM” INCLUDING CONTENTS AND DATAFILES;
DROP USER “XS_WORKFLOW” CASCADE;
DROP TABLESPACE “WORKFLOW” INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE “WAS” LOGGING DATAFILE ‘D:\oracle\xs\XS_WAS.DBF’ SIZE 1M autoextend on;

CREATE USER “XS_WAS” IDENTIFIED BY “XS_WAS” DEFAULT TABLESPACE “WAS” PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT “CONNECT” TO “XS_WAS”;

GRANT “DBA” TO “XS_WAS”;

GRANT UNLIMITED TABLESPACE TO “XS_WAS”;

ALTER USER “XS_WAS” DEFAULT ROLE ALL;

CREATE TABLESPACE “UM” LOGGING DATAFILE ‘D:\oracle\xs\XS_UM.DBF’ SIZE 1M autoextend on;

CREATE USER “XS_UM” IDENTIFIED BY “XS_UM” DEFAULT TABLESPACE “UM” PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT “CONNECT” TO “XS_UM”;

GRANT “DBA” TO “XS_UM”;

GRANT UNLIMITED TABLESPACE TO “XS_UM”;

ALTER USER “XS_UM” DEFAULT ROLE ALL;

CREATE TABLESPACE “WORKFLOW” LOGGING DATAFILE ‘D:\oracle\xs\XS_WORKFLOW.DBF’ SIZE 1M autoextend on;

CREATE USER “XS_WORKFLOW” IDENTIFIED BY “XS_WORKFLOW” DEFAULT TABLESPACE “WORKFLOW” PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT “CONNECT” TO “XS_WORKFLOW”;

GRANT “DBA” TO “XS_WORKFLOW”;

GRANT UNLIMITED TABLESPACE TO “XS_WORKFLOW”;

ALTER USER “XS_WORKFLOW” DEFAULT ROLE ALL;

QUIT;

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据