This 90-minute assessment will allow you to show-off your PL/SQL development, database administration, and C# or Angular skills. You can complete some steps ahead of time (as designated below), but you will complete the majority over a screen-sharing Zoom call. The assessment is open book/internet.
1. Create an Oracle database capable of recovering from the loss of a single file.
a. You must complete this prior to the assessment meeting.
2. Using PL/SQL, create a table [redacted] and include a reference to the date in the name.
3. Using PL/SQL, add a trigger to each table that will [redacted].
a. You may use the same sequence across tables.
4. Using PL/SQL, create a function that [redacted] and returns a custom data type.
5. Using PL/SQL, call your function for each table created previously, and [redacted].
6. Using PL/SQL, create a new table, and [redacted].
7. Identify and delete the data file that contains your new combined table.
8. Recover your database, so you can see the new combined table again.
The tasks below are optional.
1. Using C# or Angular (2.0+), create an application that can display the contents of a table.
a. You must complete this prior to the assessment meeting.
2. Modify your application to use the combined table from the steps above.
3. Add another data field to your display using a modified value of one of the existing data fields.
(1).Full redaction.对列中的数据全部redact,number类型的列将全部返回为0,character类型的列将全部返回为空格,日期类型返回为2001-01-01。
(2).Partial redaction. 对列中的一部分数据进行redact,比如,可以对社会保险号的前几位设置返回为*,剩下的几位保持不变。只有列中的数据为固定宽度时才能使用这种方式,如果列中存储的是email地址,每个email地址的宽度不尽相同,此时要使用Regular expressions。
(3).Regular expressions. 对不同长度的内容加密,例如e-mail,仅仅对字符类型有效。
(4).Random redaction. 随机乱码加密
(5).No redaction.测试加密的内部运行机制,并不会真正加密。
--创建表空间:
create tablespace SCOTT_DATA
datafile '/oradata/data/OTTER/scott_data.dbf'
size 100m autoextend on next 10m;
create user scott identified by scott
default tablespace scott_DATA
temporary tablespace TEMP
quota unlimited on SCOTT_DATA;
grant connect, resource, create session to SCOTT;
create table employee(
id number,
name varchar2(20),
salary number,
onboard_date date,
mobile varchar2(20)
);
insert into employee values(1,'Tom Cruise',60000,to_date('01-11-2001','dd-mm-yyyy'),'710-1984-8763');
insert into employee values(2,'Mary Elizabeth',90000,to_date('08-10-1996','dd-mm-yyyy'),'540-2864-7345');
--
create or replace procedure create_table(
v_table_name in VARCHAR2,
v_ret out number --0 succes 1 fail
)
as
v_temp_table_name VARCHAR2;
begin
v_ret := 0;
create table v_table_name (name varchar2(20), created_date date);
end;
--full redaction salary(number)
begin dbms_redact.add_policy(
object_schema=>'scott',
object_name=>'employee',
policy_name=>'full_redaction',
column_name=>'salary',
function_type=>dbms_redact.full,
enable=>true,
expression=>'1=1');
end;
/
begin dbms_redact.alter_policy(
object_schema=>'scott',
object_name=>'employee',
policy_name=>'full_redaction',
column_name=>'name',
action=>dbms_redact.add_column,
function_type=>dbms_redact.full,
expression=>'1=1');
end;
/
begin dbms_redact.alter_policy(
object_schema=>'scott',
object_name=>'employee',
policy_name=>'full_redaction',
column_name=>'name',
action=>dbms_redact.drop_column);
end;
/
SQL> begin dbms_redact.alter_policy(
object_schema=>'scott',
object_name=>'employee',
policy_name=>'full_redaction',
column_name=>'onboard_date',
action=>dbms_redact.add_column,
function_type=>dbms_redact.full,
expression=>'1=1');
end;
/
begin dbms_redact.alter_policy(
object_schema=>'scott',
object_name=>'employee',
policy_name=>'full_redaction',
column_name=>'mobile',
action=>dbms_redact.add_column,
function_type=>dbms_redact.partial,
expression=>'1=1',
function_parameters=>'VVVFVVVVFVVVV,VVV-VVVV-VVVV,*,1,8');
end;
/
create restore point before_delete_table guarantee flashback database;
col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from v$restore_point;
RMAN> list restore point all;
flashback database to restore point before_delete_table;
ORA-38757: Database must be mounted and not open to FLASHBACK.
alter database open RESETLOGS;
drop restore point xxxx;
rman target /
RMAN> run
{
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
BACKUP DATABASE FORMAT '/oradata/bck/db_%d_%T_%U.bak' TAG before_delete_file;
BACKUP CURRENT CONTROLFILE FORMAT '/oradata/bck/cntrl_%s_%p_%s.bak';
BACKUP ARCHIVELOG ALL FORMAT '/oradata/bck/arc_%t_%s.bak' delete all input;
BACKUP SPFILE FORMAT '/oradata/bck/spf_%d_%U.bak';
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
}
startup mount;
RMAN>restore tablespace SCOTT_DATA;
RMAN>recover database;
Or: restore datafile 5;
alter database open;
col FILE_NAME for a59
select file_id, file_name from dba_data_files;
FILE_ID FILE_NAME
---------- -----------------------------------------------------------
1 /oradata/data/OTTER/system01.dbf
3 /oradata/data/OTTER/sysaux01.dbf
4 /oradata/data/OTTER/undotbs01.dbf
7 /oradata/data/OTTER/users01.dbf
5 /oradata/data/OTTER/scott_data.dbf
create or replace trigger addStafffCheck
before insert on xgj_test
declare
-- local variables here
begin
if to_char(sysdate, 'day') in ('SAT', 'SUN') or
to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
--forbid insert
raise_application_error(-20001,'非工作时间禁止插入数据');
end if;
end addStafffCheck;
create or replace function Fn_First
return varchar2 --不需要长度
is
Result varchar2(100); --declare,长度定义。
begin
NULL;
return(Result);
end Fn_First;
CREATE OR REPLACE FUNCTION SF_PUB_STRMINUSINT
(p_beg in varchar2,
p_end in varchar2
)
return int
/**
AUTH
FUNC 两个随机号相减得数
**/
AS
v_ret int;
vs_geb varchar2(30);
vs_str varchar2(30);
vn_beg int;
vs_dne varchar2(30);
vn_end int;
begin
--反转
select reverse(p_beg) into vs_geb from dual;
--正则
select regexp_substr(vs_geb,'[0-9]+',1,1) into vs_str from dual;
--取得随机码
select to_number(reverse(vs_str)) into vn_beg from dual;
--止号
select reverse(p_end) into vs_dne from dual;
--正则
select regexp_substr(vs_dne,'[0-9]+',1,1) into vs_str from dual;
--取得随机码
select to_number(reverse(vs_str)) into vn_end from dual;
v_ret:=vn_end-vn_beg+1;
return v_ret;
end SF_PUB_STRMINUSINT;