Ask the Experts

Ask the Experts

• Function is nested too deeply

  I am trying to run this query and I get an error: "ORA-00935: group function is nested too deeply." Please help me.  Continue Reading

• Cumulative quantities

  I have a table containing three fields: code, date, quantity. I want to know the date, quantity and cumulative quantity for the given code and period also.  Continue Reading

• Can a foreign key also be used as the primary key in the same table?

  Can a foreign key also be used as the primary key in the same table?  Continue Reading

• Cannot determine which trigger has COMMIT clause

  I am getting these errors: "ORA-00604: error occurred at recursive SQL level 1," "ORA-04092: cannot COMMIT in a trigger," "ORA-06512: at line 3." But when I try to determine which trigger has the COMMIT clause in it with the query, I have no row ...  Continue Reading

• Restricting access to database via trigger

  I'm trying to restrict access to a database via a trigger after logon. When we connect to the database through a tool like SQL*Plus, the name of that program appears in the column "program" of the view v$session. However, I've got a sly end user and...  Continue Reading

• "Input is too long" error

  Whenever I try to run a certain query in the SQL prompt, it gives the following error: "SP2-0027: Input is too long - line ignored." Please suggest how I can change the default character length so that I am able to run the query.  Continue Reading

• Data truncated after 4,000 characters

  You have given an answer to the following question: "Is it possible to do a UNION of two Oracles 8i views where there are CLOB fields involved?" But I found that the data is getting truncated after 4,000 characters if I follow your suggestion.  Continue Reading

• Unable to drop a user

  I'm not able to drop a user. I get the errors "ORA-00604: error occurred at recursive SQL level 2," "ORA-20001: You did not want this object to be dropped, contact DBA check_beforedrop," and "ORA-06512."  Continue Reading

• Packaging project built in Oracle 9i/Forms 6i

  How to package a project built in Forms 6i/Oracle 9i? Is there an alternative to Oracle File Packager?  Continue Reading

• ORA-01078: failure in processing system parameters

  I'm getting the error message "ORA-01078: failure in processing system parameters."  Continue Reading

• Retention policy in RMAN

  I have several Oracle 8i and Oracle 10g database instances on a Windows 2003 server. We use RMAN backups on these servers. Oracle 9i has the CONFIGURE RETENTION POLICY TO RECOVERY WINDOWS OF 7 DAYS parameter. What parameter can I use on the Oracle ...  Continue Reading

• Transferring tablespace to another database

  I want to transfer one tablespace from one database to another database by using the command for export and import. The target database does not have a tablespace named users. Do I have to create one tablespace with the name users in the target ...  Continue Reading

• Export errors with Oracle 7 on SCO OpenServer

  We have Oracle 7 on SCO OpenServer 5.0.5. We export data in full exp system/manager and the other options are default. But when we import this data we have too many errors. Please help us to find the solution.  Continue Reading

• Using exp80 and imp80

  Could you please help me with the step-by-step procedure of using exp80 and imp80 for export and import in Oracle8? Or suggest a Web site where I can get this information?  Continue Reading

• Consistent vs. inconsistent backup

  What are consistent and inconsistent backups?  Continue Reading

• Using Oracle trace files

  How can we generate and use SQL trace files, and how they can be helpful in SQL tuning?  Continue Reading

• Time taken to execute query

  Suppose I execute a query like "select * from tab." This statement will give some names of tables. I want to know the time taken for the execution of this statement.  Continue Reading

• Error at recursive SQL level

  I am getting these errors: "ERROR: ORA-00604: error occurred at recursive SQL level 1"; "ORA-04092: cannot COMMIT in a trigger"; "ORA-06512: at line 3."  Continue Reading

• Task-level expenditure transaction control has been violated

  I am new to Oracle, and therefore I do not know how to handle the following error message when I am doing data entry into my Oracle database: "APP-PA-19268: Task-level expenditure transaction control has been violated."  Continue Reading

• Problem loading comma-separated values

  I have a problem using comma-separated values to load into an Oracle table using SQL Loader. Due to the presence of a comma in the address provided in the datafile, I am unable to load it successfully.  Continue Reading

• Maximum open cursors exceeded

  I've got two procedures in order to insert and append content to a BLOB field. In my code I call these procedures to insert content bigger than 4,000 characters. I call the second procedure add_more( p_text in raw ) in a loop. If the loop is ...  Continue Reading

