Oracle Database Discussions

Dear Jens and everyone,

I am thankful to you that you give me the useful info.
I created the following table “CLIENT_INFO”:

Sql> connect system/manager
sql>CREATE TABLE CLIENT_INFO(client_id varchar2(20),client_name VARCHAR2(30),
work_station varchar2(40),host_name varchar2(30),database_server varchar2(40),
db_domain varchar2(40),ip_address varchar2(30)
,network_protocol varchar2(60));

sql>grant select,insert on client_info to public;
sql >create public synonym client_info for client_info;



Then I create a LOGON trigger in order to store the required Information of all the clients into my table
“CLIENT_INFO”:

LOGON Trigger:

SQl> Connect system/manager
Sql>create or replace trigger logon_trigger
after logon ON database
begin

insert into client_info(client_id ,client_name,work_station,host_name,database_server,
db_domain,ip_address,network_protocol)
select SYS_CONTEXT('USERENV','CURRENT_USERID'),SYS_CONTEXT('USERENV','CURRENT_USER'),
SYS_CONTEXT('USERENV','OS_USER'),SYS_CONTEXT('USERENV','HOST'),
SYS_CONTEXT('USERENV','DB_NAME'),SYS_CONTEXT('USERENV','DB_DOMAIN'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
FROM DUAL;
COMMIT;
end if;

END;
/

I created the above Logon trigger,so every session when logon to database, insert its own environment info. into the client_info table.

But when I run the query >select * from client_info;

It displays the information for only two users(system,sys).However there are several other sessions connected to the database like scott,test etc.

for example:


CLIENT_ID CLIENT_NAME WORK_STATION HOST_NAME DATABASE_SERVER DB_DOMAIN IP_ADDRESS
5 SYSTEM SAMAD\Administrator RUSH\SAMAD TEST
0 SYS SAMAD\Administrator RUSH\SAMAD TEST
5 SYSTEM SAMAD\Administrator RUSH\SAMAD TEST
0 SYS SAMAD\Administrator RUSH\SAMAD TEST
5 SYSTEM SAMAD\Administrator RUSH\SAMAD TEST
0 SYS SAMAD\Administrator RUSH\SAMAD TEST
6 rows selected.


How should I get the required info from my historical table “CLIENT_INFO” for each and every user.

I am looking forward to your suggestion.

Thanks

Bilal