Oracle PL/SQL Tutorial/System Tables Data Dictionary/sqlarea
Query v$sqlarea
<source lang="sql">
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></source>
Query v$sqlarea for running dba jobs
<source lang="sql">
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></source>