常用sql

查询外键

 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】