Monday, December 17, 2007

A Testcase for Transportable Tablespace

To be familiar myself with the transportable tablespace feature, I conducted this test:

Test Steps

1. Create a tablespace called TEST_TTS for test

connect / as sysdba
create tablespace test_tts datafile='e:\temp\test_tts01.dbf' size 2M;
connect scott/tiger
create table t tablespace test_tts as
select rownum id, a.* from all_objects a where rownum <100;


2. Check if the tablespace is self-contained (optional)

sys@TEST10GR> execute dbms_tts.transport_set_check('test_tts',true);

PL/SQL procedure successfully completed.

3. Make the tablespace READ ONLY:

SQL>  alter tablespace test_tts read only;

scott@TEST10GR> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
~
TEST_TTS                       READ ONLY

4. Export the tablespace. At the source host, from the OS prompt, issue:

#> exp tablespaces=test_tts transport_tablespace=y file=exp_test_tts.dmp

Export: Release 10.2.0.1.0 - Production on Mon Dec 17 13:38:45 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Username: sys/oracle as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TEST_TTS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              T
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings


Notes: Must log in as SYSDBA to perform the exp

5. Copy the files exp_test_tts.dmp and e:\temp\test_tts01.dbf to the directory e:\Denis_File\temp at the target host

6. Make the tablespace read write at source database

SQL> alter tablespace test_tts read write;

7. At the target host, plug the tablespace into the target database.
From the OS command prompt, issue:

cd E:\Denis_Files\temp
E:\Denis_Files\temp>ls -lh  *.DBF *.dmp
-rw-r--r-- 1 Denis Sun None 2.1M Dec 17 09:28 TEST_TTS01.DBF
-rw-r--r-- 1 Denis Sun None 4.0K Dec 17 09:40 exp_test_tts.dmp

E:\Denis_Files\temp>imp tablespaces=test_tts transport_tablespace=y file=exp_tes
t_tts.dmp datafiles='E:\Denis_Files\temp\TEST_TTS01.DBF' tts_owners=scott

Import: Release 10.2.0.1.0 - Production on Mon Dec 17 13:45:42 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: sys/oracle as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table                            "T"
. importing SYS's objects into SYS
Import terminated successfully without warnings.


Notes: we can specify fromuser, touser to map differect schemas.

8. Verify the target database

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------

D:\ORADATA\DB10G\SYSTEM01.DBF
D:\ORADATA\DB10G\UNDOTBS01.DBF
D:\ORADATA\DB10G\SYSAUX01.DBF
D:\ORADATA\DB10G\USERS01.DBF
E:\DENIS_FILES\TEMP\TEST_TTS01.DBF


9. Log into the source and target database, issue

SQL> drop tablespace test_tts including contents and datafiles;




Notes:
-----

Find the byte order of operating systems

select * from v$transportable_platform order by platform_id

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
1           Solaris[tm] OE (32-bit)             Big
2           Solaris[tm] OE (64-bit)             Big
3           HP-UX (64-bit)                      Big
4           HP-UX IA (64-bit)                   Big
5           HP Tru64 UNIX                       Little
6           AIX-Based Systems (64-bit)          Big
7           Microsoft Windows IA (32-bit)       Little
8           Microsoft Windows IA (64-bit)       Little
9           IBM zSeries Based Linux             Big
10          Linux IA (32-bit)                   Little
11          Linux IA (64-bit)                   Little
12          Microsoft Windows 64-bit for AMD    Little
13          Linux 64-bit for AMD                Little
15          HP Open VMS                         Little
16          Apple Mac OS                        Big
17          Solaris Operating System (x86)      Little
18          IBM Power Based Linux               Big

Friday, December 14, 2007

RLS workshop

The purpsoe of this workshop is to demonstrate the use of RLS ( row level security, aka virtual private database (VPD), find-grained access )


Scenario - 1

Descriptsion:
Use RLS to modify the following query:
select * from emp;
transparently to the query:
select * from emp where sal <= 1500;

Steps:

1. Create a function authorized_emps
Logon as SCOTT
Script: f_authorized_emps.sql

2. set up an RLS policy
Logon as SCOTT
Script: add_policy.sql

3. Test
a. select statement
select * from emp;

Notes: only 7 rows returned not 14 rows

