Skip to content

Oracle - 55. page

误删除唯一索引的补救办法

SQL> create table t1 (t_id number);

Table created.

SQL> select index_name from user_indexes where table_name=’T1′;

no rows selected

SQL> insert into t1 values(110);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(130);

1 row created.

SQL> insert into t1 values(140);

1 row created.

SQL> create index idx_t1_id on t1(id) online;
create index idx_t1_id on t1(id) online
*
ERROR at line 1:
ORA-00904: “ID”: invalid identifier

SQL> create index idx_t1_id on t1(t_id) online;

Index created.

SQL> select * from t1;

T_ID
———-
110
120
130
140

SQL> commit
2 ;

Commit complete.

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable validate;

Table altered.

SQL> insert into t1 values(110);
insert into t1 values(110)
*
ERROR at line 1:
ORA-00001: unique constraint (LUDA.PK_T1_ID) violated

SQL>
enable validate 是对当前存在的数据进行唯一性效验。如果当前表中存在重复数据,那么添加pk_t1_id的主键就会出错。

下面来测试
enable novalidate

SQL> drop table t1
2 ;

Table dropped.

SQL>
SQL> create table t1 (t_id number);

Table created.

SQL> insert into t1 values(110);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(130);

1 row created.

SQL> create index idx_t1_id on t1(t_id);

Index created.

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable validate;
alter table t1 add constraint pk_t1_id primary key(t_id) enable validate
*
ERROR at line 1:
ORA-02437: cannot validate (LUDA.PK_T1_ID) – primary key violated

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable novalidate;

Table altered.

SQL> insert into t1 values(120);
insert into t1 values(120)
*
ERROR at line 1:
ORA-00001: unique constraint (LUDA.PK_T1_ID) violated

SQL>

这里novalidate的作用就是不对旧的数据进行效验,只对新加进来的数据进行效验。

BUG:shutdown immediate (hung住)

ora 10g 10201

BUG1:shutdown immediate 时系统hung住,经metalink查询:

Verify that temporary segments are decreasing
———————————————
To verify that the temporary segments are decreasing have an active session
available in Server Manager or SQLPLUS during the SHUTDOWN IMMEDIATE. Issue the following
query to ensure the database is not hanging, but is actually perform extent
cleanup:

