'ORCALE'에 해당되는 글 1건

<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