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;