Unable to load oci.dll

Had a problem in office with the oci.dll

In short, the oracle folder in the server has not enough permission. Perhaps, there is enough permission but somehow it is nto inherited down to the sub folders etc.

Found a fix and it is simply to re-apply the permission for the folder.

Steps below

Information from here.

1. Log on to Windows as a user with Administrator privileges.
2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME folder. This is typically the "Ora92" folder under the "Oracle" folder (i.e. D:\Oracle\Ora92) in Oracle9iR2 or "Client_1" folder under the "Oracle\product\10.1.0" folder (i.e. D:\Oracle\product \10.1.0\Client_1) in Oracle10gR1.
3. Right-click on the ORACLE_HOME folder and choose the "Properties" option from the drop down list. A "Properties" window should appear.
4. Click on the "Security" tab of the "Properties" window. NOTE: If you are running Windows XP and do not have a "Security" tab please do the following:
(a) Open "Folder Options" in your Control Panel. (
(b) Choose the "View" tab.
(c) Under "Advanced Settings" uncheck the option "Use simple file sharing (Recommended)". The "Security" tab should now be available.
5. Click on "Authenticated Users" item in the "Name" list (on Windows XP the "Name" list is called "Group or user names").
6. Uncheck the "Read and Execute" box in the "Permissions" list under the "Allow" column (on Windows XP the "Permissions" list is called "Permissions for Authenticated Users").
7. Re-check the "Read and Execute" box under the "Allow" column (this is the box you just unchecked).
8. Click the "Advanced" button and in the "Permission Entries" list make sure you see the "Authenticated Users" listed there with: Permission = Read & Execute Apply To = This folder, subfolders and files If this is NOT the case, edit that line and make sure the "Apply onto" drop-down box is set to "This folder, subfolders and files". This should already be set properly but it is important that you verify this.
9. Click the "Ok" button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.
10. Reboot your computer to assure that these changes have taken effect.
11. Re-execute the application and it should now work.
NOTE: If the problem continues to occur, please go back through these same 11 steps but when performing step 8 additionally check the box entitled "Replace permission entries on all child objects with entries shown here that apply to child objects" and be sure to reboot in step 10.

10G oracle recycle bin

SQL> show recyclebin
SQL> purge table <>
SQL> purge table "<>"

Selection of 2 fields to output as 1 field

You can simply pipe the selection so as to output the 2 or multiple fields into 1 field.

Example
Select FNAME || ' ' || LNAME AS NAME from USERS.

This will output one NAME only.

Oracle import script for data export and import only

Just a little script to import from an export dump file.
Note that the below script is based on a simple export dump from a list of generated table names. A dump file and a control file, named .dmp and .ctl, will be generated. .ctl will be the list of the generated table names for use in exp

Feel free to copy if you need to use the script or drop me a comment and I try to get back to you if you need help.

Execution command : file.ksh


#!/bin/sh
#

option='f'
export ORACLE_HOME=/software/oracle/product/9.2.0.8
export PATH=${ORACLE_HOME}/bin:${PATH}

while [ $option = 'f' ]
do

option='p'
#prompt and check password
echo 'Enter DBO password'
read passwd

#try to connect to DB
#sqlplus operation silenced by nohup and diverting output to /dev/null
#file test.txt will only be generated if connection is possible
nohup sqlplus -s DBO/$passwd@${ORACLE_SID} <<> /dev/null
spool test.txt
select * from dual ;
spool off
EOF

#checking for text.txt, prompt for correct password again if not present.
if [[ ! -f test.txt ]]
then
echo 'Incorrect password. Please enter password again.'
option='f'
fi
done

rm -f test.txt

#Check dump and control file
#Simple file ops to check for file. If files do not exist, stop operation.
if [[ ! -f $1.dmp ]]
then
echo 'Missing dump file ' $1.dmp
exit ;
fi

if [[ ! -f $1.ctl ]]
then
echo 'Missing control file ' $1.ctl
exit ;
fi

#disable constraints
#doing a sqlplus connection and selecting only relational constraints from the table that are enabled only.
#Choosing enabled only to reduce time in disabling disabled constraints already.
echo 'Disabling Constraints'
nohup sqlplus DBO/$passwd@${ORACLE_SID} <<> /dev/null
spool alter.sql
set pages 1000
SELECT 'alter table '||table_name||' disable novalidate constraint
'||constraint_name||' cascade;'
FROM user_constraints where CONSTRAINT_TYPE='R' and STATUS='ENABLED' ;
spool off
start alter.sql
EOF

rm alter.sql

#delete data
# using the original generated list of table names.
# the format of the list is
# table ={
# , table1
# , table2
# }
#script to extract table names only and to add delete statements
echo 'Generating delete data'
grep , #1.ctl | cut -c 3- > list.txt
echo "Prompt Generate list" > remove.sql
while read table
do
echo "delete from " $table " ;" >> remove.sql
echo "commit ;" >> remove.sql
done < list.txt

echo "Deleting Data"
nohup sqlplus DBO/$passwd@${ORACLE_SID} <<> /dev/null
start remove.sql
EOF

rm remove.sql
rm list.txt

#import data
echo 'Importing starts'
imp DBO/$passwd@${ORACLE_SID} FULL=Y CONSTRAINTS=N INDEXES=N GRANTS=N STATISTICS=NONE IGNORE=Y FILE=$1.dmp LOG=a.log

echo 'Enabling Constraints'
#enable constraints
# same as disabling, only enable disabled relational constraints
nohup sqlplus DBO/$passwd@${ORACLE_SID} <<> /dev/null
spool alter.sql
set pages 1000
SELECT 'alter table '||table_name||' enable novalidate constraint
'||constraint_name||' ;'
FROM user_constraints where CONSTRAINT_TYPE='R' and STATUS='DISABLED' ;
spool off
start alter.sql
EOF

rm alter.sql

echo 'End of importing'

Oracle how to check any resources being locked by any process

et lines 100 pages 999
col username  format a20
col sess_id  format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
 , 0, 'Not Blocking'
 , 1, 'Blocking'
 , 2, 'Global') status
, decode(v.locked_mode
 , 0, 'None'
 , 1, 'Null'
 , 2, 'Row-S (SS)'
 , 3, 'Row-X (SX)'
 , 4, 'Share'
 , 5, 'S/Row-X (SSX)'
 , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where  v.object_id = d.object_id
and  v.object_id = l.id1
and  v.session_id = s.sid
order by oracle_username
, session_id

Oracle hangs during importing

When an import session hangs, it is important that the underlying problem is
identified to be able to solve the problem.

First identify the session in the database serving the import :

select sid, user, program
from v$session
where upper(program) like '%IMP%';

Then verify the wait events for that session :

select sid, event, state, wait_time, seconds_in_wait
from v$session_wait
where sid = ;

This query will list the event that is being waited on, hence it will reveal
the underlying problem. Then act accordingly to solve the problem. Typically
this will be events like :

Too many server connections

Keep having this problem of logging into remote server and too many connections already.

A quick google on the issue revealed to me only just 2 connections are available at any time. So pathetic. Found a way to terminate inactive connections, credits here.

How to check Oracle version in Database

Login to database and type the below query

SELECT * FROM V$VERSION