oracle数据库 锁表解锁 批量解锁
《PS:留下小赞赞,比心比心》
---登录sqlplus
sqlplus xzzc/xzzc@192.168.10.1:1521/shitan
---设置空表可以导出
show parameter deferred_segment_creation;
alter system set deferred_segment_creation=false;
---先查看是否有空表---select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
---执行上面查询出的语句,如下所示
alter table XZZX0210 allocate extent;
alter table XZZX0211 allocate extent;
alter table ZZBG01 allocate extent;
alter table DC_XXBZ_DATAITEM1 allocate extent;
...........
普通方式导数据库
---导出整个实例(数据库)数据
exp xzzc/xzzc@192.168.10.1/shitan full=y file=C:\xzzc1219.dmp log=C:\xzzc1219.txt
---导出数据库中system用户与sys用户的表导出
exp xzzc/xzzc@192.168.10.1/shitan owner=(system,sys) file=C:\xzzc1219.dmp log=C:\xzzc1219.txt
---导入数据
imp xzzc/xzzc@192.168.10.1/shitan full=y buffer=40960000 ignore=y file=C:\xzzc1219.dmp log=C:\xzzc1219.txt
数据泵方式导数据库
---创建导入导出数据库目录
create or replace directory exp_dir as 'd:\test\dump';
grant read,write on directory exp_dir to hengshui;
---导出数据
expdp hengshui/hengshui@192.168.10.83:1521/shitan directory=exp_dir full=y dumpfile=NBSJ_2018.dmp logfile=NBSJ_2018.txt version=10.2.0.1.0
---导入数据
impdp hengshui/hengshui@192.168.10.83:1521/shitan directory=exp_dir exclude=PROCACT_INSTANCE EXCLUDE=STATISTICS table_exists_action=append PARALLEL=3 REMAP_SCHEMA=scott:hengshui dumpfile=NBSJ_2018.dmp logfile=NBSJ_2018.txt
PS:REMAP_SCHEMA=scott:hengshui中scott为导出用户,衡水为导入用户
---条件导出
exp test/test@192.168.20.20/orcl file = d:\test.dmp tables=(t_test) query = \"where oildate like '2016%'\"
expdp scott/scott123@192.168.11.232:1521/jnzc dumpfile=222j.dmp logfile=222.log QUERY="""WHERE SAASDM IN ('745691483')""" buffer=20480000