• Recovering .dbf files without .dmp files

  I have a problem to recover .dbf files from one server (which already crashed) to another server. We don't have any .dmp files. We only have the .dbf and .ctl plus all files in the /oracle/admin folder.  Continue Reading

• Errors with ARCserve backup

  My environment is BrightStor ARCserve Backup 11.5 SP1 on RHEL 4.0 with Oracle 9i. While backing up the Oracle database, I got the following errors. Please help and let me know if it is a problem with my tape drive, ARCserve or OS.  Continue Reading

• Taking backup with only DBF files

  Can I take up a whole database backup using only DBF files? Because we don't have a .dmp file for that database, but we have DBF files or control files for it.  Continue Reading

• Taking backup of table structures without content

  What is the command to export only the schema without the database in Oracle9i? I want to take a backup of the table structure only, not the contents of the table. We have nearly 500 tables in our database.  Continue Reading

• What do we do after installing patch 9.2.0.6.0?

  After installation of patch 9.2.0.6.0 which SQL command do we need to run?  Continue Reading

• Upgrading from 8i to 10g with import utility

  I would like to upgrade from 8.1.7 to 10g. How to use the import utility to import the 8i dump to the 10g database?  Continue Reading

• OIP-00005 error

  We are migrating our client from Oracle 8.5 to Oracle 9.2. On executing one of the procedures, we receive the following error: "OIP-00005 (Variable is not a)."  Continue Reading

• Need higher JRE version?

  I am trying to run the Oracle Universal Installer (version 10) on a Solaris 10 box and get the following error: "The Java RunTime Environment was not found at bin/java. Hence, the Oracle Universal Installer cannot be run. Please visit http://...  Continue Reading

• Configuring RAC on Windows laptop

  I would like to configure Oracle 9i RAC on my Windows XP laptop. I already have three 9i personal instances running on there. I do not have any external hard drive. Let me know how to go about this.  Continue Reading

• Character set name is not recognized

  Would you please guide me on why I am getting these errors when I try to run the below script? ORA-06550: line 1, column 29: PLS-00553: character set name is not recognized, ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated.  Continue Reading

• Creating different mount points for production databases

  We are in the process of setting up production databases. There are three databases, db1, db2 and db3, for three different applications. We are supposed to create three different mount points, like /db1, /db2 and /db3, and install the Oracle ...  Continue Reading

• Best tool for monitoring Oracle database

  For monitoring the Oracle database, which is the best tool, OEM Management Server or BMC Patrol for Oracle?  Continue Reading

• Method other than DB link for large data transfer

  We are working on the development of a datamart (in 9i) which takes data from two source systems. Since this is a transaction system, there is a lot of data. Currently we are doing incremental loads using a spool file and external tables. But the ...  Continue Reading

• Using backup taken on Windows to restore Linux database

  I am running a database on Windows and one on Linux. I have taken a backup of the database from the Windows system. Will this backup be utilized to restore the database running on Linux? If yes, can you tell me the procedure?  Continue Reading

• Header verification failed

  I am using an Oracle dump from Sun Solaris and putting it on a Windows 2003 server. The following error gets generated: "Not a valid export file. Header verification failed." Please let me know what the problem could be.  Continue Reading

• Scheduling automatic RMAN backups

  We have configured RMAN at our organization and we want to automatically schedule an RMAN backup every day at 5:00 a.m. Is there is any procedure or command to schedule our RMAN backups automatically?  Continue Reading

• Perl script calling SQL scripts with SQL*Plus

  I have a Perl script that calls several SQL scripts by using SQL*Plus. Whenever there is an error in the SQL script, it returns a value to the Perl that stops the job from running. When the database is down, SQL*Plus does not return any execution ...  Continue Reading

• Switching to auto space management

  I am operating Oracle 10g. Some of my tablespaces are on manual space management. I want to migrate segment space management from manual to auto.  Continue Reading

• Query to find user who is locking record from rowid

  I have the rowid of a locked record in a specified table in Oracle 10g. I need to know the username of the user locking the record from the rowid that I have. Which query can be used to get such information?  Continue Reading

