Cannot drop a database link after changing the global_name ORA-02024 [ID 382994.1]

Cannot drop a database link after changing the global_name ORA-02024 [ID 382994.1]

Modified 22-NOV-2010Type PROBLEMStatus MODERATED

In this Document
Symptoms
Cause
Solution

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.6 and later[Release: 9.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 14-Jan-2010***

Symptoms

Not able to drop a database link after changing the global_name of the database

Earlier global_name had did not have domain name attached to it. The newly added
global_name has a domain name attached to it

When trying to drop the database link after this change throws the following error

ORA-02024: database link not found

But database link is present and the query on user_db_links displays the value

Example :-

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
DB10GR2

SQL> create database link l1 connect to scott identified by tiger;

Database link created.

SQL> select db_link from user_db_links;

DB_LINK
---------------------------------------------------------
L1

SQL> alter database rename global_name to DB10GR2.WORLD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
DB10GR2.WORLD

SQL> drop database link l1;
drop database link l1
*
ERROR at line 1:
ORA-02024: database link not found

Even if the global_name is changed back to the original value, the same errors
occurs.

Cause

Initially when a database is created without domain in the global name, null will
used from domain as opposed to .world in earlier releases

Later on when the global_name is altered to contain the domain part also, this
domain remains even when the global_name is altered back a name without domain name

Example :-

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
DB10GR2

SQL> alter database rename global_name to DB10GR2.WORLD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
DB10GR2.WORLD

SQL> alter database rename global_name to DB10GR2;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
DB10GR2.WORLD

The only option left to correct this is to update the base table props$

Solution

To implement the solution, please execute the following steps:

1)Take a complete consistent backup of the database

2)Execute the following:

$sqlplus /nolog
connect / as sysdba

SQL> update props$ set value$ = '<globalname without domain>' where name ='GLOBAL_DB_NAME';

SQL>commit;

3)

a) Then connect as the schema user that owns the DBLINK and try to drop it.

If you still get ORA-2024: database link not found , that means the domain name is in your cache and needs to be cleared.

b) Flush shared pool thrice and retry drop database link.
alter system flush SHARED_POOL;
alter system flush SHARED_POOL;
alter system flush SHARED_POOL;

c) If step b doesn't help, you need to bounce your database and try to drop the database link.

4)Once the database link is dropped, the global_name can be changed back to the
desired name containing domain part using the alter database rename global_name
statement

------------------------------------------------------------------------------

Blog http://blog.youkuaiyun.com/tianlesoftware

网上资源: http://tianlesoftware.download.youkuaiyun.com

相关视频:http://blog.youkuaiyun.com/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群:62697716(); DBA2 群:62697977()

DBA3 群:62697850 DBA 超级群:63306533;

聊天 群:40132017

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

<think>嗯,用户遇到了一个ORA-00904错误,指出“TIMESTAMPDIFF”标识符无效。首先,我需要确定这个错误的原因。ORA-00904通常表示列名或函数名不正确,可能是拼写错误或者Oracle不支持该函数。 用户使用的SQL中包含了一个TIMESTAMPDIFF函数,但Oracle数据库本身并没有这个函数。TIMESTAMPDIFF常见于MySQL或其他数据库系统,Oracle处理日期差的方法是使用两个日期相减,得到的是天数差。然后用户需要将天数转换为秒或者其他单位,再进一步计算小时数。 接下来,我需要检查用户代码中的TIMESTAMPDIFF部分,并建议替换为Oracle支持的函数。例如,计算两个时间戳之间的秒数差,可以使用(最新的结束时间 - 最早的开始时间) * 86400,因为一天有86400秒。然后除以3600得到小时数,再用ROUND函数保留三位小数。 此外,还需要确认用户是否正确处理了窗口函数中的PARTITION BY和ORDER BY子句。用户的窗口函数中使用了ORDER BY NULL,这在Oracle中可能不太常见,但应该没问题,因为MIN和MAX聚合函数在窗口内不需要特定的排序。 可能还需要提醒用户检查其他潜在问题,比如字段名是否正确,是否有权限问题,或者表别名是否正确引用。但根据错误信息,主要问题还是TIMESTAMPDIFF函数不被Oracle支持,所以重点应放在替换这个函数上。 最后,确保给出的修改建议清晰易懂,并提供示例代码,帮助用户直接替换错误部分,并解释修改的原理,让用户理解为什么这样做可以解决问题。</think>这个错误是由于Oracle数据库不支持`TIMESTAMPDIFF`函数导致的。以下是逐步分析和解决方案: --- ### **错误原因分析** 1. **函数不兼容**:`TIMESTAMPDIFF`是MySQL/SQL Server的日期计算函数,Oracle中需改用日期运算语法 2. **时间差计算逻辑**:Oracle中计算时间差的正确方式是直接相减时间戳,再通过数学运算转换单位 --- ### **解决方案** 修改最后计算工时的公式部分: #### **原错误代码**: ```sql ROUND( TIMESTAMPDIFF(SECOND, earliest_start, latest_end) / 3600, 3 ) AS work_hours ``` #### **Oracle兼容写法**: ```sql ROUND( (CAST(latest_end AS DATE) - CAST(earliest_start AS DATE)) * 86400 / 3600, 3 ) AS work_hours ``` 或更精确的写法: ```sql ROUND( EXTRACT(DAY FROM (latest_end - earliest_start)) * 86400 + EXTRACT(HOUR FROM (latest_end - earliest_start)) * 3600 + EXTRACT(MINUTE FROM (latest_end - earliest_start)) * 60 + EXTRACT(SECOND FROM (latest_end - earliest_start)) ) / 3600, 3 ) AS work_hours ``` --- ### **改进建议** 1. **验证时间字段格式**: ```sql SELECT DUMP(earliest_start), DUMP(latest_end) FROM base_data WHERE ROWNUM < 5 ``` 确保时间字段是`DATE`或`TIMESTAMP`类型 2. **空值处理**: ```sql COALESCE(latest_end, SYSDATE) -- 如果存在未结束的记录 ``` 3. **优化窗口函数**: ```sql MIN(ACTUALSTARTTIME) OVER (PARTITION BY a3_code, a2_code, a2_name, a6_code) MAX(ACTUALENDTIME) OVER (PARTITION BY a3_code, a2_code, a2_name, a6_code) ``` 移除`ORDER BY NULL`(Oracle中窗口函数不需要必须包含ORDER BY) --- ### **完整修改后的SQL** ```sql WITH base_data AS (...原有逻辑不变...) SELECT a3_code, a2_code, a2_name, a6_code, login_name, a6_code AS 序列号, earliest_start, latest_end, ROUND( (CAST(latest_end AS DATE) - CAST(earliest_start AS DATE)) * 24, 3 ) AS work_hours FROM base_data ORDER BY a2_code DESC, a6_code; ``` --- ### **关键修改说明** | 原始代码问题 | 解决方案 | 原理 | |------------|--------|-----| | `TIMESTAMPDIFF`函数无效 | 改用日期直接相减 | Oracle中日期相减返回的是天数差 | | 秒级精度需求 | `天数差*86400`转换秒数 | 1天=86400秒 | | 保留三位小数 | 使用`ROUND(...,3)` | 控制精度 | 建议先在测试环境验证时间差计算结果,再部署到生产环境。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值