Oracle View及dbms_metadata.get_ddl看对象生成的代码

View
	SQL> create view abc31 as select * from employees where salary>10000;       View created.	创建视图
	SQL> create or replace view av10000 as select * from employees where salary>=10000;   View created.	创建或替代
	SQL>  create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000 with read only; View created. SQL>select CONSTRAINT_NAME,TABLE_NAME,STATUS from  user_constraints where TABLE_NAME='AV10000'; SYS_C008958 SQL> alter view AV10000 drop constraint SYS_C008958; View altered. SQL> select CONSTRAINT_NAME,TABLE_NAME,STATUS from  user_constraints where TABLE_NAME='AV10000'; no rows selected SQL> delete from AV10000 where salary='12300'             * ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view 去掉只读的约束，还是不能删除。。。   SQL> create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000; View created. SQL> delete from AV10000 where salary='12300'; 1 row deleted.	只读不能通过视图去插入原表，会自动产生约束用默认名   去掉只读的约束，还是不能删除，说明只读属性还在创建的视图上面
	SQL> create or replace view av10000 as select * from employees where salary>=10000 with read only constraints avcon;   View created.	加只读约束
	select CONSTRAINT_NAME,TABLE_NAME,STATUS from   user_constraints where CONSTRAINT_NAME='AVCON';   CONSTRAINT_NAME  TABLE_NAME  STATUS -------------------------------------------------------------------------------- AVCON             AV10000                      ENABLED	查约束
	SQL> create or replace view av10000 as select * from employees where salary>=10000 with check option constraints avcon2;   View created.	加检查约束
	select CONSTRAINT_NAME,TABLE_NAME,STATUS from   user_constraints where TABLE_NAME='AV10000';   CONSTRAINT_NAME  TABLE_NAME  STATUS -------------------------------------------------------------------------------- AVCON2             AV10000                      ENABLED	查约束，视图在约束表和表一样都在TABLE_NAME列
	SQL> create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000; SQL> insert into av10000 values('997','peter','1230','a997@b.com',to_date('2021-02-05','YYYY-MM-DD'),'AD_PRES'); SQL>	没有约束符合原表的not null,unique,foreign key,pk才可以插入
	SQL> create or replace view av10000 as select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000  with check option constraints avcon2;   View created. SQL> insert into av10000 values('998','peter','12300','a998@b.com',to_date('2021-02-05','YYYY-MM-DD'),'AD_PRES'); SQL> insert into av10000 values('997','peter','1230','a997@b.com',to_date('2021-02-05','YYYY-MM-DD'),'AD_PRES');	加了with check option 不符合视图where insert都会失败   来源多表的视图不能做insert/delete等操作
	可以用本方法看dbms_metadata.get_ddl可以看当前表、视图、索引、存储过程、功能的再次产生的代码，如果本身是OWNER，其第三个参数可以不用 select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual; 所以用这个可以看create table newtab as select * from oldtable；到底过去了什么只有not null和check其它的约束都没有 select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual; select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual; select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;   SQL> SELECT DBMS_METADATA.GET_DDL('VIEW','AV10000')  from dual;   CREATE OR REPLACE FORCE EDITIONABLE VIEW "HR"."AV10000" ("EMPLOYEE_ID", "LAST_NAME", "SALARY", "EMAIL", "HIRE_DATE", "JOB_ID") AS   select employee_id,LAST_NAME ,salary,email,hire_date,job_id from employees where salary>=10000	可以查看视图产生的代码   SQL>create view dept_s as select * dept; SQL> create synonym dept_s for dept_v;  Synonym created. SQL> drop table dept; Table dropped. SQL> select * from dept_s; select * from dept_s               * ERROR at line 1: ORA-04063: view "HR.DEPT_V" has errors 会有一个错误，因为视图是无效的