• Converting clob to string

  I'm new to Oracle and JDBC. I am using the thin client to retrieve clobs from Oracle 10g using a query. I then want to convert the clob to a string for display. Any sample code out there for me to see how this may be done?  Continue Reading

• Data migration with ETL

  I am new to Oracle's ETL tool and I need information on data migration using Oracle ETL.  Continue Reading

• Invalid identifier error

  Can anybody help me with this?: ERROR at line 5: ORA-00904: "MAXAWARDS"."ROLE": invalid identifier  Continue Reading

• How does DBMS_STATS work?

  How does DBMS_STATS work? I want to know what really happens when collecting statistics using DBMS_STATS. How does Oracle get the number of rows from a table?  Continue Reading

• Using Data Pump to move a database from HP-UX to Linux

  Because of the high cost of HP-UX machines, we plan to upgrade and move our current 9i database to 10g on a Linux platform. The current production database is about 200GB, and using imp/exp may take a long time in Oracle9i. Therefore, we are ...  Continue Reading

• Migrating Oracle from HP-Unix to AIX

  We are migrating Oracle from HP-Unix to IBM AIX 5.3. We have been experiencing a lot of errors; some tables have not migrated properly. What are the key areas we must ascertain for a successful migration?  Continue Reading

• Oracle 8.1.6 compatible with Windows 2003 Server?

  Is Oracle 8i (8.1.6) compatible with Windows 2003 R2 Server Standard Edition?  Continue Reading

• Applying 9.2.0.6 patch

  Can you describe in detail the steps to apply a patch on Oracle9i Rel2? I am trying to apply the 9.2.0.6 patch on 9.2.0.2.  Continue Reading

• DIM-00019 error

  I have installed Oracle9i on Windows 2000. When I start the Oracle Database Assistant I am getting the "DIM-00019: message 19 not found" error.  Continue Reading

• Database creation stops at 81%

  I'm trying to install Oracle9i on a Windows XP platform and when I try to create a database, it stops at 81% and gives me an empty message. What is the problem?  Continue Reading

• Cannot install into existing Oracle home

  I am installing Oracle Enterprise Edition 10g software on a Solaris Sparc server. There is exisiting Oracle 9i software installed in the same ORACLE_BASE. At the command line I type export ORACLE_HOME=/oracle/10g and then start the install. It makes...  Continue Reading

• Using DBUA for RAC databases

  I need to upgrade a RAC 9.0.X database to RAC 9.2.X. The upgrade guide doesn't seem to have the info I need for using the Database Upgrade Assistant for RAC databases. When I start DBUA and select the db to be upgraded, I get an "ORA-01034: Oracle ...  Continue Reading

• Data corruption in dump file

  We had an outage problem this weekend in the office and one of our dump files has a data corruption issue. When trying to import the dump file I get "IMP-00058: ORACLE ERROR 1034 encountered," which means that the database is down. We use RedHat 6.2...  Continue Reading

• Where is the SCN number written?

  Does the SCN number get written to the online redo log files? What are all the places where the SCN number does get written?  Continue Reading

• ORA-2264 error when importing data

  We export data in full mode and in consistent mode. But when we import this data we have too many errors, such as "ORA-2264: name already used by an existing constraint."  Continue Reading

• Monday to Sunday weeks in Oracle

  I am trying to retrieve data for the previous week. Using the code below, I get the previous week starting on Sunday and ending on Saturday. How can I retrieve the data from Monday to Sunday?  Continue Reading

• ORA-12505 error after upgrading hard drive

  I recently upgraded the hard drive on my Windows machine where I have Oracle 9.2.0 installed. I use this instance as a testbed for other applications that require database connectivity. On my old hard drive, I had no issues, but now, when I attempt...  Continue Reading

• "No interface driver connected" error

  I've installed Oracle8i on my Windows XP system. When I open the application the following error pops up: "ORA-03121: no interface driver connected. Function not performed." I give the username and password but still get the error.  Continue Reading

• Avoiding fragmentation in tablespace

  How to avoid fragmentation in the tablespace?  Continue Reading

• Accessing Oracle remotely with Toad

  I have Oracle9i installed. I have Toad software tools for access to the database. Toad is installed on Windows. I want to access the Oracle server remotely. The Oracle server is installed on Unix. Toad does not give me the option to have network ...  Continue Reading

