С²ËÄñÎÊ£ºoracleÖеÄÒ»¸ö´¥·¢Æ÷ÎÊÌâ
ÎÊÌâ
£¨1£©±íemployeesºÍdepartment´æÔÚ²ÎÕÕÍêÕûÐÔÔ¼Êø£¬ÔÚ±íemployeesÖвåÈë¼Ç¼£¨‘70’£¬‘999’£©department_idΪ‘999’ʱ£¬department±íÖл¹²»´æÔÚdepartment_idΪ‘999’µÄ¼Ç¼£¬ËùÒÔÎ¥±³Á˲ÎÕÕÍêÕûÐÔÔ¼Êø,³öÏÖ“Integrity constraint violation error”ÌáʾÐÅÏ¢¡£
UPDATE employees SET department_id = 999 WHERE employee_id = 170;
-- Integrity constraint violation error
£¨2£©
CREATE OR REPLACE TRIGGER constr_emp_trig
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO departments
VALUES (999, 'dept999', 140, 2400);
END;
/
½á¹û£ºInsert²Ù×÷ËäȻΥ·´Á˲ÎÕÕÍêÕûÐÔÔ¼Êø£¬µ«employees±íÖÐÒÀÈ»³É¹¦²åÈëÁËÒ»ÌõмǼ¡£
Ïà¹ØÎĵµ£º
ºÜ¾ÃûÓиüÐÂOracleÊý¾Ý¿âµÄÎÄÕÂÁË£¬½ñÌìС±àÕûÀíÁËÒ»ÏÂ×ÊÁÏ£¬ºÍ´ó¼Ò·ÖÏíOracleµÄÌåϵ¡£oracleµÄÌåϵºÜÅÓ´ó£¬ÒªÑ§Ï°Ëü£¬Ê×ÏÈÒªÁ˽âoracleµÄ¿ò¼Ü¡£ÔÚÕâÀ¼òÒªµÄ½²Ò»ÏÂoracleµÄ¼Ü¹¹£¬ÈóõѧÕ߶ÔoracleÓÐÒ»¸öÕûÌåµÄÈÏʶ¡£
1¡¢ÎïÀí½á¹¹£¨ÓÉ¿ØÖÆÎļþ¡¢Êý¾ÝÎļþ¡¢ÖØ×öÈÕÖ¾Îļþ¡¢²ÎÊýÎļþ¡¢¹éµµÎļþ¡¢ÃÜÂëÎļþ×é³É£© ¿Ø ......
Ò». °²×°Ç°µÄ×¼±¸
1.ÔÚÊý¾Ý¿â·þÎñÆ÷ÉÏÒÔAdministratorÉí·ÝµÇ¼µ½Windows
2.Í£Ö¹ËùÓÐOracleÏà¹ØµÄ·þÎñ¡£ÕâЩ·þÎñÃû³Æͨ³£ÊÇÒÔOracle´òÍ·
3.Í£Ö¹Distributed Transaction Coordinator·þÎñ
4.±¸·ÝÓëOracleÊý¾Ý¿âÏà¹ØµÄËùÓÐÎļþ£¨°üÀ¨OracleÊý¾Ý¿âÈí¼þ¼°Êý¾ÝÎļþ£©£¬°ÑÕâЩÎļþ°´ÔÓеÄĿ¼½á¹¹¸´ÖƵ½°²È«µÄµØ·½£¬²¢× ......
¶ÔÓÚx$µÄÇé¿ö
SQL> grant select on sys.x$bh to t1;
grant select on sys.x$bh to t1
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
¶ÔÓÚfixed tables/viewsÖ»ÄÜselect
Èç¹ûÏë°Ñx$bhµÄselectȨÏÞgrant¸øÆäËûÓû§Ôõô°ìÄØ,¿ÉÒÔ±äͨһÏÂ
SQL> create view xbh as select * from sys.x$ ......
start with....connect by prior....×Ó¾ä
ÀýÈ磺employee±íÖеÄÊý¾ÝÈçÏ£º
employee_id name manager_id
1   ......
µ¼³öOracleÊý¾Ý¿â½á¹¹
1.·½·¨Ò»£ºÖ±½ÓдsqlÓï¾ä
//»ñÈ¡Ò»¸öSCHEMAϵÄËùÓн¨±íºÍ½¨Ë÷ÒýµÄÓï·¨£¬ÒÔscottΪÀý£º
spool portal_schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
from USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
from USER_INDEXES u;
spool off;
2. ......