oracle常用命令

批量insert数据

insert all into ksys_test(pckgsq, prcscd, pckgsq2, content) values ('9','10','11','12') into ksys_test(pckgsq, prcscd, pckgsq2, content) values ('13','14','15','16')  SELECT 1 FROM DUAL

—
查看不同用户的连接数

select username,count(username) from v$session where username is not null group by username

show parameter sessions

select value from v$parameter where name = 'processes'

show parameter processes 

 —
查询数据库当前进程的连接数：

select count(*) from v$process;

—
查看数据库当前会话的连接数：

select count(*) from v$session;

—
查询数据库允许的最大连接数

select value from v$parameter where name = 'processes';

—
分页查询，查询第6到第18条数据

SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM ksys_jyyxrz) A WHERE ROWNUM <= 18)WHERE RN >= 6

—
插入日期时间的类型的数据

insert into testDate(num,Test1,Test2,Test3) values('11',to_date('20161207','YYYYDDMM'),to_date('21-08-2003 20:28:03','DD-MM-YYYY HH24:MI:SS'),to_date('20161207','YYYYDDMM'));

—
查询库中所有表

select * from user_tables

—
清空表中数据

truncate table ksys_test;

–oracle
获取时间戳的几种办法

SELECT (SYSDATE – TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) AS random_pich FROM DUAL;

SELECT (SYSDATE – TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400 as aaa FROM DUAL;

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as systime from dual

–oracle
获取随机数的方法

select round (dbms_random.value(1,999999999),0) as num from dual;

—
给表同时插入两条数据

insert all into ksys_test(pckgsq, prcscd, pckgsq2, content) values ('9','10','11','12') 

into ksys_test(pckgsq, prcscd, pckgsq2, content) values ('13','14','15','16') SELECT 1 FROM DUAL;