Oracle PL/SQL/System Tables Views/USER VIEWS — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
Содержание
Get all views
<source lang="sql">
SQL> SQL> set heading off SQL> set feedback off SQL> set linesize 1000 SQL> set trimspool on SQL> set verify off SQL> set termout off SQL> set embedded on SQL> SQL> select "@getaview " || view_name
2 from user_views 3 /
@getaview EMP_PUBLIC_DATA @getaview AQ$EMP_CHANGES_S @getaview VIEW_T @getaview DEPT_EMP_VIEW @getaview DEPARTMENT_10 @getaview AQ$_EMP_CHANGES_F @getaview AQ$EMP_CHANGES @getaview AQ$EMP_CHANGES_R SQL> SQL> SQL> select "@" || view_name
2 from user_views 3 /
@EMP_PUBLIC_DATA @AQ$EMP_CHANGES_S @VIEW_T @DEPT_EMP_VIEW @DEPARTMENT_10 @AQ$_EMP_CHANGES_F @AQ$EMP_CHANGES @AQ$EMP_CHANGES_R SQL> SQL> set termout on SQL> set heading on SQL> set feedback on SQL> set verify on SQL> SQL>
</source>
If view exists, drop view
<source lang="sql">
SQL> SQL> BEGIN
2 FOR i IN (SELECT null FROM user_views WHERE view_name = "BOOKS_emp") LOOP 3 EXECUTE IMMEDIATE "DROP VIEW books_emp"; 4 END LOOP; 5 END; 6 /
PL/SQL procedure successfully completed.
</source>
List view text from user_views
<source lang="sql">
SQL> SQL> set long 999 SQL> column text format a42 word wrapped SQL> SQL> select view_name, text
2 from user_views;
VIEW_NAME TEXT
------------------------------------------
EMP_HQ select "ID","FNAME","LNAME","START_DATE","
END_DATE","SALARY","CITY","DESCRIPTION" fr om emp where id = "2"
V select "X" from t AVG_SAL select trunc(avg(salary)) avg_sal
from emp
EMPDEPT_V select e.empno
, e.ENAME , e.init , d.dname , d.location , m.ENAME as MANAGER from employees e join departments d using (deptno) join
VIEW_NAME TEXT
------------------------------------------
employees m on (m.empno = d.mgr)
DEPT_SAL select dname,round(avg(sal),2) avgSalary
from emp,dept where emp.deptno = dept.deptno group by dname
ALL_ORACLE_ERRORS SELECT "ERRNUMBER","MESSAGE" FROM TABLE(Or
acleErrorTable())
INVENTORY_VIE SELECT i.item_id, i.reorder_status, i.pric
e FROM inventory_tbl i
TOP_EMP SELECT empno, ename, sal
FROM emp WHERE sal > 2999
EMP_BONUS SELECT dname, empno, ename, sal, sal * .15 VIEW_NAME TEXT
------------------------------------------
bonus FROM emp a, dept b WHERE a.deptno = b.deptno
SHARED SELECT shared_id
, shared_text FROM shared_all WHERE NVL(striping_id,0) = NVL(TO_NUMBER(SUBSTR(USERENV( "CLIENT_INFO"),1,10)),0)
PHONE_LIST select emp_id, firstname || " " || midinit
|| ". " || lastname as name,"(" || area_c ode || ")" || phone as telephone# from emp
11 rows selected. SQL>
</source>
Query user-defined view info from USER_VIEWS table
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer") 3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester") 3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester") 3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager") 3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester") 3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester") 3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager") 3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester") 3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> create type employee_type is object(
2 id VARCHAR2(4 BYTE), 3 name varchar2(47), 4 salary number 5 ); 6 /
Type created. SQL> SQL> create view ov_employee_view
2 of employee_type 3 with object oid ( id ) as 4 select e.id, 5 e.last_name, 6 e.salary 7 from employee e 8 /
View created. SQL> SQL> SQL> SQL> SQL> select VIEW_TYPE_OWNER, VIEW_TYPE, OID_TEXT
2 from user_views 3 where view_name = "OV_EMPLOYEE_VIEW" 4 /
VIEW_TYPE_OWNER VIEW_TYPE
------------------------------
OID_TEXT
sqle EMPLOYEE_TYPE id
SQL> SQL> SQL> SQL> SQL> desc ov_employee_view;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- ID VARCHAR2(4) NAME VARCHAR2(10) SALARY NUMBER(8,2)
SQL> SQL> drop type employee_type; Type dropped. SQL> SQL> drop view ov_employee_view; View dropped. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Query user_views
<source lang="sql">
SQL> SQL> SQL> select text from user_views
2 where rownum < 50;
TEXT
select "LIB_IDX","LIB_NAME","VENDOR_NAME","PROTOCOL_NUM","VERSION_NUM","PATH_NAM select "FILE_MAP_IDX","FILE_CFGID","FILE_STATUS","FILE_NAME","FILE_TYPE","FILE_S select "FILE_MAP_IDX","EXT_NUM","EXT_ELEM_OFF","EXT_SIZE","EXT_FILE_OFF","EXT_TY select "ELEM_NAME","ELEM_IDX","ELEM_CFGID","ELEM_TYPE","ELEM_SIZE","ELEM_NSUBELE select "ELEM_IDX","NUM_ATTRB","ATTRB1_NAME","ATTRB1_VAL","ATTRB2_NAME","ATTRB2_V select "ELEM_IDX","NUM_COMP","COMP1_NAME","COMP1_VAL","COMP2_NAME","COMP2_VAL"," select "CHILD_IDX","PARENT_IDX","SUB_NUM","SUB_SIZE","ELEM_OFFSET","SUB_FLAGS" f select "FILE_MAP_IDX","DEPTH","ELEM_IDX","CU_SIZE","STRIDE","NUM_CU","ELEM_OFFSE select "ADDRESS","PARENT_HANDLE","HASH_VALUE","SQL_ID","CHILD_NUMBER","PARSING_U select "ADDRESS","HASH_VALUE","SQL_ID","PLAN_HASH_VALUE","CHILD_ADDRESS","CHILD_ select "ADDRESS","HASH_VALUE","SQL_ID","PLAN_HASH_VALUE","CHILD_ADDRESS","CHILD_ TEXT
select "ADDRESS","HASH_VALUE","SQL_ID","PLAN_HASH_VALUE","CHILD_ADDRESS","CHILD_ select "ADDRESS","HASH_VALUE","SQL_ID","CHILD_NUMBER","WORKAREA_ADDRESS","OPERAT select "SQL_HASH_VALUE","SQL_ID","WORKAREA_ADDRESS","OPERATION_TYPE","OPERATION_ select "LOW_OPTIMAL_SIZE","HIGH_OPTIMAL_SIZE","OPTIMAL_EXECUTIONS","ONEPASS_EXEC select "PGA_TARGET_FOR_ESTIMATE","PGA_TARGET_FACTOR","ADVICE_STATUS","BYTES_PROC select "PGA_TARGET_FOR_ESTIMATE","PGA_TARGET_FACTOR","ADVICE_STATUS","LOW_OPTIMA select "NAME","VALUE","UNIT" from v$pgastat select "ID","NAME","ISDEFAULT","VALUE","DEFAULT_VALUE" from v$sys_optimizer_env select "SID","ID","NAME","ISDEFAULT","VALUE" from v$ses_optimizer_env select "ADDRESS","HASH_VALUE","SQL_ID","CHILD_ADDRESS","CHILD_NUMBER","ID","NAME select "STATISTIC#","NAME","VALUE" from v$dlm_misc TEXT
select "ADDR","LATCH#","LEVEL#","NAME","GETS","MISSES","SLEEPS","IMMEDIATE_GETS" select "INST_ID","CONVERT_TYPE","AVERAGE_CONVERT_TIME","CONVERT_COUNT" from v$dl select "INST_ID","CONVERT_TYPE","AVERAGE_CONVERT_TIME","CONVERT_COUNT" from v$dl select "LOCKP","GRANT_LEVEL","REQUEST_LEVEL","RESOURCE_NAME1","RESOURCE_NAME2"," select "LOCKP","GRANT_LEVEL","REQUEST_LEVEL","RESOURCE_NAME1","RESOURCE_NAME2"," select "RESP","RESOURCE_NAME","ON_CONVERT_Q","ON_GRANT_Q","PERSISTENT_RES","MAST select "HV_ID","CURRENT_MASTER","PREVIOUS_MASTER","REMASTER_CNT" from v$hvmaster select "HV_ID","CURRENT_MASTER","PREVIOUS_MASTER","REMASTER_CNT" from v$gcshvmas select "FILE_ID","OBJECT_ID","CURRENT_MASTER","PREVIOUS_MASTER","REMASTER_CNT" f select "INST_ID","LOCAL_NID","REMOTE_NID","REMOTE_RID","REMOTE_INC","TCKT_AVAIL" select "LOCAL_NID","REMOTE_NID","REMOTE_RID","REMOTE_INC","TCKT_AVAIL","TCKT_LIM TEXT
select "HANDLE","GRANT_LEVEL","REQUEST_LEVEL","RESOURCE_NAME1","RESOURCE_NAME2", select "HANDLE","GRANT_LEVEL","REQUEST_LEVEL","RESOURCE_NAME1","RESOURCE_NAME2", select "GC_ELEMENT_ADDR","INDX","CLASS","GC_ELEMENT_NAME","MODE_HELD","BLOCK_COU select "CR_REQUESTS","CURRENT_REQUESTS","DATA_REQUESTS","UNDO_REQUESTS","TX_REQU select "PIN1","PIN10","PIN100","PIN1000","PIN10000","FLUSH1","FLUSH10","FLUSH100 select "GC_ELEMENT_ADDR" from v$gc_elements_with_collisions select "FILE_NUMBER","X_2_NULL","X_2_NULL_FORCED_WRITE","X_2_NULL_FORCED_STALE", select "FILE_NUMBER","X_2_NULL","X_2_NULL_FORCED_WRITE","X_2_NULL_FORCED_STALE", select "CLASS","X_2_NULL","X_2_NULL_FORCED_WRITE","X_2_NULL_FORCED_STALE","X_2_S select "FILE#","BLOCK#","CLASS#","STATUS","XNC","FORCED_READS","FORCED_WRITES"," select "LOCK_ELEMENT_ADDR","INDX","CLASS","LOCK_ELEMENT_NAME","MODE_HELD","BLOCK TEXT
select "LOCK_ELEMENT_ADDR" from v$locks_with_collisions select "FILE_NUMBER","FREQUENCY","X_2_NULL","X_2_NULL_FORCED_WRITE","X_2_NULL_FO select "FILE_NUMBER","FREQUENCY","X_2_NULL","X_2_NULL_FORCED_WRITE","X_2_NULL_FO select "CLASS","X_2_NULL","X_2_NULL_FORCED_WRITE","X_2_NULL_FORCED_STALE","X_2_S select "INSTANCE","CLASS","CR_BLOCK","CR_BUSY","CR_CONGESTED","CURRENT_BLOCK","C 49 rows selected. SQL>
</source>