【oracle 学习笔记 9】视图 同义词 事务

创建视图权限，视图无效
SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 4月 21 14:37:06 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> edit Wrote file afiedt.buf 1* CREATE TABLE t(id NUMBER) SQL> / Table created. SQL> edit Wrote file afiedt.buf 1 CREATE or replace VIEW tView 2* as SELECT * FROM t; SQL> / as SELECT * FROM t; * ERROR at line 2: ORA-00911: invalid character SQL> edit Wrote file afiedt.buf 1 CREATE or replace VIEW tView 2* as SELECT * FROM t SQL> / CREATE or replace VIEW tView * ERROR at line 1: ORA-01031: insufficient privileges SQL> conn system Enter password: *********** Connected. SQL> edit Wrote file afiedt.buf 1 --CREATE or replace VIEW tView 2 -- as SELECT * FROM t 3* grant CREATE VIEW to ymc SQL> / Grant succeeded. SQL> conn ymc Enter password: *** Connected. SQL> edit Wrote file afiedt.buf 1 CREATE or replace VIEW tView 2* as SELECT * FROM t SQL> / View created. SQL> edit Wrote file afiedt.buf 1 ALTER TABLE t 2* add(d DATE) SQL> / Table altered. SQL> edit Wrote file afiedt.buf 1 SELECT object_name,status 2 FROM user_objects 3* WHERE object_name=upper('t_view') SQL> / no rows selected SQL> edit Wrote file afiedt.buf 1 SELECT object_name,status 2 FROM user_objects 3* WHERE object_name=upper('tView') SQL> / OBJECT_NAME -------------------------------------------------------------------------------- STATUS ------- TVIEW INVALID SQL> edit Wrote file afiedt.buf 1* ALTER VIEW tView compile SQL> / View altered. SQL> edit Wrote file afiedt.buf 1 SELECT object_name, status 2 FROM user_objects 3* WHERE object_name=upper('tView') SQL> / OBJECT_NAME -------------------------------------------------------------------------------- STATUS ------- TVIEW VALID SQL>

创建视图

SQL> edit Wrote file afiedt.buf 1 CREATE or REPLACE VIEW studentMath 2 AS 3 SELECT * 4 FROM school.student 5 WHERE sclass IN ( 6 SELECT c.cname 7 FROM class c, department d 8 WHERE c.department=d.did 9 AND d.dname='数学系' 10 ) 11* with check option SQL> / View created. SQL> edit Wrote file afiedt.buf 1 CREATE or REPLACE VIEW departmentClass 2 AS 3 SELECT d.dname, c.cname 4 FROM class c,department d 5* WHERE c.department=d.did SQL> / View created. SQL> edit Wrote file afiedt.buf 1 SELECT * 2* FROM departmentClass SQL> / DNAME CNAME -------- ---------------- 法律系 06法2班 法律系 05法1班 计算机 05计1班 计算机 06计1班 通信 06计2班 外语 06外1班 外语 05外1班 化工 05化3班 化工 06化2班 9 rows selected. SQL> edit Wrote file afiedt.buf 1 SELECT * 2* FROM studentMath SQL> / no rows selected SQL> SELECT c.cname 2 FROM class c, department d 3 WHERE c.department=d.did 4 AND d.dname='数学系' 5 / no rows selected SQL> edit Wrote file afiedt.buf 1 SELECT text 2 FROM user_views 3* WHERE view_name=upper('studentMath') SQL> / TEXT -------------------------------------------------------------------------------- SELECT "SID","SNAME","SBIRTH","SCLASS","SADDRESS" FROM school.student WHERE SQL> col owner format a20 SQL> col table_name format a20 SQL> col column_name format a20 SQL> edit Wrote file afiedt.buf 1 SELECT * 2 FROM user_updatable_columns 3* WHERE table_name=upper('departmentClass') SQL> / OWNER TABLE_NAME COLUMN_NAME UPD INS DEL -------------------- -------------------- -------------------- --- --- --- SCHOOL DEPARTMENTCLASS DNAME NO NO NO SCHOOL DEPARTMENTCLASS CNAME YES YES YES SQL> spool off

同义词

SQL> conn scott/tiger Connected. SQL> edit Wrote file afiedt.buf 1 CREATE public synonym empInfo 2* for scott.emp SQL> / CREATE public synonym empInfo * ERROR at line 1: ORA-01031: insufficient privileges SQL> conn system Connected. SQL> edit Wrote file afiedt.buf 1 -- CREATE public synonym empInfo 2 -- for scott.emp 3* GRANT CREATE PUBLIC SYNONYM to scott SQL> / Grant succeeded. SQL> edit Wrote file afiedt.buf 1 CREATE public synonym empInfo 2* for scott.emp SQL> / Synonym created. SQL> conn ymc Connected. SQL> conn scott/tiger Connected. SQL> edit Wrote file afiedt.buf 1* SELECT * FROM empInfo SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 14 rows selected. SQL> edit Wrote file afiedt.buf 1* DROP PUBLIC SYNONYM empInfo SQL> / DROP PUBLIC SYNONYM empInfo * ERROR at line 1: ORA-01031: insufficient privileges SQL> conn system Connected. SQL> edit Wrote file afiedt.buf 1 -- DROP PUBLIC SYNONYM empInfo 2* GRANT DROP PUBLIC SYNONYM to scott SQL> / Grant succeeded. SQL> conn scott/tiger Connected. SQL> edit Wrote file afiedt.buf 1 DROP PUBLIC SYNONYM empInfo 2* -- GRANT DROP PUBLIC SYNONYM to scott SQL> / Synonym dropped. SQL> conn system Connected. SQL> desc dba_synonyms Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) SYNONYM_NAME NOT NULL VARCHAR2(30) TABLE_OWNER VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) DB_LINK VARCHAR2(128) SQL> spool off

事务1 打开两个sqlplus

SQL> select * from testPri 2 / no rows selected SQL> edit Wrote file afiedt.buf 1 INSERT 2 INTO testPri 3* VALUES(1) SQL> / 1 row created. SQL> edit Wrote file afiedt.buf 1 SELECT * 2* FROM testPri SQL> / ID -------- 1 SQL> spool off

SQL> conn ymc Connected. SQL> edit Wrote file afiedt.buf 1 SELECT * 2* FROM testPri SQL> / no rows selected SQL> spool off

事务2 打开两个sqlplus

SQL> select * from testPri 2 / no rows selected SQL> edit Wrote file afiedt.buf 1* select * from testPri SQL> / ID -------- 1

SQL> select * from testPri 2 / no rows selected SQL> edit Wrote file afiedt.buf 1 INSERT 2 INTO testPri 3* VALUES(1) SQL> / 1 row created. SQL> edit Wrote file afiedt.buf 1 SELECT * 2* FROM testPri 3 / ID -------- 1 SQL> commit 2 / Commit complete. SQL> spool off