V$SESSION dynamic contains all the currently active users' usernames and the SQL they are executing in the database.

V$SESSION dynamic contains all the currently active users' usernames and the SQL they are executing in the database.

SQL>
SQL> select a.username,
2 s.sql_text
3 from v$session a,v$sqltext s
4 where a.sql_address = s.address
5 and a.sql_hash_value = s.hash_value
6 AND A.STATUS='ACTIVE'
7 order by a.username,a.sid,s.piece
8 /
USERNAME
------------------------------
SQL_TEXT
--------------------------------------------------
JAVA2S
select a.username, s.sql_text from v$session a,v$s
qltext s where

JAVA2S
a.sql_address = s.address and a.sql_hash_value =
s.hash_value A

JAVA2S
ND A.STATUS='ACTIVE' order by a.username,a.sid,s.p
iece

JAVA2S
DECLARE v_MyNumber NUMBER := 0; BEGIN LOOP
IF v_

JAVA2S
MyNumber = 7 THEN EXIT; END IF;
v_MyNumber


USERNAME
------------------------------
SQL_TEXT
--------------------------------------------------
JAVA2S
:= v_MyNumber + 2; END LOOP; END;


6 rows selected.

SQL>
SQL> --

Query dba_updatable_columns



SQL>
SQL> set echo off
SQL> set verify off
SQL> set linesize 72
SQL> set pagesize 9999
SQL> set feedback on
SQL>
SQL> column view_name format a30
SQL> column column_name format a30
SQL> column i format a1
SQL> column u format a1
SQL> column d format a1
SQL>
SQL> clear breaks
breaks cleared
SQL> break on view_name
SQL>
SQL> select table_name view_name,
2 column_name,
3 decode( insertable, 'YES', null, '*' ) i,
4 decode( updatable, 'YES', null, '*' ) u,
5 decode( deletable, 'YES', null, '*' ) d
6 from dba_updatable_columns
7 where rownum <>
SQL> prompt

SQL> prompt '*' indicated action not permitted.
'*' indicated action not permitted.
SQL> prompt

SQL>
SQL> --

Query user_triggers

SQL>
SQL>
SQL> select trigger_name, trigger_type,
2 triggering_event, trigger_body
3 from user_triggers
4 where rownum <>

Query user_triggers with trigger name

SQL>
SQL>
SQL> CREATE TABLE employees
2 ( employee_id number(10) not null,
3 last_name varchar2(50) not null,
4 email varchar2(30),
5 hire_date date,
6 job_id varchar2(30),
7 department_id number(10),
8 salary number(6),
9 manager_id number(6)
10 );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, 'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, 'Oracle', 'wvelasq@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL>
SQL>
SQL> create or replace trigger MyTrigger
2 before insert or update
3 of department_id
4 on employees
5 referencing old as old_value
6 new as new_value
7 for each row
8 when ( new_value.department_id <> 80 )
9 begin
10 :new_value.commission_pct := 0;
11 end;
12 /

Warning: Trigger created with compilation errors.

SQL>
SQL>
SQL> select trigger_type, triggering_event, when_clause, trigger_body
2 from user_triggers
3 where trigger_name = 'MYTRIGGER';

TRIGGER_TYPE TRIGGERING_EVENT
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
WHEN_CLAUSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

TRIGGER_BODY
--------------------------------------------------------------------------------
BEFORE EACH ROW INSERT OR UPDATE
new_value.department_id <> 80
begin
:new_value.commission_pct := 0;
end;


SQL>
SQL> drop table employees;

Table dropped.

SQL>

Restore trigger create statement from user_trigger table
select
2 'create or replace trigger "' || trigger_name || '"' || chr(10)||
3 decode( substr( trigger_type, 1, 1 ),'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||chr(10) ||triggering_event || chr(10) ||
4 'ON "' || table_owner || '"."' || table_name || '"' || chr(10) ||
5 decode( instr( trigger_type, 'EACH ROW' ), 0, null,'FOR EACH ROW' ) || chr(10) ,
6 trigger_body
7 from user_triggers
8 where trigger_name = upper('YourTriggerName')
9 and rownum <>
SQL> --