Gather statistics on a specific table without invalidating cursors

Reference:
Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 -> 140 DBMS_STATS
Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

When gathering statistics using DBMS_STATS package, NO_INVALIDATE parameter can be used to not invalidate the cursors in the cache automatically. This parameter accepts 3 values: 1) TRUE doesn’t invalidate dependent cursors 2) FALSE invalidate dependent cursors 3) AUTO_INVALIDATE which is the default value that lets Oracle decide when to invalidate the cursors. To get the default value of NO_VALIDATE parameter, use GET_PREFS function as follows:

SQL> SELECT DBMS_STATS.GET_PREFS( ‘NO_INVALIDATE’ ) FROM dual;
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE’)
————————————————————
DBMS_STATS.AUTO_INVALIDATE
SQL>

In the following example NO_INVALIDATE with FALSE option will be used to show how cursors are invalidated in the cache. Let’s flush the shared pool, create a new table and gather the statistics:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

SQL> CREATE TABLE tbl_invalidation_test AS SELECT * FROM dba_objects;
Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,’tbl_invalidation_test’);
PL/SQL procedure successfully completed.

Now query the table twice and check V$SQL to see count of executions, status of the cursor and count of invalidations:

SQL> SELECT COUNT(1) FROM tbl_invalidation_test;
COUNT(1)
———-
71972

SQL> /
COUNT(1)
———-
71972

SQL> SELECT child_number,
parse_calls,
executions,
object_status,
invalidations
FROM v$sql
WHERE sql_text = ‘SELECT COUNT(1) FROM tbl_invalidation_test’;

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME INVALIDATIONS
———— ———– ———- ——————- ————-
0            2           2          2016-07-19/19:09:06 0

Now gather the statistics of the table with NO_INVALIDATE=>FALSE which will invalidate the cached cursors, query the table and check V$SQL view again:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(
NULL,’TBL_INVALIDATION_TEST’,
NO_INVALIDATE => FALSE);
PL/SQL procedure successfully completed.

SQL> SELECT COUNT(1) FROM tbl_invalidation_test;
COUNT(1)
———-
71972

SQL> SELECT child_number, parse_calls, executions, object_status, invalidations
FROM v$sql WHERE sql_id = ‘002swby2jr0qz’;

CHILD_NUMBER PARSE_CALLS EXECUTIONS OBJECT_STATUS INVALIDATIONS
———— ———– ———- ——————- ————-
0            2           2          INVALID_UNAUTH      1

As it is seen from the output, the status of the cursor is INVALID_UNAUTH and the count of invalidations is 1.
To change the default value of NO_VALIDATE parameter, use SET_PREFS procedure as follows:

SQL> EXEC DBMS_STATS.SET_PARAM(‘NO_INVALIDATE’,’FALSE’);
PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS( ‘NO_INVALIDATE’ ) FROM dual;
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE’)
——–
FALSE
SQL>

▼Practice:
Perform the following practice in 15 min.

► Flush the shared pool, create a new table based on DBA_OBJECTS and gather table statistics. Query the table twice and get count of executions and invalidations from V$SQL view. Then gather a statistics again and invalidate the cached cursors and query V$SQL view again.

If you want to get more information on this topic, get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/ 

To speed up the recovery process during a failure, image copy backups can be updated with daily incremental backups. By taking an incremental backup, you recover it over the image copy backups. And when database is crashed, you will only need to restore the incrementally updated backup and apply changes from the redo log files. The restored backup will already contain all incremental backups applied.

Let’s try the following scenario to show how it works and make sure we can use and implement incrementally updated backup/recovery during OCM exam:

– Create an Incrementally updated backup

– Add new tablespace with new datafile, create table on that tablespace

– Take an incrementally updated backup again

– Delete the datafile and query the table

– Restore a datafile from the backup

First of all, enable block change tracking as follows:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/tmp/blk_change.trc’;

Next, connect to RMAN and take an incrementally updated backup:

RMAN> RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘mydb_incr_backup’ DATABASE;
RECOVER COPY OF DATABASE WITH TAG ‘mydb_incr_backup’;
}

This script will take a LEVEL 1 image backup of the database. Now add new tablespace, create a table and query it:

