:

RSSScripts

DBMS_SPACE.SPACE_USAGE

When using this procedure you must ensure that the  tablespace in which the segment resides has segment space management set to  AUTO. If it is manual you are likely to get the following error: ORA-10614: Operation not allowed on this segment Cause: This procedure can be used only on segments in  tablespaces with AUTO SEGMENT […]

Continue Reading

ORA-38792: encountered unknown flashback record

I was attempting to flashback my test database after having created the guaranteed flashback restore point in Oracle version 10g, and subsequently upgrading the DB to 11g. SQL> flashback database to restore point PRE_CHANGE_DB; flashback database to restore point PRE_CHANGE_DB * ERROR at line 1: ORA-38792: encountered unknown flashback record from release 11.0.0.0.0 According to […]

Continue Reading

ORA-00997: illegal use of LONG datatype

I had a requirement to keep track of the LOBs which were in the DB, their partition names and high values. We wanted to keep this information in a small lookup table so I first attempted to run a Create Table As Select (CTAS) statement, to which I received the following error: ERROR at line […]

Continue Reading

ORA-14160: this physical attribute may not be specified for a table subpartition

The ORA-14160 this physical attribute may not be specified for a table subpartition erro occurred when I forgot to add the comma and the end of each subpartition specification. 9 times out of 10 this error will be the result of a typo or incorrectly specified table definition. In the example below, commas should be […]

Continue Reading

NID-00107: File I/O error on datafile

I was using the NID utility the other day to change the name of a database that I had just restored. The restore had been carried out using data pump and the transportable tablespaces feature. When using NID I got the following error: NID-00107: File I/O error on datafile “R:\CONTROL01.CTL” during block 1 offset operation  (27047/kunidb1c) This […]

Continue Reading

Rename Oracle Database – NID Command Explained

You can use the NID command to change an Oracle database’s name and/or ID. This is useful, for example, when making a clone of a production database and wanting to use it in development. You may find that when using it you come across the NID-00107: File I/O error on datafile or perhaps the NID-00137: […]

Continue Reading

RMAN – A Cumulative Incremental Backup Problem?

We were using a cumulative incremental backup strategy in Oracle version 9.2.0.8, patchset 21. We thought that each cumulative level 1 backup plus the level 0 which was taken prior to it, would be sufficient to restore the database. However, we found out while restoring the database to a DEV environment that this is not […]

Continue Reading

File Added to Control File as ‘UNNAMED’ Because it was Created Under Name Already Used in this Database

Platform: Windows server 2003, SP2 Oracle: 10.2.0.4, patchset 3 (base release) The Problem You have a primary database and a physical standby database which has the standby_file_management parameter set to AUTO. You have been adding files to your primary database, and are expecting them to be added to the physical standby database automatically. Unfortunately, you notice […]

Continue Reading

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

If you experience the following error messsage when starting up your database, try changing the “COMPATIBLE” parameter to a 5 digit one. For example, I was running Oracle 11g and originally the COMPATIBLE parameter was set to 11.2.0. I changed it to 11.2.0.3.0, attempted another STARTUP MOUNT command and it worked without a problem. SQL> […]

Continue Reading

Change the RMAN Date Format

Question: I’m using Oracle’s RMAN and when I run scripts the date format is truncated, but I want to know the exact time my script started and stopped. Answer: Follow one of the below, depending upon which platform you are using. RMAN Date Format on UNIX Use the SETENV command to set the NLS_DATE_FORMAT variable […]

Continue Reading