I want to be able to query the last record or a few of the last records in a table

Hi, can you describe your alert_log table? You cannot select a 'last' record the way you are trying.

SQL> desc alert_log
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 MSG                                                VARCHAR2(80)

Your alert_log has no timestamp column or anything like it, you cannot get the last record from such a table. Adding a timestamp or sequence column would fix this. Or maybe is your MSG containing some sort of timestamp?

MSG
--------------------------------------------------------------------------------
SUCCESS: diskgroup ARCHIVE was dismounted
SUCCESS: diskgroup ARCHIVE was mounted
Fri Sep 30 09:55:41 2011
SUCCESS: diskgroup ARCHIVE was dismounted
Fri Sep 30 12:05:07 2011
Thread 2 advanced to log sequence 4290
  Current log# 4 seq# 4290 mem# 0: +DATA/scsd/onlinelog/group_4.304.637965419
Fri Sep 30 12:05:07 2011
SUCCESS: diskgroup ARCHIVE was mounted
SUCCESS: diskgroup ARCHIVE was dismounted
SUCCESS: diskgroup ARCHIVE was mounted

MSG
--------------------------------------------------------------------------------
Fri Sep 30 12:05:38 2011
SUCCESS: diskgroup ARCHIVE was dismounted
Fri Sep 30 14:18:09 2011
Fri Sep 30 14:18:09 2011
Fri Sep 30 14:22:16 2011
Thread 2 advanced to log sequence 4291
  Current log# 3 seq# 4291 mem# 0: +DATA/scsd/onlinelog/group_3.306.637965409
Fri Sep 30 14:22:16 2011
SUCCESS: diskgroup ARCHIVE was mounted
SUCCESS: diskgroup ARCHIVE was dismounted
SUCCESS: diskgroup ARCHIVE was mounted

MSG
--------------------------------------------------------------------------------
Fri Sep 30 14:22:48 2011

etc .....

Can't think of any filter for that content. You could add a timestamp column if you have Oracle 10g or above like this:

alter table alert_log
add column insert_time TIMESTAMP default SYSTIMESTAMP;

Test this on a development db as this could take some time, insert a record in alert_log and then select the last row like this:

select * from alert_log
where insert_time = (select max(insert_time) from alert_log);

(I can't test this for you now btw)

create directory BDUMP as '/u01/app/oracle/admin/SCSD/bdump';

create table
     alert_log ( insert_time TIMESTAMP default SYSTIMESTAMP,
                       msg varchar2(80)  )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alert_SCSD2.log')
)
reject limit 1000;



     alert_log ( insert_time TIMESTAMP default SYSTIMESTAMP,
                                               *
ERROR at line 2:
ORA-30657: operation not supported on external organized table


SQL>

Since we now know you're referencing an external table, that is, visible to the operating system, I speculate you may be running upon a UNIX/LINUX server.

Execute "tail" to display the last few lines of a file. Type:

% tail <filename> -<n - number of lines>

where n indicates last n lines. If -n is omitted, it will display the last 10 lines.



Read more: A Tutorial for AIX on Unix | eHow.com http://www.ehow.com/how_7738270_tutorial-aix-unix.html#ixzz1w1P0R8u2

@slightwv - nice use of rownum:) just ; at line 7 instead of / imho :D

Thanks.  It can be cleaned up even more using the data warehouse functions:

select myrownum, msg from (
select row_number() over(order by rownum desc) myrownum, msg from alert_log
)
where rownum <= 20
; -- just for you gerwinjansen...  :)

Select allOpen in new window

LindaC,

Can I ask why you accepted the answer you did when it does not appear to answer you question?

@LindaC - please close this one different, I think slightwv's comment 38027456 is your solution.

Did I accept my question?

If I did that, is wrong.

Let me ask how to solve this.

I just click "Request Attention" and told them a I made a mistake.
The correct answer os for slightwv.
So sorry for this mess.

Thank you.