oracle个人常用sql（导入导出数据库的操作）

 

--查看oracle版本(11.2.0.1.0)
SELECT  VERSION FROM V$INSTANCE;
--查看表空间以及大小
select tablespace_name, sum(bytes)/1024/1024,t.* from dba_data_files t group by tablespace_name;
--查看表空间以及大小2
select * from dba_data_files
--查询xxx_DATA表空间下的用户：
select distinct s.owner from dba_segments s where s.tablespace_name ='xxx_DATA';
--查询所有用户所对应的表空间
select username,default_tablespace from dba_users;
--查找dump所在目录
select * from all_directories where directory_name like '%PUMP%'

 

DECLARE
v_table tabs.table_name%TYPE;
v_sql VARCHAR2(888);
v_q NUMBER;
CURSOR c1 IS
SELECT table_name tn FROM tabs;
TYPE c IS REF CURSOR;
c2 c;
BEGIN
DBMS_OUTPUT.PUT_LINE('以下为空数据表的表名:');
FOR r1 IN c1 LOOP
v_table :=r1.tn;
v_sql :='SELECT COUNT(*) q FROM '||v_table;
OPEN c2 FOR v_sql;
LOOP
FETCH c2 INTO v_q;
EXIT WHEN c2%NOTFOUND;
IF v_q=0 THEN
DBMS_OUTPUT.PUT_LINE(v_table);
END IF;
END LOOP;
CLOSE c2;
END LOOP;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error occurred');
END;

 

 

---------------------导入 -------------------------

注意事项：导入前先搞清楚，用户名和表空间，否则导入了不同的用户名和表空间名会报错
cmd中导入dup
--1把dup文件拷贝到oracle安装目录C:\app\Administrator\admin\orcl\dpdump 这个文件夹下面（即dump所在目录）
--2在cmd中执行

impdp user/pwd dumpfile=20230328.DUP REMAP_SCHEMA=a:b REMAP_TABLESPACE=a_data:b_data table_exists_action=replace TRANSFORM=segment_attributes:n
参数说明：
table_exists_action=replace  会替换原有的表数据

REMAP_SCHEMA=a:b 其中b是目标用户，即把a用户的数据导入到b用户中去

REMAP_TABLESPACE=a:b 其中 b是目标表空间，即把a表空间的东西导入到b表空间中去

TRANSFORM=segment_attributes:n 加入此参数的意义在于，可与忽略expdp导出时附带的相关表空间和存储子句约束

 

cmd中导入dmp

imp 用户名/密码@实例名 file=导入的dmp文件路径 full=y

如果表已经存在则需要添加ignore=y 不然报错

imp bpmx/bpmx@127.0.0.1/orcl file='C:\Users\Administrator\Desktop\dlbb\dlbb.dmp' full=y   ignore=y

如果报 IMP-00032: SQL statement exceeded buffer length 则是缓冲不够 没法读下整个SQL语句，这时要添加 buffer=100000000

imp bpmx/bpmx@127.0.0.1/orcl file='C:\Users\Administrator\Desktop\dlbb\dlbb.dmp' full=y   ignore=y buffer=100000000

---------------------导出 -------------------------
cmd中导出dmp
exp zdjs/zdjs@192.168.1.153/orcl file=d:\zdjsqq.dmp log=D:\zdjsqq.log owner=zdjs

默认导出的表不包括空表，如需导出空表，参考链接 https://www.cnblogs.com/oldzhang1222/p/13098358.html

cmd中导出某个表数据
exp bpmx/bpmx file=d:\test1.dmp statistics=none TABLES=(W_DLBB__XLPXB)

 

cmd中导出dup

expdp dddw/dddw@127.0.0.1/orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=new.bak.dup logfile=new.bak.log

可以使用tables=(表1, 表2,表3,...) 参数，指定导出那些表

列如：expdp mzzdjs/mzzdjs@127.0.0.1/orcl tables=(Z_LIST_CIRCUIT_VAL, Z_LIST_CIRCUIT_VAL_CAT,Z_LIST_CIRCUIT) DIRECTORY=DATA_PUMP_DIR DUMPFILE=2022.dup logfile=2022.log

 

导出后会在C:\app\Administrator\admin\orcl\dpdump 中生成new.bak.bak.log日志文件

---------------------高版本库导进低版本库 -------------------------
正式环境导入数据后完整了的sql
impdp xxx/xxx@192.168.1.6/ticp DIRECTORY=DATA_PUMP_DIR DUMPFILE=bpm20190821.dup SCHEMAS=bpmx logfile=bpm20190821.log version=11.2.0.1.0 transform=segment_attributes:n
注意：
高版本库导进低版本库，加目标版本号（version=11.2.0.1.0 ），不要full=y参数
11.2.0.4.0--->11.2.0.1.0