DarkDBA: SQL REFRESH BY SQL PROMPT

Create User :

--
-- Please execute by login as SYS by commenting PRC_GRANT_ACL as mentioned in below
--
DROP TRIGGER SYS.TRG_GRANT;
DROP TRIGGER SYS.TRG_DDL;

--
-- To restrict DBA role to any other user
--
CREATE OR REPLACE PROCEDURE PRC_GRANT
IS
lt_priv DBMS_STANDARD.ORA_NAME_LIST_T;
lt_who DBMS_STANDARD.ORA_NAME_LIST_T;
i_priv PLS_INTEGER;
i_who PLS_INTEGER;
BEGIN
i_priv:=ORA_PRIVILEGE_LIST(lt_priv);
i_who:=ORA_GRANTEE(lt_who);
FOR i IN 1..i_priv
LOOP
FOR j in 1..i_who
LOOP
IF lt_priv(i)='DBA' THEN
RAISE_APPLICATION_ERROR(-20001,'Attempted to "'||DBMS_STANDARD.SYSEVENT||' DBA Role" which is restricted for '||ORA_LOGIN_USER||'!!!');
END IF;
END LOOP;
END LOOP;
END PRC_GRANT;
/

CREATE OR REPLACE TRIGGER TRG_GRANT
BEFORE GRANT
ON DATABASE
WHEN (ORA_LOGIN_USER NOT IN ('SYS','SYSTEM'))
BEGIN
PRC_GRANT;
END TRG_GRANT;
/

--
-- To restrict alter/create user from SQL prompt
--

CREATE OR REPLACE PROCEDURE PRC_DDL
IS
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Attempted to "'||DBMS_STANDARD.SYSEVENT||' '||ORA_DICT_OBJ_TYPE||'" which is restricted for '||ORA_LOGIN_USER||'!!!');
END PRC_DDL;
/

CREATE OR REPLACE TRIGGER TRG_DDL
BEFORE DDL ON DATABASE
WHEN (ORA_LOGIN_USER NOT IN ('SYS','SYSTEM') AND ORA_DICT_OBJ_TYPE='USER' AND ORA_SYSEVENT IN ('CREATE','ALTER'))
BEGIN
PRC_DDL;
END TRG_DDL;
/

--
-- To grant Access Control List(12c compatible)
--
CREATE OR REPLACE PROCEDURE PRC_GRANT_ACL_12C (
p_schema IN VARCHAR2,
p_mailhost IN VARCHAR2 DEFAULT NULL
) IS
i_cnt NUMBER:=0;
i NUMBER:=0;
i_port NUMBER;
TYPE t_principal IS TABLE OF DBA_HOST_ACES.PRINCIPAL%TYPE INDEX BY BINARY_INTEGER;
a_principal t_principal;
TYPE t_privilege IS TABLE OF DBA_HOST_ACES.PRIVILEGE%TYPE INDEX BY BINARY_INTEGER;
a_privilege t_privilege;
TYPE t_host IS TABLE OF DBA_HOST_ACES.HOST%TYPE INDEX BY BINARY_INTEGER;
a_host t_host;
BEGIN
SELECT COUNT(*)
INTO i_cnt
FROM DBA_NETWORK_ACL_PRIVILEGES
WHERE ACL NOT LIKE 'NETWORK_ACL%'
AND LENGTH(ACL)<>44;

IF i_cnt > 0 THEN
DBMS_OUTPUT.PUT_LINE('One or more schemas already granted with 11g ACLs. Re-granting 12c ACE privileges!');

FOR rec_acl IN (
SELECT
A.ACL,
B.HOST,
B.LOWER_PORT,
B.UPPER_PORT,
B.PRINCIPAL,
B.PRIVILEGE
FROM DBA_NETWORK_ACL_PRIVILEGES A,DBA_HOST_ACES B
WHERE A.PRINCIPAL=B.PRINCIPAL
AND UPPER(A.PRIVILEGE)=UPPER(B.PRIVILEGE)
AND (ACL NOT LIKE 'NETWORK_ACL%'
AND LENGTH(ACL)<>44)
ORDER BY B.ACE_ORDER
)
LOOP
a_principal(i) := rec_acl.PRINCIPAL;
a_privilege(i) := rec_acl.PRIVILEGE;
a_host(i) := rec_acl.HOST;
i := i + 1;
END LOOP;

