-------------------------------------------------------------------------------- -- logicalschema.sql -- 1. Set initial preferences for the logicalschema instance -- 2.Install clean-up procedure for logical schemas -- 3.Install init and done procedure for logical schema -------------------------------------------------------------------------------- -- Input parameter: -- N/A -------------------------------------------------------------------------------- -- Usage: -- Save the script as sql file logicalschema.sql -- C:\> sqlplus dbauser/password[@TNS_name] -- SQL> @[<Path\>]logicalschema.sql -------------------------------------------------------------------------------- -- Notice: -------------------------------------------------------------------------------- -- my company -- Author: eureka -------------------------------------------------------------------------------- -- History: -- Who When What -- --- -------- ---------------------------------------------------------------- -- eureka 10.23.2013 -- add standard header -------------------------------------------------------------------------------- WHENEVER SQLERROR EXIT declare tbname varchar(255); begin SELECT tablespace_name into tbname FROM dba_tablespaces where tablespace_name=upper('&mytablespace'); if ( tbname is null) then raise_application_error (-20000,'the space is not in the database:' || chr(10)|| dbms_utility.format_error_stack); end if; exception when no_data_found then raise_application_error (-20001,'Error in select tablespace:' || chr(10)|| dbms_utility.format_error_stack); when others then raise_application_error (-20002,'Error in select tablespace:' || chr(10)|| dbms_utility.format_error_stack); end; / declare exportflag varchar(255); dcname varchar(255); begin exportflag:='&myexportflag'; if ( exportflag='TRUE' ) then SELECT directory_name into dcname FROM dba_directories where directory_name = upper('&myOracleDirectory'); end if; exception when no_data_found then raise_application_error (-20001,'the directory name is not in the database:' || chr(10)|| dbms_utility.format_error_stack); when others then raise_application_error (-20002,'the directory name is not in the database:' || chr(10)|| dbms_utility.format_error_stack); end; / declare l_dml VARCHAR2(32767); begin l_dml :='CREATE USER &myschemaowner IDENTIFIED BY &mypass DEFAULT TABLESPACE &mytablespace QUOTA UNLIMITED ON &mytablespace'; EXECUTE IMMEDIATE l_dml; end; / declare e_exit_table exception; pragma exception_init(e_exit_table, -00955); l_dml VARCHAR2(32767); begin l_dml := 'create table &myschemaowner.LookUpTable (schemaname varchar(100), sourcename varchar(100), tablename varchar(30), createtime date, primary key(schemaname,sourcename))'; EXECUTE IMMEDIATE l_dml; exception when e_exit_table then null; when others then raise_application_error (-20000,'Error in create table:' || chr(10)|| dbms_utility.format_error_stack); end; / declare e_exit_sequence exception; pragma exception_init(e_exit_sequence, -00955); l_dml VARCHAR2(32767); begin l_dml:= 'CREATE SEQUENCE &myschemaowner.SEQMYCACHE MINVALUE 1 MAXVALUE 9999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER CYCLE'; EXECUTE IMMEDIATE l_dml; exception when e_exit_sequence then null; when others then raise_application_error (-20000,'Error in create sequence:' || chr(10)|| dbms_utility.format_error_stack); end; / declare l_dml VARCHAR2(32767); begin l_dml :='grant create session to &myschemaowner'; EXECUTE IMMEDIATE l_dml; l_dml :='grant create table to &myschemaowner'; EXECUTE IMMEDIATE l_dml; l_dml :='grant connect, resource to &myschemaowner'; EXECUTE IMMEDIATE l_dml; l_dml :='grant select any dictionary to &myschemaowner'; EXECUTE IMMEDIATE l_dml; l_dml := 'create or replace type &myschemaowner.ints as table of number(10,0)'; EXECUTE IMMEDIATE l_dml; end; / declare exportflag varchar(255); dcname varchar(255); begin exportflag:='&myexportflag'; if ( exportflag='TRUE' ) then EXECUTE IMMEDIATE 'GRANT read, write ON DIRECTORY &myOracleDirectory TO &myschemaowner'; end if; exception when no_data_found then raise_application_error (-20001,'the directory name is not in the database:' || chr(10)|| dbms_utility.format_error_stack); when others then raise_application_error (-20002,'the directory name is not in the database:' || chr(10)|| dbms_utility.format_error_stack); end; / create or replace PACKAGE &myschemaowner.my$imp_util IS PROCEDURE cleanup(sch_prefix_in IN VARCHAR2 := 'MY$', sch_name_in IN VARCHAR2, lo_threshold_in IN INTEGER, hi_threshold_in IN INTEGER, backup_in IN BOOLEAN, cleanup_in IN BOOLEAN, dir_in IN VARCHAR2, tsmaxsize IN INTEGER, status_out OUT VARCHAR2); PROCEDURE expdp_schema(sch_in IN VARCHAR2, dir_in IN VARCHAR2, res_out OUT VARCHAR2, success_out OUT BOOLEAN); PROCEDURE impdp_schema(dmpfile_in IN VARCHAR2, dir_in IN VARCHAR2, status_out OUT VARCHAR2); END my$imp_util; / create or replace PACKAGE BODY &myschemaowner.my$imp_util IS TYPE bytes_per_schema_rt IS record( v_user LookUpTable.schemaname%TYPE ,v_bytes user_segments.bytes%TYPE ); TYPE bps_tt IS TABLE OF bytes_per_schema_rt INDEX BY PLS_INTEGER; -------------------------------------------------- -- Private constant declarations -------------------------------------------------- -------------------------------------------------- -- Private function and procedure implementations -------------------------------------------------- PROCEDURE to_scr (msg_in IN VARCHAR2) IS BEGIN dbms_output.put_line(msg_in); END; PROCEDURE stripline IS BEGIN to_scr (lpad('=', 50, '=')); END; FUNCTION bytes_used RETURN INTEGER IS retval INTEGER; BEGIN SELECT SUM(bytes) INTO retval FROM user_segments; RETURN retval; END; FUNCTION pct_used (bytes_in IN INTEGER, c_max_bytes IN INTEGER) RETURN INTEGER IS BEGIN RETURN round((bytes_in / c_max_bytes) * 100); END; PROCEDURE load_bps_tab ( usr_like_in IN VARCHAR2 ,bps_tab_out OUT NOCOPY bps_tt ) IS BEGIN select lp.schemaname, SUM(nvl(us.bytes, 0)) BULK COLLECT INTO bps_tab_out from user_segments us, LookUpTable lp where us.segment_name = lp.tablename group by lp.schemaname order by min(lp.createtime); END; PROCEDURE drop_table (table_in IN VARCHAR2) IS table_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT(table_not_exist, -00942); BEGIN EXECUTE IMMEDIATE 'DROP table '||table_in||' cascade constraints'; EXCEPTION when table_not_exist then return; WHEN OTHERS THEN raise_application_error (-20000, 'Error in DROP_TABLE:' || chr(10) || dbms_utility.format_error_stack); END; PROCEDURE drop_user (user_in IN VARCHAR2) IS BEGIN for mytablename in (select table_name from user_tables ut, LookUpTable lp where ut.table_name = lp.tablename and lp.schemaname =user_in and ut.table_name != 'LOOKUPTABLE') loop drop_table(mytablename.table_name); end loop; EXCEPTION WHEN OTHERS THEN raise_application_error (-20000, 'Error in DROP_USER:' || chr(10) || dbms_utility.format_error_stack); END; FUNCTION remove_last_cr (txt_in IN VARCHAR2) RETURN VARCHAR2 IS l_txt VARCHAR2(32767) := txt_in; BEGIN WHILE substr(l_txt, -1, 1) = chr(10) LOOP l_txt := substr(l_txt, 1, length(l_txt) - 1); END LOOP; RETURN l_txt; END; PROCEDURE cleanup_internal ( schema_like_in IN VARCHAR2 ,lo_threshold_in IN INTEGER ,hi_threshold_in IN INTEGER ,savefile_in IN BOOLEAN ,c_max_bytes IN INTEGER ,dir_in IN VARCHAR2 ,int_status_out OUT VARCHAR2 ) IS l_usr_like VARCHAR2(30) := upper(schema_like_in); l_min_pct INTEGER := lo_threshold_in; l_max_pct INTEGER := hi_threshold_in; l_used_bytes INTEGER := bytes_used; l_used_bytes_start INTEGER; l_bps_tab bps_tt; l_idx PLS_INTEGER; l_success BOOLEAN := TRUE; l_status VARCHAR2(32767); BEGIN l_used_bytes_start := l_used_bytes; IF l_max_pct <= pct_used (l_used_bytes,c_max_bytes) THEN load_bps_tab (l_usr_like, l_bps_tab); l_idx := l_bps_tab.FIRST; LOOP EXIT WHEN(l_min_pct >= pct_used (l_used_bytes,c_max_bytes)) OR (l_idx IS NULL); l_used_bytes := l_used_bytes - l_bps_tab(l_idx).v_bytes; IF savefile_in THEN expdp_schema (sch_in => l_bps_tab(l_idx).v_user ,dir_in => dir_in ,res_out => l_status ,success_out => l_success); END IF; if l_success then drop_user (l_bps_tab(l_idx).v_user); else int_status_out := l_status; return; end if; l_idx := l_bps_tab.NEXT(l_idx); END LOOP; END IF; int_status_out := 'L3Cache storage used[MB]/free[MB]/cleaned[MB]: ' || round(l_used_bytes / 1024 / 1024) || '/' || round((c_max_bytes - l_used_bytes) / 1024 / 1024) || '/' || round((l_used_bytes_start - l_used_bytes) / 1024 / 1024); EXCEPTION WHEN OTHERS THEN int_status_out := 'Exception in CLEANUP_INTERNAL:' || chr(10) || dbms_utility.format_error_stack; END; -------------------------------------------------- -- Public function and procedure implementations -------------------------------------------------- PROCEDURE cleanup ( sch_prefix_in IN VARCHAR2 := 'MY$' ,sch_name_in IN VARCHAR2 ,lo_threshold_in IN INTEGER ,hi_threshold_in IN INTEGER ,backup_in IN BOOLEAN ,cleanup_in IN BOOLEAN ,dir_in IN VARCHAR2 ,tsmaxsize IN INTEGER ,status_out OUT VARCHAR2 ) IS l_usr_like VARCHAR2(30) := CASE substr(sch_prefix_in, -1, 1) WHEN '%' THEN upper(sch_prefix_in) ELSE upper(sch_prefix_in) || '%' END; l_status VARCHAR2(32767); BEGIN IF cleanup_in THEN cleanup_internal ( schema_like_in => l_usr_like ,lo_threshold_in => lo_threshold_in ,hi_threshold_in => hi_threshold_in ,savefile_in => backup_in ,c_max_bytes => tsmaxsize ,dir_in => dir_in ,int_status_out => l_status ); status_out := status_out || chr(10) || remove_last_cr (l_status); END IF; END; PROCEDURE expdp_schema(sch_in IN VARCHAR2, dir_in IN VARCHAR2, res_out OUT VARCHAR2, success_out OUT BOOLEAN) is l_dt_stmp VARCHAR2(30) := to_char(SYSDATE, 'YYMMDDHH24MISS'); l_exp_job VARCHAR2(30) := 'my$EXPDP_JOB_' || l_dt_stmp; l_dmp_file VARCHAR2(100) := upper(sch_in) || '_' || l_dt_stmp || '.DMP'; l_log_file VARCHAR2(100) := upper(sch_in) || '_' || l_dt_stmp || '_EXP.LOG'; l_handle NUMBER; l_job_state VARCHAR2(30); l_log_entry ku$_logentry; l_status_entry ku$_jobstatus; l_status ku$_status; l_idx PLS_INTEGER; l_file VARCHAR2(100); l_table_list VARCHAR2(2000); l_subquery VARCHAR2(200); BEGIN for mytablename in (select table_name from user_tables ut, LookUpTable lp where ut.table_name = lp.tablename and lp.schemaname = sch_in and ut.table_name != 'LOOKUPTABLE') loop if l_table_list is null then l_table_list := '''' || mytablename.table_name; else l_table_list := l_table_list || ''',''' || mytablename.table_name; end if; end loop; if l_table_list is not null then l_table_list := l_table_list || ''',''LOOKUPTABLE'''; l_subquery := 'WHERE SCHEMANAME=''' || sch_in || ''''; l_handle := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', job_name => l_exp_job); dbms_datapump.add_file(handle => l_handle, filename => l_dmp_file, directory => dir_in); dbms_datapump.add_file(handle => l_handle, filename => l_log_file, filetype => dbms_datapump.ku$_file_type_log_file, directory => dir_in); dbms_datapump.metadata_filter(handle => l_handle, NAME => 'NAME_LIST', VALUE => l_table_list, object_type => 'TABLE'); DBMS_DATAPUMP.DATA_FILTER(handle => l_handle, NAME => 'SUBQUERY', value => l_subquery, table_name => 'LOOKUPTABLE'); IF (bitand(l_status.mask, dbms_datapump.ku$_status_job_status) != 0) THEN l_status_entry := l_status.job_status; IF l_status_entry IS NOT NULL THEN l_idx := l_status_entry.files.FIRST; IF l_idx IS NOT NULL THEN l_file := l_status_entry.files(l_idx).file_name; END IF; END IF; END IF; dbms_datapump.start_job(l_handle); dbms_datapump.wait_for_job(handle => l_handle, job_state => l_job_state); dbms_datapump.detach(l_handle); IF l_job_state = 'COMPLETED' THEN success_out := TRUE; res_out := 'data export successfully completed' || chr(10); ELSE res_out := 'data export: final job state = ' || l_job_state || chr(10); END IF; IF l_file IS NOT NULL THEN res_out := res_out || 'Dump-File: ' || l_file; END IF; res_out := remove_last_cr(res_out); else res_out := 'No table found for the logical schema!' || chr(10); end if; EXCEPTION WHEN OTHERS THEN success_out := FALSE; res_out := 'Exception in data export:' || chr(10); l_status := dbms_datapump.get_status(l_handle, dbms_datapump.ku$_status_job_error); IF (bitand(l_status.mask, dbms_datapump.ku$_status_job_error) != 0) THEN l_log_entry := l_status.error; IF l_log_entry IS NOT NULL THEN l_idx := l_log_entry.FIRST; WHILE l_idx IS NOT NULL LOOP res_out := res_out || l_log_entry(l_idx).logtext; IF l_idx != l_log_entry.LAST THEN res_out := res_out || chr(10); END IF; l_idx := l_log_entry.NEXT(l_idx); END LOOP; END IF; ELSE res_out := res_out || dbms_utility.format_error_stack; END IF; dbms_datapump.detach(l_handle); res_out := remove_last_cr(res_out); end expdp_schema; PROCEDURE impdp_schema(dmpfile_in IN VARCHAR2, dir_in IN VARCHAR2, status_out OUT VARCHAR2) IS l_dt_stmp VARCHAR2(30) := to_char(SYSDATE, 'YYMMDDHH24MISS'); l_imp_job VARCHAR2(30) := 'MY$IMPDP_JOB_' || l_dt_stmp; l_log_file VARCHAR2(100) := upper(substr(dmpfile_in, 1, length(dmpfile_in) - 4)) || '_IMP.LOG'; l_log_lookup_file VARCHAR2(100) := upper(substr(dmpfile_in, 1, length(dmpfile_in) - 4)) || '_LOOKUP_IMP.LOG'; l_handle NUMBER; l_job_state VARCHAR2(30); l_log_entry ku$_logentry; l_status ku$_status; l_idx PLS_INTEGER; BEGIN --import business data l_handle := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE', job_name => l_imp_job); dbms_datapump.add_file(handle => l_handle, filename => dmpfile_in, directory => dir_in); dbms_datapump.add_file(handle => l_handle, filename => l_log_file, filetype => dbms_datapump.ku$_file_type_log_file, directory => dir_in); dbms_datapump.start_job(l_handle); dbms_datapump.wait_for_job(handle => l_handle, job_state => l_job_state); dbms_datapump.detach(l_handle); --import lookuptable records l_handle := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE', job_name => l_imp_job || '_lookup'); DBMS_DATAPUMP.metadata_filter(handle => l_handle, name => 'NAME_EXPR', VALUE => 'IN(''LOOKUPTABLE'')'); dbms_datapump.add_file(handle => l_handle, filename => dmpfile_in, directory => dir_in); dbms_datapump.add_file(handle => l_handle, filename => l_log_lookup_file, filetype => dbms_datapump.ku$_file_type_log_file, directory => dir_in); dbms_datapump.set_parameter(handle => l_handle, name => 'TABLE_EXISTS_ACTION', value => 'APPEND'); dbms_datapump.start_job(l_handle); dbms_datapump.wait_for_job(handle => l_handle, job_state => l_job_state); dbms_datapump.detach(l_handle); IF l_job_state = 'COMPLETED' THEN status_out := 'data import successfully completed'; ELSE status_out := 'data import: final job state = ' || l_job_state; END IF; EXCEPTION WHEN OTHERS THEN status_out := 'Exception in data import:' || chr(10); l_status := dbms_datapump.get_status(l_handle, dbms_datapump.ku$_status_job_error); IF (bitand(l_status.mask, dbms_datapump.ku$_status_job_error) != 0) THEN l_log_entry := l_status.error; IF l_log_entry IS NOT NULL THEN l_idx := l_log_entry.FIRST; WHILE l_idx IS NOT NULL LOOP status_out := status_out || l_log_entry(l_idx).logtext; IF l_idx != l_log_entry.LAST THEN status_out := status_out || chr(10); END IF; l_idx := l_log_entry.NEXT(l_idx); END LOOP; END IF; ELSE status_out := status_out || dbms_utility.format_error_stack; END IF; dbms_datapump.detach(l_handle); status_out := remove_last_cr(status_out); END impdp_schema; END my$imp_util; / CREATE OR REPLACE PROCEDURE &myschemaowner.my$initschema ( user_in IN VARCHAR2 ,res_out OUT VARCHAR2 ) IS l_status VARCHAR2(32767); BEGIN res_out := res_out || l_status; END; / create or replace PROCEDURE &myschemaowner.my$doneschema ( user_in IN VARCHAR2, lo_threshold_in IN INTEGER, hi_threshold_in IN INTEGER, auto_cleanup IN BOOLEAN := TRUE, export IN BOOLEAN := FALSE, file_path IN VARCHAR2, tablespacemaxsize IN INTEGER, res_out OUT VARCHAR2 ) IS l_dml VARCHAR2(32767); BEGIN my$imp_util.cleanup( sch_name_in => user_in ,lo_threshold_in => lo_threshold_in ,hi_threshold_in => hi_threshold_in ,backup_in => export ,cleanup_in => auto_cleanup ,dir_in => file_path ,tsmaxsize => tablespacemaxsize ,status_out => res_out ); l_dml := 'delete from LookUpTable where tablename not in ( select table_name from user_tables )'; execute immediate l_dml; commit; EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; END; /