• Using UNION without truncating CLOB data

  You answered the following question way back in 2002 ("Is it possible to do a UNION of two Oracle 8i views where there are CLOB fields involved?"). When I tried the solution I was able to get the UNION to work, but the CLOB is truncated to 4,000 ...  Continue Reading

• Invoking a stored procedure in a select statement

  How to invoke a stored procedure in a select statement?  Continue Reading

• User unable to access table in schema of another user

  Why is a procedure owned by user1 not able to access a table in the schema of user2 through a private synonym, wherein user1 has the 'select any table' system privilege? He is however able to access the table at the SQL prompt (Oracle 9i Release 2).  Continue Reading

• End of file on communication channel

  I had to uninstall Oracle9i and reinstall it again, and during that process I encountered a TNS error, but I continued with the installation. After that I logged into PL/SQL to execute some scripts. After the execution it gave an 'end of file on ...  Continue Reading

• Storing image file in database and retrieving in Visual Basic

  My questions are as follows: 1) How do I store an image file in an Oracle database table? 2) I then need to retrieve that image to a form in Visual Basic. Any help or advice would be much appreciated.  Continue Reading

• Dedicated or shared server mode?

  Where can I find the best resources on Oracle9i installation? While creating a new database after a new installation, which mode do I select, "dedicated server" or "shared server"? The number of clients will be 10 maximum for my application. Is ...  Continue Reading

• Parsing out components from address column

  I have a TEMP_TBL which I am using as a staging area to clean up data before inserting it into an EMPLOYEE table. One of my big issues is with the column ADDRESS, which contains all address components. I need to parse out the address components ...  Continue Reading

• ORA-00406 error when creating tablespace

  When I tried to create a tablespace, Oracle gave me this error: "ORA-00406: COMPATIBLE parameter needs to be 9.0.1.3.0 or greater." I have changed the COMPATIBLE entry in my init*.ora file in the pfile subfolder. Then I restarted the database. ...  Continue Reading

• Error in writing to directory

  I am getting an error message while I am trying to install Oracle9i on my Windows XP machine. It gives me the error message "Oracle Universal Installer: Error in writing to directory. Please ensure that this directory is writable and has at least ...  Continue Reading

• ORA-27140 error after creating another user

  Recently, I did an installation of Oracle 10g on an AIX 10.2.0 machine using user 'oracle.' Everything was running fine using 'oracle' until I created another user, i.e., ora1 to login. I received the following error: "ORA-27140: attach to post/wait...  Continue Reading

• Designing concept similar to RACF for Oracle

  Are you familiar with RACF (IBM security app)? I need to design and adapt a similar concept for our Oracle database. Can you provide some direction?  Continue Reading

• Time required for hot backup of 80GB database

  How much time will it take to take a hot backup of an 80GB database?  Continue Reading

• Need deeper understanding of Oracle and RMAN

  Being a Unix/Linux guru with storage and backup experience, I need to gain some deeper knowledge about Oracle and RMAN. The books/sites I have seen so far are either too lengthy, or assume a background in databases. Can you point me to a concise ...  Continue Reading

• Steps to recover with cold backup

  I have a cold backup (copy of datafile folder and .dbf ). What are the steps to recovery?  Continue Reading

• How do DML transactions affect redo log buffer?

  When the DML transaction makes changes to a row, how is this changed record or data written to the redo log buffer? What does Oracle write to the redo log buffer when insert/update/delete transactions make changes to the database?  Continue Reading

• Passing a parameter to an RMAN script

  I need to pass a parameter to an RMAN script on Windows 2003. For example, I want to pass DBID as a parameter. Is there a way to do this?  Continue Reading

• Copying data from test server database

  My database machine crashed recently, a Windows 2000 server. Since it was a test server, data is not that important. But I wanted to get back the data used. It was not in archive mode. How can I easily copy that data? After installing the OS and ...  Continue Reading

• What is Oracle's default sorting behavior?

  I have a case where I need to compare data from Oracle and SQL Server. My question is what is the default sorting behavior for Oracle?  Continue Reading

• How do I know if the column is BLOB or BFILE?

  I have one table which contains five columns. One of the columns is defined as LONG RAW. How do I know whether it is BLOB or BFILE? My other question is how can I convert BFILE data to BLOB? Is there any utility or do I need to write PL/SQL ...  Continue Reading

