常用sql-优快云博客

本文链接：https://blog.youkuaiyun.com/Lloyd2008/article/details/84607959

本文提供了一系列Oracle SQL查询示例，包括删除外键约束、查找特定表的外键、查看用户会话数量及表空间使用情况等实用技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询外键


 select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R';

select  A.CONSTRAINT_NAME 
       from user_constraints A, user_constraints B
       WHERE b.table_name =upper( 'file_mapping_temptable')
        and a.constraint_type = 'R'
        and a.r_constraint_name = b.constraint_name;

select * from user_cons_columns cl where cl.constraint_name='TMP$$_FK9F4C6713B4B894D1';

查看用户连接数


select username,osuser,machine,count(*) from v$session group by  username,osuser,machine order by 4 desc;

查看表空间占用情况


select * from dba_segments where segment_name =upper('speech_analyze_info')
select fs.tablespace_name "表空间名",
(df.totalspace - fs.freespace) "已使用空间(MB)",
fs.freespace "剩余空间(MB)",
df.totalspace "总的预分配空间(MB)",
round(100*((df.totalspace - fs.freespace)/df.totalspace),2)||' %' "已使用百分比",
round(100*(1-(df.totalspace - fs.freespace)/df.totalspace),2)||' %' "剩余百分比" from 
(select tablespace_name,round(sum(bytes)/1048576) Totalspace 
from dba_data_files group by tablespace_name) df,
(select tablespace_name,round(sum(bytes)/1048576) Freespace
from dba_free_space group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name;


ora 26563
drop materialized view log on 【tablename】