www.eygle.com

[oracle@danaly udump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 31 10:31:47 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> alter system flush shared_pool;

System altered.

SQL> set serveroutput on
SQL> declare
  2       type rc is ref cursor;
  3       l_rc rc;
  4       l_dummy all_objects.object_name%type;
  5       l_start number default dbms_utility.get_time;
  6   begin
  7       for i in 1 .. 1000
  8       loop
  9           open l_rc for
 10           'select object_name
 11              from all_objects
 12             where object_id = ' || i;
 13           fetch l_rc into l_dummy;
 14           close l_rc;
 15       end loop;
 16       dbms_output.put_line
 17       ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 18         ' seconds...' );
 19   end;
 20  /
27.01 seconds...

PL/SQL procedure successfully completed. 

SQL> declare
  2       type rc is ref cursor;
  3       l_rc rc;
  4       l_dummy all_objects.object_name%type;
  5       l_start number default dbms_utility.get_time;
  6   begin
  7       for i in 1 .. 1000
  8       loop
  9           open l_rc for
 10           'select /*+ rule */ object_name
 11              from all_objects
 12             where object_id = ' || i;
 13           fetch l_rc into l_dummy;
 14           close l_rc;
 15       end loop;
 16       dbms_output.put_line
 17       ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 18         ' seconds...' );
 19   end;
 20  /
16.56 seconds...

PL/SQL procedure successfully completed. 

SQL> declare
  2       type rc is ref cursor;
  3       l_rc rc;
  4       l_dummy all_objects.object_name%type;
  5       l_start number default dbms_utility.get_time;
  6   begin
  7       for i in 1 .. 1000
  8       loop
  9           open l_rc for
 10           'select object_name
 11              from all_objects
 12             where object_id = :x'
 13           using i;
 14           fetch l_rc into l_dummy;
 15           close l_rc;
 16       end loop;
 17       dbms_output.put_line
 18       ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 19         ' seconds...' );
 20   end;
 21  /
.19 seconds...

PL/SQL procedure successfully completed.

SQL>  

*** 2005-12-16 20:54:53.496
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4193], [1171], [1187], [], [], [], [], []
Current SQL statement for this session:
UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIME_DP=:4 
WHERE TIME_MP = :5  AND   THREAD = :6  AND   ROWNUM <= 1

SQL> create undo tablespace undotbs2 
   2 datafile '/opt/oracle/oradata/conner/undotbs2.dbf' size 10m;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  114365800 bytes
Fixed Size                   451944 bytes
Variable Size              50331648 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Sun Dec 18 22:37:59 2005
drop tablespace undotbs1 including contents and datafiles
Sun Dec 18 22:37:59 2005
Deleted file /opt/oracle/oradata/conner/undotbs01.dbf
Starting control autobackup
Control autobackup written to DISK device
        handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20051218-01'
Completed: drop tablespace undotbs1 including contents and da

64.193.62.232 - - [21/Dec/2005:17:54:07 +0800] "HEAD / HTTP/1.1" 200 
 - "http://medportal.dynu.net/buy-tamiflu/" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
64.193.62.232 - - [21/Dec/2005:18:13:38 +0800] "HEAD / HTTP/1.1" 200 
 - "http://hydrocodone.coolfreeprizes.com" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
64.193.62.232 - - [21/Dec/2005:18:28:21 +0800] "HEAD / HTTP/1.1" 200 
 - "" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
64.193.62.232 - - [21/Dec/2005:18:37:36 +0800] "HEAD / HTTP/1.1" 200 
 - "http://users.rol.ro/i/ideax/buy-phentermine/buy-cheap-phentermine.html" ...
64.193.62.232 - - [21/Dec/2005:18:37:41 +0800] "HEAD / HTTP/1.1" 200 
 - "http://pills-medical.dynu.net/phentermine-sale/phentermine-sales.html" ...

mysql> select FROM_UNIXTIME(dateline) from spambook limit 1;
+-------------------------+
| FROM_UNIXTIME(dateline) |
+-------------------------+
| 2005-12-13 15:53:29     |
+-------------------------+
1 row in set (0.06 sec) 

mysql> select count(*) from spambook;
+----------+
| count(*) |
+----------+
|     1885 |
+----------+
1 row in set (0.01 sec) 

mysql> select DAYOFMONTH(FROM_UNIXTIME(dateline)) 'date' ,count(*)
    -> from spambook group by 1;
+------+----------+
| date | count(*) |
+------+----------+
|   13 |       30 |
|   14 |       96 |
|   15 |      127 |
|   16 |      125 |
|   17 |      124 |
|   18 |      128 |
|   19 |      119 |
|   20 |      121 |
|   21 |      153 |
|   22 |      109 |
|   23 |      124 |
|   24 |      124 |
|   25 |      107 |
|   26 |      110 |
|   27 |      112 |
|   28 |      100 |
|   29 |       76 |
+------+----------+
17 rows in set (0.76 sec) 

