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的表空间。
最后那个一级到十一级时什么,是要按什么等级字段group一下吗?
select 需要的对象
from 有关系表
where 几张表的关联
AND pub_mnt_region.片区 IN(香一,香二,香三,吉大,拱北,前山
)
GROUP BY ?表的等级
我想知道你那个表里有什么属性列?要不怎么查,否则按照我设想的最佳属性列来写命令,这很简单。我设想的属性列有:学号,学生名,课程,成绩。
他们的关系是每个学生有一个学号,每个学生可以选修多门课程,总成绩是多门成绩的和,但是表中不存在总成绩这个属性列。
如下:
1、select
学号,sum(成绩)
from
学生表
group
by
学号
(用来查询总成绩,group
by用来分组)
2、select
max(成绩)
from
学生表
3、select
学生名
from
学生表
group
by
学生名
having
count(成绩)3
大致写个思路给你 ,要写个存储过程
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’
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)
写的太急,可能多少有点错误,自己能改就自己改了吧
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问题排查
文章出自:https://www.cdcxhl.com/article30/dssshso.html
成都网站建设公司_创新互联,为您提供微信公众号、网站设计、定制开发、ChatGPT、商城网站、外贸建站
声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联