How to drop an Oracle database without DBCA

If, when you come to remove an Oracle database, you find that DBCA is not available perhaps due to some installation issue or missing files then do not fret as it is still possible to remove the database by following these steps… 1 – Set the Oracle SID D:\Oracle\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=TESTDB 2 – Connect to the database and verify the name D:\Oracle\product\11.2.0\dbhome_1\BIN>sqlplus […]

Kill Oracle processes in Unix

Found this article from Donald Burleson (Burleson Consulting) on his website which gives a very good example of how to identify and kill processes associated with a specific ORACLE_SID. ps -ef|grep $ORACLE_SID| grep -v grep|awk ‘{print $2}’|xargs -i kill -9 {}  Check out the original article and take a look around his website as there are other useful gems there.

ERROR: Cannot find the file bin\ONSCLIENT.dll in the ORACLE_HOME

Trying to use the deinstall tool packaged with the Oracle client on one particular server failed and displayed the following output. C:\oracle\product\11.2.0\client_2\deinstall>deinstall Checking for required files and bootstrapping … Please wait … ERROR: Cannot find the file bin\ONSCLIENT.dll in the ORACLE_HOME C:\oracle\product\11.2.0\client_2. You must download the standalone version of the deinstall tool from OTN to deconfigure/deinstall this Oracle Home. cannot […]

How to Enable/Disable Archive Log Mode in Oracle 11g

It is advisable to backup your database before either enabling or disabling archive log mode as a precaution. Enabling archive log mode Verify the current archive log mode. We can see that database log mode is “No Archive Mode”. Also worth noting is the destination used for archiving. In order to see where that location is mapped to we can […]

SQLTools: “OCI8: Cannot allocate OCI handle” when starting

When starting SQLTools, a colleague was getting an error message “OCI8: Cannot allocate OCI handle”. Helping to investigate this, I found some guidance on the web with suggestions such as: Run the app with Administrator privileges Delete the OCI.DLL Oracle DLL from the Windows directory Neither of the above worked (the OCI.DLL wasn’t even present in my Windows folder), or any others […]

SnipIT No 4: Run an Oracle SQL script from DOS prompt

SnipIT is a series of short blogs containing useful information in an easily digestible format. To run an Oracle SQL script file from a DOS prompt (Command Line) you execute sqlplus passing credentials and the database name along with the script to run. @sqlplus.exe <username>/<password>@<database name> @<script filename> @sqlplus.exe scott/[email protected] @browseUser.sql Additionally, you can pass values to the script being […]

ORA-19502: write error on file

Description: Adding a new tablespace threw the following Oracle error, the sql file contained the following statement: PROMPT Creating Tablespace TS_SCHMASK2 create tablespace TS_SCHMASK2 datafile ‘/data/oradata/FTEST2/TS_SCHMASK201.DBF’ SIZE 32000M REUSE autoextend on next 1000M, ‘/data/oradata/FTEST2/TS_SCHMASK202.DBF’ SIZE 32000M REUSE autoextend on next 1000M, ‘/data/oradata/FTEST2/TS_SCHMASK203.DBF’ SIZE 32000M REUSE autoextend on next 1000M, ‘/data/oradata/FTEST2/TS_SCHMASK204.DBF’ SIZE 20000M REUSE autoextend on next 1000M; Symptom: SQL*Plus: Release […]

How to identify the Tablespace names from an EXPDP file

If you have been given a datapump dump file to import you may not have been given the export log file or a list of schemas and tablespaces contained in that file. Fortunately all is not lost, datapump provides an option to create a text-based file containing the SQL DDL statements used to import the file without actually executing them. The […]