FAQ of oracle 5

最新推荐文章于 2024-05-03 01:38:22 发布

一起学IT技能

最新推荐文章于 2024-05-03 01:38:22 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/ququhouse/article/details/8802240

本文汇总了Oracle数据库中常见的错误及其解决办法，包括表空间无法扩展、归档日志失败、SQL Plus认证失败等问题，提供了详细的排查思路和技术指导。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.1.1 表空间无法扩展 ORA-01653 unable to extend table SYS.T_SG1 by 4 in tablespace TBS_TEST

故障现象：

在向表插入数据时，报表空间无法扩展的错误:

SQL> insert into t_sg1 select * from dba_objects;

insert into t_sg1 select * from dba_objects

ERROR at line 1:

ORA-01653: unable to extend table SYS.T_SG1 by 4 in tablespace TBS_TEST

原因分析

表空间已经没有剩余空间可用，并且表空间对应的数据文件不是自动扩展的

或者尽管数据库文件是自动扩展的，但数据文件所在的文件系统已经没有剩余空间了

解决:

方法一：修改表空间的数据文件扩展属性(假设数据文件所在文件系统有剩余空间)

alter database datafile 14 autoextend on;

方法二：增大已有数据文件的大小

Alter database datafile 14 resize xxxx;

方法三：为表空间增加数据文件

Alter tablespace tbs_test add datafile ‘xxxx’ size xxxxxxx;

1.1.2 ORA-00257

故障现象：

对重做日志进行归档时,报ORA-00257错误,数据库处于hang状态

原因分析

该错误表示归档目录空间不够,无法完成重做日志的归档工作,并造成数据库hang

解决方法

扩大归档目录所在的文件系统

备份和清理无用的归档文件

如果使用了db_recovery_file,调整db_recovery_file_dest_size参数

1.1.3 无法使用SQLPLUS,报ORA-12638

故障现象

Windows平台上Oracle数据库，本机无法通过SQL plus， PLSQL Developer等工具访问Oracle数据库，连接时报错：Oracle ORA-12638： Credential retrieval failed 。但其它服务器上可通过SQL plus， PLSQL Developer正常访问Oracle。

解决方法

打开SQLNET.ora文件，将SQLNET.AUTHENTICATION_SERVICES= （NTS）一行注释，如： #SQLNET.AUTHENTICATION_SERVICES= （NTS）

1.1.4 Oracle 启动报 ORA-32004

版本：Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.6
This problem can occur on any platform.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

因为设置了REMOTE_OS_AUTHENT参数；

alter system set remote_os_authent = true scope=spfile;

To remove the deprecated parameter from the spfile issue:

alter system reset remote_os_authent scope=spfile;

REMOTE_OS_AUTHENT specifies whether remote clients will be authenticated over insecure connections, this parameter is now deprecated. It is retained for backward compatibility only. Stepping up 'out-of-the-box' security Oracle has decided to deprecate this potentially dangerous parameter in the 11g release.

1.1.5 conn / as sysdba 报错ora-01012: not logged on

conn / as sysdba也报错，为ORA-01012: not logged on

查了alert.log日志也没发现什么，就试着用其他用户登了下，报的错误不同了

ORA-01089: immediate shutdown in progress - no operations are permitted

这个错误，看来数据库关闭的时候出了问题，于是就考虑kill掉oracle进程

ps -ef|grep ora_dbw0_$ORACLE_SID

kill -9 pid

这次再conn / as sysdba就可以了

1.1.6 ORA-01659 ORA-32771问题

操作系统版本：# uname -a

HP-UX dialog1 B.11.23 U ia64 1031013528 unlimited-user license

数据库版本：Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

进行导表操作（2G的dmp文件），总是报1659异常，bdf看空间，还是有几G的，这个怎么办？

<54 dialog1 [oracle] :/oracle/oracle>imp king/king buffer=65536 feedback=100000 volsize=0 FULL=Y file=/tmp/exp_userdb.dmp;

