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.会提示提示符无效。

建议别名不需要加个双引号，除非你特意区别大小写或其他用处