Search ip via sql

2

Does anyone know if there is a way I can search via sql (firebird) all the ips that are using a Table or Views?

I'm trying to update a view, however I'm trying the following error:

This operation is not defined for system tables. 
unsuccessful metadata update.
MinhaView.
Implementation of text subtype 65280 not located.
Changes will be rolled back...

Note: I can not delete either.

Note: If you create a new view, I can record it, but I can not create another view or shutdown the bank.

Or does anyone else have a clue how I can get around this?

    
asked by anonymous 04.01.2018 / 11:26

1 answer

0

Thiago, Firebird as in other banks have internal tables for controls, through these internal tables you can get the information of connections, transactions and executed commands, etc.

Use the following select to get the information you want and more.

select
    A.MON$ATTACHMENT_ID as "Attachment ID",
    A.MON$SERVER_PID as "Server PID",
    A.MON$STATE as "State",
    A.MON$ATTACHMENT_NAME as "Attachment Name",
    A.MON$USER as "User",
    A.MON$ROLE as "Role",
    A.MON$REMOTE_PROTOCOL as "Remote Protocol",
    A.MON$REMOTE_ADDRESS as "Remote Address",
    A.MON$REMOTE_PID as "Remote PID",
    CS.RDB$CHARACTER_SET_NAME as "Character Set",
    A.MON$TIMESTAMP as "Established At",
    A.MON$GARBAGE_COLLECTION as "Garbage Collection",
    A.MON$REMOTE_PROCESS as "Remote Process",
    A.MON$STAT_ID as "Statistics ID",
    ST.MON$STATEMENT_ID as "Statement ID",
    ST.MON$TRANSACTION_ID "Transaction ID",
    case
      when ST.MON$STATE = 0
        then
              'IDLE'
      when ST.MON$STATE = 1
        then
              'ACTIVE'
    end as "State",
    ST.MON$TIMESTAMP "Started At",
    ST.MON$SQL_TEXT "Statement Text",
    R.MON$RECORD_SEQ_READS as "Non-indexed Reads",
    R.MON$RECORD_IDX_READS as "Indexed Reads",
    R.MON$RECORD_INSERTS as "Records Inserted",
    R.MON$RECORD_UPDATES as "Records Updated",
    R.MON$RECORD_DELETES as "Records Deleted",
    R.MON$RECORD_BACKOUTS as "Records Backed Out",
    R.MON$RECORD_PURGES as "Records Purged",
    R.MON$RECORD_EXPUNGES as "Records Expunged",
    IO.MON$PAGE_READS as "Page Reads",
    IO.MON$PAGE_WRITES as "Page Writes",
    IO.MON$PAGE_FETCHES as "Page Fetches",
    IO.MON$PAGE_MARKS as "Page Marks"
from
    MON$ATTACHMENTS A
join MON$STATEMENTS ST on ST.MON$ATTACHMENT_ID = A.MON$ATTACHMENT_ID
join RDB$CHARACTER_SETS CS on (A.MON$CHARACTER_SET_ID = CS.RDB$CHARACTER_SET_ID)
left join MON$RECORD_STATS R on (A.MON$STAT_ID = R.MON$STAT_ID)
left join MON$IO_STATS IO on (A.MON$STAT_ID = IO.MON$STAT_ID)
where
    upper(ST.MON$SQL_TEXT) like upper(:PSQL) -- Comando SQL que deseja encontrar
    and
    ST.MON$STATE = 1 -- Para somente conexões ativas

In the parameter of select you can enter a complete SQL or just a part.

Example: ST.MON$SQL_TEXT like '%USUARIO%' or ST.MON$SQL_TEXT like 'select * from usuario where id =1' .

The "Remote Address" field is the IP of the connection you ran.

Now if you are using the IBExpert tool you can access the top menu under Services > Database Monitoring, there you can query this information and overturn a particular connection.

    
12.01.2018 / 19:24