--------------------------------------------------------------------------------
-- 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;
/
