Archive for the ‘daily oddities’ Category

The short history is that we have a tablespace that was filled with 150G. Doing some cleaning and relocating shrunk this down to what should be zero. In reality, the tablespace could be dropped as it is no longer in use, but we’ll be using it again soon so I just wanted to reclaim a large portion of space.

So, the standard process –

alter database datafile ‘/u02/mydatafile.dbf’ resize 1000m;

but this returned:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 50
ORA-03297: file contains used data beyond requested RESIZE value

I checked dba_extents:

select owner, segment_name, segment_type from dba_extents where tablespace_name=’MY_TABLESPACE’;

and no rows returned.

Puzzled, I went to master Google and found this post by Connor McDonald:
which says that things don’t always show in dba_extents, one must also check dba_segments – which showed the bin remnants of prior objects.

Ah yes, flashback.. For him, the following purge worked:

purge recyclebin;

but the data remained and thus the datafile still wouldn’t resize.
The magic fix:

purge dba_recyclebin;

and the datafile resized with ease. *sigh*


Read Full Post »

we run regular queries against dba_data_files and dba_free_space to check tablespace growth/available space. they seem to have been running fine until seemingly out of nowhere, this query began to timeout on our monitoring setup.

select a.tablespace_name tbs
, NVL(b.free,0.0) free
, a.total total
,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 pct
from (
select tablespace_name,sum(bytes)/1024/1024 total
from dba_data_files group by tablespace_name) A
( select tablespace_name,sum(bytes)/1024/1024 free
from dba_free_space group by tablespace_name) B
ON a.tablespace_name=b.tablespace_name
order by 1

on further research, dba_data_files was fine, but any queries against dba_free_space were extremely slow..

purge recyclebin

fixed the issue immediately..as per Oracle docs, this is expected behaviour.

Read Full Post »

Trying to run:

ALTER TABLE my_table DROP CONSTRAINT my_constraint_pk;

ALTER TABLE my_table ADD CONSTRAINT my_constraint_pk PRIMARY KEY (my_col_1, my_col_2);

On 9i, this works fine. If memory serves, I believe it works fine on 10g as well. But on 11gR2 (not sure about R1), this statement results in:

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 51

ORA-00955: name is already used by an existing object

Though on both versions, an index is created by default, it ends up that on the newer version, it no longer drops the associated index when dropping the constraint.

So, in order to run this statement successfully, one must drop the index as well:

ALTER TABLE my_table DROP CONSTRAINT my_constraint_pk;

DROP INDEX my_constraint_pk;

ALTER TABLE my_table ADD CONSTRAINT my_constraint_pk PRIMARY KEY (my_col_1, my_col_2);

Read Full Post »

It has been almost a year since my last post. And in the meantime, the blog has supposedly reached 15,000 hits. Kind of crazy..

I’ve gotten into a very bad habit of beginning to write my encountered errors in a notepad with the intention of transferring them to the blog at a later date – which is a habit I now hope to break as the whole point of this blog was to a. have a central spot where I could easily look up all those things I say “i know i saw this before…what did i do to fix it???” – and b.pay back the many other blogs and posts that have shared experiences and assisted me in learning about Oracle and troubleshooting over the years.

So, jabbering aside – back to the purpose of the blog – and hopefully back to updating it in a timely manner when i encounter an issue..

Today’s issue:
I’ve been testing variations of a jdbc Data Guard failover connect string with an app we have.

The string:


Which – though I’ve used this string before, resulted in the app reporting:

SEVERE: There was a problem executing a database call.
java.sql.SQLException: Io exception: NL Exception was generated

A very helpful error..
I found this post – https://forums.oracle.com/forums/thread.jspa?messageID=2860787 mentioning that they received this error due to a missing parenthesis before the “DESCRIPTION”.
On further review, I too hit this error due to missing parenthesis -> the middle of this section “(PORT=1521))(CONNECT_DATA=”
should read “(PORT=1521)))(CONNECT_DATA=”

Read Full Post »

Another one that had me banging my head for a bit:

I was trying to start a database and received:

ORA-09925: Unable to create audit trail file
Linux Error: 27: File too large
Additional information: 9925

Fine..i go to the audit directory and indeed, it is quite full. So, I:

*cleaned it up and tried again
*checked the space with df -h
*verified directory permissions
*got rid of the spfile, changed the pfile’s audit location
*altered the sid to use a different pfile in that home
*went through the steps in 1227964.1 (all that remained was checking the inode)

After all of this with no luck, it suddenly dawned on me that perhaps, instead of using the directory listed in the (s)pfile, it’s just using the default directory (http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams014.htm). So I check one of the defaults and it indeed is also very full…Cleaned it up and all is well.

Read Full Post »

While trying to start the listener from a newly created listener.ora file, I received this error:

TNS-01192: Missing SID_LIST_ value left of equation for SID description in LISTENER.ORA

I have before seen issues resolved by too many spaces/not enough spaces in tnsnames and listener.ora files; nonetheless, I was doubtful of the fix proposed by anton1968 at 2:58am. But, adding a few spaces to my listener.ora file solved the problem..

Original (replace dots with spaces):

…..(SID_LIST =
……..(SID_DESC =
……..(SID_NAME = mydb)
……..(ORACLE_HOME = /u01/app/oracle/myhome)
……..(GLOBAL_DBNAME = mydb.cookie))

Updated version:

……..(SID_LIST =
……..(SID_DESC =
……..(SID_NAME = mydb)
……..(ORACLE_HOME = /u01/app/oracle/myhome)
……..(GLOBAL_DBNAME = mydb.cookie))

Read Full Post »

A simple error of sorts..

Upon trying to mount a new db, I received the following error:

ORA-01990: error opening password file
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

The error is somewhat misleading as it appears that it wants a file called ‘orapw’ – thus leading to the thought that perhaps a db_name is missing, or another, similar issue.

In reality, it has defaulted to ‘orapw’ as it can’t find the file it wants..

In this case, I had a file – orapwmysid, but for some reason, it didn’t like it. (Even though the case was proper as was the name..) I recreated the file and the db happily mounted.

I found this odd enough that I triple-checked the old and new names, but they were (as per the naked eye) identical.

So, a fix: check for typos and proper case..if nothing comes of that, recreate the file.

Read Full Post »

Older Posts »