b. update statement
scott@TEST10GR> update emp set sal=1600;
7 rows updated.

c. delete statement
scott@TEST10GR> delete from emp;
0 rows deleted.

Notes: becuase as a result of (b) all the row have sal > 1500, so in (c)
we see 0 rows deleted. If we are not aware of RLS, this can be confuse.

d. issue: rollback

4. cleaup
script: cleanup_scnr1.sql



 



Scenario - 2: Using update_check in a policy

Steps:

1. Create a function authorized_emps
Logon as SCOTT
Script: f_authorized_emps.sql

2. Set up an RLS policy
Logon as SCOTT
Script: add_policy_uc.sql

3. Test with update

scott@TEST10GR> update emp set sal=1600;
update emp set sal=1600
*
ERROR at line 1:
ORA-28115: policy with check option violation

Notes:
The ORA-28115 error is raised because the policy now prevents any
updates to the value of columns in a row that will cause a change
in the visibility of those rows with the specified predicate.

4. Clean up
Script: cleanup_scnr1.sql (note: cleanup_scnr2.sql not exist)



 



Scenario - 3: Problems with static policys

Assume that the predicate depends on a changing value

Steps:
1. Setup the test
Script: setup_scnr3.sql

2. Set up an RLS policy
Logon as SCOTT
Script: add_policy_scnr3.sql

3. Test with dynamic policy (by default)
(a) Issue following statement once:
select count(*) from emp;
returen 14

(b) check trigger_fire

scott@TEST10GR> select * from trigger_fire;

VAL
----------
3

Notes: Because the policy function was called twice: once during
the parse phase and once during the execution phase,
the value was incremented by 2 from 1.

(c) select count for 5 times:

scott@TEST10GR> select count(*) from emp;

COUNT(*)
----------
14

scott@TEST10GR> select count(*) from emp;

COUNT(*)
----------
0

scott@TEST10GR> select count(*) from emp;

COUNT(*)
----------
0

scott@TEST10GR> select count(*) from emp;

COUNT(*)
----------
0

scott@TEST10GR> select count(*) from emp;

COUNT(*)
----------
7

(d) check trigger_fire
scott@TEST10GR> select * from trigger_fire;

VAL
----------
13
Notes: it can be seen for each selec count statement, the function gets
called twice

4. Test with static policy

(a) Run script: reset_scnr3.sql

(b) Run following statment multiple times:
select count(*) from emp;

Notes: the return number always the same.
(c) check trigger_fire

scott@TEST10GR> select * from trigger_fire;
VAL
----------
2
Notes: By declaring a policy as static, we have effectively instructed
the policy function to execute only once, and the policy to reuse
the predicate originally created, even though the predicate might
have changed in the course of time. This behavior might produce
unexpected results in your application, so you should use static
policies with great caution.



  



Scenario - 4 : Dynamic policy
-----------------------------
Description: A user can only see the infomation in his own department
in the emp table

Implement:
Create a special schema named RLSOWNER, which creates all the
RLS policies and owns all the policy functions. Only this user,
and no others, is granted the EXEMPT ACCESS POLICY system privilege.


Test steps:

1. Setup

scripts setup_scnr4.sql

2. test
script: test_scnr4.sql

Logon as differenct users, issuem the same statement:
select ename, sal, deptno from emp;

Output:

--- Logon as Martin
Connected.

ENAME SAL DEPTNO
---------- ---------- ----------
ALLEN 1600 30
WARD 1250 30
MARTIN 1250 30
BLAKE 2850 30
TURNER 1500 30
JAMES 950 30

6 rows selected.

--- Logon as Ford
Connected.

ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 840 20
JONES 2975 20
SCOTT 3000 20
ADAMS 1100 20
FORD 3000 20

--- Logon as Miller
Connected.

ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
KING 5000 10
MILLER 1300 10

--- Logon as Noexist
Connected.
select ename, sal, deptno from emp
*
ERROR at line 1:
ORA-28112: failed to execute policy function


Notes: if a user is not exist in the scott.emp table we saw ORA-28112 error
and we can find a trace file under user_dump_dest. The policy function
may be modified to give a warning and prevent the trace file in this case

Solution: we can create a role called EMP_SELECT, we grant select on
emp to this role, then only grant this role to those users in the emp table.

