Impdp – parâmetro TABLE_EXISTS_ACTION
No artigo de hoje vou falar sobre o parâmetro TABLE_EXISTS_ACTION do impdp, também vou criar um exemplo prático para que vocês entendam melhor o conceito.
Este parâmetro deve ser usado em conjunto com o parâmetro TABLES, que especifica as tabelas que serão importadas. Este parâmetro define qual ação tomará o Data Pump quando encontrar uma tabela que já existe no banco de dados.
O parâmetro pode ter os seguintes valores:
- SKIP – nenhuma ação é tomada, ignora a(s) tabela(s) existente(s).
- APPEND – novas linhas serão acrescentadas a tabela existente.
- TRUNCATE – apaga as linhas da(s) tabela(s), em seguida, efetua a carga dos dados.
- REPLACE – substitui a(s) tabela(s) existente(s); implicitamente, é executado os comandos DROP TABLE e CREATE TABLE, depois é feito a carga dos dados.
PASSO A PASSO
Passo 1 – Criando o HREX, importando tabelas do HR para o HREX:
CREATE USER HREX IDENTIFIED BY HREX
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . importou "HREX"."COUNTRIES" 6.367 KB 25 linhas
. . importou "HREX"."DEPARTMENTS" 7.007 KB 27 linhas
. . importou "HREX"."EMPLOYEES" 16.81 KB 107 linhas
. . importou "HREX"."JOB_HISTORY" 7.054 KB 10 linhas
. . importou "HREX"."JOBS" 6.992 KB 19 linhas
. . importou "HREX"."LOCATIONS" 8.273 KB 23 linhas
. . importou "HREX"."REGIONS" 5.476 KB 4 linhas
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/ TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/ TABLE/COMMENTPasso 2 – Inserindo novas linhas nas tabelas do HR:
-----
-- Listing 3.2: Add new Jobs, Departments, and Employees
-----
INSERT INTO hr.departments (department_id, department_name, manager_id, location_id)
VALUES (280, 'Science Fiction Writers', 108, 1500);
INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary)
VALUES ('EDITOR', 'Science Fiction Editor', 100000, 199999);
INSERT INTO hr.jobs (job_id, job_title, min_salary, max_salary)
VALUES ('WRITER-1', 'Science Fiction Writer 1', 5000, 29999);
COMMIT;
INSERT INTO hr.employees (
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
)
VALUES (
901,
'John',
'Campbell',
'jcampbell@astounding.com',
'212-555-1212',
TO_DATE('02/08/1943', 'MM/DD/YYYY'),
'EDITOR',
110000,
NULL,
100,
280
);
INSERT INTO hr.employees (
employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
)
VALUES (
902,
'Isaac',
'Asimov',
'iasimov@astounding.com',
'212-555-1313',
TO_DATE('01/01/1949', 'MM/DD/YYYY'),
'WRITER-1',
5000,
NULL,
901,
280
);
COMMIT;
-----
-- Listing 3.3: Sample transactions:
-- 1.) Update salaries and department IDs for selected employees
-----
UPDATE hr.employees
SET salary = salary * 1.05
WHERE employee_id >= 902;
COMMIT;Passo 3 – Criando tabela HR.APPLICANTS:
-----
-- Listing 3.6: Create a new table (HR.APPLICANTS)
-----
DROP TABLE hr.applicants CASCADE CONSTRAINTS;
create table HR.APPLICANTS
(
applicant_id NUMBER(5) NOT NULL,
last_name VARCHAR2(24) NOT NULL,
first_name VARCHAR2(24) NOT NULL,
middle_initial VARCHAR2(1),
gender VARCHAR2(1),
application_date DATE NOT NULL,
job_desired VARCHAR2(10) NOT NULL,
salary_desired NUMBER(10,2) NOT NULL,
added_on DATE DEFAULT SYSDATE NOT NULL,
added_by VARCHAR2(12) NOT NULL,
changed_on DATE DEFAULT SYSDATE NOT NULL,
changed_by VARCHAR2(12) NOT NULL
)
TABLESPACE EXAMPLE
PCTFREE 10
PCTUSED 40
INITRANS 1
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
-- Comments
COMMENT ON TABLE hr.applicants IS 'Controls domain of Applicants, i.e. persons who have applied for an employment opportunity';
COMMENT ON COLUMN hr.applicants.applicant_id IS 'Unique identifier for an Applicant';
COMMENT ON COLUMN hr.applicants.last_name IS 'Applicant Last Name';
COMMENT ON COLUMN hr.applicants.first_name IS 'Applicant First Name';
COMMENT ON COLUMN hr.applicants.middle_initial IS 'Applicant Middle Initial';
COMMENT ON COLUMN hr.applicants.gender IS 'Applicant Gender';
COMMENT ON COLUMN hr.applicants.application_date IS 'Application Date';
COMMENT ON COLUMN hr.applicants.job_desired IS 'Job Applied For';
COMMENT ON COLUMN hr.applicants.salary_desired IS 'Desired Salary';
COMMENT ON COLUMN hr.applicants.added_on IS 'Added On';
COMMENT ON COLUMN hr.applicants.added_by IS 'Added By';
COMMENT ON COLUMN hr.applicants.changed_on IS 'Last Updated On';
COMMENT ON COLUMN hr.applicants.changed_by IS 'Last Updated By';
-- Create indexes and constraints
CREATE UNIQUE INDEX hr.applicants_pk_idx
ON hr.applicants(applicant_id)
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
ALTER TABLE hr.applicants
ADD CONSTRAINT applicants_pk
PRIMARY KEY (applicant_id);
CREATE INDEX hr.applicants_last_name_idx
ON hr.applicants(last_name)
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
-- Create/Recreate check constraints
ALTER TABLE hr.applicants
ADD CONSTRAINT applicant_gender_ck
CHECK ((gender IN('M', 'F') or gender IS NULL));
-- Create sequence
DROP SEQUENCE hr.seq_applicants;
CREATE SEQUENCE hr.seq_applicants
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 3;
-- Create INSERT/UPDATE row-level trigger
CREATE OR REPLACE TRIGGER hr.tr_briu_applicants
BEFORE INSERT OR UPDATE ON hr.applicants
FOR EACH ROW
DECLARE
entry_id NUMBER := 0;
BEGIN
IF INSERTING THEN
BEGIN
SELECT
hr.seq_applicants.NEXTVAL
INTO entry_id
FROM DUAL;
:new.applicant_id := entry_id;
:new.added_on := SYSDATE;
:new.added_by := DBMS_STANDARD.LOGIN_USER;
:new.changed_on := SYSDATE;
:new.changed_by := DBMS_STANDARD.LOGIN_USER;
END;
ELSIF UPDATING THEN
BEGIN
:new.changed_on := SYSDATE;
:new.changed_by := DBMS_STANDARD.LOGIN_USER;
END;
END IF;
END TR_BRIU_APPLICANTS;
/
-- Create a first set of applicants
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Aniston', 'Seth', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 88017.94);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Niven', 'Ray', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 82553.39);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Brown', 'Jackson', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 70113.04);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Murdock', 'Charlton', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 70389.16);
COMMIT;
-----
-- Listing 3.7: Create a second set of applicants
-----
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Sandler', 'Joanna', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 56205.25);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Callow', 'Ramsey', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 90966.42);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Skerritt', 'Rade', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 44394.27);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('MacLachlan', 'Walter', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 97292.06);
COMMIT;
-----
-- Listing 3.11: Create a third set of applicants
-----
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Winwood', 'Chloe', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR3', 57301.55);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('King', 'Clint', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 50291.11);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Carrington', 'Joan', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR2', 91919.56);
insert into HR.APPLICANTS (LAST_NAME, FIRST_NAME, APPLICATION_DATE, JOB_DESIRED, SALARY_DESIRED) values ('Tyson', 'Hex', to_date('01-01-2005 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'IT_CNTR1', 56582.30);
COMMIT;Passo 4 – Importando tabela DEPARTMENTS:
Comparando a tabela dos esquemas HR e HREX, nota-se a ausência do departamento 280 na tabela HREX.DEPARTMENTS.
Para que as tabelas fiquem iguais, vamos substituir a tabela do esquema HREX pela tabela do HR.
Passo 5 – Importando tabela APPLICANTS:
Neste passo, vamos importar a tabela APPLICANTS do esquema HR para o HREX, através do “comando” TRUNCATE. Quando utilizamos esta opção, o Data Pump apaga todas as linhas da tabela e “popula” ela com os dados do ambiente de origem.
Passo 6 – Importando tabelas EMPLOYEES, APPLICANTS, APPLICANTS2:
Vamos importar as tabelas EMPLOYEES, APPLICANTS e APPLICANTS2 do esquema HR para o esquema HREX. As tabelas que já existem no esquema HREX não serão importadas, as outras tabelas sim. Para exemplificar, criei a tabela HR.APPLICANTS2.
Conclusão
Após vários testes, conclui que não é recomendado utilizar o parâmetro TABLE_EXISTS_ACTION=APPEND, devido às restrições de integridade que são impostas pela regra de negócio do cliente. Para este parâmetro trabalhar corretamente, observei que é preciso checar as dependências das tabelas envolvidas, caso contrário, vai ocorrer o erro ORA-0001: unique constraint violated.
Referências
Até o próximo artigo!
