Re: Search if tables fo an schema are used

"Gonzalo" <gsegarra_at_hospitalaleman.com> a écrit dans le message de news:751a519f.0407150618.d18b9e0_at_posting.google.com...
> Hi,
> I need to audit a third-party software. They want to check whom is
> using any of their software tables. I can't set the instance in AUDIT
> because this will be made independent of cliente configuration and
> without changing any parameter.
> Once solution was to create triggers in some tables.
> The other is to check every 30 minutos all the selects sql used by the
> connected users.
> But is this ok??
> I've tried to set variable v_sql with the complete SQL command and
> then search for the schema.
> FOR v_proc in (
> SELECT UNIQUE vs.sid,vs.username,c.address,v.piece,v.sql_text
> FROM sys.V_$SQLTEXT v,sys.v_$open_cursor c, sys.v_$session vs
> WHERE c.saddr=vs.saddr
> AND v.address = c.address
> AND v.hash_value = c.hash_value
> AND v.command_type = 3
> ORDER BY vs.sid,c.address,piece
> ) LOOP
>
> IF v_address IS NULL OR v_address != v_proc.address THEN
> IF INSTR(v_sql,'SCHEMA_I_SEARCH.') > 0 THEN
>
> END IF;
> v_sql := UPPER(v_proc.sql_text);
> ELSE
> v_sql := v_sql || UPPER(v_proc.sql_text);
> END IF;
> END LOOP;
>
> Thanks,
> Gonzalo.

alter table ... monitoring and query dba_tab_modifications for DML. For select, you can query v$access instead of v$sqltext which doesn't give you the base tables if you use views.

--
Regards
Michel Cadot