SQL> CREATE TABLESPACE mytbs01 DATAFILE ‘/home/oracle/oradata/mytbs03.dbf’;
SQL> CREATE TABLE tbl_test01 (id NUMBER, name VARCHAR2(10)) TABLESPACE mytbs01;
SQL> INSERT INTO tbl_test01 VALUES(1, ‘oracle’);
SQL> COMMIT;
SQL> SELECT * FROM tbl_test01;
ID NAME
---------- ----------
1          oracle

Run the same backup command again. This time it will take an incremental backup and recover it over the image copy backup of the database:

RMAN> RUN {
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘mydb_incr_backup’ DATABASE;
RECOVER COPY OF DATABASE WITH TAG ‘mydb_incr_backup’;
}

Remove the datafile that you’ve already created, flush the buffer and query the table:

[oracle@ocm11g oradata] mv mytbs01.dbf mytbs01.dbf_backup
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> SELECT * FROM tbl_test01;
SELECT *FROM tbl_test01
*

ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/home/oracle/oradata/mytbs01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

As we have incrementally updated backup, we can restore it using SWITCH DATAFILE … TO COPY command as follows:

RMAN> LIST COPY OF DATAFILE 5;
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time

——- —- – ————— ———- —————

21 5 A 24-OCT-14 1458646 24-OCT-14
Name: /home/oracle/flash_recovery_area/OCM_NEW/datafile/o1_mf_mytbs01_b4mqo4k5_.dbf
Tag: MYDB_INCR_BACKUP

RMAN> SWITCH DATAFILE 5 TO COPY;
datafile 5 switched to datafile copy “/home/oracle/flash_recovery_area/OCM_NEW/datafile/o1_mf_mytbs01_b4mqo4k5_.dbf”
RMAN>

Now recover the datafile and query the table:

RMAN> RECOVER DATAFILE 5;
SQL> SELECT * FROM tbl_test01;
ID NAME
---------- ----------
1          oracle

Implement fine-grained access control

Reference:

Database Security Guide -> 7 Using Oracle Virtual Private Database to Control Data Access

Database PL/SQL Packages and Types Reference -> DBMS_RLS

Understanding Fine-Grained Access Control (DBMS_RLS) on INSERT (Doc ID 99250.1)

Master Note For Oracle Virtual Private Database ( VPD / FGAC / RLS ) (Doc ID 1352641.1)

Oracle8i Fine Grained Access Control – Working Examples (Doc ID 67977.1)

Evolution of Fine Grain Access Control FGAC Feature From 8i to 10g (Doc ID 281829.1)

Virtual Private Database (VPD) is used to prevent a user from access to unrelated data. VPD uses DBMS_RLS package that contains fine grained access control interface to limit specific data to specific users by applying the security policy directly to the database objects and modifies the SQL statement automatically before accessing the data by adding a WHERE condition that is returned from the security policy function.

Let’s create a simple VPD and see how it works. Refer to the Chapter 7 under “Database Security Guide” documentation to get syntax of main commands. In the following example, we create a table (TBL_VPD_TEST) based on ALL_OBJECTS view under the new user USR_VPD, grant SELECT access to the second user USR_VPD_2 and create a policy to prevent the second user to query some rows.

To implement this scenario, let’s create users and a table:

SQL> CREATE USER USR_VPD IDENTIFIED BY usr_vpd;
User created.

SQL> CREATE USER usr_vpd_2 IDENTIFIED BY usr_vpd_2;
User created.

SQL> GRANT CONNECT, RESOURCE TO usr_vpd, usr_vpd_2;
Grant succeeded.

SQL> CONN usr_vpd/usr_vpd;
Connected.


SQL> CREATE TABLE tbl_vpd_test
AS
SELECT * FROM all_objects;
Table created.

SQL> GRANT SELECT ON tbl_vpd_test TO usr_vpd_2;
Grant succeeded.

SQL>

Now we need to have a function which generates a dynamic WHERE clause. This function must take a schema and table name as an input, must provide a return value for the WHERE clause and must generate a valid WHERE clause. The following error will appear if the WHERE clause is invalid:

SQL>
ERROR at line 1:
ORA-28113: policy predicate has error

To access user’s session information to build up a WHERE clause, the next topic comprehensively describes the context is used. Create a function as follows:

SQL> CREATE OR REPLACE FUNCTION hide_all_except_100 (v_schema IN VARCHAR2, v_objname IN VARCHAR2)
RETURN VARCHAR2
AS
where_clause VARCHAR2 (100);
BEGIN
IF USER = ‘USR_VPD_2’
THEN
where_clause := ‘object_id=100’;
ELSE
where_clause := ‘’;
END IF;
RETURN (where_clause);
END hide_all_except_100;
/
Function created.

