File Permissions in Unix - Numeric Representation

chmod 777 file_name - gets you full access to everybody in Unix. What does 777 mean?
First digit is OWNER, Second digit is GROUP and the Third is OTHERS.

So, if a file has rwx-x-x which means, Owner can Read, Write an Execute (rwx) and the Group and the Other can only Execute (x). Below chart represents the numbers (like 755, 644) and its meaning.

Octal digit	Text equivalent	Binary value	Meaning
0	---	000	All types of access are denied
1	--x	001	Execute access is allowed only
2	-w-	010	Write access is allowed only
3	-wx	011	Write and execute access are allowed
4	r--	100	Read access is allowed only
5	r-x	101	Read and execute access are allowed
6	rw-	110	Read and write access are allowed
7	rwx	111	Everything is allowed

As you see that "1" stands for execute only, "2" stands for write only, "4" stands for read only. To combine the permissions you can simply add 1, 2 and 4 to get a needed combination. For instance, to get read and write permissions, you add 4 (read) and 2 (write), thus getting 6 (read and write). To get read and execute permissions, you add  4 (read) and 1 (execute), thus getting 5 (read and execute).

PS: Adding this here for my quick reference not that the world doesn't know about it :)

How to find Cluster Interconnect IP in RAC

Couple of ways around to find the Interconnect IP Address defined in RAC environment.

First thing would be to look in /etc/hosts to see which IP is assigned based on the comments or naming conventions.

Or check ASM Alert logs for the wording "Cluster Communication is configured to use the following interface(s)"

Or
Run the following sql:

select inst_id, ip_address from gv$cluster_interconnects;

ORA-13516: AWR Operation failed: only a subset of SQL can be issued

First started noticing that OEM is not showing any stats in other words no change in the session activity...
Digging into more found that the error "ORA-13516: AWR Operation failed: only a subset of SQL can be issued" showing up.

So, tried to create a snapshot manually and got the same error:

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-13516: AWR Operation failed: only a subset of SQL can be issued
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 10
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 33
ORA-06512: at line 1

Looking into metalinks note#1226534 its apparent that either you have to run CATAWR.SQL or just bounce the instance (well, in my case its RAC database so no need to bounce the entire db but just the instance). 

Bounced the instance and now everything is running smooth again...

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

ORA-00245: control file backup operation failed

If you are getting the error "ORA-00245: control file backup operation failed" in 11gR2 then the solution is to use the backup location which is a Shared Location (which can be seen by all Nodes within the Cluster) for any form of controlfile backups.

Here is the Metalink Notes (ID: 1472171.1) that describes about this New Feature in 11g Release 2 RAC Environment:

Excerpt from Metalinks for whom they do not have access to metalinks:


Oracle Server - Enterprise Edition - Version 11.2.0.1.0 and later
Information in this document applies to any platform.
This issue is only applicable to RAC database.

From 11gR2 onwards, the controlfile backup happens without holding the controlfile enqueue. For non-RAC database, this doesn't change anything. But for RAC database, due to the changes made to the controlfile backup mechanism in 11gR2, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances.

The snapshot controlfile MUST be accessible by all nodes of a RAC database, if the snapshot controlfile does not reside on a shared device error will be raised at the time of RMAN backup while taking snapshot of controlfile.

This applies to backing up controlfile using sqlplus / having autobackup of controlfile configured on non
shared location.

Description

1. In RAC environment controlfile autobackup fails with ora-0245
Autobackup of controlfile in RMAN is failing with error:
RMAN-571: ===========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-571: ===========================================================
RMAN-3009: failure of Control File and SPFILE Autobackup command on
ORA_DISK_1 channel at 10/27/2010 12:13:31
ORA-245: control file backup operation failed

2. In RAC environment, backup controlfile to non shared location fails
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/home/rac1122/test/control.bk' REUSE
*
ERROR at line 1:
ORA-245: control file backup operation failed

3. In RAC environment backing up standby controlfile to non shared location fails
SQL> alter database create standby controlfile as '/home/oracle/renostdbycntrl.ctl';
alter database create standby controlfile as
'/home/oracle/renostdbycntrl.ctl'
*
ERROR at line 1:
ORA-245: control file backup operation failed

4. In RAC environment copy current controlfile to '${DB_BACKUP_DIR}/rac_tnctv_control.bak';
channel ch1: starting datafile copy
copying current control file
RMAN-571: ===========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-571: ===========================================================
RMAN-3009: failure of backup command on 10/07/2011 11:36:42 channel at ch1
ORA-245: control file backup operation failed

