- 浏览: 959068 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
孤星119:
好熟悉的数据库字段啊, 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace:
quxiaoyong 写道遇到个问题,网上一搜,全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong:
遇到个问题,网上一搜,全他妈这篇文章。你转来转去的有意思吗?
数据库连接错误ORA-28547 -
hctech:
关于version count过高的问题,不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace:
invalid 写道写的不错,我根据这个来安装,有点理解错误了 ...
AIX 配置vncserver
客户生产库版本为AIX 11.2.0.2,客户端版本为11.2.0.1,在客户端按用户级别导出表格的时候出现以下错误:
. . 正在导出表 S61_B214_GT_2010_3301
导出了 1224 行
EXP-00008: 遇到 ORACLE 错误 1455
ORA-01455: 转换列溢出整数数据类型
EXP-00000: 导出终止失败
为了排除错误,客户端以表方式导出,S61_B214_GT_2010_3301,的下一张表格报同样错误,同时在某些客户端报错信息多了以下错误:
EXP-00003: no storage definition found for segment(0,0)
这个错误已经很明显了,因为在11.2.0.1时,由参数deferred_segment_creation(默认为true)控制是否对新建表格默认分配段空间,所以在exp新建表格时,往往会出现此类错误。
最终Oracle认为此类错误为bug,并在11.2.0.2时得以修正。知道问题的原因之后,我们就可以有以下3种解决方案:
1、在生产端导出
2、客户端升级到11.2.0.2,由于客观原因,不能升级客户端.
3、生产库将参数deferred_segment_creation由true改为false(所幸该参数可以动态修改),并重建相关新建表格
select owner,table_name from dba_tables where table_name not in (select segment_name from dba_segments where segment_type = 'TABLE') and owner='HZ';
以下为测试过程:
SQL> create user zhoul identified by zhoul;
User created.
SQL> grant dba to zhoul;
Grant succeeded.
SQL> conn zhoul/zhoul
Connected.
SQL> create table test1 (id number);
Table created.
SQL> create table test2 as select file# from v$datafile;
Table created.
SQL> select owner,table_name from dba_tables where table_name not in (select segment_name from dba_segments where segment_type = 'TABLE') and owner='ZHOUL';
OWNER TABLE_NAME
------------------------------ ------------------------------
ZHOUL TEST1
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/zhoul.dmp owner=zhoul
Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:54:18 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZHOUL
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZHOUL
About to export ZHOUL's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZHOUL's tables via Conventional Path ...
. . exporting table TEST1 0 rows exported
. . exporting table TEST2 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/test1.dmp tables=test1 owner=zhoul
Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:55:39 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00026: conflicting modes specified
EXP-00000: Export terminated unsuccessfully
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/test1.dmp tables=test1
Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:55:45 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TEST1 0 rows exported
Export terminated successfully without warnings.
D:\>exp zhoul/zhoul@drb_11g file=d:\zhoul.dmp tables=test1
Export: Release 11.2.0.1.0 - Production on 星期五 11月 11 11:29:04 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 TEST1
EXP-00003: 未找到段 (0,0) 的存储定义
导出成功终止, 但出现警告。
SQL> conn zhoul/zhoul
Connected.
SQL> alter system set deferred_segment_creation=false;
System altered.
SQL> drop table test1;
Table dropped.
SQL> create table test1 (id number);
Table created.
D:\>exp zhoul/zhoul@drb_11g file=d:\zhoul.dmp tables=test1
Export: Release 11.2.0.1.0 - Production on 星期五 11月 11 11:31:03 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 TEST1导出了 0 行
成功终止导出, 没有出现警告。
. . 正在导出表 S61_B214_GT_2010_3301
导出了 1224 行
EXP-00008: 遇到 ORACLE 错误 1455
ORA-01455: 转换列溢出整数数据类型
EXP-00000: 导出终止失败
为了排除错误,客户端以表方式导出,S61_B214_GT_2010_3301,的下一张表格报同样错误,同时在某些客户端报错信息多了以下错误:
EXP-00003: no storage definition found for segment(0,0)
这个错误已经很明显了,因为在11.2.0.1时,由参数deferred_segment_creation(默认为true)控制是否对新建表格默认分配段空间,所以在exp新建表格时,往往会出现此类错误。
最终Oracle认为此类错误为bug,并在11.2.0.2时得以修正。知道问题的原因之后,我们就可以有以下3种解决方案:
1、在生产端导出
2、客户端升级到11.2.0.2,由于客观原因,不能升级客户端.
3、生产库将参数deferred_segment_creation由true改为false(所幸该参数可以动态修改),并重建相关新建表格
select owner,table_name from dba_tables where table_name not in (select segment_name from dba_segments where segment_type = 'TABLE') and owner='HZ';
以下为测试过程:
SQL> create user zhoul identified by zhoul;
User created.
SQL> grant dba to zhoul;
Grant succeeded.
SQL> conn zhoul/zhoul
Connected.
SQL> create table test1 (id number);
Table created.
SQL> create table test2 as select file# from v$datafile;
Table created.
SQL> select owner,table_name from dba_tables where table_name not in (select segment_name from dba_segments where segment_type = 'TABLE') and owner='ZHOUL';
OWNER TABLE_NAME
------------------------------ ------------------------------
ZHOUL TEST1
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/zhoul.dmp owner=zhoul
Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:54:18 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZHOUL
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZHOUL
About to export ZHOUL's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZHOUL's tables via Conventional Path ...
. . exporting table TEST1 0 rows exported
. . exporting table TEST2 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/test1.dmp tables=test1 owner=zhoul
Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:55:39 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00026: conflicting modes specified
EXP-00000: Export terminated unsuccessfully
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/test1.dmp tables=test1
Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:55:45 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TEST1 0 rows exported
Export terminated successfully without warnings.
D:\>exp zhoul/zhoul@drb_11g file=d:\zhoul.dmp tables=test1
Export: Release 11.2.0.1.0 - Production on 星期五 11月 11 11:29:04 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 TEST1
EXP-00003: 未找到段 (0,0) 的存储定义
导出成功终止, 但出现警告。
SQL> conn zhoul/zhoul
Connected.
SQL> alter system set deferred_segment_creation=false;
System altered.
SQL> drop table test1;
Table dropped.
SQL> create table test1 (id number);
Table created.
D:\>exp zhoul/zhoul@drb_11g file=d:\zhoul.dmp tables=test1
Export: Release 11.2.0.1.0 - Production on 星期五 11月 11 11:31:03 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 TEST1导出了 0 行
成功终止导出, 没有出现警告。
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 512BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 434Oracle管理云服务(OMC)的大数据平台,自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 4142019年7月22日,受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 781某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1348性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 463从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 1898数据库的启动是一个相当复杂的过程。比如,Oracle在启动之前 ... -
怎么样彻底删除数据库?
2018-09-18 11:10 545Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 799LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1189“一朝被蛇咬,十年怕井绳”。在没被“蛇”咬之前,很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1082在DTCC 2018发表《如何落地智能化运维》演讲,主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 525问题一:ping不通问题 在虚拟机上安装好linux系统后, ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 885即日起,不定期更新《OracleDBA实战攻略》一书电子版,请 ... -
Oracle 12c新特性
2018-05-11 10:33 847查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3919操作系统:RHEL6.5 数据库版本:11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 62811g rac安装在二节点跑脚本一般会报此错误: 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 749故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2465由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1675数据库中的log file sync等待事件指的是,当user ...
相关推荐
Oracle JDBC驱动因为授权问题,没有放到Maven的中央仓库里面,当然了,阿里云的镜像也没有了。压缩包中包含ojdbc6-11.2.0.2,ojdbc6-11.2.0.4。
这是本人在实施oracle的时候遇到的问题整理,绝对优秀。
oracle11.2.0.4.0_64_linux下载地址,附百度网盘地址和提取码
oracle 11.2.0.2版本的最后的GI(包含DB)psu补丁11.2.0.2.12
适配oracle11.2.0.1版本的oci
<!--mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.1.0 -Dpackaging=jar -Dfile=c:\ojdbc14.jar--> <groupId>com.oracle</groupId>... <version>11.2.0.1.0 </dependency>
内容包含: p13390677_112040_MSWIN-x86-64_1of7.zip p13390677_112040_MSWIN-x86-64_2of7.zip p13390677_112040_MSWIN-x86-64_3of7.zip p13390677_112040_MSWIN-x86-64_4of7.zip p13390677_112040_MSWIN-x86-64_5of...
oracle11g的最新版本11.2.0.4,适用于Linux。 11.2.0.4安装包在oracle官网已经不能免费下载,网上全的安装包已经没有了,而11.2.0.1BUG较多,是Linux版本。这个资源找了很久,在centos和redhat上都安装成功了,现在...
Oracle+11.2.0.2+Patch+说明.doc
Oracle 11.2.0.4的最新补丁包,有说明安装顺序, 经测试验证没问题,请放心下载。 经测试验证没问题,请放心下载。 经测试验证没问题,请放心下载。 经测试验证没问题,请放心下载。
包含oracle11.2.0.4_x86-64位linux版本全套安装包: p13390677_112040_Linux-x86-64_1of7.zip p13390677_112040_Linux-x86-64_2of7.zip p13390677_112040_Linux-x86-64_3of7.zip p13390677_112040_Linux-x86-64_4of7...
oracle11.2.0.4最后一个版本补丁 windows X64
oracle 11.2.0.4 包括如下版本: oracle 11.2.0.4 win64 oracle 11.2.0.4 Solaris_x86-64 oracle 11.2.0.4 linux_x86-64 oracle 11.2.0.4 linux_x86 网上看到我,只是想让人更多的人看到,侵权勿怪
5为Gateway software,gateways是指透明网关,如果要从oracle访问其它数据库系统(sqlserver,sybase等),需要安装Gateway; 6表示 examples, 是示例文件安装包; 7为deinstall,是Oracle自带的界面化卸载工具;
oracle 11.2.0.1的最后PSU补丁(11.2.0.1.6) p12419378_112010_Linux-x86-64.zip 很难找的奥!
Linux Oracle11.2.0.4安装包全集Linux版本,免去官网下载的大部分时间。百度云盘链接
Oracle client 11.2.0.4 for linux(x64) 客户端安装包 含basic,sdk,sqlplus
oracle11.2.0.4全套安装包(珍藏版)包含: p13390677_112040_Linux-x86-64_1of7.zip p13390677_112040_Linux-x86-64_2of7.zip p13390677_112040_Linux-x86-64_3of7.zip p13390677_112040_Linux-x86-64_4of7.zip p...
为了方便广大命苦的程序员,在此分享 Oracle 11.2.0.4数据库安装包。在此文件是我分享的windows 64位的Oracle 11.2.0.4数据库安装包的百度网盘分享链接,是永久有效的。因为不知道是不是我的问题,在这里本人只能...