3. Cleanup
script: cleanup_scnr4.sql




 



Appendix A - Scripts


***** Script: add_policy.sql **************************************************
BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT'
);
END;
/

***** Script: add_policy_uc.sql ***************************************
BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE
);
END;
/
***** Script: add_policy_scnr3.sql ************************************
BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE
static_policy => FALSE
);
END;
/


***** Script: cleanup_scnr1.sql ***************************************

BEGIN
DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY') ;
END;
/

drop function authorized_emps
/

***** Script: cleanup_scnr3.sql ****************************************

BEGIN
DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY') ;
END;
/

drop function authorized_emps
/

drop table trigger_fire
/

**** Script: cleanup_scnr4.sql ****************************************

connect / as sysdba
drop user rlsowner cascade;
drop user martin cascade;
drop user miller cascade;
drop user ford cascade;
drop user noexist cascade;
drop public synonym emp;

***** Script: f_authorized_emps.sql ***********************************

CREATE OR REPLACE FUNCTION authorized_emps (
p_schema_name IN VARCHAR2,
p_object_name IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
RETURN 'SAL <= 1500';
END;
/

***** Script: reset_scnr3.sql *****************************************

BEGIN
DBMS_RLS.drop_policy (object_name => 'EMP', policy_name => 'EMP_POLICY');
END;
/


BEGIN
DBMS_RLS.add_policy (
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'SCOTT',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE,
static_policy => TRUE
);
END;
/

-- Reset the table TRIGGER_FIRE
UPDATE trigger_fire SET val = 1;
COMMIT;


***** Script: setup_scnr3.sql *****************************************

create table trigger_fire(val number);
insert into trigger_fire values(1);
commit;

CREATE OR REPLACE FUNCTION authorized_emps (
p_schema_name IN VARCHAR2,
p_object_name IN VARCHAR2
)
RETURN VARCHAR2
IS
l_return_val VARCHAR2 (2000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
l_return_val := 'SAL <= ' TO_NUMBER (TO_CHAR (SYSDATE, 'ss')) * 100;
UPDATE trigger_fire SET val = val + 1;
COMMIT;
RETURN l_return_val;
END;
/


**** Script: setup_scnr4.sql ***************************************

connect / as sysdba
create user rlsowner identified by rlsowner;
grant connect, resource to rlsowner;
grant exempt access policy to rlsowner;
grant execute on dbms_rls to rlsowner;


REM create users appeared in the emp table for testing purpose
create user martin identified by martin;
create user miller identified by miller;
create user ford identified by ford;
create user noexist identified by noexist;
grant connect to martin;
grant connect to miller;
grant connect to ford;
grant connect to noexist;


create public synonym emp for scott.emp;
grant select on emp to public;

connect rlsowner/rlsowner

CREATE OR REPLACE FUNCTION authorized_emps (
p_schema_name IN VARCHAR2,
p_object_name IN VARCHAR2
)
RETURN VARCHAR2
IS
l_deptno NUMBER;
l_return_val VARCHAR2 (2000);
BEGIN
IF (p_schema_name = USER)
THEN
l_return_val := NULL;
ELSE
SELECT deptno
INTO l_deptno
FROM emp
WHERE ename = USER;
l_return_val := 'DEPTNO = ' l_deptno;
END IF;

RETURN l_return_val;
END;
/

BEGIN
DBMS_RLS.add_policy (
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'EMP_POLICY',
function_schema => 'RLSOWNER',
policy_function => 'AUTHORIZED_EMPS',
statement_types => 'INSERT, UPDATE, DELETE, SELECT',
update_check => TRUE
);
END;
/

exit;


**** Script: test_scnr4.sql ****************************************

Prompt --- Logon as Martin
connect martin/martin
select ename, sal, deptno from emp;

Prompt --- Logon as Ford
connect ford/ford
select ename, sal, deptno from emp;

Prompt --- Logon as Miller
connect miller/miller
select ename, sal, deptno from emp;

Prompt --- Logon as Noexist
connect noexist/noexist
select ename, sal, deptno from emp;


Tuesday, December 04, 2007

Sort operation is counted same regardless of the table size

Sort operation is counted same regardless of whether the table is big or small

In the following example, one sort is in memory against a small table, one sort is in disk against a big table, but they both count 1 sort operation.



**********************  Case 1  ***************************

scott@ORCL> select * from emp order by 5;

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 301026995

----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 15 540 4 (25) 00:00:01
1 SORT ORDER BY 15 540 4 (25) 00:00:01
* 2 FILTER
3 TABLE ACCESS FULL EMP 15 540 3 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(CASE WHEN "SAL">=2000 THEN
SYS_AUDIT('SCOTT','EMP','EMP_SEL',3) ELSE NULL END IS NULL)


Statistics
----------------------------------------------------------
4 recursive calls
15 db block gets
9 consistent gets
0 physical reads
696 redo size
1356 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed

********************** Case 2 ***************************

scott@ORCL> select * from big_table order by 5;

1000000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1472477105

----------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 998K 98M 24421 (1) 00:12:03
1 SORT ORDER BY 998K 98M 260M 24421 (1) 00:12:03
2 TABLE ACCESS FULL BIG_TABLE 998K 98M 3383 (2) 00:01:41
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
117 recursive calls
43 db block gets
14457 consistent gets
30123 physical reads
520 redo size
21149727 bytes sent via SQL*Net to client
733707 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1000000 rows processed

Sunday, December 02, 2007

Combine Multiples Scans with CASE Statements

Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation.



hr@ORCL> select count(*) from employees where salary < 10000;
COUNT(*)
----------
87

hr@ORCL> select sum(salary) from employees where salary < 10000;
SUM(SALARY)
-----------
463795.5

hr@ORCL> select avg(salary) from employees where salary < 10000;
AVG(SALARY)
-----------
5330.98276

hr@ORCL> SELECT COUNT (CASE WHEN salary < 10000
2 THEN 1 ELSE null END) count1,
3 SUM (CASE WHEN salary < 10000
4 THEN salary ELSE null END) sum1,
5 AVG (CASE WHEN salary < 10000
6 THEN salary ELSE null END) avg1
7 FROM employees;

COUNT1 SUM1 AVG1
---------- ---------- ----------
87 463795.5 5330.98276


Understanding Oracle Analytical Function: row_number()


Purpose
-------
Understanding Oracle analytical function: row_number()

Syntax
------
row_number() over (order by col_1 [, col_2 ...])
row_number() over (partition by col_n [, col_m ... ]
order by col_1 [, col_2 ...])

row_number() returns an integer greater or equal to one



Test steps
-----------

1. Without partition

*** Run script: row_number_noprttn.tst from sqlplus

scott@ORCL> ho cat row_number_noprttn.tst

create table row_number_test (
a number,
b varchar2(20)
);

insert into row_number_test values (22, 'twenty two');
insert into row_number_test values ( 1, 'one');
insert into row_number_test values (13, 'thirteen');
insert into row_number_test values ( 5, 'five');
insert into row_number_test values ( 4, 'four');

select a, b, row_number() over (order by b)
from row_number_test
order by a;

drop table row_number_test;

*** Results:

A B ROW_NUMBER()OVER(ORDERBYB)
---------- -------------------- --------------------------
1 one 3
4 four 2
5 five 1
13 thirteen 4
22 twenty two 5


*** Notes: the value of ROW_NUMER col is obtained by order by B
whith five the lowest and twenty two the highest


2. With partition

*** Run script: row_number_prttn.tst from sqlplus

scott@ORCL> ho cat row_number_prttn.tst
create table row_number_test_2 (
a number,
b varchar2(20),
c char(1)
);

insert into row_number_test_2 values (22, 'twenty two', '*');
insert into row_number_test_2 values ( 1, 'one', '+');
insert into row_number_test_2 values (13, 'thirteen', '*');
insert into row_number_test_2 values ( 5, 'five', '+');
insert into row_number_test_2 values ( 4, 'four', '+');

select
a, b, row_number() over (partition by c order by b)
from
row_number_test_2
order
by a;

drop table row_number_test_2;

*** Output

A B ROW_NUMBER()OVER(PARTITIONBYCORDERBYB)
---------- -------------------- --------------------------------------
1 one 3
4 four 2
5 five 1
13 thirteen 1
22 twenty two 2

*** Notes:
In this case, there are two partitions resulted by partition by c
and the row_number col value starts from 1 in each partition