SQL>

 Note: Please note that the preferred method to pass a session information to the function used in the policy is to use an application context. Detailed information is provided about an application context in the next chapter.

Now DBMS_RLS.ADD_POLICY procedure to add fine-grained access control policy to the table.

The syntax of ADD_POLICY command is as follows:

DBMS_RLS.ADD_POLICY (
object_schema IN VARCHAR2 NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2 NULL,
update_check IN BOOLEAN FALSE,
enable IN BOOLEAN TRUE,
static_policy IN BOOLEAN FALSE,
policy_type IN BINARY_INTEGER NULL,
long_predicate IN BOOLEAN FALSE,
sec_relevant_cols IN VARCHAR2,
sec_relevant_cols_opt IN BINARY_INTEGER NULL);

Detailed information about all parameters of ADD_POLICY procedure is available in the “Database PL/SQL Packages and Types Reference” chapter “110 DBMS_RLS”. Now connect with SYS user and create a new policy that prevents user USR_VPD_2 from seeing all rows except where id=100 as follows:

SQL> BEGIN
DBMS_RLS.add_policy (object_schema => ‘USR_VPD’,
object_name => ‘TBL_VPD_TEST’,
policy_name => ‘MY_VPD_POLICY_01’,
function_schema => ‘USR_VPD’,
policy_function => ‘HIDE_ALL_EXCEPT_100’,
statement_types => ‘SELECT’);
END;
/
PL/SQL procedure successfully completed.
SQL>

You can also specify rest DML command for the statement_types parameter like INSERT, UPDATE and DELETE.

Now query table with both USR_VPD and USR_VPD_2 users:

SQL> SHOW USER
USER is “USR_VPD”

SQL> SELECT COUNT(1) FROM usr_vpd.tbl_vpd_test;
COUNT(1)
----------
55713

SQL> CONN usr_vpd_2/usr_vpd_2
Connected.

SQL> SELECT COUNT(1) FROM usr_vpd.tbl_vpd_test;
COUNT(1)
----------
1

Exam tip: Remember, if you successfully create a policy, but it doesn’t work for certain reason and you need to recreate it, use DROP_POLICY procedure to drop the policy from the table. It is easy to use, simply provide the schema name, table name and policy name as follows:

SQL> EXEC DBMS_RLS.DROP_POLICY(‘usr_vpd’,’tbl_vpd_test’,’my_vpd_policy_01’);
PL/SQL procedure successfully completed.
SQL>

update_check parameter

During an exam you might be asked to create a policy that must prevent a user from making an update that violates WHERE condition of the policy. In this special case, update_check parameter must be set to TRUE. To understand how it works, check the following example. The policy we have created earlier doesn’t contain update_check parameter and the default value of this parameter is FALSE.

Let’s connect with the second user, query the table and try to update it:

SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test;
OBJECT_ID
----------
100


SQL> UPDATE usr_vpd.tbl_vpd_test SET object_id=500;
1 row updated.
SQL>

Update completed successfully. If you query the table, you will get no rows:

SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test;
no rows selected


SQL> ROLLBACK
Rollback complete.

SQL> CONN / AS SYSDBA
Connected.

SQL>

Make sure the second user has a grant privilege on the table:

SQL> GRANT UPDATE ON usr_vpd.tbl_vpd_test TO usr_vpd_2;
Grant succeeded.
SQL>

Next, drop the policy and recreate it:

SQL> EXEC DBMS_RLS.DROP_POLICY(‘usr_vpd’,’tbl_vpd_test’,’my_vpd_policy_01’);
PL/SQL procedure successfully completed.


SQL> BEGIN
DBMS_RLS.add_policy (object_schema => ‘USR_VPD’,
object_name => ‘TBL_VPD_TEST’,
policy_name => ‘MY_VPD_POLICY_01’,
function_schema => ‘USR_VPD’,
policy_function => ‘HIDE_ALL_EXCEPT_100’,
update_check => TRUE,
statement_types => ‘SELECT, UPDATE’);
END;
/
PL/SQL procedure successfully completed.
SQL>

Now run update command of the row that is the only row you can view:

SQL> SELECT object_id FROM usr_vpd.tbl_vpd_test;
OBJECT_ID
----------
100


