Exforsys

H I D E

Home arrow Technical Training arrow Oracle 11g Tutorial

Table of Contents

 Oracle 11g SecureFiles
 Oracle 11g SecureFiles - Page 2
 Oracle 11g SecureFiles - Page 3
 Oracle 11g SecureFiles - Page 4

Oracle 11g SecureFiles Page - 2

Page 2 of 4
Author: Saurabh Gupta     Published on: 4th Sep 2011

Oracle 11g SecureFiles

Case 1: DB_SECUREFILE=PERMITTED 

This mode represents the ideal state of LOB creation. SecureFiles are created on ASSM tablespaces, while BasicFiles are created on both ASSM as well as non ASSM tablespaces.

Sample Code
  1. ----Set the DB_SECUREFILE parameter as PERMITTED----
  2. SQL> ALTER SYSTEM SET DB_SECUREFILE=PERMITTED;
  3.  
  4. System altered.
  5.  
  6. Now, we shall CREATE TABLES WITH SecureFiles AND BasicFiles ON ASSM AND non ASSM tablespaces respectively AND observe the results.
  7.  
  8. ----Create table with SecureFile column on ASSM tablespace----
  9. SQL> CREATE TABLE PERMITTED_SECUREFILE_1
  10.   2  (DOC BLOB)
  11.   3  LOB(DOC) STORE AS SECUREFILE
  12.   4  TABLESPACE USERS;
  13.  
  14. TABLE created.
  15.  
  16. ----Create table with SecureFile column on non ASSM tablespace----
  17. SQL> CREATE TABLE PERMITTED_SECUREFILE_2
  18.   2  (DOC BLOB)
  19.   3  LOB(DOC) STORE AS SECUREFILE
  20.   4  TABLESPACE SYSTEM;
  21. CREATE TABLE PERMITTED_SECUREFILE_2
  22. *
  23. ERROR at line 1:
  24. ORA-43853: SECUREFILE lobs cannot be used IN non-ASSM tablespace "SYSTEM"
  25.  
  26. ----Create table with BasicFile column on ASSM tablespace----
  27. SQL> CREATE TABLE PERMITTED_BASICFILE_1
  28.   2  (DOC BLOB)
  29.   3  LOB(DOC) STORE AS BASICFILE
  30.   4  TABLESPACE USERS;
  31.  
  32. TABLE created.
  33.  
  34. ----Create table with BasicFile column on non ASSM tablespace----
  35. SQL> CREATE TABLE PERMITTED_BASICFILE_2
  36.   2  (DOC BLOB)
  37.   3  LOB(DOC) STORE AS BASICFILE
  38.   4  TABLESPACE SYSTEM;
  39.  
  40. TABLE created.
Copyright exforsys.com


The above table creation scripts reveal the ideal behavior 11g LOBs and differentiate them with LOB prior to 11g. SecureFiles can only reside in ASSM tablespace, while there is no such restriction on BasicFiles.

The dictionary view USER_LOBS lists the properties of an LOB column. SECUREFILE is a new column added in USER_LOBS dictionary view. Let us query the view to check the SECUREFILE feature of the above tables.

The query results show that Securefile has been enabled only for BLOB column contained in the table PERMITTED_SECUREFILE_1.

Case 2: DB_SECUREFILE = ALWAYS

This mode of DB_SECUREFILE ensures that all the LOBs created on an ASSM tablespace shall be treated as SecureFiles, irrespective of the LOB specification (SecureFile or BasicFile). Only the BasicFile LOBs on non ASSM tablespace shall be treated as BasicFile.

Sample Code
  1. ----Set the DB_SECUREFILE parameter as ALWAYS----
  2. SQL> ALTER SYSTEM SET DB_SECUREFILE=ALWAYS;
  3.  
  4. System altered.
  5.  
  6. ----Create table with SecureFile column on ASSM tablespace----
  7. SQL> CREATE TABLE ALWAYS_SECUREFILE_1
  8.   2  (DOC BLOB)
  9.   3  LOB(DOC) STORE AS SECUREFILE
  10.   4  TABLESPACE USERS
  11.   5  /
  12.  
  13. TABLE created.
  14.  
  15. Still, a SecureFile cannot be created ON non ASSM tablespace.
  16.  
  17. ----Create table with SecureFile column on non ASSM tablespace----
  18. SQL> CREATE TABLE ALWAYS_SECUREFILE_2
  19.   2  (DOC BLOB)
  20.   3  LOB(DOC) STORE AS SECUREFILE
  21.   4  TABLESPACE SYSTEM
  22.   5  /
  23. CREATE TABLE ALWAYS_SECUREFILE_2
  24. *
  25. ERROR at line 1:
  26. ORA-43853: SECUREFILE lobs cannot be used IN non-ASSM tablespace "SYSTEM"
  27.  
  28. ----Create table with BasicFile column on ASSM tablespace----
  29. SQL> CREATE TABLE ALWAYS_BASICFILE_1
  30.   2  (DOC BLOB)
  31.   3  LOB(DOC) STORE AS BASICFILE
  32.   4  TABLESPACE USERS
  33.   5  /
  34.  
  35. TABLE created.
  36.  
  37. ----Create table with BasicFile column on non ASSM tablespace----
  38. SQL> CREATE TABLE ALWAYS_BASICFILE_2
  39.   2  (DOC BLOB)
  40.   3  LOB(DOC) STORE AS BASICFILE
  41.   4  TABLESPACE SYSTEM
  42.   5  /
  43.  
  44. TABLE created.
Copyright exforsys.com


Ads

Now, check the SecureFile property for the above tables in USER_LOBS view.

Observe that only the BasicFile on non ASSM tablespace is under BasicFile category. Otherwise, all the LOB columns on ASSM tablespace are stored as SecureFiles.

Read Next: Oracle VPD implementation


 
This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial

  1. Oracle 11g Result Cache
  2. Compound Triggers in Oracle 11g
  3. Oracle 11g Invisible Index
  4. Oracle 11g Virtual Columns
  5. Oracle 11g Collections
  6. Oracle Functions and Procedures
  7. Oracle 11g Exception Handling
  8. Oracle 11g Read Only Tables
  9. Oracle 11g Subprogram Overloading
  10. Conditional compilation in Oracle PL/SQL
  11. Oracle Pragma
  12. UTL_FILE
  13. Manage Oracle dependencies
  14. Oracle 11g SecureFiles
  15. Oracle VPD implementation
  16. PL/Scope and PL/SQL Hierarchical Profiler
  17. Oracle Partitioning Overview
  18. Oracle Analytic Enhancements
  19. Oracle XML Storage
 

Comments

 

Ads

Exforsys Connect

Ads

Exforsys e-Newsletter

ebook