Import: Release 11.1.0.6.0 - Production on Wed Nov 11 11:27:23 2009

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path

Warning: the objects were exported by USERDB, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

IMP-00046: using FILESIZE value from export file of 2097152000

. importing USERDB's objects into KING

. . importing table "ACCOUNT"

.........................................

4160893 rows imported

. . importing table "ACCOUNTATTR"

0 rows imported

. . importing table "ACCOUNTLIST"

0 rows imported

. . importing table "ACCOUNT_BAK2009"

0 rows imported

. . importing table "ADDACCTRELATION"

9 rows imported

. . importing table "C2CRELATION"

0 rows imported

. . importing table "CBE_ACCOUNT_CUM"

0 rows imported

. . importing table "CBE_ACCOUNT_DYN"

.........................................

4160881 rows imported

. . importing table "CBE_ACCOUNT_DYN_BAK2009"

0 rows imported

. . importing table "CBE_ACCTITEMSUM"

0 rows imported

. . importing table "CBE_CUSTOMERORDER"

0 rows imported

. . importing table "CBE_CUSTOMER_CUM"

0 rows imported

. . importing table "CBE_EXPRGROUP"

0 rows imported

. . importing table "CBE_GROUP"

0 rows imported

. . importing table "CBE_GROUPKEY"

0 rows imported

. . importing table "CBE_GRPPRODORDER"

0 rows imported

. . importing table "CBE_PRODORDERRELAT"

0 rows imported

. . importing table "CBE_RENTINFO_I"

0 rows imported

. . importing table "CBE_S2GRELATION"

0 rows imported

IMP-00017: following statement failed with ORACLE error 1659:

"CREATE TABLE "CBE_SUBSCRIBER" ("SUBSCRIBERKEY" NUMBER(10, 0) NOT NULL ENABL"

"E, "APPLYTIME" DATE NOT NULL ENABLE, "EXPIRETIME" DATE, "SUBSCRIBERCODE" VA"

"RCHAR2(23), "SUBSCRIBERTYPE" NUMBER(10, 0), "USERTYPE" NUMBER(10, 0), "USER"

"MAINACCT" NUMBER(20, 0), "MAINPRODUCTKEY" NUMBER(10, 0), "OWNCUSTOMER" NUMB"

"ER(10, 0), "MAINNUMSERVKEY" NUMBER(10, 0), "BRANDID" NUMBER(10, 0), "NETWOR"

"KTYPE" NUMBER(10, 0), "CREDITLEVEL" NUMBER(10, 0), "SUBSCREDITLIMIT" NUMBER"

"(10, 0), "SUBSCRIBERCLASS" NUMBER(10, 0), "LANGUAGETYPE" NUMBER(10, 0), "SM"

