수집 데이터

분석 내용

DB 대기 이벤트 및 통계 정보

오라클 자원(경합현상, 메모리, 세그먼트, 락, RAC등) 사용 현황 파악

시스템 통계 정보

CPU, 디스크, 네트워크 등 시스템 자원 이용 통계

데이터베이스 부하 정보

그룹화된 DB 사용 부하의 통계 수치

SQL 수행 정보

SQL 통계 정보 및 실행 계획 정보

활동 세션 정보

ASH(Active Session Hisory)를 이용한 해당 스냅샷 시점의 세션 정보 파악 가능

항목

사용법

AWR 스냅샷 삭제

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHO_RANGE

(LOW_SNAP_ID => snap_id, HIGH_SNAP_ID => snapid [DBID => dbid]);

END;

/

AWR 스냅샷 설정 변경

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS

([RETENTION => retention_time][,INTERVAL => interval_time]

[,TOPNSQL => topn_sql_number]);

END;

/

베이스라인 설정

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

(START_SNAP_ID => sanp_id, END_SNAPID => sanp_id,

BASELINE_NAME => 'baseline_name'[, DBID => dbid]);

END;

/

베이스라인 삭제

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE

(BASELINE_NAME => 'baseline_name'[, CASCADE => true|false][,DBID => dbid]);

END;

/

사용 용도

스크립트

AWR DB 보고서 생성

awrrpt.sql, awrrpti.sql

AWR DB 비교 보고서 생성

awrddrpt.sql, awrddrpi.sql

AWR SQL 보고서 생성

awrsqrpt.sql, awrsqrpi.sql

AWR 정보 검색 보고서

awrinfo.sql

ASH 보고서 생성

ashrpt.sql, ashrpti.sql

ADDM 보고서 생성

addmrpt.sql, addmrpti.sql

기타 스크립트

awrddinp.sql, awrinput.sql, awrinpnmp.sql

스크립트

설명

awrrpt.sql

스크립트를 수행한 인스턴스의 AWR 보고서를 생성한다.

awrrpti.sql

DB ID와 인스턴스 번호를 지정해서 AWR 보고서를 생성한다.

프로시저

설명

AWR_REPORT_TEXT

텍스트 형식의 AWR 보고서 생성

AWR_REPORT_HTML

웹 문서 형식의 AWR 보고서 생성

<file_error msg="지정된 경로를 찾을 수 없습니다." n="3"/>

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 18:42

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_48" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "EMAIL" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 3, 0);

execute immediate 'create table MGMT_DM_TT_48

(orig

_val null, new_val null) NOLOGGING PARALLEL as

select CAST(null AS VARCHAR2(25)) orig_val, CAST(null AS VARCHAR2(25)) new_val from dual union all

select s.orig_val,

case

when s.subset = 1 then

CAST(

lpad(trunc(dbms_

random.value(0, power(10, 2 - least(2, greatest('||adj||' - 0, 0))) - 1)), 2 - least(2, greatest('||adj||' - 0, 0)), 0)

|| lpad(nvl(substr(s.new_num, 1, 3 + least(2, greatest('||adj||' - 0, 0))),0),3 + least(2, greatest('||adj||' - 0, 0)), 0)

AS VARCHAR2(25))

end new_val

from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num

from (select "EMAIL" orig_val, min(rowid) rid, min(

case

when 1=1 then 1

end

)

subset

from "HR"."EMPLOYEES" group by "EMAIL")) s

where 1=1 and s.orig_val is not null

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_48"', 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_47 AS

grant_cur INTEGER;

BEGIN

grant_cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" F

ROM "OE"', DBMS_SQL.NATIVE);

DBMS_SQL.CLOSE_CURSOR (grant_cur);

END;

BEGIN "HR".MGMT$MASK_GRANT_47; END;

DROP procedure "HR".MGMT$MASK_GRANT_47

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_47 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_47; END;

DROP procedure "HR".MGMT$MASK_GRANT_47

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", c0m48.NEW_VAL "EMAIL", s."PHONE_

NUMBER", 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_48 c0m48 WHERE sys_op_map_nonnull(s."EMAIL") = sys_op_map_nonnull(c0m48.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_47 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_47; END;

DROP procedure "HR".MGMT$MASK_GRANT_47

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_47 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_47; END;

DROP procedure "HR".MGMT$MASK_GRANT_47

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;

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

