数据泵导出
Expdp BDCDJ0616/BDCDJ0616@orcl schemas=BDCDJ0616 directory=BDCDJ dumpfile=BDCDJ0616.dmp logfile=BDCDJ0616.log nologfile=y (单个用户方案导出)
数据泵导入
impdp SHX/SHX@yayzt schemas=SHX directory=SHX dumpfile=SHX0911.dmp logfile=yayzt0911.log nologfile=y (单个用户方案导入)
impdp BDCDJ_GY/BDCDJ_GY@orcl directory=BDCDJ dumpfile=BDCDJ_0202.dmp remap_schema=BDCDJ:BDCDJ_GY remap_tablespace=BDCDJ:BDCDJ_GY logfile=BDCDJ_GY0609.log
impdp BDCDJ/BDCDJ@172.10.21.248:1521/orcl directory=DX dumpfile=BDCDJ_ZF20161900.DMP remap_schema=BDCDJ_ZF:BDCDJ remap_tablespace=BDCDJ_ZF(原库):BDCDJ(要还原的库) logfile=BDCDJ_0111.log nologfile=y EXCLUDE=STATISTICS
注意:directory的位置
drop directory test 删除目录
1、 创建目录并赋予权限:
CREATE DIRECTORY BDCSJCS AS 'F:\backup';
GRANT READ,WRITE ON DIRECTORY BDCSJCS TO BDCDJ1 ;
查看数据库中已创建的directory的两个视图:
SELECT * FROM All_Directories;
SELECT * FROM Dba_Directories;
修改索引的表空间
select 'alter index '||INDEX_NAME||' rebuild tablespace '|| 'BDCBD'||';' from user_indexes;
select 'alter table '||TABLE_NAME||' move tablespace '|| 'DAGL'||';' from user_tables;
批量删除表
SELECT 'DROP TABLE ' || TABLE_NAME || ' CASCADE CONSTRAINTS;' V_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = 'BDCDJ_GY' and Table_Name like '%BAK%';