5. In RAC environment, Rman backup fails if snapshot controlfile is not in shared location.
 RMAN-00571: ========================================================
RMAN-00569: ============ ERROR MESSAGE STACK FOLLOWS =============
RMAN-00571: =========================================================
RMAN-03009: failure of resync command on default channel at 03/13/2012 10:19:41
ORA-00245: control file backup operation failed

Occurrence

Only affect Real application Cluster (RAC ), 11.2 specific.

Symptoms

 In RAC environment any form of controlfile backup may fail with ORA-0245 if the location of the Snapshot Controlfile is not a shared location.

The backup of the controlfile actualy makes a backup of the SNAPSHOT controlfile. The Snapshot controlfile is created when the controlfile is about to be backed up.

The Snapshot controlfile is a read-consistent copy of the controlfile.

Workaround

It is changed behaviour which requires that the snapshot controlfile in a RAC environment, is on a shared location. 

1. Check the snapshot controlfile location:

RMAN> show snapshot controlfile name;

2. Configure the snapshot controlfile to a shared disk:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/snapcf_.f';

Or in case of ASM use

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+/snapcf_.f';

Wait event: kksfbc child completion

Noticed this wait event "kksfbc child completion" in one of our database when a client complained that the report which was generally taking few seconds is hung today for more than hours!!!

When looked into the sessions, found that this session is waiting on "kksfbc child completion".

This is confirmed bug "6795880 - Session spins / OERI after 'kksfbc child completion' wait - superceded [ID 6795880.8]"

Bug Description:


This fix has been superseded by bug:8575528.

A session may go into an infinite spin just after a wait
for 'kksfbc child completion'. The spin occurs with
a stack including kksSearchChildList -> kkshgnc where
kksSearchChildList loops forever.

This problem can also lead to internal error such as any of
ORA-600 [kksSearchChildList1], ORA-600 [kksSearchChildList2]
ORA-600 [kksSearchChildList3], ORA-600 [kkshgnc-nextchild]

Or Trace dumps on kksSearchChildList4 .

Note:
  This fix is disabled by default in 10g.
  To enable this fix you must explicitly set the following
  parameter for instance startup:
    "_cursor_features_enabled" = 10

Metalinks Info on this bug:

Bug 6795880  Session spins / OERI after 'kksfbc child completion' wait - superceded

 This note gives a brief overview of bug 6795880. 
 The content was last updated on: 05-APR-2011
 Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 11.2
Versions confirmed as being affected	11.1.0.6 10.2.0.4 10.2.0.3
Platforms affected	Generic (all / most platforms affected)

 Note that this fix can cause / expose the problem described in Bug:8575528

 Note that this fix has been superceded by the fix in Bug:8575528 

Fixed:

This issue is fixed in

11.1.0.7 (Server Patch Set) 10.2.0.5 (Server Patch Set) 10.2.0.4 Patch 5 on Windows Platforms

Symptoms:	Related To:
Hang (Process Spins) Internal Error May Occur (ORA-600) ORA-600 [kksSearchChildList1] ORA-600 [kksSearchChildList2] ORA-600 [kksSearchChildList3] ORA-600 [kkshgnc-nextchild] Dump in or under kksSearchChildList4 Waits for "cursor: pin S" Waits for "kksfbc child completion"	This fix is DISABLED by default _CURSOR_FEATURES_ENABLED

How to find RAC Cluster Name and Version?

Run cemutlo command under grid home to get Cluster info such as name and version.

Options:

        -n prints the cluster name

        -w prints the clusterware version in the following format:

                 ::

How to Install Oracle TEXT manually in 10gR2

OK, By Default Oracle TEXT (CTXSYS Schema) is installed in the database if DBCA is used.

In case if its missed out and need to install manually then dont panic as installing Oracle Text is too simple as showed below:

Step#1:
Connect to the database as SYS and run:

SQL> @?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK

Above, CTXSYS is the password and SYSAUX is the default tablespace for user CTXSYS and TEMP is the temp tablespace and NOLOCK specifies that this account is not locked.

This will create CTXSYS schema with all objects.

Step#2:

The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in /ctx/admin/defaults directory, such as English(US), Danish(DK), Dutch(NL), Finnish(SF), French(F), German(D), Italian(IT), Portuguese(PT), Spanish(E), and Swedish(S). They are named in the form drdefXX.sql, where XX is the international license plate code.
To manually install US default preferences, for example, log into sqlplus as CTXSYS, and run 'drdefus.sql' as described below:

