Fetch ip via sql

Question

Does anyone know if there is a way for me to search via sql (Firebird) all ips that are using a Table or Views?

I am trying to update a view, but am 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’t delete either.

Note: If you create a new view, I can record, but I can’t create another view or shutdown in the bank.

Or someone has another idea of how I can get around this problem?

Answer 1

Thiago, the Firebird just as in other banks has 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 inform a full SQL or only 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 executed.

Now if you are using the tool IBExpert you can access the top menu in Services > Database Monitoring, there you can consult this information and even take down a certain connection.