英文:
Oracle - denie columns in where clause
问题
在Oracle中，是否有一种方法可以禁用/限制/在使用where子句中某些列时进行警告？
我之所以问这个问题，是因为我有一个非常复杂的系统（跨越数百万行代码，包含成千上万个SQL语句，在敏感的生产环境中运行），与Oracle数据库一起工作，我需要从一个列迁移到另一个列，而该列是键的一部分（并且具有非常不唯一的名称）。
简单的搜索是不可能的...
我正在进行的步骤如下：
- 填充新列
- 每当第一个列上有索引时，在第二个列上添加索引。
- 将所有在where子句中使用的旧列更改为新列
- 停止从第一个列中读取
- 停止向第一个列中写入
- 删除该列
我目前已完成第3步，希望验证我是否已找到所有情况。
英文:
Is there a way to disable/restrict/alert-when-using some column in Oracle in a where clauses?
The reason that I'm asking this is because I have a very complex system (~30 services span cross millions of lines of code with thousends of sqls in it, in a sensitive production environment) working with an Oracle DB I need to migrate from using one column that is part of a key (and have a very not uniqu name) to another column.
Simple search is impossible....
The steps I'm having are:
- populate new column
- Add indexes on with the second column whenever there's an index with the first one.
- Migrate all uses in where caluses from old to new column
- Stop reading from the first column
- Stop writing to the first column
- Delete the column
I'm currently done step 3 and want to verify I've found all of the cases.
答案1
得分: 1
所以，你正在用另一个列替换一个列。你完成后期望得到什么好处？这将如何改善该应用程序的整体体验？我希望这将值得努力。
至于你的问题：查询user_source（或将其扩展到all_source甚至dba_source，但你需要额外的权限来执行此操作），看看那个非常不唯一的名称在哪里使用。类似这样的：
SQL> select * from user_source where lower(text) like '%empno%';
NAME TYPE LINE TEXT
--------------- ------------ ----- --------------------------------------------------------------------------------
P_RAISE PROCEDURE 22 WHERE empno = par_empno;
P_RAISE PROCEDURE 14 WHERE empno = par_empno;
P_RAISE PROCEDURE 1 PROCEDURE p_raise (par_empno IN emp.empno%TYPE)
GET_LIST FUNCTION 7 'select empno, ename, job, sal from emp where deptno = 10 order by '
英文:
So, you're replacing one column with another. Which benefit do you expect once you're done? How will that improve overall experience with that application? I hope it is worth the effort.
As of your question: query user_source (or expand it to all_source or even dba_source, but you'll need additional privileges to do that) and see where's that very not unique name used. Something like this:
SQL> select * from user_source where lower(text) like '%empno%';
NAME TYPE LINE TEXT
--------------- ------------ ----- --------------------------------------------------------------------------------
P_RAISE PROCEDURE 22 WHERE empno = par_empno;
P_RAISE PROCEDURE 14 WHERE empno = par_empno;
P_RAISE PROCEDURE 1 PROCEDURE p_raise (par_empno IN emp.empno%TYPE)
GET_LIST FUNCTION 7 'select empno, ename, job, sal from emp where deptno = 10 order by '
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论