How to install OraOLEDB12.dll 64 bit / OraOLEDB11.dll 32 bit ? or How to solve "OraOLEDB.Oracle Provider is not registered" error?


32 Bit
-------
1. login into cmd prompt (using Run as Administrator option) and run the below command

cd C:\ORACLE\cli11gR2_32\bin

C:\ORACLE\cli11gR2_32\bin> c:\Windows\system32\regsvr32.exe OraOLEDB11.dll

Note:
Ensure the path variable was set and referring the location "C:\ORACLE\cli11gR2_32\bin"



64 Bit
--------

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

 

1. Download  "ODAC112040Xcopy_64bit"
2. Unzip it
3.login into cmd prompt (using Run as Administrator option) and run the below command

cd C:\Users\Downloads\ODAC112040Xcopy_64bit

C:\Users\Downloads\ODAC112040Xcopy_64bit>dir
 Volume in drive C is System Volume
 Volume Serial Number is DC2F-3B02

 Directory of C:\Users\Downloads\ODAC112040Xcopy_64bit

04/11/2017  05:10 PM    <DIR>          .
04/11/2017  05:10 PM    <DIR>          ..
01/06/2014  03:55 PM    <DIR>          asp.net
01/06/2014  03:56 PM    <DIR>          asp.net4
10/23/2013  04:36 PM            26,323 configure.bat
10/23/2013  04:21 PM            11,538 install.bat
01/06/2014  04:00 PM    <DIR>          instantclient_11_2
01/06/2014  03:46 PM    <DIR>          network
01/06/2014  03:57 PM    <DIR>          odp.net20
01/06/2014  03:58 PM    <DIR>          odp.net4
01/06/2014  03:59 PM    <DIR>          oledb
01/06/2014  03:59 PM    <DIR>          oramts
01/06/2014  03:42 PM             8,766 readme.htm
01/03/2014  03:10 PM            14,042 unconfigure.bat
01/06/2014  04:00 PM            20,676 uninstall.bat
               5 File(s)         81,345 bytes
              10 Dir(s)  282,921,463,808 bytes free

C:\Users\Downloads\ODAC112040Xcopy_64bit>INSTALL ALL “C:\Oracle\Oracle64Driver” Oracle64Driver TRUE

4. copy the content of instantclient folder into to bin folder

All about Linux swap space

Refer : https://www.linux.com/news/all-about-linux-swap-space

How to stop currently running oracle scheduled jobs?

begin
   dbms_scheduler.stop_job('<JOB_NAME>',true);
end;


example

begin
   dbms_scheduler.stop_job('PRJ_EMP_UPDATE',true);
end;

once after stopping the job, we can disable it from the PL/SQL developer.

How to identify current running oracle scheduled jobs?

SELECT * FROM USER_SCHEDULER_RUNNING_JOBS;

How to identify current consumption of memory (UGA and PGA) ?

UGA - User Global Area or Session memory
PGA - Process Global Area


select name, sum(value/1024/1024) "MB" from v$statname n, v$session s, v$sesstat t  where s.sid = t.sid
and n.statistic# = t.statistic# and s.TYPE = 'USER' and s.USERNAME is not null and n.name in ('session pga memory', 'session uga memory')
group by name

reason for ORA-12008: error in materialized view refresh path ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

ORA-12008: error in materialized view refresh path

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP



The above error is due to lack of TEMP space ...



To identify the used and free space




SELECT   A.tablespace_name tablespace, D.mb_total,
 SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
 D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
 FROM     v$sort_segment A,
 (
 SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
 FROM     v$tablespace B, v$tempfile C
 WHERE    B.ts#= C.ts#
 GROUP BY B.name, C.block_size
 ) D
 WHERE    A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;





To fix it need to increase the size...



.  Temporary fix

a) Resize temporary file as
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;

or

b) Add temp datafile to temporary tablespace as

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/VIS11i/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 1024M;

.

Root Cause Analysis

1.  Identify temp segment usages per session

—
— Temp segment usage per session.
—
SQL> SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

2. Identify temp segment usages per statement
—
— Temp segment usage per statement.
—
SQL> SELECT  S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
Depending on outcome of temp segment usage per session and per statement focus on problematic session/statement.


reference : http://onlineappsdba.com/index.php/2009/07/27/ora-1652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/

How to grant SYSDBA privilege to an user?

grant connect ,resource,DBA to USER