EMP_DEPT_FK        Referential Integrity        Enabled        No Action        Not Deferrable        Immediate        Validated                        1        DEPARTMENT_ID

EMP_EMAIL_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "EMAIL" IS NOT NULL

EMP_EMAIL_UK        Unique Key        Enabled                Not Deferrable        Immediate        Validated                        1        EMAIL

EMP_EMP_ID_PK        Primary Key        Enabled                Not Deferrable        Immediate        Validated                        1        EMPLOYEE_ID

EMP_HIRE_DATE_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "HIRE_DATE" IS NOT NULL

EMP_JOB_FK        Referential Integrity        Enabled        No Action        Not Deferrable        Immediate        Validated                        1        JOB_ID

EMP_JOB_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "JOB_ID" IS NOT NULL

EMP_LAST_NAME_NN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        "LAST_NAME" IS NOT NULL

EMP_MANAGER_FK        Referential Integrity        Enabled        No Action        Not Deferrable        Immediate        Validated                        1        MANAGER_ID

EMP_SALARY_MIN        Check        Enabled                Not Deferrable        Immediate        Validated                        1        salary > 0

-- ¿¿ ¿¿¿¿¿¿: 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

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 13:30

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_7" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "FIRST_NAME" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 2, 0);

execute immediate 'create table MGMT_DM_TT_7

(

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(

a0.new_val

AS VARCHAR2(20))

end new_val

from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num

from (select "FIRST_NAME" orig_val, min(rowid) rid, min(

case

when 1=1 then 1

end

) subset

from "HR"."EMPLOYEES" group by "FIRST_NAME")) s

