Oracle PL/SQL/System Tables Views/USER VIEWS — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:01, 26 мая 2010
Содержание
Get all views
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>
If view exists, drop view
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.
List view text from user_views
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>
Query user-defined view info from USER_VIEWS table
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>
Query user_views
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>