Scripts
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 […]
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 […]
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 […]
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 […]
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 […]
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: […]
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 […]
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 […]
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> […]
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 […]