Connect as CTXSYS:

SQL> connect CTXSYS/password@your_DB
SQL> @?/ctx/admin/defaults/drdefus.sql

Step3#:
Grant CTXAPP Role to the developer who needs it along with any other EXECUTE Privs on CTXSYS Packages that are required.

Hope this helps.

ORA-03001: unimplemented feature

Change the current_schema to the actual table owner by connecting as some other user in sql*plus and try renaming the table:


SQL> alter session set current_schema=scott;
Session altered.
SQL> rename emp to emp_back;
rename emp to emp_back
*
ERROR at line 1:
ORA-03001: unimplemented feature

Now: Try running the following command to rename the same table being in the same session with current_schema=scott;

SQL> alter table emp rename to emp_back;
Table altered.
SQL>

How to get explain plan from Netezza?

explain verbose select * from tablename;

Importance of GROOM after Altering a Table...

In Netezza, when you update your table definition, like adding columns, Netezza creates a separate version of the table behind the scenes with the new schema. 

New records then get inserted into the new version. 

During query execution the multiple versions of the table are merged together using a UNION ALL operation.  

To merge the two versions of your table together do a GROOM TABLE tablename VERSIONS; 

How can I find the tables that are versioned? 


SELECT tablename
FROM _v_table
WHERE   RELVERSION<>0


Note:
If you have multiple versions of a table due to adding/dropping a column, you cannot use the groom command to clean up deleted rows until you first use the groom versions command to merge the multiple versions of the table into a single current version.

SRVCTL Commands List...

Here are the SRVCTL commands list summary. Click on the command to get more details from Ora Docs.

Command	Description
srvctl add	Adds the node applications, database, database instance, ASM instance, or service.
srvctl remove	Removes the node applications, database, database instance, ASM instance, or service.
srvctl config	Lists the configuration for the node applications, database, ASM instance, or service.
srvctl enable	Enables the database, database instance, ASM instance, or service.
srvctl disable	Disables the database, database instance, ASM instance, or service.
srvctl start	Starts the node applications, database, database instance, ASM instance, or service.
srvctl stop	Stops the node applications, database, database instance, ASM instance, or service.
srvctl modify	Modifies the node applications, database, database instance, or service configuration.
srvctl relocate	Relocates the service from one instance to another.
srvctl status	Obtains the status of the node applications, database, database instance, ASM instance, or service.
srvctl getenv	Displays the environment variable in the configuration for the node applications, database, database instance, or service.
srvctl setenv and unsetenv	Sets and unsets the environment variable in the configuration for the node applications, database, database instance, or service.

SCAN SRVCTL Commands

Here are the list of commands that are pertaining to 11gR2 SCAN:

SRVCTL Command	What it does
srvctl config scan	Shows the current SCAN configuration
srvctl config scan_listener	Shows the existence and port numbers for the SCAN listeners
srvctl add scan -n cluster01-scan	Adds new SCAN information for a cluster
srvctl remove scan -f	Removes SCAN information
srvctl add scan_listener	Adds a new SCAN listener for a cluster on the default port of 1521
srvctl add scan_listener -p 65001 ## non default port number ##	Adds a new SCAN listener on a different port
srvctl remove scan_listener	Removes the SCAN listener
srvctl modify scan -n cluster_scan	Modifies SCAN information (used when changing SCAN to DNS after initially using /etc/hosts)
srvctl modify scan_listener -u	Modifies the SCAN listener information to match the new SCAN VIP information from the modify scan command
srvctl status scan	Shows the status of SCAN
srvctl status scan_listener	Shows the status of SCAN_LISTENERs

Cannot load ICU resource bundle 'ggMessage', error code 2

Environment:
OS: Linux x86 32bit
Oracle: 11g 32bit
GoldenGate: 11.2.1.0.1

You get the following error when ggsci is initiated from any other directory other than its own installed directory.
------------------------------------------------------------------------------------------------------
/home/oracle:(san11g)$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (sanoralnx.sanora) 1> dblogin userid ggs_owner, password ggs_owner
Cannot load ICU resource bundle 'ggMessage', error code 2 - No such file or directory
Aborted
------------------------------------------------------------------------------------------------------

Solution:
Initiate ggsci from the directory where GoldenGate is installed and you will be fine:
------------------------------------------------------------------------------------------------------
/home/oracle:(san11g)$ cd $GG_HOME
/home/app/ggs:(san11g)$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (sanoralnx.sanora) 1> dblogin userid ggs_owner, password ggs_owner
Successfully logged into database.