,(select rownum rn, new_val from (select distinct "FIRST_NAME" new_val from "HR"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0

where 1=1 and s.orig_val is not null

and s.rn = a0.rn

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_7"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

DROP TRIGGER "HR"."UPDATE_JOB_HISTORY"

DROP TRIGGER "HR"."SECURE_EMPLOYEES"

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_6 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_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_6 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_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

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", c0m7.NEW_VAL "FIRST_NAME", s."LAST_NAME", s."EMAIL", s."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_7 c0m7 WHERE sys_op_map_nonnull(s."FIRST_NAME") = sys_op_map_nonnull(c0m7.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_6 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_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_6 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_6; END;

DROP procedure "HR".MGMT$MASK_GRANT_6

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"."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

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

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

<file_error msg="지정된 경로를 찾을 수 없습니다." n="3"/>

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 14:19

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_42" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "LAST_NAME" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 1, 0);

execute immediate 'create table MGMT_DM_TT_42

(

orig_val null, new_val null) NOLOGGING PARALLEL as

select CAST(null AS VARCHAR2(25)) orig_val, CAST(null AS VARCHAR2(25)) new_val from dual union all

select s.orig_val,

case

when s.subset = 1 then

CAST(

lpad(lower(d

bms_random.string(''l'', trunc(dbms_random.value(1, 1 - least(1, greatest('||adj||' - 0, 0)))))), 1 - least(1, greatest('||adj||' - 0, 0)),''a'')

|| mgmt$mask_randomencode(nvl(substr(s.new_num, 1, 1 + least(1, greatest('||adj||' - 0, 0))), ''0''),

1 + least(1, greatest('||adj||' - 0, 0)))

AS VARCHAR2(25))

end new_val

from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num

from (select "LAST_NAME" orig_val, min(rowid) rid, min(

case

when 1=1 then 1

end

) subset

from "HR"."EMPLOYEES" group by "LAST_NAME")) s

where 1=1 and s.orig_val is not null

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_42"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_S

IZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

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_41 AS

grant_cur INTEGER;

BEGIN

grant_cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" F

ROM "OE"', DBMS_SQL.NATIVE);

DBMS_SQL.CLOSE_CURSOR (grant_cur);

END;

BEGIN "HR".MGMT$MASK_GRANT_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_41 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_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

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", c0m42.NEW_VAL "LAST_NAME", s."EMAIL", s."PHONE_

NUMBER", 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_42 c0m42 WHERE sys_op_map_nonnull(s."LAST_NAME") = sys_op_map_nonnull(c0m42.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_41 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_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_41 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_41; END;

DROP procedure "HR".MGMT$MASK_GRANT_41

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

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 14:26

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_45" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "EMAIL" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 3, 0);

execute immediate 'create table MGMT_DM_TT_45

(orig

_val null, new_val null) NOLOGGING PARALLEL as

select CAST(null AS VARCHAR2(25)) orig_val, CAST(null AS VARCHAR2(25)) new_val from dual union all

select s.orig_val,

case

when s.subset = 1 then

CAST(

a0.new_val

AS VARCHAR2(25))

end new_val

from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num

from (select "EMAIL" orig_val, min(rowid) rid, min(

case

when 1=1 then 1

end

)

subset

from "HR"."EMPLOYEES" group by "EMAIL")) s

,(select rownum rn, new_val from (select distinct "EMAIL" new_val from "HR"."EMPLOYEES" where 1=1 order by dbms_random.value)) a0

where 1=1 and s.orig_val is not null

and s.rn = a0.rn

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_45"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

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_44 AS

grant_cur INTEGER;

BEGIN

grant_cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" F

ROM "OE"', DBMS_SQL.NATIVE);

DBMS_SQL.CLOSE_CURSOR (grant_cur);

END;

BEGIN "HR".MGMT$MASK_GRANT_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_44 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_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

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", c0m45.NEW_VAL "EMAIL", s."PHONE_

NUMBER", 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_45 c0m45 WHERE sys_op_map_nonnull(s."EMAIL") = sys_op_map_nonnull(c0m45.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_44 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_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_44 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_44; END;

DROP procedure "HR".MGMT$MASK_GRANT_44

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

-- ¿¿ ¿¿¿¿¿¿: test

-- ¿¿¿¿ ¿¿ ¿¿: 03-9¿ -2012 13:48

¿¿¿ ¿¿¿ ¿¿ ¿

User is "SYS"

COMMIT

ALTER SESSION ENABLE PARALLEL DML

DROP TABLE "MGMT_DM_TT_21" PURGE

¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿¿, ¿¿ ¿¿ ¿

declare

adj number:=0;

num number:=0;

begin

select length(count(*)) into adj from (select distinct "EMAIL" from "HR"."EMPLOYEES");

num := adj;

adj := greatest(adj - 3, 0);

execute immediate 'create table MGMT_DM_TT_21

(orig

_val null, new_val null) NOLOGGING PARALLEL as

select CAST(null AS VARCHAR2(25)) orig_val, CAST(null AS VARCHAR2(25)) new_val from dual union all

select s.orig_val,

case

when s.subset = 1 then

CAST(

substr(s.orig_va

l, 4, 4)

AS VARCHAR2(25))

end new_val

from (select rownum rn, orig_val, subset, rid, lpad(rownum - 1, '||num||', 0) new_num

from (select "EMAIL" orig_val, min(rowid) rid, min(

case

when 1=1 then 1

end

) subset

from "HR"."EMPLOYEES" group by "EMAIL")) s

where 1=1 and s.orig_val is not null

';

DBMS_STATS.GATHER_TABLE_STATS(NULL, '"MGMT_DM_TT_21"', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>DBMS_STATS.DEFAULT_DEGREE);

end;

DROP TRIGGER "HR"."UPDATE_JOB_HISTORY"

DROP TRIGGER "HR"."SECURE_EMPLOYEES"

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_20 AS

grant_cur INTEGER;

BEGIN

grant_cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE (grant_cur, ' REVOKE REFERENCES ON "HR"."EMPLOYEES" F

ROM "OE"', DBMS_SQL.NATIVE);

DBMS_SQL.CLOSE_CURSOR (grant_cur);

END;

BEGIN "HR".MGMT$MASK_GRANT_20; END;

DROP procedure "HR".MGMT$MASK_GRANT_20

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿¿ ¿¿ ¿¿ ¿:

CREATE OR REPLACE procedure "HR".MGMT$MASK_GRANT_20 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_20; END;

DROP procedure "HR".MGMT$MASK_GRANT_20

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", c0m21.NEW_VAL "EMAIL", s."PHONE_

NUMBER", 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_21 c0m21 WHERE sys_op_map_nonnull(s."EMAIL") = sys_op_map_nonnull(c0m21.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

¿¿¿ ¿¿¿¿ ¿ ¿¿ ¿¿

ORA-01452:

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

errorExitOraError!

¿¿¿ ¿¿¿¿ ¿¿¿¿¿¿¿. ¿¿ ¿¿¿ ¿¿¿¿¿.

¿¿¿ ¿¿¿¿ ¿¿¿ ¿¿ ¿

¿¿¿ ¿¿¿¿ ¿¿¿¿ ¿¿¿

¿¿¿¿ ¿¿ ¿¿

SQL> set ver on