The Latest Oracle Posts
How to Move Oracle Datafiles
You can move your oracle data files, temp files and redo log files very easily using commands which dynamically create sql from sql within a pl/sql block. This approach has many advantages, such as very little manual work or risk of the scripts being out of date if the rename commands were created in advance. Steps to […]
Drop TEMP File in the Oracle Database
Unlike Oracle datafiles which make up a tablespace, it is possible to drop an individual TEMP file from a TEMPORARY type tablespace. You can use statement: ALTER DATABASE TEMPFILE ‘<tempfile_location>’ drop including datafiles; When doing this, sometimes the statement will execute but the tempfile won’t drop from the file system. You try to delete it […]
Wait for unread message on broadcast channel
When using data pump you will have a calling session which is the session that you log into the DB with when executing the impdp or expdb utility. Within the DB there will be one or more sessions which are called by this main data pump session; namely DWn. Where N is a number. I […]
How to logoff a User from a Windows Server
As with so many other things, there are multiple ways to do this. This is the easiest: 1. Log onto another server 2. START -> Administrative Tools -> Terminal Services Manager 3. right click on “All Listed Servers”, and select “Connect to Computer…” 4. Type the name or IP address of the server that you […]
TNS-12542: TNS:address already in use
We were receiving the TNS-12542 error message intermittently from an application server when attempting to connect to the DB. TNS-12542: TNS:address already in use The first thing to do is check that the ping is OK: Pinging 10.60.40.40 with 32 bytes of data: Reply from 10.60.41.55: bytes=32 time<1ms TTL=128 Reply from 10.60.41.55: bytes=32 time=-23ms TTL=128 […]
ORA-12154: TNS: could not resolve service name
5 Checks to Resolve The 12154 ORA Error I assume that you are using the TNSNaming method to connect to your databases, using a TNSNames.ora file for this article. Check 1 – Connectivty, IP The first thing to check out is that the server is up…So, open up a command prompt and use the PING utility to check that: […]
ORA-00054: resource busy and acquire with NOWAIT specified
The ORA-00054: resource busy and acquire with NOWAIT specified error message usually happens when there are active transactions against an object in the database which mean other sessions cannot get the lock they require on the object to complete their transaction. There are two ways that a transaction can attempt to obtain a lock in […]
ORA-04021: timeout occurred while waiting to lock object
You’re here because you want to resolve the ORA-04021 timeout error error. And below you will find one solution to that problem, so let’s dive straight in. The Most Likely Cause… The ORA-04021 timeout error is most likely caused by another session locking/using a package that you are attempting to execute. Personally, I came across this […]
TNS-12560: TNS:protocol adapter error
The Problem Running Oracle 11g database on a Windows platform you are trying to log onto the database using the listener and you receive the following error message: Fatal NI connect error 12560, connecting to: (LOCAL=NO) You decide to try a local connection, which just means that you connect directly to the database without going […]
User Privileges and Oracle Roles
I want as many Oracle privileges as possible… It’s common place for people to grant privileges to Oracle users without taking the time to work out exactly what privileges they actually need. For example, a developer comes along and says “I don’t have permission to create tables and sequences, plus I need some tablespace quota […]