GGSCI (sanoralnx.sanora) 2>
------------------------------------------------------------------------------------------------------

Setting up 10g Database with 11g SCAN

What is SCAN?
SCAN is Single Client Access Name with which you do not have to worry about sending out all your RAC Node information to your developers to add them into their TNS Entries... With SCAN, you just need the SCAN Address that goes in their TNS which internally maps to all your RAC Nodes. Read more in Oracle DOCS.

This cool feature is available only in 11g and not in 10g. If you are reading this then I would assume you have 10g database running in 11g RAC and you would like to make use of this SCAN Feature for 10g databases.

Here are the simple steps to integrate 10g Databases with 11g SCAN.
What you need is SCAN IP Addresses (remember just the host name is not enough for 10g).
Find the IP Addresses defined for SCAN by doing nslookup on the SCAN Host Name:

oragrid@racnode1 $ nslookup orascan.at.your.company.com 
Server: 10.1.10.111
Address: 10.1.120.111#53

Name: orascan.at.your.company.com
Address: 10.1.120.201
Name: orascan.at.your.company.com 
Address: 10.1.120.202
Name: orascan.at.your.company.com 
Address: 10.1.120.203


What you need is the above three IP addresses (might be more or less depending on your SCAN setup).

Step#1:
There should be a CRS service configured  in the OCR for clients to connect to this database.
Run the following and see if its registered and running fine:

$ srvctl status service -d DB10G 
Service DB10G_SCAN is running on instance(s) DB10G1, DB10G2

If you see the above results for your database then you are ready for Step#2. If not then do the following to add:

$ srvctl add service -d DB10G -s DB10G_SCAN -r DB10G1,DB10G2 --This will add the service

$ srvctl start service -d DB10G -s DB10G_SCAN --This will start the service. Once this is done run the above to check the status.

Step#2:
Add Local and Remote Listener entries in TNSNAMES.ora on each node where this database instance is running. Oh which Oracle Home TNSNAMES? I have 11g and 10g???. Add it in 10g Home TNSNAMES.ora as this is the entry that is used for 10g databases.

LISTENER_DB10G1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip.your.domain.com)(PORT = 1521))

LISTENER_DB10G2 =
   (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip.your.domain.com)(PORT = 1521))

LISTENERS_GRID =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.120.201)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.120.202 )(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.120.203 )(PORT = 1521))
   )
Note: please make sure to double check the entries in TNSNAMES.ORA thoroughly as any typos (and syntax with parenthesis) from the TNS and below parameter settings will gives you more trouble.

Step#3:
Now login into database instance(s) and add LOCAL_LISTENER and REMOTE_LISTENER parameters to spfile:

alter system set local_listener='LISTENER_DB10G1' scope=spfile sid='DB10G1'; -- on Node1
alter system set local_listener='LISTENER_DB10G2' scope=spfile sid='DB10G2'; -- on Node2
alter system set remote_listener='LISTENERS_GRID' scope=spfile sid='*'; --on Both Nodes

Step#4:
Since we only used scope=spfile in the above, we need to bounce the database to take those parameters effective. So, go ahead and bounce the database using either srvctl stop database or individually in each node.

Step#5:
Check the listener status (lsnrctl stat) and you should be seeing this new DB10G_SCAN (the name used in Step#1) entry with the instances.

Service "db10g_scan" has 1 instance(s).

  Instance "db10g1", status READY, has 1 handler(s) for this service...

Above is from the node1

You can also check the GRID Listener status for the scan from the GRID Home:

grid@racnode1 $ lsnrctl service LISTENER_SCAN1

Service "db10g_scan" has 2 instance(s).
  Instance "db10g1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST= racnode1-vip.your.domain.com )(PORT=1521))
  Instance "db10g2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST= racnode2-vip.your.domain.com )(PORT=1521))
The command completed successfully

Step#6:
Now, all server side setup is ready, move on the client side.
Here is the TNS Entry that will be used at the clients PC:

DB10G_SCAN =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST =  10.1.120.201 )(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST =  10.1.120.202 )(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST =  10.1.120.203 )(PORT = 1521))
    ) 
    (LOAD_BALANCE = yes)
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB10G_SCAN) #This is the name that you defined in Step#1
    )
  )

As you see, the actual RAC VIP nodes are not used in the HOST but the SCAN IP Addresses.