mysql> select distinct(username),count(*) ct
    -> from spambook group by 1 order by 2 desc limit 30;
+--------------+----+
| username     | ct |
+--------------+----+
| mm           | 91 |
| 鱼鱼         | 91 |
| 性福家园     | 48 |
| 机电仪器仪表 | 46 |
| 网络白领     | 44 |
| 心情         | 40 |
| 输遍天涯     | 34 |
| 上海美术培训 | 34 |
| sogonet时尚  | 31 |
| 王先生       | 29 |
| 六合菜       | 26 |
| 刘小姐       | 25 |
| 视频聊天     | 25 |
| 斑竹网站做的 | 24 |
| 王惠         | 21 |
| Fly_Diy      | 21 |
| 用友软件优惠 | 21 |
| 杭州门禁     | 20 |
| 节目总监刘慧 | 20 |
| e444444      | 19 |
| mm.p         | 17 |
| 你好         | 16 |
| 涂腾         | 16 |
| 不用CPU的电  | 16 |
| 刘先生       | 16 |
| 福利彩       | 16 |
| 条码         | 16 |
| 深圳航空机票 | 15 |
| 白华         | 15 |
| 咨询大虾     | 14 |
+--------------+----+
30 rows in set (0.05 sec) 

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Dec 19 18:19:27 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from smon_scn_time where rownum <10;

    THREAD    TIME_MP TIME_DP                SCN_WRP    SCN_BAS
---------- ---------- ------------------- ---------- ----------
         1 1134916635 2005-12-18 22:37:15         25      65931
         1 1134916648 2005-12-18 22:37:28         25      65979
         1 1134917029 2005-12-18 22:43:49         25      66224
         1 1134917336 2005-12-18 22:48:57         25      66325
         1 1134917643 2005-12-18 22:54:04         25      66426
         1 1134917950 2005-12-18 22:59:11         25      66527
         1 1134918257 2005-12-18 23:04:18         25      66628
         1 1134918564 2005-12-18 23:09:25         25      66733
         1 1134918871 2005-12-18 23:14:33         25      66836

9 rows selected.

[oracle@jumper oracle]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Dec 19 19:34:53 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

19:34:58 SQL> col myscn for 9999999999999999
19:34:58 SQL> variable myscn number;
19:34:58 SQL> create table eygle (c1 number);

Table created.

19:35:00 SQL> exec :myscn := dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

19:35:00 SQL> print myscn;

            MYSCN
-----------------
     107374274756

19:35:00 SQL> insert into eygle values(1);

1 row created.

19:35:00 SQL> commit;

Commit complete.

19:35:00 SQL> exec :myscn := dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

19:35:00 SQL> print myscn;

            MYSCN
-----------------
     107374274777

19:35:00 SQL> select * from eygle as of scn(:myscn);
select * from eygle as of scn(:myscn)
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

19:41:10 SQL> select * from eygle as of scn(:myscn);
select * from eygle as of scn(:myscn)
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

19:43:03 SQL> select dbms_flashback.get_system_change_number myscn from dual;

            MYSCN
-----------------
     107374274944

19:43:15 SQL> select * from eygle as of scn 107374274944;

        C1
----------
         1

19:45:11 SQL> select * from (select * from smon_scn_time order by time_dp desc) where rownum <5;

    THREAD    TIME_MP TIME_DP                SCN_WRP    SCN_BAS
---------- ---------- ------------------- ---------- ----------
         1 1134992658 2005-12-19 19:44:18         25      92565
         1 1134992351 2005-12-19 19:39:11         25      92463
         1 1134992042 2005-12-19 19:34:04         25      92256
         1 1134991731 2005-12-19 19:28:53         25      92149

[oracle@danaly ~]$ sqlplus eygle/eygle

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 19 19:37:19 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

19:37:22 SQL> col myscn for 9999999999999999
19:37:22 SQL> variable myscn number;
19:37:22 SQL> create table eygle (c1 number);

Table created.

19:37:22 SQL> exec :myscn := dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

19:37:22 SQL> print myscn;

            MYSCN
-----------------
      16011230122

19:37:22 SQL> insert into eygle values(1);

1 row created.

19:37:22 SQL> commit;

Commit complete.

19:37:22 SQL> exec :myscn := dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

19:37:22 SQL> print myscn;

            MYSCN
-----------------
      16011230124

19:37:22 SQL> select * from eygle as of scn(:myscn);
select * from eygle as of scn(:myscn)
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

19:37:22 SQL> select scn_to_timestamp(16011230124) from dual;

SCN_TO_TIMESTAMP(16011230124)
---------------------------------------------------------------------------
19-DEC-05 07.37.21.000000000 PM

19:38:50 SQL> select timestamp_to_scn('19-DEC-05 07.37.27.000000000 PM') MYSCN from dual;

            MYSCN
-----------------
      16011230125

19:39:02 SQL> select * from eygle as of scn 16011230125;

        C1
----------
         1