Image/PDF data via DML

 

1.  Upload the mybook.pdf to IMAGES folder /tmp

2. Create directory in the database using Create directory images as ‘/tmp/’;

3.  Run this DML

DECLARE

    f_lob BFILE;

    b_lob BLOB;

BEGIN

    INSERT INTO dummy_table (BILLING_ID,

                                      BILLING_RUN_ID,

                                      BILLING_INFO_ID,

                                      STMT_DATA,

                                      CREATED_DATE)

    VALUES (myseqeunce.NEXTVAL,

            108,

            26753,

            EMPTY_BLOB (),

            TO_DATE ('31-AUG-2011'))

    RETURN STMT_DATA

    INTO   b_lob;

    f_lob := BFILENAME ('IMAGES', 'mybook.pdf');

    DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);

    DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));

    DBMS_LOB.fileclose (f_lob);

    COMMIT;

END;

Hope this help. Regards Rupam

Use of Message Broker

 Testing the use of Message Broker to the database and then to an object in another database via a database link. They received an ORA-24777 error. Metalink reveals this note:

Error "ORA-24777: Use Of Non-Migratable Database Link Not Allowed" Using Oracle XA Datasource When Executing a Select via Database Link. [ID 879543.1]

Which recommends configuring shared servers for network connections. To test this, I made the following changes in the spfile for database db1:

ALTER SYSTEM SET shared_servers=5 SCOPE=BOTH SID='*';

ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)' SCOPE=BOTH SID='*';

The listener now shows a shared server listener available for db1:

oracle@tiger  > lsnrctl services

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 11-OCT-2011 15:13:31

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

..snipped..

Service "db1" has 1 instance(s).

  Instance "db1", status READY, has 2 handler(s) for this service...

    Handler(s):

      "D000" established:592 refused:0 current:9 max:1022 state:ready

         DISPATCHER

         (ADDRESS=(PROTOCOL=tcp)(HOST=tiger)(PORT=23033))

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

developed a small test using Message Broker connecting to db1 and referencing an object in db2 via a database link. It worked. They did not get the ORA-24777 error and they did get the expected results.

Hope this help. Regards Rupam

swap area in database server

The command to see what’s available is:

free –m This will show you the free space in megabytes

Here’s the output:

             total       used       free     shared    buffers     cached
Mem:         48299      26358      21940          0        647      19719
-/+ buffers/cache:       5992      42306
Swap:         4095          0       4095

So, the swap line shows you that you have 4 gig of swap free…excellent.

For the amount of free memory, you want to look at the -/+ buffers/cache line. So, looking at that line, you have 21940 megabytes (21 gig) free.

Keep in mind the cached value and the buffers value will be reclaimed by the system is needed. You actually want a large cached value. I’m not sure what’s stored in it but….


Caution ! Running out  of SWAP area may  cause  the server to hang

Rupam

count # of processes for each database

Count number of client connections to oracle Database on a server

countproc.sh

#/bin/ksh

# count # of processes for each database

for i in ` pgrep -fl smon|awk -F_ '{print $3}'`

do

echo "database name : " $i

ps -ef|grep $i|grep LOCAL=NO |wc -l

done

Rupam

sudo

How to sudo to oracle account in solaris

/usr/local/bin/sudo /bin/su - oracle

Rupam