Oracle PL/SQL Tutorial/System Tables Data Dictionary/sqlarea

Query v$sqlarea

SQL>
SQL> CREATE TABLE employee
  2  (employee_id         NUMBER(7),
  3   last_name           VARCHAR2(25),
  4   first_name          VARCHAR2(25),
  5   userid              VARCHAR2(8),
  6   start_date          DATE,
  7   comments            VARCHAR2(255),
  8   manager_id          NUMBER(7),
  9   title               VARCHAR2(25),
 10   department_id       NUMBER(7),
 11   salary              NUMBER(11, 2),
 12   commission_pct      NUMBER(4, 2)
 13  );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL> SELECT sql_text
  2  FROM   v$sqlarea
  3  WHERE  INSTR(UPPER(sql_text), "EMPLOYEE") > 0;
INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OP
ERATIONS", 41, 1450, NULL)
SELECT COUNT(*) FROM EMPLOYEE_EVALUATION
INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOU
SE MANAGER", 41, 1300, NULL)
SELECT  topology   FROM  SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers
) b   WHERE b.owner = "sqle"  AND b.table_name = "EMPLOYEE"
INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WARE
HOUSE MANAGER", 41, 1250, NULL)

SELECT sql_text FROM   v$sqlarea WHERE  INSTR(UPPER(sql_text), "EMPLOYEE") > 0
INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FIN
ANCE", 10, 1450, NULL)
DECLARE    lv_salary_increase NUMBER := .10; BEGIN    EXECUTE IMMEDIATE "UPDATE
employee SET salary = NVL(salary, 0) * (1 + :increase)" USING lv_salary_increase
; END;
INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHO
USE MANAGER", 41, 1307, NULL)
INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SA
LES", 31, 1400, NULL)

INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUS
E MANAGER", 41, 1100, NULL)
SELECT COUNT(*) FROM WORKING_EMPLOYEES
INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHO
USE MANAGER", 41, 1200, NULL)
INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-m
on-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL)
INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, AD
MINISTRATION", 50, 1550, NULL)

15 rows selected.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>

Query v$sqlarea for running dba jobs

SQL>
SQL>
SQL> SELECT sql_text, address, hash_value,
  2         sharable_mem, kept_versions
  3  FROM   v$sqlarea
  4  WHERE  INSTR(UPPER(sql_text), "DBA_JOBS_RUNNING") > 0
  5  and    rownum < 50;
SQL_TEXT
--------------------------------------------------------------------------------
ADDRESS  HASH_VALUE SHARABLE_MEM KEPT_VERSIONS
-------- ---------- ------------ -------------
SELECT sql_text, address, hash_value,        sharable_mem, kept_versions FROM
v$sqlarea WHERE  INSTR(UPPER(sql_text), "DBA_JOBS_RUNNING") > 0 and    rownum <
50
1D18CBD0 2636155149        16967             0
SELECT a.job, what,        TO_CHAR(SYSDATE, "mm/dd/yyyy hh24:mi:ss") now,
 TO_CHAR(a.this_date, "mm/dd/yyyy hh24:mi:ss") this FROM   dba_jobs_running a, d
ba_jobs b WHERE  a.job = b.job and rownum < 50
1D1B8F38 1543191033        80595             0
SQL_TEXT
--------------------------------------------------------------------------------
ADDRESS  HASH_VALUE SHARABLE_MEM KEPT_VERSIONS
-------- ---------- ------------ -------------

SQL>