不常用到的sql语句记录

1：查询触发器是否存在

--oracle

select count(*) as count from User_Triggers where trigger_name = upper(' triName ');

--sqlserver

select count(*) as count from sysobjects where name='  triName ' and xtype='TR'

2：查询表是否已经存在

--oracle

select count(1) as count from user_tables where table_name = upper('{0}')

--sqlserver

select count(1) as count from Sysobjects where name='{0}' and xtype='U'

3：查询所有的触发器，单个触发器，删除触发器

select * from sysobjects where Xtype = 'TR';
exec sp_helptext 'TR_CDR_MS_GHMX';
drop trigger TR_CDR_MS_GHMX;

4：查询视图是否存在

--oracle

select count(1) as count from user_views where view_name = upper('{0}')

--sqlserver

select count(1) as count from Sysobjects where name='{0}' and xtype='v'

5：根据视图名称 得到其创建语句

--sqlserver

EXEC sp_helptext 'name'

--oracle

select TEXT  from dba_views where view_name = 'V_CDR_MZDA'

SELECT count(0) FROM All_Sequences where sequence_name='LOG_OV_HealthEventsSummary_SEQ';

查看当前数据库的版本

SELECT * FROM V$version;

查看当前数据库的实例名

select * from v$instance 

查看连接数

 1.select count(*) from v$process;                         取得数据库目前的进程数。
2.select value from v$parameter where name = 'processes'; 取得进程数的上限。

在sql命令行下 使用dba的方式登陆

1.修改processes；

SQL> show parameter process


SQL> alter system set processes=1000 scope=spfile;



SQL> shutdown immediate


SQL> show parameter session


注意processes得值

查看服务名  和  实例名

select * from v$instance ;

select *  from v$parameter where name = 'service_names';