Oracle技巧
# SQL技巧
# 索引
# 唯一索引
CREATE UNIQUE INDEX "PK_APP_XXXX" ON "TAB_APP_XXXX" ("REC_ID");
1
# 联合索引
CREATE INDEX "TAB_xxx_UK2" ON "TAB_XXX" ("对应xx编号", "货品编号");
1
# 使用强制索引
select /*+index(T 索引名)*/ 字段1,字段2,... from 表名 AS T
1
# 配置同义词
create synonym 用户2.TAB_XXX for 用户1.TAB_XXX;
1
# 权限
# Oracle用户添加权限
grant insert,select,update on TAB_XXX to 用户2;
1
# 查询技巧
# Oracle根据表名反查存储过程
select *
from user_dependencies
where referenced_name=upper('TAB_NET_SIGNOMITARRIVE_STAT')
1
2
3
2
3
# Oracle根据表名查询job(数据库定时任务)
select *
from user_jobs
where upper(what) like '%SIGNOMITARRIVE%'
select * from dba_scheduler_jobs where job_action like ('%pie_day_order_source%')
1
2
3
4
5
2
3
4
5
# Oracle根据表名查询job执行记录(数据库定时任务)
select *
from tab_job_monitor
where upper(procedure_name) like '%SIGNOMITARRIVE%'
order by createdate desc
1
2
3
4
2
3
4
# Oracle查询库表所有列
select
DISTINCT
a.data_type,
a.column_name 列名,
b.COMMENTS 注释,
CASE a.nullable
WHEN 'Y' THEN ''
WHEN 'N' THEN '否'
END 是否可为空
from all_tab_columns a
left join user_col_comments b
on a.COLUMN_NAME = b.COLUMN_NAME
and a.TABLE_NAME = b.TABLE_NAME
where a.table_name='表名'
AND a.owner = UPPER('用户名');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#
# 常见问题
# 1、子查询使用两个时间类型出现 ORA-00918: column ambiguously defined
SELECT * FROM (SELECT SYSDATE,SYSDATE AS sysdata1（'此处必须加上别名，否则会出现未明确定义列错误'） FROM dual) A
1
上次更新: 2022/12/05, 10:57:24