"SLANGUAGE" NUMBER(10, 0), "USSDLANGUAGE" NUMBER(10, 0), "STATUS" VARCHAR2(1"

"5), "LASTACTIVEDATE" DATE, "CREATEFROM" NUMBER(10, 0), "SERVICEORDERFLAG" C"

"HAR(15), "SRVREQSUSPENDFLAG" CHAR(15), "POSTSWITCH" DATE, "LOCALAREA" VARCH"

"AR2(11), "HOMECBP" NUMBER(10, 0), "TIMEZONE" NUMBER(10, 0), "MEMRESERVE1" N"

"UMBER(10, 0), "ISGROUPMEMBER" NUMBER(10, 0), "MEMRESERVE3" NUMBER(10, 0), ""

"MEMRESERVE4" NUMBER(10, 0), "MEMRESERVE5" NUMBER(10, 0), "MEMRESERVE6" NUMB"

"ER(10, 0), "MEMRESERVE7" NUMBER(10, 0), "MEMRESERVE8" NUMBER(10, 0), "MEMRE"

"SERVE9" NUMBER(10, 0), "MEMRESERVE10" NUMBER(10, 0), "MNGRESERVE1" NUMBER(1"

"0, 0), "MNGRESERVE2" NUMBER(10, 0), "MNGRESERVE3" NUMBER(10, 0), "MNGRESERV"

"E4" NUMBER(10, 0), "MNGRESERVE5" NUMBER(10, 0), "MNGRESERVE6" NUMBER(10, 0)"

", "MNGRESERVE7" NUMBER(10, 0), "MNGRESERVE8" NUMBER(10, 0), "MNGRESERVE9" N"

"UMBER(10, 0), "MNGRESERVE10" NUMBER(10, 0), "MNGRESERVESTR1" VARCHAR2(63), "

""MNGRESERVESTR2" VARCHAR2(63), "MNGRESERVESTR3" VARCHAR2(63), "MNGRESERVEST"

"R4" VARCHAR2(63), "MNGRESERVESTR5" VARCHAR2(63), "PASSWORD" VARCHAR2(63), ""

"CREATEMODE" NUMBER(10, 0), "REMOVETYPE" NUMBER(10, 0), "ADDRESS" VARCHAR2(2"

"55), "PREDEACTIVEDATE" DATE, "COMPLETEDDATE" DATE, "CREOPERID" VARCHAR2(23)"

", "CREDATE" DATE, "CREDEPTID" VARCHAR2(23), "LASTUPDOPERID" VARCHAR2(23), ""

"LASTUPDDATE" DATE, "LASTUPDDEPTID" VARCHAR2(23), "DISTRIBUTEDCBPSET" VARCHA"

"R2(800)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 224"

"6049792 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL"

" DEFAULT) TABLESPACE "CBS_USER_DAT" LOGGING NOCOMPRESS"

IMP-00003: ORACLE error 1659 encountered

ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace CBS_USER_DAT

但是看空间还是有几G的

<59 dialog1 [oracle] :/tmp>bdf

Filesystem kbytes used avail %used Mounted on

/dev/vg00/lvol3 1081344 689600 388736 64% /

/dev/vg00/lvol1 1835008 127112 1694664 7% /stand

/dev/vg00/lvol8 8912896 2731768 6132968 31% /var

/dev/vg00/lvol7 8355840 2703240 5608496 33% /usr

/dev/vg00/lv21 4325376 2065741 2118527 49% /tmp

/dev/vg00/lv20 20971520 17109338 3620819 83% /oracle

/dev/vg00/lvol6 10551296 4852920 5653888 46% /opt

/dev/vg00/lv19 62488576 15055321 44468689 25% /home1

/dev/vg00/lvol5 131072 8496 121624 7% /home

现在表空间扩不了，

SQL> alter tablespace CBS_USER_DAT add datafile '/oracle/oracle/db/oradata/appdata/CBS_DEFAULT_DAT1.dbf' size 3G;

alter tablespace CBS_USER_DAT add datafile '/oracle/oracle/db/oradata/appdata/CBS_DEFAULT_DAT1.dbf' size 3G

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace

autoextend 加了这个也不行

SQL> alter tablespace CBS_USER_DAT autoextend on ;

Tablespace altered.

还是报这个错

IMP-00003: ORACLE error 1659 encountered

ORA-01659: unable to allocate MINEXTENTS beyond 13 in tablespace CBS_USER_DAT

文件系统大小的问题

在HP-UX文件系统中，系统默认的是小文件系统（即不支持大于2GB的文件），如果用户希望当前的文件系统能支持大于2GB的文件时，我们可以这样做：

1。新建文件系统时：

mkfs -F hfs -o largefiles /dev/vg02/rlvol1

或者

newfs -F hfs -o largefiles /dev/vg02/rlvol1

2。文件系统内有数据文件时：

fsadm -F hfs -o largefiles /dev/vg02/rlvol1

当然，你也可以将大文件系统转换为小文件系统，不过要注意的是这个大文件系统中不能有大于2GB的文件，否则无法转换成功。示例如下：

fsadm -F hfs -o nolargefiles /dev/vg02/rlvol1