• Linking SQL Server to Oracle server

  How can I link SQL Server from its manager environment to an Oracle server? My idea is to get information stored in the Oracle server through SQL Server.  Continue Reading

• ON DELETE RESTRICT

  I would like to know where we use DELETE RESTRICT?  Continue Reading

• Delete all but two rows in each group

  I have a table with several columns in the primary key, one of which is timestamp. For each Col4 value, I want to prune the table to make sure that only the two most recent times are in the table for each unique Col2, Col3. I am getting wrapped ...  Continue Reading

• Beginner data integration questions

  We have multiple Oracle databases, and we are looking at different options that will help to make things more organized and provide the same level of service to users. When to consolidate data sets into a single database? What are the advantages and...  Continue Reading

• Questions about tablespace and user

  Can anybody give me a vivid idea of the tablespace and the user? I have a user scott that has its own tablespace. So if I create a new user under user scott, it will share the same tablespace by default. Why am I not able to access the objects of ...  Continue Reading

• Validation process followed in GL interfaces

  I would like to know the validation process followed in GL interfaces?  Continue Reading

• How to export a tablespace in 9i?

  I don't know how to export a tablespace in Oracle9i. Can you help? I want step-by-step instructions?  Continue Reading

• Can I take a compressed backup with RMAN?

  I am using RMAN for my backups. But it failed. The reason is the size of the backup -- the filesystem got filled before it started compressing the backup files. Is there any way I can take a compressed backup?  Continue Reading

• Export failing with EXP-00002

  I'm having trouble with exporting using the Oracle 8.1.6 export utility to export tables from an Oracle 9.2.0.5.2 database. These exports are part of a bill close we do every month and have been working fine for about a year. Now suddenly the ...  Continue Reading

• Does RMAN generate separate backup set for each block size?

  You maintain a database using the multiple block size feature. Using RMAN you back up the database. Does RMAN generate a separate backup set for each block size?  Continue Reading

• Query sometimes returns ORA-1403

  We have a query that sometimes returns ORA-1403. The same query executes successfully from the SQL*Plus prompt and many times from our application. Also, we can access the same data using cursors or an update query. The solution is to restart Oracle...  Continue Reading

• ORA-00904 error on select statement

  I am working in JDEVELOPER using JDBC. This program is working with objects. I am getting this error on a SQL statement: "java.sql.SQLException: ORA-00904: "R": invalid identifier." How do I fix this? It seems like object r is not connected to ...  Continue Reading

• Using roles/grants vs. public synonym

  I created two users, U1 and U2, then created role R. I created tables in user U1 and gave grants to select, insert, update and delete to role R. I assigned role R to U2. Now if I log in through user U2 then I am able to see all objects of user U1. I...  Continue Reading

• SQL statement gets very long very fast

  I have the following SQL statement. I now need to substitute pkColumn in that statement with a composite primary key of, let's say, two integer columns. The problem, besides performance issues, is that the statement gets very long very fast and ...  Continue Reading

• Can't get data with synonym name

  I created a public synonym with the same object name to allow me to refer to that object without prefixing the schema name, and I gave a select insert and update grant to public on that object, but I still can't get the data by using the synonym ...  Continue Reading

• What is High Water Mark?

  What exactly is High Water Mark and how to relate it with PCTFREE and PCTUSED?  Continue Reading

• Multiple SIDs in same database?

  Can I create multiple Oracle SIDs in the same Oracle database or do I have to create a different SID using DBCA?  Continue Reading

• Is Oracle easy to learn?

  Is Oracle easy to learn? I am creating an information system using Visual Studio and Microsoft Access. I chose MS Access as it is not based on a command language whereas Oracle is. Also Access has a GUI whereas Oracle does not, correct? I'm a bit ...  Continue Reading

• Equivalent Oracle syntax of SQL:1999 syntax

  What is the equivalent Oracle syntax of the following SQL:1999 syntax?  Continue Reading

• ORDER BY a specified sequence

  I'm trying to create an SQL query with the IN clause where I give the IDs to match. Now I want the order of these records to be the same as I give in the IN clause. But it's not happening.  Continue Reading

• Advanced features of Oracle 10g

  What are the advanced features of 10g over Oracle9i?  Continue Reading