分类目录归档:Oralce BAT

有关oracle批处理相关内容

常用的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;

导入bat[Import.bat]

sqlplus system/oracle @createTableSpaceAndUser.sql

imp system/oracle tablespaces=’XS_UM’ file=’um_星期三.dmp’ fromuser=xs_um touser=xs_um log=’um.log’ ignore=y

imp system/oracle tablespaces=’XS_WORKFLOW’ file=’workflow_星期三.dmp’ fromuser=xs_workflow touser=xs_workflow log=’workflow.log’ ignore=y

imp system/oracle tablespaces=’XS_WAS’ file=’was_星期三.dmp’ fromuser=xs_was touser=xs_was log=’was.log’ ignore=y

常用Oracle的bat命令

导出单张表的数据

exp CX_NEWAPAS/CX_NEWAPAS@ORCL FILE=”G:\LineWell\OA\APAS_INFO.dmp” TABLES=(CX_NEWAPAS.APAS_INFO) LOG=”exp.log”

导出全部表的数据

exp system/oracle file=’data/wys_um.dmp’ owner=wys_um

exp system/oracle file=’data/wys_was.dmp’ owner=wys_was

exp system/oracle file=’data/wys_workflow.dmp’ owner=wys_workflow

执行sql

sqlplus system/oracle @createTableSpaceAndUser.sql

导入

imp system/oracle tablespaces=’XS_UM’ file=’um_星期三.dmp’ fromuser=xs_um touser=xs_um log=’um.log’ ignore=y

imp system/oracle tablespaces=’XS_WORKFLOW’ file=’workflow_星期三.dmp’ fromuser=xs_workflow touser=xs_workflow log=’workflow.log’ ignore=y

imp system/oracle tablespaces=’XS_WAS’ file=’was_星期三.dmp’ fromuser=xs_was touser=xs_was log=’was.log’ ignore=y