一：用命令 imp/exp 的方式进行数据的导入和导出
一：文件后缀名：
二：oracle 导出 exp 命令
1 echo 开始备份数据库
2 if not exist D:\oracle_bak\files md D:\oracle_bak\files
3 if not exist D:\oracle_bak\logs md D:\oracle_bak\logs
4
5 set var=%date:~0,4%%date:~5,2%%date:~8,2%%time:~9,2%
6 exp GDSDCZJ/GDSDCZJ1234@192.168.1.100/pdborcl file=D:\oracle_bak\files\GDSDCZJ_%var%.dmp log=D:\oracle_bak\logs\GDSDCZJ_%var%.log
7 echo 备份数据库结束
8
9 echo 删除30天前的备份记录
10 forfiles /p "D:\oracle_bak" /s /m *.dmp /d -30 /c "cmd /c del @path"
11 forfiles /p "D:\oracle_bak" /s /m *.log /d -30 /c "cmd /c del @path"
12 echo 删除30天前的备份记录结束
13 exit
14
三：oracle 导入 imp 命令
1 echo 开始导入数据库
2 set var=%date:~0,4%%date:~5,2%%date:~8,2%
3 imp GDSDYTH/GDSDYTH@192.168.0.110/pdborcl full=y ignore=y file=D:\oracle_bak\files\gdsd_czj_%var%.dmp log=D:\oracle_bak\logs\gdsd_czj_to_apa_imp_%var%.log
4 echo 导入数据库结束
5 exit
6
四：oracle 删除用户 再创建用户并赋予相对应的操作权限，然后再 导入 imp 命令
1 drop user sync_plus_1_0501 cascade;
2 create user sync_plus_1_0501 identified by sync_plus_1_0501
3 default tablespace sync_plus_1 temporary tablespace temp quota 500m on users;
4 grant all privileges to sync_plus_1_0501 ;
5 grant ALLTAX to SYNC_PLUS_1_0501;
6 grant ALLINONE_JX to sync_plus_1_0501 ;
7 grant ALLOUTSIDE to sync_plus_1_0501 ;
8 grant dba to sync_plus_1_0501;
9 grant RDCX to sync_plus_1_0501;
10 quit;
11
1 echo 开始导入数据库
2 sqlplus sys/oracle@192.168.1.168/pdborcl as sysdba @F:\decoument\DB_soft\createUser.sql
3
4 set var=%date:~0,4%%date:~5,2%%date:~8,2%
5 imp sync_plus_1_0501/sync_plus_1_0501@192.168.1.168/pdborcl full=y ignore=y
6 file=F:\decoument\DB_soft\imp_data\SYNC_PLUS_1_%var%.dmp
7 log=F:\decoument\DB_soft\imp_data\logs\SYNC_PLUS_1_to_apa_imp_%var%.log
8 echo 导入数据库结束
9 exit；
执行 效果：
五：每天自动创建当天日期为后缀名的oracle用户。并给该用户导入数据
1：看执行效果
2：上脚本：
Imp_toDay_Data.bat
1 echo 开始导入数据库
2 set var=%date:~0,4%%date:~5,2%%date:~8,2%
3 sqlplus sys/oracle@192.168.1.168/pdborcl as sysdba @F:\decoument\DB_soft\imp_toDay_Data\createUser.sql
4
5 imp sync_plus_1_%var%/sync_plus_1_%var%@192.168.1.168/pdborcl full=y ignore=y file=F:\decoument\DB_soft\SYNC_PLUS_1_%var%.dmp log=F:\decoument\DB_soft\imp_toDay_Data\logs\SYNC_PLUS_1_to_apa_imp_%var%.log
6 echo 导入数据库结束
7createUser.sql
1 declare
2 v_time varchar(20);
3 v_sql varchar(4000);
4 v_count int :=0;
5 v_count_sql varchar(4000);
6 v_username varchar(300);
7 begin
8 select to_char(sysdate,'yyyyMMdd') into v_time from dual;
9 v_username :='SYNC_PLUS_1_'||v_time;
10 dbms_output.put_line(v_username);
11 SELECT count(USERNAME) into v_count FROM ALL_USERS t where t.username =v_username ;
12 dbms_output.put_line(v_count);
13 if v_count = 0 then
14 v_sql :='create user sync_plus_1_'||v_time ||' identified by sync_plus_1_'||v_time ||' default tablespace sync_plus_1 temporary tablespace temp quota 500m on users';
15 dbms_output.put_line(v_sql);
16 execute immediate v_sql;
17 end if;
18 v_sql:='grant all privileges to sync_plus_1_'||v_time ;
19 dbms_output.put_line(v_sql);
20 execute immediate v_sql;
21 v_sql:='grant ALLTAX to sync_plus_1_'||v_time;
22 dbms_output.put_line(v_sql);
23 execute immediate v_sql;
24 v_sql:='grant ALLINONE_JX to sync_plus_1_'||v_time ;
25 dbms_output.put_line(v_sql);
26 execute immediate v_sql;
27 v_sql:='grant ALLOUTSIDE to sync_plus_1_'||v_time ;
28 dbms_output.put_line(v_sql);
29 execute immediate v_sql;
30 v_sql:='grant dba to sync_plus_1_'||v_time;
31 dbms_output.put_line(v_sql);
32 execute immediate v_sql;
33 v_sql:='grant RDCX to sync_plus_1_'||v_time;
34 dbms_output.put_line(v_sql);
35 execute immediate v_sql;
36 commit;
37 end;
38 /
39
40 quit;
六： 对oracle数据库某个用户 进行删表后再导入数据
Imp_SYNC_PLUS_1.bat
1 echo 开始导入数据库
2 sqlplus /nolog @F:\decoument\DB_soft\imp_data\createUser.sql
3
4 set var=%date:~0,4%%date:~5,2%%date:~8,2%
5 imp SYNC_PLUS_1/SYNC_PLUS_1@192.168.1.168/pdborcl full=y ignore=y file=F:\decoument\DB_soft\SYNC_PLUS_1_%var%.dmp log=F:\decoument\DB_soft\imp_data\logs\SYNC_PLUS_1_to_apa_imp_%var%.log
6 echo 导入数据库结束
7 exit;
createUser.sql
--drop user sync_plus_1 cascade;
--create user sync_plus_1 identified by sync_plus_1 default tablespace sync_plus_1 temporary tablespace temp quota 500m on users;
--grant all privileges to sync_plus_1 ;
--grant ALLTAX to sync_plus_1;
---grant ALLINONE_JX to sync_plus_1 ;
--grant ALLOUTSIDE to sync_plus_1 ;
--grant dba to sync_plus_1;
--grant RDCX to sync_plus_1;
conn SYNC_PLUS_1/SYNC_PLUS_1@192.168.1.168/pdborcl ; ----禁用所有的外键
/*--批量生成规则
select 'alter table ' || table_name || ' disable constraint ' ||
constraint_name || ';'
from user_constraints
where constraint_type = 'R';
*/
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
commit; ------禁用所有的主键；
/*
select 'alter table ' || table_name || ' disable constraint ' ||
constraint_name || ';'
from user_constraints
where constraint_type = 'P'; */
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='P') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
commit; ----删除所有外键：
/*
select 'alter table ' || table_name || ' drop constraint ' ||
constraint_name || ';'
from user_constraints
where constraint_type = 'R';
*/ SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' drop CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
/*for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;*/
end;
/
commit; ----删除主键：
/*
select 'alter table ' || table_name || ' drop constraint ' ||
constraint_name || ';'
from user_constraints
where constraint_type = 'P'; */ SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' drop CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='P') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
/*for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;*/
end;
/
commit; -----删除所有表
--- 批量生成规则 select 'drop table '||table_name||';' from cat where table_type='TABLE' ; SET SERVEROUTPUT ON SIZE 1000000
BEGIN
for c in (select 'drop TABLE '||TABLE_NAME||' ' as v_sql from cat where table_type='TABLE') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
/*for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;*/
end;
/
commit; quit;
二：使用 PL/SQL 工具 进行数据的导入 导出
1： 前提：需要安装oracle Xe 的客户端
2： 使用Plsql 工具 进行demp 文件 的数据导入
——————————————————————————————————————————————————————————————————————————————————————————————————————