admin 管理员组文章数量: 887021
2024年1月15日发(作者：transports翻译)
ORACLE 导出空表和
将表导入到指定表空间
解决方案
背景
Oracle 11g使用exp时，默认无法导出空表；
导入时也无法指定表空间。
导出空表的解决方法
查看是否能导出空表
show parameter deferred_segment_creation
deferred_segment_creation=TRUE表示空表不创建数据段，这是就不能导出表了。只有在这个参数设置为FALSE后新建的表才能导出。
alter system set deferred_segment_creation=false;
解决方法
给空表增加一条记录，然后再删除，然后就能导出了。
导出表
Exp xxx/xxx@dbsid file= owner=(xxx,xxx);
然后将导入一个临时的用户A中，
将临时用户A的表和原用户表比较，查询所有空表，
用sys登录，运行
select object_name,CREATED,STATUS from dba_objects where owner='XXX'
OBJECT_TYPE='TABLE' and OBJECT_NAME NOT IN
(select OBJECT_NAME from dba_objects where owner='A' ) ORDER BY OBJECT_NAME
禁用所有外键约束
AND
运行SQL,生成禁用所有外键的SQL预计
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where
constraint_type='R'
然后将结果复制下来运行即可。
使用POWERDESIGN等工具生成测试数据或者手工生成测试数据
使用IMP命令生成这些空表的创建语句
Imp xxx/xxx@dbsid show=y tables=(xxx,xxx) log=
然后将改成导入到powerdesign中，再利用powerdesign的生成测试数据生成测试数据，生成测试数据前要先设置自动填充的值的模式，否则会生成很长的字符串。
运行测试数据，然后删除测试数据，空表就会有数据段
恢复外键约束
运行SQL,生成启动所有外键的SQL预计
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where
constraint_type='R'
然后将结果复制下来运行即可。
导出所有表，这次导出的文件就包含了空表了
将数据导出到指定表空间
导出数据，最好指定导出某个用户的数据
exp userid/pwd@dbsid file= owner=(xxx ,xxx)
创建表空间
create tablespace XXXX
nologging
datafile '+DATA/webdb/datafile/'
size 1024m
autoextend on
next 100m
maxsize 30810m
extent management local
解释：
nologging 表示不用创建日志，由于新创建的表空间，无需日志
+DATA/webdb/datafile/' 这个是存储设备路径
授权用户使用该表空间
alter user XXX quota unlimited on XXXX;
修改oracle导出文件中的表空间
Vim
%s/TABLESPACE "XXX"/XXXX/g
导入
Imp xxx/xxx@dbsid fromuser=xxx touser=xxx
检查表空间的表
以用户身份登录
select tablespace_name from user_tables where table_name = 'tabname'
查询空表的另外一种方法
SET SERVEROUTPUT ON;
exec dbms_(200000);
DECLARE
v_table dba__name%TYPE;
v_sql VARCHAR2(888);
v_q NUMBER;
CURSOR c1 IS
SELECT object_name tn FROM dba_objects where owner='xxxx' and object_type='TABLE';
TYPE c IS REF CURSOR;
c2 c;
BEGIN
DBMS__LINE('empty table:');
FOR r1 IN c1 LOOP
v_table :=;
v_sql :='SELECT count(*) q FROM xxx.'||v_table||' where rownum = 1';
OPEN c2 FOR v_sql;
LOOP
FETCH c2 INTO v_q;
EXIT WHEN c2%NOTFOUND;
IF v_q=0 THEN
DBMS__LINE(v_table);
END IF;
END LOOP;
CLOSE c2;
END LOOP;
EXCEPTION
WHEN OTHERS THEN DBMS__LINE('Error occurred');
END;
/
解析
Xxx代表用户名。
需要用DBA角色登录。
修改数据库字符集
数据库字符集在创建后原则上不能更改。如果需要修改字符集，通常需要导出数据库数据，重建数据库，再导入数据库数据的方式来转换，或通过ALTER DATABASE CHARACTER
SET语句修改字符集，但创建数据库后修改字符集是有限制的，只有新的字符集是当前字符集的超集时才能修改数据库字符集，例如UTF8是US7ASCII的超集，修改数据库字符集可使用ALTER DATABASE CHARACTER SET UTF8。
在Export过程中，如果源数据库字符集与Export用户会话字符集不一致，会发生字符集转换，并在导出文件的头部几个字节中存储Export用户会话字符集的ID号。在这个转换过程中可能发生数据的丢失。
例:如果源数据库使用ZHS16GBK，而Export用户会话字符集使用US7ASCII，由于ZHS16GBK是16位字符集,而US7ASCII是7位字符集，这个转换过程中，中文字符在US7ASCII中不能够找到对等的字符，所以所有中文字符都会丢失而变成“?? ”形式，这样转换后生成的Dmp文件已经发生了数据丢失。
因此如果想正确导出源数据库数据，则Export过程中用户会话字符集应等于源数据库字符集或是源数据库字符集的超集
查询数据库字符集
查看数据字符集
Select * from v$NLS_PARAMETERS;
Select * from nls_database_parameters;
查看session
Select * from nls_session_parameters;
客户端字符集设置
NLS_LANG参数格式
NLS_LANG=
Language:显示oracle消息,校验，日期命名
Territory：指定默认日期、数字、货币等格式
Client character set：指定客户端将使用的字符集
例如：NLS_LANG=AMERICAN_7ASCII
AMERICAN是语言，AMERICA是地区，US7ASCII是客户端字符集
客户端字符集设置方法
$NLS_LANG=“simplified chinese”_16gbk
$export NLS_LANG
编辑oracle用户的profile文件
导入数据
设置字符集和目标数据库字符集
NLS_LANG=AMERICAN_16GBK
export NLS_LANG
ZHS16GBK到 AL32UTF8 是不用转换的。
修改目标数据库字符集让它和源数据库字符集相同
update props$ set values$='US7ASCII'
where name='NLS_CHARACSET'
Imp xxx
update props$ set values$='US7ASCII'
where name='NLS_CHARACSET'
版权声明：本文标题：ORACLE 导出空表和将表导入到指定表空间的解决方案 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/free/1705319261h480879.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论