SVRMGR/SQL> select count(block#) from fet$;
COUNT(BLOC
———-
7

SVRMGR/SQL> select count(block#) from uet$;
COUNT(BLOC
———-
402

After some time has elapsed, reissue the query and see that the values for fet$
have increased while the values or uet$ have decreased:

SVRMGR/SQL> select count(block#) from fet$;
COUNT(BLOC
———-
10

SVRMGR/SQL> select count(block#) from uet$;
COUNT(BLOC
———-
399

During shutdown the SMON process is cleaning up extents and updating the data
dictionary tables with the marked free extents. As the extents are marked as
freed, they are removed from the table for used extents, UET$ and placed on the
table for free extents, FET$.

How to Avoid creating many Temporary Extents
——————————————–
Once the database has shutdown cleanly, to avoid creating many temporary
extents change the initial and next extent sizes on temporary tablespaces
to a more appropriate size:

ALTER TABLESPACE DEFAULT STORAGE (INITIAL M/K NEXT M/K);

Note: If the temporary tablespace is of type TEMPORARY, then this change
will only affect temporary segments created after issuing the above
command. Any existing temporary segments already in the TEMPORARY tablespace
will not be affected till the instance is restarted. On shutdown, existing
temporary segments are dropped. If the TEMPORARY TABLESPACE is of type
PERMANENT, then cleanup is performed by SMON after completion of the process
using it.

Increasing the initial and next extent size will decrease the number of extents
that are allocated to temporary segments. Since there are fewer extents to
deallocate, the database should shutdown more speedily.

Take the following scenario:

A database was subject to large sorts with the following sort parameter in
the “init.ora” file:

– sort_area_size=1000000

The temporary tablespaces for this database were all created with initial and
next extents sized at 50k and the total database size was about 300mb.

Database sorts will utilize memory as much as possible based on the “init.ora”
parameter “sort_area_size”. Once this memory-based sort area is filled, the
database will utilize the temporary table space associated with the database
user to complete the sort operation. During a shutdown normal, the database
will attempt to clean up the temporary tablespaces.

If a small extent size is used, then a large number of extents will be created
for a large sort. The cleanup of the temporary tablespace takes much longer
with a large number of extents.

Note:
=====
You have to do a shutdown abort and then bring the database
back up to run the suggested queries.

For other reasons for slow/hung shutdown see also these notes:

Note 375935.1 – What To Do and Not To Do When ‘shutdown immediate’ Hangs
Note 428688.1 – Bug:5057695: Shutdown Immediate Very Slow To Close Database.

References:
===========Note:61997.1 SMON – Temporary Segment Cleanup and Free Space Coalescing

Search Words:
=============
hanging
shutdown

在安装完oracle后DBCA或者NETCA无法打开

确认这个错误我一共分三个步骤:

1:oracle正确安装了,但是建库时候dbca无法打开,提示没有这个命令。

那么就可以确认这个是环境变量的问题,

env | PATH

没有发现$ORACLE_HOME

cd $ORACLE_HOME | pwd

提示为: /home/oracle

我的oracle_home目录为:

/oracle/product/10.2.0/db_1

————————————————-

很显然问题出在这里,

vi .profile

修改

export PATH=$ORACLE_HOME/bin:.:$PATH:/usr/local/bin:/usr/ccs/bin

export PATH=$PATH:$ORACLE_HOME/bin:.:$PATH:/usr/local/bin:/usr/ccs/bin

经过发现这个错误一般在aix5.3下面产生。

AIX夏令时导致应用时间对比异常终端处理

前几天一个客户反应系统时间比现实晚一个小时,导致无法刷卡。因为oracle的时间是通过获取系统当前时间

11月6号。

当时我查了下发现是AIX系统开启夏令时导致的

echo $TZ

beist-8TD

————-

在aix 5.3补丁打齐下可以使用命令

chtz beist-8

修改。

或者直接修改/etc/environment

TZ=Beist-8

或者smit 里修改。

—————————————–

在aix6.1系统中推荐使用后面的2种方法修改,修改后建议重启AIX

ksvcreate: Process(m000) creation failed

10. 7. 2009, 12.09
Alert.log:
==========
Fri Jul 10 09:24:19 2009
WARNING: inbound connection timed out (ORA-3136)
Fri Jul 10 09:30:11 2009
Process m000 died, see its trace file
Fri Jul 10 09:30:11 2009
ksvcreate: Process(m000) creation failed
Fri Jul 10 09:31:12 2009
Process m000 died, see its trace file

Trace file:
===========
*** SERVICE NAME:(SYS$BACKGROUND) 2009-07-09 20:35:37.278
*** SESSION ID:(3295.1) 2009-07-09 20:35:37.278
ktsmgtur(): TUR was not tuned for 1644 secs
ktsmg_advance_slot(): MMNL advances slot after 1786 seconds
*** 2009-07-10 09:30:11.004
Process m000 is dead (pid=32562, state=3):
Unable to schedule a MMON slave at: Auto Flush Main 1
Attempt to create slave process failed.
Can happen for several reasons:
– No process state objects
– Reached OS set limits
– A shutdown was going on
Check alert log for more details.

CHECKS:
=======
SQL> show parameter process

NAME TYPE VALUE
———————————— ———– ——————————
processes integer 3000

SQL> show parameter session

NAME TYPE VALUE
———————————— ———– ——————————
sessions integer 3305

SQL> select count(*) from v$session;

COUNT(*)
———-
2994

SELECT COUNT(*) FROM v$process;

COUNT(*)
———-
2990

Solution
========
– kill processes which generates the processes ( usually from http)
– restart database
– this error coresponds with ORA-3136