Hope this notes helped you...

DBCA would not offer to create RAC Database...

Environment:
11gR2 RAC with ASM
10G software installed to accommodate 10g Databases along with 11g in the ASM.


Once installed 10g and applied patch 10.2.0.5.6, DBCA will not offer to create a database which is RAC enabled. Basically it still thinks that 10g software is standalone to that local node.

OLSNODES shows all 4 nodes active but they are Un-Pinned:

+ASM1:11.2.0:/opt/oradev> olsnodes -t
ndhdbd1 Unpinned
ndhdbd2 Unpinned
ndhdbd3 Unpinned

Try pinning them as root:

#>/u01/app/11.2.0/grid/bin/crsctl pin css -n node1

Run the above for all other nodes.

+ASM1:11.2.0:/opt/oradev> olsnodes -t
ndhdbd1 Pinned
ndhdbd2 Pinned
ndhdbd3 Pinned

Now try DBCA and it will offer to create a database which is RAC Enabled.

E-Mail from Oracle Procedures...

UTL_SMTP is the package that can be utilized to send email from Oracle Procedures. Its nothing new as its been there since 8i!!!
Here is the simple code and an example how to use it:


CREATE OR REPLACE PROCEDURE send_mail
(p_to        IN VARCHAR2,
 p_from      IN VARCHAR2,
 p_message   IN VARCHAR2,
 p_subject   in VARCHAR2,
 p_smtp_host IN VARCHAR2,
 p_smtp_port IN NUMBER DEFAULT 25)
IS
  t_mail_conn   UTL_SMTP.connection;
BEGIN

  t_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(t_mail_conn, p_smtp_host);
  UTL_SMTP.mail(t_mail_conn, p_from);
  UTL_SMTP.rcpt(t_mail_conn, p_to);

  UTL_SMTP.open_data(t_mail_conn);
 
  UTL_SMTP.write_data(t_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(t_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.write_data(t_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(t_mail_conn);

  UTL_SMTP.quit(t_mail_conn);
 
END san_send_mail;
/

Example of using the above procedure to send an email:


BEGIN
  send_mail(p_to    => 'to.me@me.com',
            p_from      =>  'to.me@me.com',
            p_message   => 'This is a test message from send_mail procedure... ',
            p_subject   => 'Test MSG - for Email Notifications...',
            p_smtp_host => 'Your.SMTP.SERVER.Address.'); --check SMTP_OUT_SERVER parameter for this value
END;
/

Oracle BLOB to File Extract...

If you are using TOAD, then you don't need to look any further to export BLOB column data into a file.
Just write a simple select statement (select blob_column, reg_col as file_name from blob_table;) in Editor and execute the statement. Right Click in the Data Grid Once the records are fetched and chose option "Export Blobs" and follow the instructions you are good to go.

Well, depending on the resources available on your PC, TOAD might throw an error with Low Memory or other issues when the exporting BLOB entries are huge or number of records to fetch are more.

Here is the simple proc that can be utilized instead and store all these files on the database server and get'em later.
Make sure that you have write access to Oracle Directory that you are going to use.


CREATE OR REPLACE procedure blob2file
is
 t_blob   blob;
 vstart  number := 1;
 bytelen number := 32000;
 t_blob_len     number;
 my_vr   raw(32000);
 x       number;
 t_file_name varchar2(200);
 l_output utl_file.file_type;
begin

  for i in (
  select reg_col file_name,
         dbms_lob.getlength(blob_column) blob_len,
         blob_column blob_val
    from blob_table
  ) loop

  begin
      -- save blob length
      x := i.blob_len;
     
      -- define output directory
      l_output := utl_file.fopen('ORACLE_DIRECTORY_NAME_HERE', i.file_name, 'WB', 32760);

      -- if small enough for a single write
      if i.blob_len < 32760 then
        utl_file.put_raw(l_output,i.blob_val);
        utl_file.fflush(l_output);
      else -- write in pieces
        vstart := 1;
        while vstart < i.blob_len
        loop
          dbms_lob.read(i.blob_val,bytelen,vstart,my_vr);

          utl_file.put_raw(l_output,my_vr);
          utl_file.fflush(l_output);

          -- set the start position for the next cut
          vstart := vstart + bytelen;

          -- set the end position if less than 32000 bytes
          x := x - bytelen;
          if x < 32000 then
            bytelen := x;
          end if;
        end loop;
      end if;
      utl_file.fclose(l_output);
 
  end;
 
  end loop;

end blob2file;
/