FOR i IN a_principal.FIRST .. a_principal.LAST
LOOP
BEGIN
SELECT DECODE(a_host(i),'*',NULL,25) INTO i_port FROM DUAL;
DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE(
host => a_host(i),
lower_port => i_port,
upper_port => i_port,
remove_empty_acl => TRUE,
ace => xs$ace_type(
privilege_list => xs$name_list(a_privilege(i)),
principal_name => a_principal(i),
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege removed from '||a_principal(i)||' successfully!');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to remove ACL privilege to '||a_principal(i)||' with '||SQLERRM);
END;
END LOOP;

FOR i IN a_principal.FIRST .. a_principal.LAST
LOOP
BEGIN
SELECT DECODE(a_host(i),'*',NULL,25) INTO i_port FROM DUAL;
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => a_host(i),
lower_port => i_port,
upper_port => i_port,
ace => xs$ace_type(
privilege_list => xs$name_list(a_privilege(i)),
principal_name => a_principal(i),
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege granted to '||a_principal(i)||' successfully!');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to append ACL privilege to '||a_principal(i)||' with '||SQLERRM);
END;
END LOOP;
END IF;

SELECT COUNT(*)
INTO i_cnt
FROM DBA_HOST_ACES
WHERE PRIVILEGE='RESOLVE'
AND PRINCIPAL=p_schema;

IF i_cnt<>0 THEN
DBMS_OUTPUT.PUT_LINE('ACL privilege already granted to user '||p_schema);
ELSE
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(
privilege_list => xs$name_list('RESOLVE'),
principal_name => p_schema,
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege granted to user '||p_schema||' successfully!');
END IF;

IF LENGTH(TRIM(p_mailhost))>0 AND TRIM(p_mailhost)<>'0' THEN
SELECT COUNT(*)
INTO i_cnt
FROM DBA_HOST_ACES
WHERE PRINCIPAL=p_schema
AND HOST=p_mailhost;

IF i_cnt<>0 THEN
DBMS_OUTPUT.PUT_LINE('Required ACL privilege already granted to user '||p_schema||' on mail host '||p_mailhost);
ELSE
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => p_mailhost,
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(
privilege_list => xs$name_list('CONNECT'),
principal_name => p_schema,
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege granted to user '||p_schema||' successfully on mail host '||p_mailhost);
END IF;
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to append ACL privilege to '||p_schema||' with '||SQLERRM);
END PRC_GRANT_ACL_12C;
/

--
-- To grant Access Control List(11g compatible)
--
CREATE OR REPLACE PROCEDURE PRC_GRANT_ACL_11G (
p_schema IN VARCHAR2,
p_mailhost IN VARCHAR2 DEFAULT NULL
) IS
i_cnt NUMBER:=0;
i_port NUMBER;
v_acl VARCHAR2(20):='simple_acl.xml';
v_acl_comment VARCHAR2(100);
i_acl_present NUMBER:=0;
v_version VARCHAR2(3);
BEGIN
SELECT DECODE(INSTR(BANNER,'11g'),0,'12c','11g')
INTO v_version
FROM V$VERSION
WHERE BANNER LIKE 'Oracle Database%Edition Release%';

IF v_version='12c' THEN
DBMS_OUTPUT.PUT_LINE ('Execution aborted as you are trying to create Oracle 11g compatible ACL!!!');
ELSE
v_acl_comment := 'ACL for user '||p_schema;

SELECT NVL(SUM(INSTR(ACL,'/'||v_acl)),0)
INTO i_acl_present
FROM DBA_NETWORK_ACLS;

IF i_acl_present=0 THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Creating ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(v_acl,v_acl_comment,p_schema,TRUE,'resolve');
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS created..');
DBMS_OUTPUT.PUT_LINE('Assigning ACL ADDRESS..');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(v_acl,'*');
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS assigned..');
EXCEPTION WHEN OTHERS THEN
i_cnt:=1;
DBMS_OUTPUT.PUT_LINE('ACL'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
ELSE
DBMS_OUTPUT.PUT_LINE('ACL'||v_acl||' Already Present');
i_cnt:=1;
END IF;

IF i_cnt=1 THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Adding ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.add_privilege(v_acl,p_schema,TRUE,'resolve',null,null,null);
dbms_output.put_line('ACL ADDRESS Added..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Add'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END IF;

IF LENGTH(TRIM(p_mailhost))>0 AND TRIM(p_mailhost)<>'0' THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Adding ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.add_privilege(v_acl,p_schema,TRUE,'connect',null,null,null);
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS Added..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Mail Add'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('ACL assigning Mail Server '||p_mailhost);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(v_acl,p_mailhost,25);
DBMS_OUTPUT.PUT_LINE('ACL assigned..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Mail Add'||v_acl||' Assigned for Mail Server '||p_mailhost);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END IF;
COMMIT;
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error creating ACL:');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END PRC_GRANT_ACL_11G;
/

--
-- Create public synonym for PRC_GRANT_ACL as per the oracle versions
--
CREATE OR REPLACE PROCEDURE PRC_GRANT_ACL_SYNONYM
IS
v_version VARCHAR2(3);
BEGIN
SELECT DECODE(INSTR(BANNER,'11g'),0,'12c','11g')
INTO v_version
FROM V$VERSION
WHERE BANNER LIKE 'Oracle Database%Edition Release%';
BEGIN
EXECUTE IMMEDIATE ('DROP PUBLIC SYNONYM PRC_GRANT_ACL');
EXCEPTION WHEN OTHERS THEN
NULL;
END;

IF v_version='12c' THEN
EXECUTE IMMEDIATE ('DROP PROCEDURE PRC_GRANT_ACL_11G');
EXECUTE IMMEDIATE ('CREATE PUBLIC SYNONYM PRC_GRANT_ACL FOR SYS.PRC_GRANT_ACL_12C');
ELSE
EXECUTE IMMEDIATE ('DROP PROCEDURE PRC_GRANT_ACL_12C');
EXECUTE IMMEDIATE ('CREATE PUBLIC SYNONYM PRC_GRANT_ACL FOR SYS.PRC_GRANT_ACL_11G');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while executing procedure PRC_GRANT_ACL_SYNONYM with SQL Error '||SQLERRM);
END PRC_GRANT_ACL_SYNONYM;
/

SET SERVEROUTPUT ON
EXEC PRC_GRANT_ACL_SYNONYM

--
-- Package for datapump jobs
--
CREATE OR REPLACE PACKAGE PKG_DATAPUMP
IS
gv_dumpfile VARCHAR2(2000);
gv_user_not_exists CHAR(1);

PROCEDURE CLEAN_IDLE_DP_JOBS;

PROCEDURE PRC_EXPDP (
p_schema IN VARCHAR2, -- Enter schema name
p_tables IN VARCHAR2 DEFAULT NULL, -- Enter schema name
p_filesizelimit IN VARCHAR2 DEFAULT '4G' -- Enter dump file-size limit
);

PROCEDURE PRC_GET_DUMP_DETAILS (
p_dumpfile IN VARCHAR2, -- Enter dump file name
p_fromschema IN VARCHAR2 DEFAULT NULL -- Enter source schema name if you know
);

PROCEDURE PRC_IMPDP (
p_dumpfile IN VARCHAR2, -- Enter dump file name
p_toschema IN VARCHAR2, -- Enter the schema name
p_fromschema IN VARCHAR2 DEFAULT NULL, -- Enter source schema name
p_fromtbs IN VARCHAR2 DEFAULT NULL, -- Enter source tablespace name
p_imp_option IN VARCHAR2 DEFAULT NULL, -- Enter 'T' only if you want to imports from dmp file exported with tables option
p_remap_system IN VARCHAR2 DEFAULT 'N'
);

PROCEDURE PRC_REFRESH (
p_toschema IN VARCHAR2,
p_fromschema IN VARCHAR2,
p_tables IN VARCHAR2 DEFAULT NULL
);
END PKG_DATAPUMP;
/

--
-- Package body for datapump jobs
--
CREATE OR REPLACE PACKAGE BODY PKG_DATAPUMP
IS
PROCEDURE CLEAN_IDLE_DP_JOBS IS
i_handler NUMBER;
BEGIN
FOR rec_dpjob_tabs IN (
SELECT O.OWNER,OBJECT_NAME
FROM DBA_OBJECTS O,DBA_DATAPUMP_JOBS J
WHERE O.OWNER=J.OWNER_NAME
AND O.OBJECT_NAME=J.JOB_NAME
AND J.STATE='NOT RUNNING'
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '||rec_dpjob_tabs.OWNER||'.'||rec_dpjob_tabs.OBJECT_NAME||' PURGE';
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to purge data pump table '||rec_dpjob_tabs.OWNER||'.'||rec_dpjob_tabs.OBJECT_NAME||' with '||SQLERRM);
END;
END LOOP;

FOR rec_jobs IN (
SELECT OWNER_NAME,JOB_NAME
FROM DBA_DATAPUMP_JOBS
WHERE STATE='DEFINING'
)
LOOP
BEGIN
i_handler:=DBMS_DATAPUMP.ATTACH(''||rec_jobs.JOB_NAME||'',''||rec_jobs.OWNER_NAME||'');
DBMS_DATAPUMP.STOP_JOB (i_handler,1,0);
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to stop datapump job '||rec_jobs.JOB_NAME||' with '||SQLERRM);
END;
END LOOP;

FOR rec_sessions IN (
SELECT B.SID,B.SERIAL#
FROM DBA_RESUMABLE A,V$SESSION B
WHERE A.SESSION_ID=B.SID
AND NAME IN (
SELECT OWNER_NAME||'.'||JOB_NAME
FROM DBA_DATAPUMP_JOBS
WHERE STATE='DEFINING'
)
AND (B.ACTION LIKE ('SYS_IMPORT%') OR B.ACTION LIKE ('SYS_EXPORT%') OR B.ACTION LIKE ('SYS_SQL_FILE%'))
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||rec_sessions.SID||','||rec_sessions.SERIAL#||''' IMMEDIATE';
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable terminate datapump session '||rec_sessions.SID||','||rec_sessions.SERIAL#||' with '||SQLERRM);
END;
END LOOP;
END CLEAN_IDLE_DP_JOBS;

PROCEDURE PRC_EXPDP (
p_schema IN VARCHAR2,
p_tables IN VARCHAR2 DEFAULT NULL,
p_filesizelimit IN VARCHAR2 DEFAULT '4G'
) IS
i_handler NUMBER; -- Data Pump job handle
v_job_state VARCHAR2(30); -- To keep track of job state
i_ind NUMBER; -- Loop index
k_le ku$_LogEntry; -- For WIP and error messages
k_status ku$_Status; -- The status object returned by get_status
v_date VARCHAR2(14);
v_job_mode VARCHAR2(6);
v_file_name VARCHAR2(255);
v_log_name VARCHAR2(255);
v_tables VARCHAR2(2000);
BEGIN
DBMS_OUTPUT.PUT_LINE('Cleaning idle datapump jobs before exporting data!!!');
CLEAN_IDLE_DP_JOBS;

SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MI')
INTO v_date
FROM DUAL;

IF TRIM(p_tables) IS NOT NULL THEN
v_job_mode := 'TABLE';
v_file_name := UPPER(p_schema)||'_TABLES_'||v_date||'_%U.dmp';
v_log_name := UPPER(p_schema)||'_TABLES_'||v_date||'.log';
ELSE
v_job_mode := 'SCHEMA';
v_file_name := UPPER(p_schema)||'_'||v_date||'_%U.dmp';
v_log_name := UPPER(p_schema)||'_'||v_date||'.log';
END IF;

i_handler := DBMS_DATAPUMP.OPEN(
operation => 'EXPORT',
job_mode => v_job_mode,
version => 'LATEST'
);

DBMS_DATAPUMP.ADD_FILE(
handle => i_handler,
filename => v_file_name,
directory => 'DATA_PUMP_DIR',
filesize => p_filesizelimit
);

DBMS_DATAPUMP.ADD_FILE(
handle => i_handler,
filename => v_log_name,
directory => 'DATA_PUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

DBMS_DATAPUMP.METADATA_FILTER(
handle => i_handler,
name => 'SCHEMA_EXPR',
value => 'IN UPPER('''||p_schema||''')'
);

IF TRIM(p_tables) IS NOT NULL THEN
FOR rec_tables IN (
SELECT TABLE_NAME FROM (
WITH PUR AS (
SELECT TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,R_CONSTRAINT_NAME,MAX(DECODE(CONSTRAINT_TYPE,'R',1,0)) OVER(PARTITION BY TABLE_NAME) IS_R
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P','U','R')
AND OWNER=UPPER(p_schema)
),
PARENT_CHILD AS (
SELECT DISTINCT C.TABLE_NAME CHILD,P.TABLE_NAME PARENT,P.CONSTRAINT_TYPE
FROM (
SELECT * FROM PUR
WHERE CONSTRAINT_TYPE='R'
OR IS_R=0
) C
LEFT JOIN PUR P
ON C.R_CONSTRAINT_NAME=P.CONSTRAINT_NAME
AND C.TABLE_NAME!=P.TABLE_NAME
)
SELECT CHILD TABLE_NAME
FROM PARENT_CHILD
START WITH CHILD IN (
SELECT DISTINCT TRIM(REGEXP_SUBSTR(UPPER(p_tables),'[^,]+',1,LEVEL)) TABLE_NAME
FROM DUAL
CONNECT BY REGEXP_SUBSTR(UPPER(p_tables),'[^,]+',1,LEVEL) IS NOT NULL
)
CONNECT BY PARENT=PRIOR CHILD
ORDER SIBLINGS BY PARENT,CHILD
)
UNION
SELECT DISTINCT TRIM(REGEXP_SUBSTR(UPPER(p_tables),'[^,]+',1,LEVEL)) TABLE_NAME
FROM DUAL
CONNECT BY REGEXP_SUBSTR(UPPER(p_tables),'[^,]+',1,LEVEL) IS NOT NULL
)
LOOP
v_tables := v_tables||''','''||rec_tables.TABLE_NAME;
END LOOP;
v_tables :=SUBSTR(v_tables,4,LENGTH(v_tables));

DBMS_DATAPUMP.METADATA_FILTER(
handle => i_handler,
name => 'NAME_EXPR',
value => 'IN ('''||v_tables||''')'
);
END IF;

DBMS_DATAPUMP.START_JOB(handle => i_handler);
v_job_state := 'UNDEFINED';

WHILE (v_job_state != 'COMPLETED') AND (v_job_state != 'STOPPED')
LOOP
DBMS_DATAPUMP.get_status(
i_handler,
DBMS_DATAPUMP.ku$_status_job_error +
DBMS_DATAPUMP.ku$_status_job_status +
DBMS_DATAPUMP.ku$_status_wip,-1,v_job_state,k_status
);

IF (bitand(k_status.mask,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
k_le := k_status.wip;
ELSE
IF (bitand(k_status.mask,DBMS_DATAPUMP.ku$_status_job_error) != 0) then
k_le := k_status.error;
ELSE
k_le := null;
END IF;
END IF;
IF k_le IS NOT NULL THEN
i_ind := k_le.FIRST;
WHILE i_ind IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(k_le(i_ind).LogText);
i_ind := k_le.NEXT(i_ind);
END LOOP;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Job has completed');
DBMS_OUTPUT.PUT_LINE('Final job state = ' || v_job_state);
DBMS_DATAPUMP.DETACH(i_handler);
gv_dumpfile:=v_file_name;
END PRC_EXPDP;

PROCEDURE PRC_GET_DUMP_DETAILS (
p_dumpfile IN VARCHAR2,
p_fromschema IN VARCHAR2 DEFAULT NULL
) IS
i_handler NUMBER; -- Data Pump job handle
v_job_state VARCHAR2(30); -- To keep track of job state
BEGIN
i_handler := DBMS_DATAPUMP.OPEN(
operation => 'SQL_FILE',
job_mode => 'SCHEMA'
);

DBMS_DATAPUMP.ADD_FILE(
handle => i_handler,
filename => p_dumpfile,
directory => 'DATA_PUMP_DIR'
);

DBMS_DATAPUMP.ADD_FILE(
handle => i_handler,
filename => p_dumpfile||'.sql',
directory => 'DATA_PUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE
);

IF p_fromschema IS NOT NULL THEN
DBMS_DATAPUMP.METADATA_FILTER(
handle => i_handler,
name => 'SCHEMA_EXPR',
value => 'IN UPPER('''||p_fromschema||''')'
);
END IF;

DBMS_DATAPUMP.START_JOB(
handle => i_handler
);

DBMS_DATAPUMP.WAIT_FOR_JOB(
handle => i_handler,
job_state => v_job_state
);

DBMS_OUTPUT.PUT_LINE('Expdp/Impdp Job state: '||v_job_state);

DBMS_DATAPUMP.DETACH(
handle => i_handler
);

DBMS_OUTPUT.PUT_LINE('Please check file '||p_dumpfile||'.sql in dump directory for Source SCHEMA and TABLESPACE details!!!');
EXCEPTION WHEN OTHERS THEN
DBMS_DATAPUMP.DETACH(
handle => i_handler
);
DBMS_OUTPUT.PUT_LINE(CHR(13) || CHR(10)||'Please use valid dump file as parameter !!!');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END PRC_GET_DUMP_DETAILS;

PROCEDURE PRC_IMPDP (
p_dumpfile IN VARCHAR2,
p_toschema IN VARCHAR2,
p_fromschema IN VARCHAR2 DEFAULT NULL,
p_fromtbs IN VARCHAR2 DEFAULT NULL,
p_imp_option IN VARCHAR2 DEFAULT NULL,
p_remap_system IN VARCHAR2 DEFAULT 'N'
) IS
i_handler NUMBER; -- Data Pump job handle
v_job_state VARCHAR2(30); -- To keep track of job state
i_ind NUMBER; -- Loop index
k_le ku$_LogEntry; -- For WIP and error messages
k_status ku$_Status; -- The status object returned by get_status
BEGIN
DBMS_OUTPUT.PUT_LINE('Cleaning idle datapump jobs before importing data!!!');
CLEAN_IDLE_DP_JOBS;

IF p_imp_option IS NOT NULL THEN
i_handler := DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'TABLE'
);
ELSE
i_handler := DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'SCHEMA'
);
END IF;

DBMS_DATAPUMP.SET_PARAMETER(
handle => i_handler,
name => 'TABLE_EXISTS_ACTION',
value => 'REPLACE'
);

DBMS_DATAPUMP.ADD_FILE(
handle => i_handler,
filename => p_dumpfile,
directory => 'DATA_PUMP_DIR'
);

DBMS_DATAPUMP.ADD_FILE(
handle => i_handler,
filename => UPPER(p_toschema)||'_imp.log',
directory => 'DATA_PUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

IF p_fromschema IS NOT NULL THEN
DBMS_DATAPUMP.METADATA_REMAP(
handle => i_handler,
name => 'REMAP_SCHEMA',
old_value => UPPER(p_fromschema),
value => UPPER(p_toschema)
);

DBMS_DATAPUMP.METADATA_FILTER(
handle => i_handler,
name => 'SCHEMA_EXPR',
value => 'IN UPPER('''||p_fromschema||''')'
);
ELSE
DBMS_DATAPUMP.METADATA_FILTER(
handle => i_handler,
name => 'SCHEMA_EXPR',
value => 'IN UPPER('''||p_toschema||''')'
);
END IF;

FOR rec_tbs IN (
SELECT TABLESPACE_NAME FROM (
SELECT DISTINCT TRIM(REGEXP_SUBSTR(p_fromtbs,'[^,]+',1,LEVEL)) TABLESPACE_NAME
FROM DUAL
CONNECT BY REGEXP_SUBSTR(p_fromtbs,'[^,]+',1,LEVEL) IS NOT NULL
UNION
SELECT DISTINCT 'USERS' TABLESPACE_NAME FROM DUAL
UNION
SELECT DISTINCT 'SYSTEM' TABLESPACE_NAME FROM DUAL
)
WHERE TABLESPACE_NAME IS NOT NULL
)
LOOP
IF rec_tbs.TABLESPACE_NAME='SYSTEM' AND p_remap_system<>'Y' THEN
DBMS_DATAPUMP.METADATA_REMAP(
handle => i_handler,
name => 'REMAP_TABLESPACE',
old_value => 'SYSTEM',
value => 'TEMP'
);
ELSE
DBMS_DATAPUMP.METADATA_REMAP(
handle => i_handler,
name => 'REMAP_TABLESPACE',
old_value => rec_tbs.TABLESPACE_NAME,
value => 'HS_DATA'
);
END IF;
END LOOP;

DBMS_DATAPUMP.start_job(
handle => i_handler
);

v_job_state := 'UNDEFINED';

WHILE (v_job_state != 'COMPLETED') AND (v_job_state != 'STOPPED')
LOOP
DBMS_DATAPUMP.GET_STATUS(
i_handler,
DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR + DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS + DBMS_DATAPUMP.KU$_STATUS_WIP,-1,v_job_state,k_status
);

IF (BITAND(k_status.MASK,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
k_le := k_status.wip;
ELSE
IF (BITAND(k_status.MASK,DBMS_DATAPUMP.ku$_status_job_error) != 0) THEN
k_le := k_status.ERROR;
ELSE
k_le := NULL;
END IF;
END IF;
IF k_le IS NOT NULL THEN
i_ind := k_le.FIRST;
WHILE i_ind IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(k_le(i_ind).LogText);
IF INSTR(k_le(i_ind).LogText,'ORA-01918') > 0 THEN
gv_user_not_exists:='Y';
END IF;
i_ind := k_le.NEXT(i_ind);
END LOOP;
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Import job final state is ' || v_job_state);
DBMS_DATAPUMP.DETACH(i_handler);
END PRC_IMPDP;

PROCEDURE PRC_REFRESH (
p_toschema IN VARCHAR2,
p_fromschema IN VARCHAR2,
p_tables IN VARCHAR2 DEFAULT NULL
) IS
BEGIN
IF gv_dumpfile IS NULL THEN
PRC_EXPDP (
p_schema => p_fromschema,
p_tables => p_tables
);
ELSE
DBMS_OUTPUT.PUT_LINE('Skipping export step as valid dump file '||gv_dumpfile||' already exists!!!');
END IF;

WHILE (gv_dumpfile IS NOT NULL)
LOOP
BEGIN
PRC_IMPDP (
p_dumpfile => gv_dumpfile,
p_toschema => p_toschema,
p_fromschema => p_fromschema,
p_imp_option => p_tables
);
IF gv_user_not_exists='Y' THEN
DBMS_OUTPUT.PUT_LINE('Refresh unsuccessful as target schema '||UPPER(p_toschema)||' does not exists!!!');
gv_user_not_exists:=NULL;
EXIT;
ELSE
IF p_tables IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Successfully refreshed schema '||UPPER(p_toschema)||' from schema '||UPPER(p_fromschema)||'!!!');
ELSE
DBMS_OUTPUT.PUT_LINE('Successfully copied tables - '||UPPER(p_tables)||' along with their child tables to schema '||UPPER(p_toschema)||' from schema '||UPPER(p_fromschema)||'!!!');
END IF;
gv_dumpfile:=NULL;
EXIT;
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unsuccessuful with error '||SQLERRM);
EXIT;
END;
END LOOP;
END PRC_REFRESH;

PROCEDURE MONITOR_EXP_IMP (
p_job_name IN VARCHAR2 -- enter job name
) IS
i_handler NUMBER; -- Data Pump job handle
v_job_state VARCHAR2(30); -- To keep track of job state
i_ind NUMBER; -- Loop index
k_le ku$_LogEntry; -- For WIP and error messages
k_js ku$_JobStatus; -- The job status from get_status
k_jd ku$_JobDesc; -- The job description from get_status
k_status ku$_Status; -- The status object returned by get_status
e_job_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(e_job_not_exist,-31626);
BEGIN
i_handler := DBMS_DATAPUMP.ATTACH(p_job_name,'HSDBA');

DBMS_DATAPUMP.GET_STATUS(
i_handler,
DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR + DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS + DBMS_DATAPUMP.KU$_STATUS_WIP,-1,v_job_state,k_status
);
k_js := k_status.job_status;

DBMS_OUTPUT.PUT_LINE('*** Job percent done = ' || TO_CHAR(k_js.PERCENT_DONE));

-- If any work-in-progress (WIP) or Error messages were received for the job, display them.
IF (BITAND(k_status.MASK,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
k_le := k_status.wip;
ELSE
IF (BITAND(k_status.MASK,DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0) then
k_le := k_status.error;
ELSE
k_le := null;
END IF;
END IF;
IF k_le IS NOT NULL THEN
i_ind := k_le.FIRST;
WHILE i_ind IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(k_le(i_ind).LogText);
i_ind := k_le.NEXT(i_ind);
END LOOP;
END IF;
DBMS_DATAPUMP.DETACH(i_handler);
EXCEPTION
WHEN e_job_not_exist THEN
DBMS_OUTPUT.PUT_LINE ('Import/Export Job finished!!!');
END MONITOR_EXP_IMP;
END PKG_DATAPUMP;
/

--
-- Package for schema related operations
--
CREATE OR REPLACE PACKAGE PKG_SCHEMA
IS
PROCEDURE PRC_DROP_SCHEMA (
p_schema IN VARCHAR2
);

PROCEDURE PRC_CREATE_SCHEMA (
p_schema IN VARCHAR2, -- Enter schema name to be created
p_dumpfile IN VARCHAR2 DEFAULT NULL, -- Enter dump file name which needs to be imported
p_fromschema IN VARCHAR2 DEFAULT NULL, -- Enter source schema name
p_fromtbs IN VARCHAR2 DEFAULT NULL, -- Enter source tablespace name received from client
p_mailhost IN VARCHAR2 DEFAULT NULL, -- Enter if email notification required for ACL
p_mast_schema IN VARCHAR2 DEFAULT NULL, -- Enter the master schema only if you are importing slave schema
p_imp_option IN VARCHAR2 DEFAULT NULL, -- Enter 'T' only if you want to imports from dmp file exported with tables option
p_remap_system IN VARCHAR2 DEFAULT 'N' -- Enter 'Y' only if user tables are created in SYSTEM tablespace at source
);
END PKG_SCHEMA;
/

--
-- Package body for schema related operations
--
CREATE OR REPLACE PACKAGE BODY PKG_SCHEMA
IS
PROCEDURE PRC_DROP_SCHEMA (
p_schema IN VARCHAR2
) IS
i_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO i_cnt
FROM DBA_USERS
WHERE USERNAME=UPPER(p_schema);

WHILE i_cnt<>0
LOOP
FOR rec_ses IN (
SELECT SES.SID,SES.SERIAL#,SES.INST_ID
FROM GV$SESSION SES,GV$PROCESS PRC
WHERE SES.INST_ID=PRC.INST_ID
AND SES.PADDR=PRC.ADDR
AND SES.USERNAME=UPPER(p_schema)
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||rec_ses.SID||','||rec_ses.SERIAL#||',@'||rec_ses.INST_ID||''' IMMEDIATE';
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
BEGIN
EXECUTE IMMEDIATE 'DROP USER '||UPPER(p_schema)||' CASCADE';
EXCEPTION WHEN OTHERS THEN
NULL;
END;

SELECT COUNT(*)
INTO i_cnt
FROM DBA_USERS
WHERE USERNAME=UPPER(p_schema);

IF i_cnt=0 THEN
DBMS_OUTPUT.PUT_LINE('Schema '||UPPER(p_schema)||' dropped successfully!!!');
EXIT;
END IF;
END LOOP;
END PRC_DROP_SCHEMA;

PROCEDURE PRC_REORG (
p_schema IN VARCHAR2
) IS
BEGIN
FOR rec_tables IN (
SELECT TABLE_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER=p_schema
UNION
SELECT TABLE_NAME
FROM DBA_TABLES
WHERE TEMPORARY='N'
AND OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||p_schema||'.'||rec_tables.TABLE_NAME||' DEALLOCATE UNUSED KEEP 1';
DBMS_OUTPUT.PUT_LINE('Deallocated unused space for partitioned table '||rec_tables.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to deallocate unused space for partitioned table '||rec_tables.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_tables IN (
SELECT TABLE_NAME
FROM DBA_TABLES
WHERE TEMPORARY='N'
AND OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||p_schema||'.'||rec_tables.TABLE_NAME||' STORAGE (NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Modified next extents of '||rec_tables.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to modify next extents of '||rec_tables.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_tab_part IN (
SELECT DISTINCT TABLE_NAME,PARTITION_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||p_schema||'.'||rec_tab_part.TABLE_NAME||' MODIFY PARTITION '||rec_tab_part.PARTITION_NAME||' STORAGE (NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Modified next extents for partition '||rec_tab_part.PARTITION_NAME||' of '||rec_tab_part.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to modify next extents for partition '||rec_tab_part.PARTITION_NAME||' of '||rec_tab_part.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_tables IN (
SELECT DISTINCT TABLE_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||p_schema||'.'||rec_tables.TABLE_NAME||' MODIFY DEFAULT ATTRIBUTES STORAGE (INITIAL 1 NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Modified default attributes storage for partitioned table '||rec_tables.TABLE_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to modify default attributes storage for partitioned table '||rec_tables.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_tab_subpart IN (
SELECT DISTINCT TABLE_NAME,PARTITION_NAME
FROM DBA_TAB_SUBPARTITIONS
WHERE TABLE_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE '||p_schema||'.'||rec_tab_subpart.TABLE_NAME||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION '||rec_tab_subpart.PARTITION_NAME||' STORAGE (INITIAL 1 NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Modified default attributes for partition '||rec_tab_subpart.PARTITION_NAME||' of '||rec_tab_subpart.TABLE_NAME);

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to modify default attributes for partition '||rec_tab_subpart.PARTITION_NAME||' of '||rec_tab_subpart.TABLE_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_indexes IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME
FROM DBA_IND_PARTITIONS A
WHERE (INDEX_OWNER,INDEX_NAME) NOT IN (
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES B
WHERE INDEX_TYPE='LOB'
AND A.INDEX_OWNER=B.OWNER
)
AND INDEX_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_indexes.INDEX_OWNER||'.'||rec_indexes.INDEX_NAME||' DEALLOCATE UNUSED KEEP 0';
DBMS_OUTPUT.PUT_LINE('Deallocated unused space for partitioned table '||rec_indexes.INDEX_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to deallocate unused space for partitioned table '||rec_indexes.INDEX_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_ind_part IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME,PARTITION_NAME
FROM DBA_IND_PARTITIONS A
WHERE (INDEX_OWNER,INDEX_NAME) NOT IN (
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES B
WHERE INDEX_TYPE='LOB'
AND A.INDEX_OWNER=B.OWNER
)
AND INDEX_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_ind_part.INDEX_OWNER||'.'||rec_ind_part.INDEX_NAME||' MODIFY PARTITION '||rec_ind_part.PARTITION_NAME||' STORAGE (NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Modified next extents for partition '||rec_ind_part.PARTITION_NAME||' of '||rec_ind_part.INDEX_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to modify next extents for partition '||rec_ind_part.PARTITION_NAME||' of '||rec_ind_part.INDEX_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_indexes IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME
FROM DBA_IND_PARTITIONS A
WHERE (INDEX_OWNER,INDEX_NAME) NOT IN (
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES B
WHERE INDEX_TYPE='LOB'
AND A.INDEX_OWNER=B.OWNER
)
AND INDEX_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_indexes.INDEX_OWNER||'.'||rec_indexes.INDEX_NAME||' MODIFY DEFAULT ATTRIBUTES STORAGE (INITIAL 1 NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Modified default attributes storage for partitioned index '||rec_indexes.INDEX_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to modify default attributes storage for partitioned index '||rec_indexes.INDEX_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_ind_subpart IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME,PARTITION_NAME
FROM DBA_IND_SUBPARTITIONS A
WHERE (INDEX_OWNER,INDEX_NAME) NOT IN (
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES B
WHERE INDEX_TYPE='LOB'
AND A.INDEX_OWNER=B.OWNER
)
AND INDEX_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_ind_subpart.INDEX_OWNER||'.'||rec_ind_subpart.INDEX_NAME||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION '||rec_ind_subpart.PARTITION_NAME||' STORAGE (INITIAL 1 NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Modified default attributes for partition '||rec_ind_subpart.PARTITION_NAME||' of '||rec_ind_subpart.INDEX_NAME);

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to modify default attributes for partition '||rec_ind_subpart.PARTITION_NAME||' of '||rec_ind_subpart.INDEX_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_indexes IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME,PARTITION_NAME
FROM DBA_IND_PARTITIONS A
WHERE (INDEX_OWNER,INDEX_NAME) NOT IN (
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES B
WHERE INDEX_TYPE='LOB'
AND A.INDEX_OWNER=B.OWNER
)
AND COMPOSITE='NO'
AND INDEX_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_indexes.INDEX_OWNER||'.'||rec_indexes.INDEX_NAME||' REBUILD PARTITION '||rec_indexes.PARTITION_NAME||' TABLESPACE HS_DATA STORAGE(INITIAL 1 NEXT 1)';
DBMS_OUTPUT.PUT_LINE('Successfully rebuilt index '||rec_indexes.INDEX_NAME||'on partition '||rec_indexes.PARTITION_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to rebuild index '||rec_indexes.INDEX_NAME||'on partition '||rec_indexes.PARTITION_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;

FOR rec_indexes IN (
SELECT DISTINCT INDEX_OWNER,INDEX_NAME,TABLESPACE_NAME,SUBPARTITION_NAME
FROM DBA_IND_SUBPARTITIONS A
WHERE (INDEX_OWNER,INDEX_NAME) NOT IN (
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES B
WHERE INDEX_TYPE='LOB'
AND A.INDEX_OWNER=B.OWNER
)
AND INDEX_OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER INDEX '||rec_indexes.INDEX_OWNER||'.'||rec_indexes.INDEX_NAME||' REBUILD SUBPARTITION '||rec_indexes.SUBPARTITION_NAME||' TABLESPACE HS_DATA';
DBMS_OUTPUT.PUT_LINE('Successfully rebuilt index '||rec_indexes.INDEX_NAME||'on partition '||rec_indexes.SUBPARTITION_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to rebuild index '||rec_indexes.INDEX_NAME||'on partition '||rec_indexes.SUBPARTITION_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while executing procedure PRC_REORG '||' with SQL Error '||SQLERRM);
END PRC_REORG;

PROCEDURE PRC_RECREATE_DB_LINKS (
p_schema IN VARCHAR2, -- Enter slave schema name
p_mast_schema IN VARCHAR2, -- Enter matser schema name
p_db_link IN VARCHAR2 DEFAULT NULL -- Enter database link which needs to be recreated
) IS
i INTEGER;
i_user_id NUMBER;
v_sqltext VARCHAR2(4000);
BEGIN
SELECT USER_ID
INTO i_user_id
FROM DBA_USERS
WHERE USERNAME=p_schema;

FOR rec_db_links IN (
SELECT DB_LINK,HOST_NAME,INSTANCE_NAME
FROM SYS.V_$INSTANCE,DBA_DB_LINKS
WHERE OWNER=p_schema
AND DB_LINK=NVL(p_db_link,DB_LINK)
)
LOOP
BEGIN
v_sqltext := 'DROP DATABASE LINK '||rec_db_links.DB_LINK;

i:=SYS.DBMS_SYS_SQL.OPEN_CURSOR();
SYS.DBMS_SYS_SQL.PARSE_AS_USER(i,v_sqltext,DBMS_SQL.NATIVE,i_user_id);
SYS.DBMS_SYS_SQL.CLOSE_CURSOR(i);

DBMS_OUTPUT.PUT_LINE('Successfully dropped DATABASE LINK '||rec_db_links.DB_LINK);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while dropping db link '||rec_db_links.DB_LINK||' with SQL Error '||SQLERRM);
END;
BEGIN
v_sqltext := '
CREATE DATABASE LINK '||rec_db_links.DB_LINK||'
CONNECT TO '||p_mast_schema||' IDENTIFIED BY banana12
USING ''(
DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST='||rec_db_links.HOST_NAME||')(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME='||rec_db_links.INSTANCE_NAME||'))
)''';

i:=SYS.DBMS_SYS_SQL.OPEN_CURSOR();
SYS.DBMS_SYS_SQL.PARSE_AS_USER(i,v_sqltext,DBMS_SQL.NATIVE,i_user_id);
SYS.DBMS_SYS_SQL.CLOSE_CURSOR(i);

DBMS_OUTPUT.PUT_LINE('Successfully created DATABASE LINK '||rec_db_links.DB_LINK);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while creating db link '||rec_db_links.DB_LINK||' with SQL Error '||SQLERRM);
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while executing procedure PRC_RECREATE_DB_LINKS with SQL Error '||SQLERRM);
END PRC_RECREATE_DB_LINKS;

PROCEDURE PRC_RECREATE_SYNONYMS (
p_schema IN VARCHAR2, -- Enter slave schema name
p_mast_schema IN VARCHAR2 -- Enter master schema name,
) IS
BEGIN
FOR rec_synonyms IN (
SELECT OWNER,SYNONYM_NAME,TABLE_NAME,DB_LINK
FROM DBA_SYNONYMS
WHERE OWNER=p_schema
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM '||rec_synonyms.OWNER||'.'||rec_synonyms.SYNONYM_NAME;
IF rec_synonyms.DB_LINK IS NULL THEN
EXECUTE IMMEDIATE 'CREATE SYNONYM '||rec_synonyms.OWNER||'.'||rec_synonyms.SYNONYM_NAME||' FOR '||p_mast_schema||'.'||rec_synonyms.TABLE_NAME;
ELSE
EXECUTE IMMEDIATE 'CREATE SYNONYM '||rec_synonyms.OWNER||'.'||rec_synonyms.SYNONYM_NAME||' FOR '||p_mast_schema||'.'||rec_synonyms.TABLE_NAME||'@'||rec_synonyms.DB_LINK;
END IF;
DBMS_OUTPUT.PUT_LINE('Recreated SYNONYM '||rec_synonyms.SYNONYM_NAME||' of user '||rec_synonyms.OWNER);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while recreating synonym '||rec_synonyms.OWNER||'.'||rec_synonyms.SYNONYM_NAME||' with SQL Error '||SQLERRM);
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while executing procedure PRC_RECREATE_SYNONYMS with SQL Error '||SQLERRM);
END PRC_RECREATE_SYNONYMS;

PROCEDURE PRC_POPULATE_SCHEMA (
p_schema IN VARCHAR2,
p_dumpfile IN VARCHAR2 DEFAULT NULL,
p_fromschema IN VARCHAR2 DEFAULT NULL,
p_fromtbs IN VARCHAR2 DEFAULT NULL,
p_mailhost IN VARCHAR2 DEFAULT NULL,
p_mast_schema IN VARCHAR2 DEFAULT NULL,
p_imp_option IN VARCHAR2 DEFAULT NULL,
p_remap_system IN VARCHAR2 DEFAULT 'N'
) IS
BEGIN
IF p_dumpfile IS NOT NULL THEN
IF p_imp_option IS NULL THEN
PKG_DATAPUMP.PRC_IMPDP(
p_dumpfile => p_dumpfile,
p_toschema => UPPER(p_schema),
p_fromschema => UPPER(p_fromschema),
p_fromtbs => UPPER(p_fromtbs),
p_remap_system => p_remap_system
);
ELSE
IF p_fromschema IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Invalid option for table level dump file! Provide valid value for parameter p_fromschema!!!');
RETURN;
ELSE
PKG_DATAPUMP.PRC_IMPDP(
p_dumpfile => p_dumpfile,
p_toschema => UPPER(p_schema),
p_fromschema => UPPER(p_fromschema),
p_fromtbs => UPPER(p_fromtbs),
p_imp_option => p_imp_option,
p_remap_system => p_remap_system
);
END IF;
END IF;
END IF;

IF p_dumpfile IS NOT NULL THEN
PRC_REORG (
p_schema => UPPER(p_schema)
);
END IF;

PRC_GRANT_ACL (
p_schema => UPPER(p_schema),
p_mailhost => p_mailhost
);

IF p_mast_schema IS NOT NULL THEN
PRC_RECREATE_DB_LINKS (
p_schema => UPPER(p_schema),
p_mast_schema => UPPER(p_mast_schema)
);

PRC_RECREATE_SYNONYMS (
p_schema => UPPER(p_schema),
p_mast_schema => UPPER(p_mast_schema)
);
END IF;
END PRC_POPULATE_SCHEMA;

PROCEDURE PRC_CREATE_SCHEMA (
p_schema IN VARCHAR2,
p_dumpfile IN VARCHAR2 DEFAULT NULL,
p_fromschema IN VARCHAR2 DEFAULT NULL,
p_fromtbs IN VARCHAR2 DEFAULT NULL,
p_mailhost IN VARCHAR2 DEFAULT NULL,
p_mast_schema IN VARCHAR2 DEFAULT NULL,
p_imp_option IN VARCHAR2 DEFAULT NULL,
p_remap_system IN VARCHAR2 DEFAULT 'N'
) IS
i_cnt NUMBER := 0;
BEGIN
PRC_DROP_SCHEMA(p_schema);

BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_GRANT DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_DDL DISABLE';
EXCEPTION WHEN OTHERS THEN
NULL;
END;

EXECUTE IMMEDIATE '
CREATE USER '||UPPER(p_schema)||'
IDENTIFIED BY banana12
DEFAULT TABLESPACE HS_DATA
QUOTA UNLIMITED ON HS_DATA
TEMPORARY TABLESPACE TEMP';

EXECUTE IMMEDIATE 'GRANT CONNECT,RESOURCE,CREATE VIEW TO '||UPPER(p_schema);
EXECUTE IMMEDIATE 'GRANT EXECUTE ON UTL_SMTP TO '||UPPER(p_schema);
EXECUTE IMMEDIATE 'GRANT EXECUTE ON UTL_INADDR TO '||UPPER(p_schema);
EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_CRYPTO TO '||UPPER(p_schema);
EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_LOCK TO '||UPPER(p_schema);

IF p_mast_schema IS NOT NULL THEN
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON DBMS_LOCK FROM '||UPPER(p_mast_schema);
EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO '||UPPER(p_schema);
EXECUTE IMMEDIATE 'GRANT CREATE SYNONYM TO '||UPPER(p_schema);
EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO '||UPPER(p_schema);
EXECUTE IMMEDIATE 'GRANT CREATE DATABASE LINK TO '||UPPER(p_schema);
END IF;

BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_GRANT ENABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_DDL ENABLE';
EXCEPTION WHEN OTHERS THEN
NULL;
END;

DBMS_OUTPUT.PUT_LINE('Schema '||UPPER(p_schema)||' created successfully!!!');

PRC_POPULATE_SCHEMA(
p_schema,
p_dumpfile,
p_fromschema,
p_fromtbs,
p_mailhost,
p_mast_schema,
p_imp_option,
p_remap_system
);
END PRC_CREATE_SCHEMA;
END PKG_SCHEMA;
/

--
-- Grant execute privilege to above created packages to one database user
--
CREATE OR REPLACE PROCEDURE PRC_GRANT_PRIVS
IS
BEGIN
PKG_SCHEMA.PRC_DROP_SCHEMA('HSDBA');
EXECUTE IMMEDIATE 'CREATE USER HSDBA IDENTIFIED BY banana12';
EXECUTE IMMEDIATE 'GRANT DBA TO HSDBA';
EXECUTE IMMEDIATE 'GRANT CREATE DATABASE LINK TO HSDBA WITH ADMIN OPTION';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_LOCK TO HSDBA WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_CRYPTO TO HSDBA WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON PENDING_TRANS$ TO HSDBA WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON DBA_2PC_PENDING TO HSDBA WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON DBA_PENDING_TRANSACTIONS TO HSDBA WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_SYSTEM TO HSDBA WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.PKG_DATAPUMP TO HSDBA';
EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.PKG_SCHEMA TO HSDBA';
EXECUTE IMMEDIATE 'CREATE SYNONYM HSDBA.PKG_DATAPUMP FOR SYS.PKG_DATAPUMP';
EXECUTE IMMEDIATE 'CREATE SYNONYM HSDBA.PKG_SCHEMA FOR SYS.PKG_SCHEMA';
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error while executing procedure PRC_GRANT_PRIVS with SQL Error '||SQLERRM);
END PRC_GRANT_PRIVS;
/

SET SERVEROUTPUT ON
EXEC PRC_GRANT_PRIVS

CREATE OR REPLACE PROCEDURE HSDBA.CLEAN_IDLE_DP_JOBS
IS
BEGIN
PKG_DATAPUMP.CLEAN_IDLE_DP_JOBS;
END;
/

CREATE OR REPLACE PROCEDURE HSDBA.PRC_GET_DUMP_DETAILS (
p_dumpfile IN VARCHAR2,
p_fromschema IN VARCHAR2 DEFAULT NULL
) IS
BEGIN
PKG_DATAPUMP.PRC_GET_DUMP_DETAILS (
p_dumpfile
);
END;
/

CREATE OR REPLACE PROCEDURE HSDBA.PRC_EXPDP (
p_schema IN VARCHAR2,
p_tables IN VARCHAR2 DEFAULT NULL,
p_filesizelimit IN VARCHAR2 DEFAULT '4G'
) IS
BEGIN
PKG_DATAPUMP.PRC_EXPDP (
p_schema,
p_tables,
p_filesizelimit
);
END;
/

CREATE OR REPLACE PROCEDURE HSDBA.PRC_IMPDP (
p_dumpfile IN VARCHAR2,
p_toschema IN VARCHAR2,
p_fromschema IN VARCHAR2 DEFAULT NULL,
p_fromtbs IN VARCHAR2 DEFAULT NULL,
p_imp_option IN VARCHAR2 DEFAULT NULL,
p_remap_system IN VARCHAR2 DEFAULT 'N'
) IS
BEGIN
PKG_DATAPUMP.PRC_IMPDP (
p_dumpfile,
p_toschema,
p_fromschema,
p_fromtbs,
p_imp_option,
p_remap_system
);
END;
/

CREATE OR REPLACE PROCEDURE HSDBA.PRC_REFRESH (
p_toschema IN VARCHAR2,
p_fromschema IN VARCHAR2,
p_tables IN VARCHAR2 DEFAULT NULL
) IS
BEGIN
PKG_DATAPUMP.PRC_REFRESH (
p_toschema,
p_fromschema,
p_tables
);
END;
/

CREATE OR REPLACE PROCEDURE HSDBA.PRC_DROP_SCHEMA (
p_schema IN VARCHAR2
) IS
BEGIN
PKG_SCHEMA.PRC_DROP_SCHEMA (
p_schema
);
END;
/

CREATE OR REPLACE PROCEDURE HSDBA.PRC_CREATE_SCHEMA (
p_schema IN VARCHAR2,
p_dumpfile IN VARCHAR2 DEFAULT NULL,
p_fromschema IN VARCHAR2 DEFAULT NULL,
p_fromtbs IN VARCHAR2 DEFAULT NULL,
p_mailhost IN VARCHAR2 DEFAULT NULL,
p_mast_schema IN VARCHAR2 DEFAULT NULL,
p_imp_option IN VARCHAR2 DEFAULT NULL,
p_remap_system IN VARCHAR2 DEFAULT 'N'
) IS
BEGIN
PKG_SCHEMA.PRC_CREATE_SCHEMA (
p_schema,
p_dumpfile,
p_fromschema,
p_fromtbs,
p_mailhost,
p_mast_schema,
p_imp_option,
p_remap_system
);
END;
/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Data Refresh by SQL Prompt

To perform data refresh of local HotScan database by using the client provided dump files, standard

approach involves use of oracle provided utility ‘impdp’. This requires connecting the database servers

by using the operating system user ‘oracle’ which is restricted to HotScan development, testing and

support team. As part of data refresh activity, HotScan team has to manually create a schema and

granting ACL privileges to newly created schema.

To address the above access restriction to database servers, there is a requirement to perform data

refresh without connecting to the database servers and automate the entire process to avoid manual

errors.

Additional DBA tasks such as copying set of tables data from one schema to another schema (between

same database or different databases), taking export data dump backup of a given schema/tables and

dropping the database USER/SCHEMA are as part of implementation.

New stored procedures are developed to perform data refresh and process automation. By using new

stored procedures few tasks of database administrator can be performed without DBA involvement. We

can create new schema by granting necessary privileges, import/export data and grant ACL privileges

without connecting OS user ‘oracle’ to databases servers.

What are restricted?

 Login to database servers by using operating system users - root and oracle software owner

 Login to databases by database super users SYS and SYSTEM

 Creating database user/schema by manual method

 Granting DBA privilege to database user/schema

Tasks can be performed without DBA involvement and with above mentioned restrictions are

 CLEAN_IDLE_DP_JOBS – to clear all idle/hanged processes belongs to data pump jobs

 PRC_GET_DUMP_DETAILS – to get information details of a given dump file/s

 PRC_DROP_SCHEMA – to drop a given database user/schema

 PRC_CREATE_SCHEMA – to create database user/schema and import data from dump files

 PRC_IMPDP – import data from dump files to given schema

 PRC_EXPDP – to export a given schema or a table or set of tables

 PRC_REFRESH – to refresh from one schema to another or refresh a table or set of tables

To execute these procedures, one can connect to the user HSDBA of corresponding databases by using

SQLDEVELOPER or SQLPLUS. For example the requirement is to import data into database “hot12perf”

of server “hotvm1”, you are required to connect the user HSDBA of database “hot12perf” of database

server “hotvm1” to execute the commands.

1. CLEAN_IDLE_DP_JOBS

Since data pump jobs are server side executions, in case of any above procedure’s execution

terminated with error, database jobs will continue or hang indefinitely until we fix the issue

reported by data pump. New attempt to export or import will be attached to the same data pump

job in case you are executing the same procedure with same parameters. Hence it is advisable to

terminate the existing job and the session from the database before you start the new attempt of

exporting or importing. This procedure can be used to clean the data pump jobs which are idle.

Usage of CLEAN_IDLE_DP_JOBS:

Please use below command to execute the procedure CLEAN_IDLE_DP_JOBS

SET SERVEROUTPUT ON

EXEC CLEAN_IDLE_DP_JOBS

2. PRC_GET_DUMP_DETAILS

Most of the time we are having only the dump files without the information about source schema

name and source tablespaces. This procedure will help you to get the information about the names

of source schema and tablespaces. We can also get the information about the mode of the export

used to create the dump. i.e. get the information about whether client DBA has used full database

or schema level or table level exports while generating the dump files. This information is

mandatory while importing.

Providing no input or partial inputs while import will not only leads to failure of import process that

may lead to database corruption when you use user like SYS.

By executing this procedure, the corresponding sql file will be generated in data pump directory

with name “<DUMPFILE_NAME>.sql”. This sql file contains all the information like source schema,

tablespace details those are required for importing.

This procedure need to be executed by providing at least dump file name as parameter.

Usage PRC_GET_DUMP_DETAILS:

Please use below command to execute the procedure PRC_GET_DUMP_DETAILS

a. Get details for dump file “HSSUSR_201808311349.dmp”

SET SERVEROUTPUT ON

EXEC PRC_GET_DUMP_DETAILS ('HSDB814_201808311349')

b. Get details for dump file “HSSUSR_201808311349.dmp” and source schema “HSDB814”

SET SERVEROUTPUT ON

BEGIN

PRC_GET_DUMP_DETAILS (

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814'

);

END;

3. PRC_DROP_SCHEMA

Use this procedure to drop a given user by terminating all the sessions connected by this user.

This procedure need to be executed by providing schema name as parameter.

Usage PRC_DROP_SCHEMA:

To drop a schema “HOTSCAN”

SET SERVEROUTPUT ON

EXEC PRC_DROP_SCHEMA ('HOTSCAN')

4. PRC_CREATE_SCHEMA

Once you have the dump files along with the information about source schema and tablespaces, you

can create new schema and populate the schema with client provided dump file. You can use the

procedure “PRC_CREATE_SCHEMA” in several ways.

You can create a new schema on the database without importing data but with appropriate ACL

grants. You can also create new schema by populating the schema with client provided dump file

and appropriate ACL grants. Full database dump files and schema level dump files can be used as

parameters for this procedure. Table level dump files can be imported.

Usage of PRC_CREATE_SCHEMA:

Note:

 ACL privileges will be granted to newly created schema by default.

 If partitions are used, space for each partition will get shrink.

 And partition indexes will be moved to tablepsace HS_DATA.

 Corresponding log file will be created with name “<SCHEMA_NAME>_imp.log” in data pump

directory for importing dump.

Please use below command to execute the procedure “PRC_CREATE_SCHEMA”

a. Create new schema “HOTSCAN” (without importing data)

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN'

);

END;

b. Create new schema “HOTSCAN” with enabled email notification for ACL grants

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN',

p_mailhost => '158.234.96.28'

);

END;

c. Import dump file “HSDB814_201808311349.dmp” by

 creating the same schema name as in source database

 source tablespaces are “HS_DATA” and “USERS”

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HSDB814',

p_dumpfile => 'HSDB814_201808311349'

);

END;

d. Import multiple dump files generated with option “HSDB814_201808311349_%U.dmp”

 creating the same schema name as in source database

 source tablespaces are “HS_DATA” and “USERS”

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HSDB814',

p_dumpfile => 'HSDB814_201808311349_%U.dmp'

);

END;

e. Import dump file “HSDB814_201808311349.dmp” by

 creating the different schema name than the source database

 source tablespaces are “HS_DATA” and “USERS”

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814'

);

END;

f. Import dump file “HSDB814_201808311349.dmp” by

 creating the different schema name than the source database

 source tablespaces are “HS_DATA” and “USERS”

 dump file is table level (set of tables exported from a given schema)

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_imp_option => 'T'

);

END;

g. Import dump file “HSDB814_201808311349.dmp” by

 creating the different schema name than the source database

 source tablespaces are different: “HSDB_DATA”

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA'

);

END;

h. Import dump file “HSDB814_201808311349.dmp” by

 creating the different schema name than the source database

 source tablespaces are “HSDB_DATA01” and “HSDB_DATA02”

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA01,HSDB_DATA02'

);

END;

i. Import dump file “HSDB814_201808311349.dmp” by

 creating the different schema name than the source database

 source tablespace is “HSDB_DATA”

 create SLAVE schema by providing the MASTER schema name “HOTMAST”

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA',

p_mast_schema => 'HOTMAST'

);

END;

k. Import dump file “HSDB814_201808311349.dmp” by

 creating the different schema name than the source database

 source tablespaces are “HSDB_DATA01” and “HSDB_DATA02”

SET SERVEROUTPUT ON

BEGIN

PRC_CREATE_SCHEMA (

p_schema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA01,HSDB_DATA02'

);

END;

5. PRC_IMPDP

Use this procedure to import data into the existing schema by using the received/exported dump

file. Database level, schema level and table level dump files can be used for importing. For using

table level dump file for importing, you must use p_import_option as ‘T’.

For importing the data into a given schema, copy the valid dump file into the dump file directory of

the respective database and execute the procedure PRC_IMPDP.

Tables will be recreated if they are already present on the schema.

Corresponding log file for importing will be created with name “<DUMPFILE_NAME>_imp.log” in

data pump directory for importing dump.

Usage of PRC_IMPDP:

Please use below command to execute the procedure “PRC_IMPDP”

a. Import dump file “HSDB814_201808311349.dmp”

 to the same schema name as in source database

 source tablespaces are “HS_DATA” and “USERS”

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HSDB814',

p_dumpfile => 'HSDB814_201808311349'

);

END;

c. Import multiple dump files generated with option “HSDB814_201808311349_%U.dmp”

 to the same schema name as in source database

 source tablespaces are “HS_DATA” and “USERS”

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HSDB814',

p_dumpfile => 'HSDB814_201808311349_%U.dmp'

);

END;

d. Import dump file “HSDB814_201808311349.dmp”

 to the different schema name than the source database

 source tablespaces are “HS_DATA” and “USERS”

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814'

);

END;

e. Import dump file “HSDB814_201808311349.dmp”

 to the different schema name than the source database

 source tablespaces are “HS_DATA” and “USERS”

 dump file is table level (set of tables exported from a given schema)

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_imp_option => 'T'

);

END;

g. Import dump file “HSDB814_201808311349.dmp”

 to the different schema name than the source database

 source tablespaces are different: “HSDB_DATA”

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA'

);

END;

h. Import dump file “HSDB814_201808311349.dmp”

 to the different schema name than the source database

 source tablespaces are “HSDB_DATA01” and “HSDB_DATA02”

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA01,HSDB_DATA02'

);

END;

i. Import dump file “HSDB814_201808311349.dmp”

 to the different schema name than the source database

 source tablespace is “HSDB_DATA”

 create SLAVE schema by providing the MASTER schema name “HOTMAST”

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA',

p_mast_schema => 'HOTMAST'

);

END;

k. Import dump file “HSDB814_201808311349.dmp”

 to the different schema name than the source database

 source tablespaces are “HSDB_DATA01” and “HSDB_DATA02”

SET SERVEROUTPUT ON

BEGIN

PRC_IMPDP (

p_toschema => 'HOTSCAN',

p_dumpfile => 'HSDB814_201808311349',

p_fromschema => 'HSDB814',

p_fromtbs => 'HSDB_DATA01,HSDB_DATA02'

);

END;

6. PRC_EXPDP

Use this procedure to take a backup of an existing schema or exporting a set of tables of a given

schema. This will create dump file in dump file directory of the respective database.

While exporting a given table or a set of tables, all the corresponding child tables will be exported

automatically to avoid data related issue while importing.

Default upper limit for dump size will be 4GB. Multiple dump files will be created incase dump file

size crosses 4GB. Number of the files will be dependent on the size of the data to be exported.

Exported dump file format is

a. For Schema level export

“<SCHEMA_NAME>_YYYYMMDDHH24MI_%U.dmp”

b. For Table Level export

“<SCHEMA_NAME>_TABLES_YYYYMMDDHH24MI_%U.dmp”

Corresponding log file format is

c. For Schema level export

“<SCHEMA_NAME>_YYYYMMDDHH24MI_%U.log”

d. For Table level export

“<SCHEMA_NAME>_TABLES_YYYYMMDDHH24MI_%U.log”

Usage of PRC_EXPDP:

Please use below command to execute the procedure “PRC_EXPDP”

a. Exporting a given schema

SET SERVEROUTPUT ON

EXEC PRC_EXPDP('HOTSCAN')

c. Exporting table with its all child tables of a given schema

 Exporting table HSUFTRNS of schema HOTSCAN with its child tables

SET SERVEROUTPUT ON

BEGIN

PRC_EXPDP (

p_schema => 'HOTSCAN',

p_tables => 'HSUFTRNS'

);

END;

 Exporting tables HSUFTRNS, HSUFTRNS_HIST of schema HOTSCAN with their child tables

SET SERVEROUTPUT ON

BEGIN

PRC_EXPDP (

p_schema => 'HOTSCAN',

p_tables => 'HSUFTRNS,HSUFTRNS_HIST'

);

END;

d. Exporting a given schema with dump file size limitations (10GB, 100MB, 100KB respectively)

SET SERVEROUTPUT ON

BEGIN

PRC_EXPDP (

p_schema => 'HOTSCAN',

p_filesizelimit => '10G'

);

END;

SET SERVEROUTPUT ON

BEGIN

PRC_EXPDP (

p_schema => 'HOTSCAN',

p_filesizelimit => '100M'

);

END;

SET SERVEROUTPUT ON

BEGIN

PRC_EXPDP (

p_schema => 'HOTSCAN',

p_filesizelimit => '100K'

);

END;

8. PRC_REFRESH

Use this procedure to refresh an existing schema or a table or a set of tables from another schema

or a table or set of tables belongs to a given database. Refresh is possible only between two

schemas of a given database and refresh is not possible between schemas belongs to two different

databases.

While refreshing a given table or a set of tables, all the corresponding child tables will be refreshed

automatically to avoid data related issue while refreshing the data.

This process internally calls procedures PRC_EXPDP and PRC_IMPDP to perform automatic export

and import. Please drop the dump files and log files after successful of refresh activity.

Dump file format is

a. For Schema level refresh-export

“<FROM_SCHEMA_NAME>_YYYYMMDDHH24MI_%U.dmp”

b. For Table Level refresh-export

“<FROM_SCHEMA_NAME>_TABLES_YYYYMMDDHH24MI_%U.dmp”

Corresponding log file format is

a. For Schema level refresh-export

“<FROM_SCHEMA_NAME>_YYYYMMDDHH24MI_%U.log”

b. For Table level refresh-export

“<FROM_SCHEMA_NAME>_TABLES_YYYYMMDDHH24MI_%U.log”

c. For refresh-import

“<TO_SCHEMA_NAME>_imp.log”

Usage of PRC_REFRESH:

Please use below command to execute the procedure “PRC_REFRESH”

a. Perform complete refresh from one schema to another

SET SERVEROUTPUT ON

BEGIN

PRC_REFRESH (

p_toschema => 'HTSNEW',

p_fromschema => 'HTSOLD'

);

END;

b. Perform a table refresh from one schema to another

SET SERVEROUTPUT ON

BEGIN

PRC_REFRESH (

p_ toschema => 'HTSNEW',

p_ fromschema => 'HTSOLD',

p_tables => 'HSUFTRNS'

);

END;

c. Perform a set of tables refresh from one schema to another

SET SERVEROUTPUT ON

BEGIN

PRC_REFRESH (

p_ toschema => 'HTSNEW',

p_ fromschema => 'HTSOLD',

p_tables => 'HSUFTRNS,HSUFTRNS_HIST'

);

END;

Monitoring the progress of impdp and expdp jobs

1. View the logfiles which are generated in dump file directory

2. Query the data dictionary views by login as HSDBA

 DBA_DATAPUMP_JOBS  STATE should be EXECUTING

SELECT * FROM DBA_DATAPUMP_JOBS;

 DBA_DATAPUMP_JOBS  No errors should be reported in ERROR_MSG

SELECT SESSION_ID,NAME,SQL_TEXT,ERROR_MSG FROM DBA_RESUMABLE;

DarkDBA

Labels

Thursday, 24 October 2019

SQL REFRESH BY SQL PROMPT

No comments:

Post a Comment

Wikipedia

Followers