oracle常用SQL

一、简单查看oracle的连接情况和内存使用情况.
1、select machine,count(*) from v$session group by machine;
2、select machine,status,count(*) from v$session group by machine,status order by status;
二、Oracle 11g设置内存自动管理:
ALTER SYSTEM SET MEMORY_TARGET = 1024M SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 1500M SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET = 0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_MAX_SIZE=800M SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE=SPFILE;
alter system set pre_page_sga=FALSE scope=spfile;
三、oracle查看允许的最大连接数和当前连接数等信息
1、当前的数据库连接数
select count(*) from v$process
2、数据库允许的最大连接数
select value from v$parameter where name = 'processes'
3、修改最大连接数:
alter system set processes = 300 scope = spfile; 
4、查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', b.sql_text,machine   
from v$session a, v$sqlarea b  
where a.sql_address =b.address order by cpu_time/executions desc; 
SELECT osuser, a.username,cpu_time/executions/1000000||'s', b.sql_text,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
5、当前的session连接数
select count(*) from v$session
6、并发连接数
select count(*) from v$session where status='ACTIVE'
7、查看当前Oracle数据库名称
select name from v$database;
8、查看Oracle实例名称
select instance_name from v$instance;
9、查看Oracle域名
select value from v$parameter where name = 'db_domain';
10、查看Oracle服务名
select * from v$parameter where name = 'service_name';
11、游标实例
declare
  v_tabname varchar2(30);
  l_nc      number;
  l_sqltext varchar2(100);
begin
  v_tabname:='t_affiche';
  execute immediate 'select count(*) from ' || v_tabname
    into l_nc;
  dbms_output.put_line(l_nc);
end;

12、查询出列数据中仅包括数字的数据：

select * from 表 where not regexp_like(列名,'^[[:digit:]]+$');

加入条件

where length(translate(a,'-.0123456789'||a,'-.0123456789'))<>length(a)