-- ¿¿ ¿¿¿¿¿¿: test
-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 13:22
¿¿¿ ¿¿¿ ¿¿ ¿
User is "SYS"
COMMIT
ALTER SESSION ENABLE PARALLEL DML
DROP TABLE "MGMT_DM_TT_4" PURGE
¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿
declare
adj number:=0;
num number:=0;
begin
select length(count(*)) into adj from (select distinct "PHONE_NUMBER" from "HR"."EMPLOYEES");
num := adj;
adj := greatest(adj - 3, 0);
execute immediate 'create table MGMT_DM_TT_4
(orig_val null, new_val null) NOLOGGING PARALLEL as
select CAST(null AS VARCHAR2(20)) orig_val, CAST(null AS VARCHAR2(20)) new_val from dual union all
select s.orig_val,
case
when s.subset = 1 then
CAST(
lpad(3 + n
vl(substr(s.new_num, 1, 1), 0), 1, 0)
AS VARCHAR2(20))
end new_val
from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num
from (select "PHONE_NUMBER" orig_val, min(rowid) rid, min(
case
when 1=1 then 1
end
) subset
from "HR"."EMPLOYEES" group by "PHONE_NUMBER")) s
where 1=1 and s.orig_val is not null
';
DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_4"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_
SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);
end;
DROP TRIGGER "HR"."SECURE_EMPLOYEES"
DROP TRIGGER "HR"."UPDATE_JOB_HISTORY"
DECLARE
CURSOR fk_sql IS select refr.owner, refr.table_name, refr.constraint_name
from dba_constraints refd, dba_constraints refr
where refd.owner = 'HR' and
refd.table_name = 'EMPLOYEES' and
refr.constraint_ty
pe = 'R' and
refr.r_owner = refd.owner and
refr.r_constraint_name = refd.constraint_name;
BEGIN
FOR fk IN fk_sql
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE "' || fk.owner || '"."' ||
fk.table_name || '" DROP CONSTRAINT "
' || fk.constraint_name || '"';
END LOOP;
END;
¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:
CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_3 AS
grant_cur INTEGER;
BEGIN
grant_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" FR
OM "OE"', DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR (grant_cur);
END;
BEGIN "HR".MGMT$MASK_GRANT_3; END;
DROP procedure "HR".MGMT$MASK_GRANT_3
¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:
CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_3 AS
grant_cur INTEGER;
BEGIN
grant_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (grant_cur, ' REVOKE SELECT ON "HR"."EMPLOYEES" FROM "
OE"', DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR (grant_cur);
END;
BEGIN "HR".MGMT$MASK_GRANT_3; END;
DROP procedure "HR".MGMT$MASK_GRANT_3
DECLARE
CURSOR c_sql IS select owner, table_name, constraint_name,
constraint_type, generated, index_name from dba_constraints
where owner = 'HR' and
table_name = 'EMPLOYEES' and
constraint_type <> 'R';
BEGIN
FOR c I
N c_sql
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE "' || c.owner || '"."' ||
c.table_name || '" DROP CONSTRAINT "' || c.constraint_name || '"';
END LOOP;
END;
DROP INDEX "HR"."EMP_DEPARTMENT_IX"
DROP INDEX "HR"."EMP_EMAIL_UK"
DROP INDEX "HR"."EMP_EMP_ID_PK"
DROP INDEX "HR"."EMP_JOB_IX"
DROP INDEX "HR"."EMP_MANAGER_IX"
DROP INDEX "HR"."EMP_NAME_IX"
ALTER TABLE "HR"."EMPLOYEES" RENAME TO "EMPLOYEES$DMASK"
CREATE TABLE "HR"."EMPLOYEES" TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING NOCOMPRESS PARALLEL AS SELECT s."EMPLOYEE_ID", s."FIRST_NAME", s."LAST_NAME", s."EMAIL", c0m4.NEW_VAL "PHONE_N
UMBER", s."HIRE_DATE", s."JOB_ID", s."SALARY", s."COMMISSION_PCT", s."MANAGER_ID", s."DEPARTMENT_ID" FROM "HR"."EMPLOYEES$DMASK" s , MGMT_DM_TT_4 c0m4 WHERE sys_op_map_nonnull(s."PHONE_NUMBER") = sys_op_map_nonnull(c0m4.ORIG_VAL)
ALTER TABLE "HR"."EMPLOYEES" NOPARALLEL
DROP TABLE "HR"."EMPLOYEES$DMASK" PURGE
CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING
CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING
CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING
CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING
CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME") TABLESPACE "EXAMPLE" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL )
ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") )
ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") )
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL )
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL )
ALTER TABLE "HR"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL )
ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) )
ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") )
ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") )
ALTER TABLE "HR"."EMPLOYEES" ADD (CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") )
¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:
CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_3 AS
grant_cur INTEGER;
BEGIN
grant_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (grant_cur, ' GRANT REFERENCES ON "HR"."EMPLOYEES" TO
"OE"', DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR (grant_cur);
END;
BEGIN "HR".MGMT$MASK_GRANT_3; END;
DROP procedure "HR".MGMT$MASK_GRANT_3
¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:
CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_3 AS
grant_cur INTEGER;
BEGIN
grant_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (grant_cur, ' GRANT SELECT ON "HR"."EMPLOYEES" TO "OE"
', DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR (grant_cur);
END;
BEGIN "HR".MGMT$MASK_GRANT_3; END;
DROP procedure "HR".MGMT$MASK_GRANT_3
ALTER TABLE "HR"."DEPARTMENTS" ADD (CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") )
ALTER TABLE "HR"."JOB_HISTORY" ADD (CONSTRAINT "JHIST_EMP_FK" FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") )
ALTER TABLE "OE"."CUSTOMERS" ADD (CONSTRAINT "CUSTOMERS_ACCOUNT_MANAGER_FK" FOREIGN KEY ("ACCOUNT_MGR_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL)
ALTER TABLE "OE"."ORDERS" ADD (CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL)
CREATE OR REPLACE TRIGGER "HR"."SECURE_EMPLOYEES" BEFORE
INSERT
OR UPDATE
OR DELETE ON "HR"."EMPLOYEES" BEGIN
secure_dml;
END secure_employees;
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE
CREATE OR REPLACE TRIGGER "HR"."UPDATE_JOB_HISTORY" AFTER
UPDATE OF "DEPARTMENT_ID", "JOB_ID" ON "HR"."EMPLOYEES" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_
id, :old.department_id);
END;
ALTER VIEW "HR"."EMP_DETAILS_VIEW" COMPILE
COMMENT ON COLUMN "HR"."EMPLOYEES"."COMMISSION_PCT" IS 'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage'
COMMENT ON COLUMN "HR"."EMPLOYEES"."DEPARTMENT_ID" IS 'Department id where employee works; foreign key to department_id
column of the departments table'
COMMENT ON COLUMN "HR"."EMPLOYEES"."EMAIL" IS 'Email id of the employee'
COMMENT ON COLUMN "HR"."EMPLOYEES"."EMPLOYEE_ID" IS 'Primary key of employees table.'
COMMENT ON COLUMN "HR"."EMPLOYEES"."FIRST_NAME" IS 'First name of the employee. A not null column.'
COMMENT ON COLUMN "HR"."EMPLOYEES"."HIRE_DATE" IS 'Date when the employee started on this job. A not null column.'
COMMENT ON COLUMN "HR"."EMPLOYEES"."JOB_ID" IS 'Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.'
COMMENT ON COLUMN "HR"."EMPLOYEES"."LAST_NAME" IS 'Last name of the employee. A not null column.'
COMMENT ON COLUMN "HR"."EMPLOYEES"."MANAGER_ID" IS 'Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)'
COMMENT ON COLUMN "HR"."EMPLOYEES"."PHONE_NUMBER" IS 'Phone number of the employee; includes country code and area code'
COMMENT ON COLUMN "HR"."EMPLOYEES"."SALARY" IS 'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)'
COMMENT ON TABLE "HR"."EMPLOYEES" IS 'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.'
BEGIN DBMS_STATS.GATHER_TABLE_STATS('"HR"', '"EMPLOYEES"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE); END;
¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿¿. ¿¿ ¿¿¿ ¿¿¿¿¿.
¿¿ ¿¿¿ ¿¿¿ ¿¿ ¿
¿¿ ¿¿¿ ¿¿¿¿ ¿¿¿
¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿
¿¿¿ ¿¿¿¿ ¿¿¿¿ ¿¿¿
¿¿¿¿ ¿¿ ¿¿
SQL> set ver on | 
