oracle如何查问题,oracle问题排查
oracle 如何查询？
Oracle查询用户表空间：select * from user_all_tables
成都创新互联公司主营洪泽网站建设的网络公司,主营网站建设方案,重庆App定制开发,洪泽h5小程序开发搭建,洪泽网站营销推广欢迎洪泽等地区企业咨询
Oracle查询所有函数和储存过程：select * from user_source
Oracle查询所有用户：select * from all_users.select * from dba_users
Oracle查看当前用户连接：select * from v$Session
Oracle查看当前用户权限：select * from session_privs
表空间含义：
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间。
Oracle数据库中至少存在一个表空间，即SYSTEM的表空间。
oracle查询问题。各路大神这怎么查呢？
最后那个一级到十一级时什么，是要按什么等级字段group一下吗？
select 需要的对象
from 有关系表
where 几张表的关联
AND pub_mnt_region.片区 IN（香一，香二，香三，吉大，拱北，前山
）
GROUP BY ？表的等级
Oracle 数据库查询问题
我想知道你那个表里有什么属性列?要不怎么查，否则按照我设想的最佳属性列来写命令，这很简单。我设想的属性列有:学号，学生名，课程，成绩。
他们的关系是每个学生有一个学号，每个学生可以选修多门课程，总成绩是多门成绩的和，但是表中不存在总成绩这个属性列。
如下:
1、select
学号，sum(成绩)
from
学生表
group
by
学号
(用来查询总成绩，group
by用来分组)
2、select
max(成绩)
from
学生表
3、select
学生名
from
学生表
group
by
学生名
having
count(成绩)3
oracle 查询问题
大致写个思路给你 ，要写个存储过程
select table_name from user_tables ut 这个查询所有用户表名字
遍历所有表
select column_name from user_tab_cols utc where utc.table_name = ‘table_name’
判断column_name 是否为mer_id
如果是则
update table_name set column_name=‘22’ where column_name =‘11’
关于Oracle数据库查询问题
1
select * from good order by price
2
select cid from SalesRecorder where gid='1002'
3
select count(distinct cid) from SalesRecorder
4
select count(*) from Customer where sex='男'
5
select avg(to_number(to_char(sysdate,'yyyy'))-to_number(to_char(birth,'yyyy'))) from Customer where sex='男'
6
select cid,cname from Customer
where cid in
(select cid from SalesRecorder where gid in('1002','1006'))
7
select cname from Customer
where cid not in (select cid from SalesRecorder)
8
select max(quantity) from SalesRecorder where gid='1001'
9
select cname from Customer where cid in
(select cid from SalesRecorder where gid='1001'
and quantity=(select max(quantity) from SalesRecorder where gid='1001'))
10
select cid from SalesRecorder where gid in
(select gid from Good where price100)
11
select cid from SalesRecorder where gid not in
(select gid Good where price100)
12
select cname from Customer where cid in
(select cid from SalesRecorder where gid not in
(select gid Good where price100))
13
select b.gid,b.gname
from Customer a,Good b,SalesRecorder c
where a.cid=c.cid and b.gid=c.gid
and a.sex='女'
group by b.gid,b.gname
14
select b.gid,b.gname,sum(c.quantity)
from Customer a,Good b,SalesRecorder c
where a.cid=c.cid and b.gid=c.gid
and a.sex='女'
group by b.gid,b.gname
15
select b.gname,b.price
from Customer a,Good b,SalesRecorder c
where a.cid=c.cid and b.gid=c.gid
and (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(a.birth,'yyyy')))
between 20 and 25
group by b.gname,b.price
16
select t.* from
(select b.gid,b.gname,sum(c.quantity) quantity
from Customer a,Good b,SalesRecorder c
where a.cid=c.cid and b.gid=c.gid
group by b.gid,b.gname)
order by t.quantity desc
17
select * from Good where gid not in
(select gid from SalesRecorder)
18
delete from Good where gid not in
(select gid from SalesRecorder)
19
update Good set price=price*(1-0.1) where gid in
(select t1.gid from
(select t.gid,t.quantity,row_number() over order by (quantity) rn
from
(select gid,sum(quantity) quantity from SalesRecorder group by gid) t) t1
where rn between 1 and 2)
写的太急，可能多少有点错误，自己能改就自己改了吧
oracle查询问题
select ename,sal,salcomm from (select ename,sal,comm,sal+nvl(comm,0) "salcomm" from emp order by 4) where rownum=1
-----"salcomm"
oracle 一般不区分大小写，加了双引号后就是不是默认了(sql_server中的习惯),就是你写小写salcomm，所以你查SALCOMM肯定不会对应小写的列salcomm.会提示提示符无效。
建议别名不需要加个双引号，除非你特意区别大小写或其他用处
本文名称：oracle如何查问题,oracle问题排查
文章网址：http://cdiso.cn/article/dssshso.html
