13518219792
看创建时间吧，可以用一个伪劣来看，ORA_ROWSCN，这个代表记录的变动
创新互联主营承德网站建设的网络公司,主营网站建设方案,APP应用开发,承德h5微信平台小程序开发搭建,承德网站营销推广欢迎承德等地区企业咨询
select ORA_ROWSCN,t.* from table t
可以用SCN_TO_TIMESTAMP(ora_rowscn)这个函数来看到ora_rowscn所代表的变动点，也就是时间
1、增量，也就是要看整个库增加了多少条数据，
（1）Oracle 查 dba_tables视图，里面有NUM_ROWS ， SUM一下，早一次，晚一次，不过这个只是在大概的数据，不是100%准；
（2）SQL Server 查sys.partitions视图，里面有ROWS，比ORACLE准一点。
2、访问量，这个实现不了，访问量是没有数据的，现在都用的连接池，谁知道真正的访问量。
第一步：创建表
--支出表
create table PAY
(
ID NUMBER(11) not null,
PAY_NAME VARCHAR2(100),
AUTHOR VARCHAR2(100),
TYPE_ID NUMBER(11),
PRICE FLOAT, --金额
BRIEF VARCHAR2(1000),
USERID NUMBER(11), --用户ID
TIMES DATE, --时间
PRIMARY KEY(ID)
)
第二步：插入数据
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (1, '1', '1', 0, 1, '1', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (0, '3', '3', 1, 3, '3', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (2, '2', '2', 1, 2, '2343444', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (6, '6', '1', 0, 4000, '1', 1, to_date('12-08-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (5, '5', '1', 0, 1, '1', 1, to_date('12-10-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (4, '4', '1', 0, 100, '1', 1, to_date('12-09-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (7, '4', '1', 0, 566, '1', 1, to_date('03-03-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (11, '4', '1', 0, 566, '1', 1, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (10, '4', '1', 0, 566, '1', 1, to_date('03-05-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (9, '4', '1', 0, 566, '1', 1, to_date('03-06-2012', 'dd-mm-yyyy'));
insert into PAY (ID, PAY_NAME, AUTHOR, TYPE_ID, PRICE, BRIEF, USERID, TIMES)
values (8, '4', '1', 0, 566, '1', 1, to_date('03-07-2012', 'dd-mm-yyyy'));
commit;
第三步：执行分组查询
SELECT SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 1, B.PRICE, 0)) AS A,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 2, B.PRICE, 0)) AS B,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 3, B.PRICE, 0)) AS C,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 4, B.PRICE, 0)) AS D,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 5, B.PRICE, 0)) AS E,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 6, B.PRICE, 0)) AS F,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 7, B.PRICE, 0)) AS G,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 8, B.PRICE, 0)) AS H,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 9, B.PRICE, 0)) AS I,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 10, B.PRICE, 0)) AS J,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 11, B.PRICE, 0)) AS K,
SUM(DECODE(EXTRACT(MONTH FROM B.TIMES), 12, B.PRICE, 0)) AS L
FROM PAY B
WHERE USERID = 1
AND EXTRACT(YEAR FROM B.TIMES) = '2012'
第四步：输出结果
当前月数量
select sum(数量) from 表名 where to_char(时间,'yyyy-mm')=to_char(sysdate,'yyyy-mm')
指定时间段内数量，比如10月1日0点0分0秒到10月3日23点59分59秒
select sum(数量) from 表名 where to_char(时间,'yyyy-mm-dd hh24:mi:ss')between '2013-10-01 00:00:00' and '2013-10-03 23:59:59'
一、创建一个表
create table A
(
TABLE_NAME VARCHAR2(200),
COUNT_NUM NUMBER
)
二、创建一个存储过程
create or replace procedure tj_data as
-- 当前存储过程用到的变量
v_tableName VARCHAR2(100);
v_sql varchar2(200);
v_count integer;
-- 获取当前数据库中的所有表
CURSOR TABLE_LOOP IS SELECT Table_name FROM User_tables;
begin
-- 打开游标
delete from A;
OPEN TABLE_LOOP;
LOOP
FETCH TABLE_LOOP INTO v_tableName;
EXIT WHEN TABLE_LOOP %NOTFOUND;
v_sql:= 'select count(1) from '||v_tableName;
execute immediate v_sql into v_count;
insert into a values (v_tableName,v_count);
END LOOP;
CLOSE TABLE_LOOP;
end;
三、查出表数据不同的信息
select * from (
select t.table_name,t.num_rows from dba_tab_statistics t where t.owner='表空间' and table_name !='A' and t.table_name in (
SELECT Table_name FROM User_tables)) B,A where a.table_name=b.table_name
你好：这个查询方式有很多；
select * from tableName where DATEPART(mm, theDate)
=DATEPART(mm, GETDATE()) and DATEPART(yy, theDate)
= DATEPART(yy, GETDATE())；
-----------可以用以下方法查找
select * from tableName t where t.dateTime =to_DATE('yyyy-mm','2014-12')
