Oracle11g使用命令导出空表,导出结果集-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle11g使用命令导出空表,导出结果集

简介:

很久没敲命令进行Oracle数据库表的导入(imp),导出(exp)了,今天猛一敲,发现有些命令居然忘了,顺手写下这篇博文,记录一下!


exp本地导出命令:

1 exp username/psw@orcl file=d:\demo.dmp full=y

2 exp username/psw@orcl file=d:\demo.dmpowner=(xx)

3 exp username/psw@orcl file=d:\demo.dmptables=(table1,table2)

1其中一是将orcl(与某一数据库对应的oracle服务名)数据库进行整体导出

2将属于用户xx的所有表导出

3将表table1,与table2导出

4d:\demo.dmp是导出的文件地址


这是在DOS  cmd 命令下的操作,如果是在SqlPlus中操作的话,需要exp前面加上$不然会报错!如下:

wKiom1N0fyTxtZpcAABac4NT3hs910.jpg


解决Oracle 11g导不出空表的方法:

解决方法:

1insert一行,再rollback就产生segment了:

  该方法是在在空表中插入数据,再删除,则产生segment。导出时则可导出空表。

2设置deferred_segment_creation 参数:

show parameterdeferred_segment_creation

NAME                                TYPE        VALUE 
------------------------------------ ----------------------------------------- 
deferred_segment_creation           boolean     TRUE 
SQL> alter system set deferred_segment_creation=false; 

系统已更改。

SQL> show parameter deferred_segment_creation 

NAME                                TYPE        VALUE 
------------------------------------ ----------------------------------------- 
deferred_segment_creation           boolean     FALSE

该参数值默认是TRUE,当改为FALSE时,无论是空表还是非空表,都分配segment

需注意的是:该值设置后对以前导入的空表不产生作用,仍不能导出,只能对后面新增的表产生作用。如需导出之前的空表,只能用第一种方法,或者是下面一种方法!

所以建议在建表的时候将此属性设为false!


3。 手动分配区:

先查询一下当前用户下的所有空表

selecttable_name from user_tables where NUM_ROWS=0;

用以下这句查找空表

select 'alter table '||table_name||' allocate extent;' from user_tableswhere num_rows=0

会出现以下类似的sql语句:

把查询结果导出,执行一下修改空表的sql语句!

'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT;'
-----------------------------------------------------------
alter table AQ$_AQ$_MEM_MC_H allocate extent;
alter table AQ$_AQ$_MEM_MC_G allocate extent;
alter table AQ$_AQ$_MEM_MC_I allocate extent;
alter table AQ$_AQ_PROP_TABLE_T allocate extent;
alter table AQ$_AQ_PROP_TABLE_H allocate extent;
alter table AQ$_AQ_PROP_TABLE_G allocate extent;
alter table AQ$_AQ_PROP_TABLE_I allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_T allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_H allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_G allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_I allocate extent;

'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT;'
-----------------------------------------------------------
alter table AQ$_SYS$SERVICE_METRICS_TAB_T allocate extent;
alter table AQ$_SYS$SERVICE_METRICS_TAB_H allocate extent;
alter table AQ$_SYS$SERVICE_METRICS_TAB_G allocate extent;
alter table AQ$_SYS$SERVICE_METRICS_TAB_I allocate extent;

然后再执行exp命令!成功!

问题在于:怎么导出结果集,网上给的资料并不完善!我特意看了一下,网上都是先建一个abc.sh,下面是我的思路!

第一步:进入sqlplus  通过spool命令创建.txt文件


wKioL1N0gC-iwxVbAAAQUJzmgpA738.jpg

第二步:执行下面这两段sql语句

先查询一下当前用户下的所有空表

selecttable_name from user_tables where NUM_ROWS=0;

用以下这句查找空表,生成修改语句

select 'alter table '||table_name||' allocate extent;' from user_tableswhere num_rows=0;


这两段代码运行之后,会产生一大段代码

第三步:spool off

这时候我们看到在c盘下的demo.txt  数据


我们将类似这样的语句拷贝出来在sqlplus中执行一下

wKioL1N0gGmSS8GyAAIWR_b8tTA747.jpg


这时候,我们在执行exp命令,发现所有的表结构,都可以被导出!


imp本地导入


1.首先进入cmd命令窗口

2.执行命令:imp username=用户名/psw@orcl file=d:\demo.dmp full=y;

解释

用户名:就是你的目标用户
密码:就是你的目标用户登录密码。
orcl:
是实例名称就是数据库名。
file
:就是你要导入的dmp文件全路径。
full=y 
是否全部导入只有当前用户是dba的时候才能用此选项

ignore=y  最后面加上这,意思是存在的表不需要导入了


第二部分:导出oracle数据库select结果集

例如将查询出来的结果集导出为excel表格(此想法是用户想导出一部分数据,但由于时间原因无法给其写程序导出,所以就有了,这种直接取结果集,完全满足用户需求)

第一步:

set markup html on; //制作成XLS格式如果是txt格式的,则不用这行
set pagesize 0;
set max value is 999;//
每行的字符数目 这样设置有问题,没时间研究了有哪位同学知道,请留言
set linesize 200;//行长度

spool c:\test.xls;
第二步:--执行
查询语句(当然你可以加任何合理条件)
select*from sfm_user;

第三步:

spool off;

我们可以看到excel的数据为:

wKiom1N0gcyheZ46AAEhPs_FWPY473.jpg

这种方式导出结果集,要根据你的表情况进行设置,相关属性,比如行长度,字符数目,标题(字段名称)等等,都是可以的,因为时间关系,我就没在sql上写,导出之后我直接手动添加上字段名称了!


附:set 命令速查:

SQL>set colsep' ';     //-域输出分隔符

SQL>set echo off;     //显示start启动的脚本中的每个sql命令,缺省为on

SQL>set echo on //设置运行命令是是否显示语句

SQL>set feedback off;  //不显示本次sql命令处理的记录条数,缺省为on

SQL>set heading off;   //输出列标题,缺省为on

SQL>set pagesize 0;   //输出每页行数,缺省为24,为了避免分页,可设定为0。

SQL>set linesize 80;   //输出一行字符个数,缺省为80

SQL>set numwidth 12;  //输出number类型域长度,缺省为10

SQL>set termout off;   //显示脚本中的命令的执行结果,缺省为on

SQL>set trimout on;   //去除标准输出每行的拖尾空格,缺省为off

SQL>set trimspool on;  //去除重定向(spool)输出每行的拖尾空格,缺省为off

SQL>set serveroutput on; //设置允许显示输出类似dbms_output

SQL>set timing on; //设置显示“已用时间:XXXX”

SQL>set autotrace on; //设置允许对执行的sql进行分析

SQL>set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.


set wrap on/off 查询返回的纪录每行超过默认宽度时,可选择换行(on)或不换行(off),默认为换行;

set linesize N 设置查询返回的纪录每行的宽度,超过这个宽度则截掉,不过这个宽度则补空格。










本文转自 小夜的传说 51CTO博客,原文链接:http://blog.51cto.com/1936625305/1411943,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章