SQL> UPDATE usr_vpd.tbl_vpd_test SET object_id=500;
UPDATE usr_vpd.tbl_vpd_test SET object_id=500
*
ERROR at line 1:
ORA-28115: policy with check option violation
SQL>

Using REMAP_DATA parameter you can apply a function to the rows that being exported and get a remapped data as a result.

In the following example, we create a function that accepts a string as an input and converts the string to uppercase. Then we use this function with REMAP_DATA parameter and remap the characters of specific column of the table.

Create a package with one function which converts input string to uppercase and return it:

SQL> CREATE OR REPLACE PACKAGE usr_dptest1.pkg_remap

AS

FUNCTION make_upper (p_val VARCHAR2)

RETURN VARCHAR2;

END;

/

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY usr_dptest1.pkg_remap

AS

FUNCTION make_upper (p_val VARCHAR2)

RETURN VARCHAR2

IS

BEGIN

RETURN UPPER (p_val);

END;

END;

/

Package body created.

Create a table with 5 rows and make values of OBJ_NAME column lowercase:

SQL> CREATE TABLE usr_dptest1.all_lowers

AS

SELECT LOWER (object_name) obj_name, object_id, object_typeFROM dba_objects

WHERE ROWNUM <= 5;

Table created.




SQL> SET LINESIZE 150




SQL> COL obj_name FORMAT a15




SQL> SELECT * FROM usr_dptest1.all_lowers ;

OBJ_NAME OBJECT_ID OBJECT_TYPE

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

icol$ 20 TABLE

i_user1 46 INDEX

con$ 28 TABLE

undo$ 15 TABLE

c_cobj# 29 CLUSTER

Create a parameter file and use REMAP_DATA parameter to convert values of OBJ_NAME column to uppercase:

[oracle@oemgrid ~]$ more /tmp/parfile03.dat

directory=my_dir

dumpfile=exp_remap_data_01.dmp

schemas=usr_dptest1

include=table:”IN (‘ALL_LOWERS’)”

remap_data=usr_dptest1.all_lowers.obj_name:usr_dptest1.pkg_remap.make_upper

[oracle@oemgrid ~]$

Export the dump file, drop the table, import it back and query the table. All rows of column OBJ_NAME will be converted to uppercase:

[oracle@oemgrid ~]$ expdp system/oracle parfile=/tmp/parfile03.dat

Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_03”: system/******** parfile=/tmp/parfile03.dat

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . exported “USR_DPTEST1”.”ALL_LOWERS” 5.937 KB 5 rows

Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_03” successfully loaded/unloaded

[oracle@oemgrid ~]$




SQL> DROP TABLE usr_dptest1.all_lowers ;

Table dropped.

SQL>




[oracle@oemgrid ~]$ impdp system/oracle directory=my_dir dumpfile=exp_remap_data_01.dmp

Master table “SYSTEM”.”SYS_IMPORT_FULL_01” successfully loaded/unloaded

Starting “SYSTEM”.”SYS_IMPORT_FULL_01”: system/******** directory=my_dir dumpfile=exp_remap_data_01.dmp

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported “USR_DPTEST1”.”ALL_LOWERS” 5.937 KB 5 rows

Job “SYSTEM”.”SYS_IMPORT_FULL_01” successfully completed at 03:37:10

[oracle@oemgrid ~]$ sqlplus / as sysdba




SQL> SET LINESIZE 150

SQL> COL obj_name FORMAT a20




SQL> SELECT * FROM usr_dptest1.all_lowers;

OBJ_NAME OBJECT_ID OBJECT_TYPE

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

ICOL$ 20 TABLE

I_USER1 46 INDEX

CON$ 28 TABLE

UNDO$ 15 TABLE

C_COBJ# 29 CLUSTER

SQL>

Encryption

You can enable block level encryption for LOB segments using ENCRYPT USING ‘encryption_algorithm” syntax. If you want to prevent the encryption explicitly, use DECRYPT option.

SecureFiles supports 4 encryption algorithms: 3DES168, AES128, AES192, AES256.

The following error will appear if you try to encrypt an object that is owned by SYS user:

ORA-28336: cannot encrypt SYS owned objects

The following error will appear if you didn’t create and open a wallet:

ORA-28365: wallet is not open

Check the following documentation to configure the wallet properly:

Reference:

Database Advanced Security Administrator’s Guide -> 3 Securing Stored Data Using Transparent Data Encryption-> 3.3.1.4 Using Transparent Data Encryption in a Multi-Database Environment

Edit sqlnet.ora file and specify the folder to store the wallet as follows:

ENCRYPTION_WALLET_LOCATION =

(SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /u01/app/oracle/product/11.2.0/dbhome_1/wallet)))

Make sure to create a wallet folder under $ORACLE_HOME and run the following command to create a wallet:

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY mypassword;

You can query V$ENCRYPTION_WALLET view to get the status of the wallet:

SQL> COL wrl_parameter format a50

SQL> SELECT status,wrl_parameter FROM V$ENCRYPTION_WALLET;

STATUS WRL_PARAMETER

—————— ————————————————–

OPEN /u01/app/oracle/product/11.2.0/dbhome_1/wallet

SQL>

Use the following command to open the wallet again after the database is restarted:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY mypassword;

Now use ENCRYPT option to encrypt the LOB segment as follows:

SQL> CREATE TABLE dbsnmp.tbl_lob_encrypt (id number, lob_data clob) LOB (lob_data)

STORE AS SECUREFILE (TABLESPACE users ENCRYPT USING ‘AES128’);

Table created.

SQL>

Caching

With caching feature you can bring the LOB segment to the buffer cache. You have to think twice before enabling caching option for the LOB segments, because it can flush necessary block from the buffer cache and can affect the performance of the database.

To put LOB segment to the cache, use CACHE clause as follows:

SQL> CREATE TABLE tbl_lob_cache (lob_data CLOB)

LOB (lob_data) STORE AS SECUREFILE (tablespace users CACHE);

Table created.

SQL>

To enable cache feature for a LOB segment, use ALTER TABLE .. MODIFY LOB command as follows:

SQL> ALTER TABLE tbl_lob_cache MODIFY LOB (lob_data)(CACHE);

Table altered.

SQL>

Reference:
Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)
Application Development -> Database SecureFiles and Large Objects Developer’s Guide -> 4 Using Oracle SecureFiles LOBs

In this section we will talk about SecureFile LOB (Large Object) segments. Detailed information about creating and managing LOB segments is provided in the next chapter.

SecureFiles is a LOB storage architecture that is used to store unstructured data and contains numerous features as deduplication, compression, encryption and etc. If you want to store a LOB as a SecureFile, use STORE AS SECUREFILE clause in the CREATE TABLE statement. If you omit this clause, the LOB will be stored in a regular traditional way which is called BasicFile.

A SecureFile LOB can be created only in a tablespace managed with Automatic Segment Space Management (ASSM). The following error will appear if you attempt to create a SecureFile LOB in a non-ASSM tablespace:

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace “SYSTEM”

DB_SECUREFILE parameter is used to control the action of the database with regards to the LOB storage. There are four values for this parameter:

– NEVER is used to make all LOBs that are created as a SecureFile to be created as a BasicFile LOB.
– PERMITTED is a default value that allows LOBs to be created as a SecureFile using STORE AS SECUREFILE clause.
– ALWAYS is used to make all LOBs in a ASSM tablespace be created as a SecureFile LOBs.
– IGNORE is used to ignore store as securefile clause and creation of SecureFile LOBs are permitted.

Note: Make sure the value of parameter DB_SECUREFILE is not set to NEVER and IGNORE if you are asked to create a SecureFile LOB during an exam.

Let’s create a simple table with a SecureFile LOB segment. First, create a table with BasicFile LOB as follows:

SQL> CREATE TABLE tbl_basicfile_lob
2 (id number,
3 lob_data clob)
4 LOB (lob_data) STORE AS BASICFILE;
Table created.
 
SQL> INSERT INTO tbl_basicfile_lob VALUES(1,’This is my long text’);
1 row created.
 
SQL> COMMIT;
Commit complete.
 
SQL>

Now create a table with SecureFile LOB data as follows:

SQL> CREATE TABLE tbl_securefile_lob (
2 id number, lob_data clob)
3 LOB (lob_data) STORE AS SECUREFILE (tablespace users);
Table created.
 
SQL> INSERT INTO tbl_securefile_lob VALUES(1,’This is my long text’);
1 row created.
 
SQL> COMMIT;
Commit complete.
SQL>

Query DBA_LOBS view to get more information about the lob segments:

SQL> SET LINESIZE 150
SQL> COL column_name FORMAT a10
SQL> SELECT table_name, column_name, segment_name, securefile FROM dba_lobs where table_name like ‘TBL%’;
 
TABLE_NAME COLUMN_NAM SEGMENT_NAME SEC
—————————— ———- —————————— —
TBL_BASICFILE_LOB LOB_DATA SYS_LOB0000096459C00002$$ NO
TBL_SECUREFILE_LOB LOB_DATA SYS_LOB0000096464C00002$$ YES
SQL>

If the LOB segment is stored as a SecureFile, you will be able to use the following features.

• Compression
• Encryption
• Deduplication
• Caching
• Logging

In this newsletter, we will cover the compression feature

Compression
This feature is used to compress the LOB segment. A table compression doesn’t affect the LOB compression. It means that if the table is compressed, the LOB segment will not be compressed automatically. Oracle has two levels of compression, i.e 11gR1 – MEDIUM and HIGH. (a new LOW compression level is added at 11gR2). Check the following example to see how to compress the LOB segment and appreciate the difference between compressed
and noncompressed LOBs.

Create a table with non-compressed LOB segment and insert some data:

SQL> CREATE TABLE tbl_lob_nocompress
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE
(TABLESPACE users NOCOMPRESS);
Table created.
 
SQL> INSERT INTO tbl_lob_nocompress
SELECT object_id, RPAD (‘ ‘, 5000, object_name) FROM dba_objects
WHERE ROWNUM < 500;
499 rows created.

Now create a table with high compressed lob segment, insert some data and gather statistics from both tables:

SQL> CREATE TABLE tbl_lob_compress_high
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE
(TABLESPACE users COMPRESS HIGH);
 
Table created.
SQL> INSERT INTO TBL_LOB_COMPRESS_HIGH
SELECT object_id, RPAD (‘ ‘, 5000, object_name) FROM dba_objects
WHERE ROWNUM < 500;
499 rows created.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER,’TBL_LOB_NOCOMPRESS’);
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER,’TBL_LOB_COMPRESS_HIGH’);
PL/SQL procedure successfully completed.

Query DBA_LOBS view to find out the status of compression:

SQL> SELECT table_name, segment_name, securefile, compression
FROM dba_lobs
WHERE table_name LIKE ‘%COMPRESS%’;
TABLE_NAME SEGMENT_NAME SEC COMPRE
—————————— —————————— — ——
TBL_LOB_NOCOMPRESS SYS_LOB0000096719C00002$$ YES NO
TBL_LOB_COMPRESS_HIGH SYS_LOB0000096722C00002$$ YES HIGH

Now query USER_SEGMENTS view to see the size difference of compressed and noncompressed lob segments:

SQL> COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN (‘SYS_LOB0000096719C00002$$’,’SYS_LOB0000096722C00002$$’);
 
SEGMENT_NAME BYTES
—————————— ———-
SYS_LOB0000096719C00002$$ 5373952
SYS_LOB0000096722C00002$$ 131072
SQL>

In the next newsletter, we will cover the rest SecureLob features.

If you want to get more information on managing SecureLobs, get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/ 

Reference:

HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES (Doc ID 728758.1)

During an OCM exam you might be asked to convert a BasicFiles LOB to SecureFiles LOB. To perform this operation, you should use online redefinition feature. More detailed information about this feature was provided in the previous chapter. Check “Using Online Redefinition for Migrating Tables with BasicFiles LOBs” part of the documentation “Database SecureFiles and Large Objects Developer’s Guide – Chapter 4” to see an example related on how to migrate from BasicFiles to Securefiles LOB. Below, we will provide a simple example.

– Create a table with BasicFiles LOB column:

SQL> CREATE TABLE DBSNMP.tbl_basic_lob

(

  id NUMBER,

  clob_data CLOB

)

TABLESPACE users;

Table created.

SQL>

– Insert a few rows in it as we did in the previous example:

SQL> INSERT INTO DBSNMP.tbl_basic_lob

SELECT object_id, RPAD (‘ ‘, 5000, ‘This is test ‘)  FROM dba_objects WHERE ROWNUM < 3000;

2999 rows created.

SQL>

The following error will appear if you try to use SecureFiles LOB features on this table:

SQL>ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE);

ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE)

*

ERROR at line 1:

ORA-43856: Unsupported LOB type for SECUREFILE LOB operation

Create a table with SecureFiles LOB option which will be used as in interim table:

SQL> CREATE TABLE DBSNMP.tbl_securefiles_lob

(

id NUMBER,

clob_data CLOB

)

LOB (clob_data) STORE AS SECUREFILE (TABLESPACE users);

Table created.

SQL>

Start the redefinition using DBMS_REDEFINITION.START_REDEF_TABLE procedure:

SQL> EXEC dbms_redefinition.start_redef_table(‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’,null,2);

PL/SQL procedure successfully completed.

Clone the dependent objects using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure:

SQL> DECLARE

error_count PLS_INTEGER := 0;

BEGIN

DBMS_REDEFINITION.copy_table_dependents (‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’,

1, TRUE, TRUE, TRUE, FALSE, error_count);

DBMS_OUTPUT.put_line (‘errors := ‘ || TO_CHAR (error_count));

END;

/

PL/SQL procedure successfully completed.

Complete the redefinition process:

SQL> EXEC dbms_redefinition.finish_redef_table(‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’);

PL/SQL procedure successfully completed.

Query DBA_LOBS view to make sure that the table is migrated to SecureFiles LOB:

SQL> SELECT securefile FROM dba_lobs WHERE table_name = ‘TBL_BASIC_LOB’;

SEC

---

YES

Now try the command you failed to run before to use SecureFiles option. The command will be running successfully:

SQL> ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE);

Table altered.

SQL>

To get more detailed information on secure lobs and managing lob segments, get the OCM preparation book from the following link:

http://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/

In the first section of the OCM exam (Server Configuration), there is a topic “1.3. Create and manage temporary, permanent, and undo tablespaces”. I have provided a very detailed information in the book on this topic and covered the following subjects:

• Create Permanent tablespace
• Create Compressed Tablespace
• Create Encrypted Tablespace
• Create Temporary Tablespace
• Create temporary tablespace group
• Specify Nonstandard block size for tablespace
• Modify Tablespace (OFFLINE, ONLINE, READ-ONLY, ALTER TABLESPACE, SHRINK, RENAME, DROP)
• Managing UNDO tablespace

In this newsletter I will show you how to create an encrypted tablespace.

In the beginning of each topic of the exam a reference from the documentation or the metalink note is provided. Check the following documentation to get more information on creating an encrypted tablespace during the exam:

Database Advanced Security Administrator’s Guide -> 8 Securing Stored Data Using Transparent

Data Encryption -> 8.2.5 Encrypting Entire Tablespaces

Any permanent tablespace can be encrypted to protect sensitive data. Tablespace encryption is completely transparent to your applications, and hence no application modification is required.

It’s not possible to specify encryption for undo and temporary tablespaces, as there is no need to do so, because data from the encrypted tablespace is automatically encrypted even when written to the online redo log files, undo tablespace and temporary tablespace to maximize the security.

Before attempting to create an encrypted tablespace, a wallet must be created to hold the encryption key, otherwise the following error will pop up “ORA-28365: wallet is not open”.

For this, create a wallet folder and change sql.net ora file as follows:

[oracle@ocm11g ~]$ cd $ORACLE_HOME

[oracle@ocm11g db_1]$ mkdir wallet

[oracle@ocm11g db_1]$ cd wallet/

[oracle@ocm11g wallet]$ pwd

/home/oracle/product/11.2.0/db_1/wallet

[oracle@ocm11g wallet]$ cd $ORACLE_HOME/network/admin

[oracle@ocm11g wallet]$ vi sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

(SOURCE=

(METHOD=file)

(METHOD_DATA=

(DIRECTORY=/home/oracle/product/11.2.0/db_1/wallet)))

Enter the following command to protect the Oracle wallet:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”;

System altered.

SQL>

The following error will pop up if you run the above command without changing sqlnet.ora file:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”;

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”

*

ERROR at line 1:

ORA-28368: cannot auto-create wallet

SQL>

Now, create encrypted tablespace as follows:

SQL> CREATE TABLESPACE encrypted_tbs

DATAFILE ‘/home/oracle/oradata/encrypted_tbs01.dbf’ SIZE 100M

ENCRYPTION USING ‘AES256’

DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL>

After creating an encrypted tablespace, check V$ENCRYPTED_TABLESPACES view:

SQL> SELECT tbs.name, tbs_en.encryptionalg encr_algorithm

FROM v$tablespace tbs, v$encrypted_tablespaces tbs_en

WHERE tbs.ts# = tbs_en.ts#;

NAME ENCR_ALGORITHM

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

ENCRYPTED_TBS AES256

If you want to get more information on creating and managing tablespaces, get my book from the following link:

https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/