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 - 3

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

Oracle 11g SecureFiles

Case 3: DB_SECUREFILE = FORCE

The option FORCE for the DB_SECUREFILE parameter forces all LOB columns to be created as SecureFiles only, irrespective of file type specification. Now since SecureFiles can reside on ASSM tablespace only, non ASSM tablespace cannot be specified for LOB creation in any way. For non ASSM tablespace, system raises exception. Check this in the below illustration.

Sample Code
  1. ----Set the DB_SECUREFILE parameter as ALWAYS----
  2. SQL> ALTER SYSTEM SET DB_SECUREFILE=FORCE;
  3.  
  4. System altered.
  5.  
  6. ----Create table with SecureFile column on ASSM tablespace----
  7. SQL> CREATE TABLE FORCE_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. ----Create table with SecureFile column on non ASSM tablespace----
  16. SQL> CREATE TABLE FORCE_SECUREFILE_2
  17.   2  (DOC BLOB)
  18.   3  LOB(DOC) STORE AS SECUREFILE
  19.   4  TABLESPACE SYSTEM
  20.   5  /
  21. CREATE TABLE FORCE_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 FORCE_BASICFILE_1
  28.   2  (DOC BLOB)
  29.   3  LOB(DOC) STORE AS BASICFILE
  30.   4  TABLESPACE USERS
  31.   5  /
  32.  
  33. TABLE created.
  34.  
  35. ----Create table with BasicFile column on non ASSM tablespace----
  36. SQL> CREATE TABLE FORCE_BASICFILE_2
  37.   2  (DOC BLOB)
  38.   3  LOB(DOC) STORE AS BASICFILE
  39.   4  TABLESPACE SYSTEM
  40.   5  /
  41. CREATE TABLE FORCE_BASICFILE_2
  42. *
  43. ERROR at line 1:
  44. ORA-43853: SECUREFILE lobs cannot be used IN non-ASSM tablespace "SYSTEM"
Copyright exforsys.com


Now, check whether the SecureFile feature has been enabled for the above tables or not.

Observe that SecureFile has been enabled for both the tables, which are created on ASSM tablespace. For the rest, Oracle strictly prevents the creation of BasicFile columns.

Case 4: DB_SECUREFILE = NEVER

This mode works as reverse of ALWAYS and FORCE mode. It prevents the creation of SecureFile LOBs and all of them shall be treated as BasicFiles only. The SECUREFILE keyword is a passive or obsolete specification under this mode. Oracle doesn’t minds even specifying a non ASSM tablespace with SecureFile LOB column.

Sample Code
  1. ----Set the DB_SECUREFILE parameter as ALWAYS----
  2. SQL> ALTER SYSTEM SET DB_SECUREFILE=NEVER;
  3.  
  4. System altered.
  5.  
  6. ----Create table with SecureFile column on ASSM tablespace----
  7. SQL> CREATE TABLE NEVER_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. ----Create table with SecureFile column on non ASSM tablespace----
  16. SQL> CREATE TABLE NEVER_SECUREFILE_2
  17.   2  (DOC BLOB)
  18.   3  LOB(DOC) STORE AS SECUREFILE
  19.   4  TABLESPACE SYSTEM
  20.   5  /
  21.  
  22. TABLE created.
  23.  
  24. ----Create table with BasicFile column on ASSM tablespace----
  25. SQL> CREATE TABLE NEVER_BASICFILE_1
  26.   2  (DOC BLOB)
  27.   3  LOB(DOC) STORE AS BASICFILE
  28.   4  TABLESPACE USERS
  29.   5  /
  30.  
  31. TABLE created.
  32.  
  33. ----Create table with BasicFile column on non ASSM tablespace----
  34. SQL> CREATE TABLE NEVER_BASICFILE_2
  35.   2  (DOC BLOB)
  36.   3  LOB(DOC) STORE AS BASICFILE
  37.   4  TABLESPACE SYSTEM
  38.   5  /
  39.  
  40. TABLE created.
Copyright exforsys.com


Ads

Checking the results in the USER_LOBS view, all the above tables have non SecureFile column.

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