Ask the Experts

Ask the Experts

• "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

• ORA-01078: failure in processing system parameters

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

• Consistent vs. inconsistent backup

  What are consistent and inconsistent backups?  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• ON DELETE RESTRICT

  I would like to know where we use DELETE RESTRICT?  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

• 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

• 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

• Import terminated successfully with warnings

  I have an export file created by EXPORT:V08.00.05 via conventional path. My system is running Oracle9i Enterprise Edition Release 9.0.1.1. When I tried to import the file, I got an error message. Do I fix it?  Continue Reading

• Comparison of RMAN and SQL-BackTrack

  Is there a white paper comparing SQL-BackTrack (BMC.com) to RMAN? Which software is best for backup and recovery of Oracle 9i databases?  Continue Reading

• RMAN-10038 error

  My RMAN connection fails intermittently with error RMAN-10038. The command I used is "rman target sys/[email protected] catalog rman/[email protected]"  Continue Reading

• What session is consuming the tablespace?

  My users are filling up the UNDO tablespace and TEMP tablespace whenever they run a process which takes up to approximately six hours. This process spans several sessions (from WebLogic Server), uses a lot of global temp tables, types, etc., from ...  Continue Reading

• Deleting Parent and Child rows

  I have two tables (Parent and Child) containing millions of records. I want to delete some rows (106 rows) from both tables. Which is the best approach to do this? I have dropped a foreign key constraint before deleting rows from Parent. But when...  Continue Reading

• Complex query with DECODE

  I have the following query but it's not working. Can we use nested queries with decode?  Continue Reading

• ORA-01552 with import command

  I am supporting a system that uses export for backup. I want to test the import command to create a new database on a different machine. I run ORADIM to create a new instance and then create database to create the new database, and @catalog.sql and ...  Continue Reading

• Check if date is greater than 2000 in PL/SQL

  How to check if the date is greater than the year 2000 in PL/SQL?  Continue Reading

• "Oracle not available" message when creating database

  I just finished installing Oracle 9.2.0 on my Solaris machine. When I create a DB using DBCA, it keep giving me the error message "ORA-01034: Oracle not available." Then I have to press "ignore" many times to make the process finish.  Continue Reading

• FRM-10142 error message

  Please explain the following error message: "FRM-10142: HTTP Listener is not running on rmurthy at port 8888. Please start the listener."  Continue Reading

• Where to write the imp command?

  I have a dump file on my local C drive. I want to import to an Oracle database. Where to write the imp command? Is it on the command prompt or on SQL*Plus?  Continue Reading

• Getting "null pointer exception" error

  I installed Oracle 10g on it. When I want to shut down the database from the em console I can't do it. I get a message saying "internal server error null pointer exception." Where do I create the username and password for OSHost?  Continue Reading

• Partitioned vs. non-partitioned tables

  I'm attempting to address some performance issues by using list partitioning. As an experiment I created two small tables (with about 1,000 rows) of vehicle maintenance data, one with no partitions and the other partitioned by vehicle type. Queries...  Continue Reading

• Cause of ORA-01830 error

  I have two users who are getting the error "ORA-01830: date format picture ends before converting entire input string." Looking at the output from the precompiler, the code looks correct. My only assumption is that the strings comprising the date ...  Continue Reading

• Database fully utilized with hang

  Our database is Oracle 9.2.0.4 running on Linux AS 3.0. Our database is around 110GB and has 16GB RAM. Even though we have set the parameters in SGA_MAX_SIZE to 12G, shared pool size as 2G, db_cache as 4G and pga_aggregate as 800m (changed to ...  Continue Reading

• What happens when we fire a DML statement?

  I want to know when we fire a particular DML (delete) statement, what are the different activities that happen inside Oracle? For instance from user session to server session, then role of dbbuffer, redo buffer, and so on through redo log files and ...  Continue Reading

• When did Oracle begin implicitly closing cursors?

  In your response regarding cursor memory leaks, you said: "It used to be that if you did not close a cursor, that memory stayed allocated and unusable to anything else. This is no longer the case with Oracle." Do you know with what version Oracle ...  Continue Reading

• EXP-00056 and ORA-03113 errors

  Using exp80 for Oracle8 release 8.0.4.0.0 on WinNT, Full=Y. This command did not cause any problem until a few days ago, when I encountered problems whereby not all tables were exported. I got these errors: "EXP-00056: ORACLE error 3113 encountered"...  Continue Reading

• Copying stored procedures to another database

  I want to copy store procedures to another user's database.  Continue Reading

• Recovering schema(user) using RMAN level 0 backup

  Can we recover a schema(user) using the RMAN level 0 backup?  Continue Reading

• Can't open Reports Builder

  I'm trying to open the Reports Builder. The problem is that I can see the Reports Builder start screen and that's all. It always hangs at that point. I can open Oracle Forms Builder and everything else in the Developer Suite.  Continue Reading

• Memory leak with open cursor

  When we open a cursor it has to be closed, or else there will be memory leak in the system global area. What is this memory leak? Under what circumstances does it occur and how is it overcome?  Continue Reading

• Using DISTINCT with TEXT, NTEXT or IMAGE

  I get the following message: "Server: Msg 8163, Level 16, State 3, Line 1: The text, ntext, or image data type cannot be selected as DISTINCT." How do I remove the duplicate rows?  Continue Reading

• Log execution time for a procedure

  I need to somehow log the execution time for two procedures every time they are called and save that data (in a table?). Do you know of a way to isolate tracing to a specific procedure in order to do this?  Continue Reading

• Detect if a column is NUMBER or VARCHAR2 in PL/SQL

  How can I tell if a column is a NUMBER or VARCHAR in PL/SQL? Is there a function to do this?  Continue Reading

• Which version to download for installation on 64-bit Windows machine

  I have a Windows 2003 Standard Edition 64-bit (Intel Xeon 64) desktop that needs to connect to an Oracle9i database running on Windows 2003 Standard Edition 64-bit. I downloaded the database software and ran the install from the 64-bit desktop, ...  Continue Reading

• Copying all objects to another user

  I want to copy all the objects (tables, stored procedures, functions, etc.) of one user to another user. Can you please tell me how to achieve this in Oracle 9i?  Continue Reading

• Counting matches in a self-join

  I just want to take the phonenumber count matches with other tapeids.  Continue Reading

• Starting database without control file

  If someone lost the database control file, how can he start the original database?  Continue Reading

• Fastest way to replicate data from DB2 to Oracle

  We need to replicate a large amount of data (about 20 million records) daily from DB2 to Oracle. What is the fastest way to do this? Currently we export/import flat files. Would Oracle Transparent Gateways be faster?  Continue Reading

• Using PL/SQL for ETL

  How can PL/SQL be best used for the ETL process?  Continue Reading

• Could not initialize the Java Virtual Machine

  I am trying to install Oracle Client 9.2.0.1 on Windows XP Professional on a DELL Optiplex GX620. It is a P4 machine. It gives me the error "Could not initialize the Java Virtual Machine. Program will exit."  Continue Reading

• DBUA does not recognize Oracle sid

  I'm upgrading 9.2.0.1 to 10g R2 on Windows XP using the Database Upgrade Assistant. The DBUA does not recognize the Oracle sid. What could be the reason for this problem?  Continue Reading

• FILE_NOT_FOUND while mapping rdbms80

  Hi, I need help regarding the installation of Oracle8i on Windows 2000 Professional. When I tried to install it gives the following error and quits: "rdbms80.map(0):FILE_NOT_FOUND while mapping rdbms80." Please help me with this.  Continue Reading

• Moving database from C: drive to D: drive

  I have Oracle 9.0.1 installed on a WIN2K server on the C: drive (ORACLE_HOME) and a database installed on the G: drive. How can I move the ORACLE_HOME from the C: drive to the D: drive and still retain the database?  Continue Reading

• Taking backup on Unix and restoring to Windows or Linux

  Can I take an Oracle database backup on Unix and restore it onto a Windows environment? What are the options available to copy an entire database from Unix to Windows or Unix to Linux?  Continue Reading

• Difference between RMAN and traditional backup methods

  What is the difference between RMAN and the traditional backup methods (like hot/cold backup and import and export)? What are the major benefits of RMAN that make it more prominent than traditional backups?  Continue Reading

• Basic steps for implementing a DBMS

  What basic steps should we take in implementing a DBMS?  Continue Reading

• Questions about headers and SCN

  What is in the headers of datafiles and blocks? What is SCN? When does SCN change, at commit or at checkpoint? When a query runs how does Oracle find that this data is in that block of that file?  Continue Reading

• Better performance with varchar2(1) or number(1)?

  Since Oracle table structures do not support a boolean datatype, I am faced with a choice (make it a varchar2(1) or number(1)), with contents of Y,N or 1,0. Which provides better performance?  Continue Reading

• Nth Saturday of the month

  Using the date functions, can I find out whether a particular date is the 1st, 2nd, 3rd, 4th or 5th Saturday of a month? Our leave rules have changed recently and I have to update my system to reflect full working on 1st, 3rd and 5th Saturdays, and ...  Continue Reading

• Removing carriage returns at the end of clob fields

  I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to make this work?  Continue Reading

• Export/import on table with clob datatype column

  How to export and import a table that has a clob datatype column? I am getting errors during export and import.  Continue Reading

• Possible to recover datafile without backup?

  Please tell me if we can recover a datafile that doesn't have any backup and the database is in noarchive mode. Is it possible to recover that datafile?  Continue Reading

• "Resource busy" error when truncating table

  I am inserting records into a sample table at the same time that I'm giving the TRUNCATE TABLE command in another session. I got the "resource busy" error, so I killed the session. But I got the same error. How can I truncate the table?  Continue Reading

• Can we restore archive logs to a cold backup?

  I would like to implement a backup strategy involving a nightly cold backup. I would also like to run the database during the day in archive log mode so that I will be able to recover my database up to the point of failure. There isn't much ...  Continue Reading

• Backing up online redo log files?

  Under the heading "What files need to be backed up under 9i for a cold backup?" you wrote, "The only files you missed in your question are the online redo log files." RMAN itself does not back up the online redo log files. And in most cases they do ...  Continue Reading

• Third-party tools for RMAN backups

  I have seen many companies use third-party tools for RMAN backups, mostly Veritas Netbackup for Oracle on Solaris. Could you please tell me the difference between RMAN use through that tool and RMAN use by itself with the Unix prompt?  Continue Reading

• Does DBMS_OUTPUT.PUT_LINE affect performance?

  Is it possible that the DBMS_OUTPUT.PUT_LINE will affect performance?  Continue Reading

• Copy a row to the same table

  I want to insert a new row in a table with the same row in the same table with a different primary key value. I think I can do it using a temporary table but is there any other way?  Continue Reading

• ORA-01092 error while executing database creation command

  I have successfully installed Oracle9i on RedHat 9.2.0. While executing the database creation command, I got the error "ORA-01092: Oracle instance terminated, disconnection forced."  Continue Reading

• Multiple tablespaces or one tablespace with multiple datafiles?

  Is it better to have multiple tablespaces than one tablespace with multiple datafiles? I'm on a Windows platform so I try to keep my datafiles to a 2GB max. Is it not a performance penalty to have only one tablespace with many datafiles for all the ...  Continue Reading

• How to insert Arabic data in Oracle?

  How to insert Arabic data in Oracle 9i?  Continue Reading

• Count consecutive years

  I am struggling with writing a query to count consecutive years from the current year. I do not want to use a cursor. There is no requirement for the dates to be 365 days apart, just that they have different years. Any thoughts would be appreciated.  Continue Reading

• Concatenate values separated with commas

  I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any function that could do that.  Continue Reading

• Standard vs. Enterprise Editions of Oracle

  What's the difference between Standard Edition and Enterprise Edition?  Continue Reading

• Difference between function, procedure and trigger

  What is the difference between a function, a procedure and a trigger? I know the basic differences, but I would like to know the answers based on the performance tuning aspect.  Continue Reading

• How do I start the listener service?

  I installed Oracle Developer 9i DS in the C: drive and Oracle8 SQL for NT on the D: drive on a Windows operating system. When I start the Forms Developer and compile it, I get the message "FRM-10142, start the oracle listener." Please tell me how I ...  Continue Reading

• Invalid objects after compilation

  I have successfully installed Oracle 10g on Windows XP. After that, I ran the file catalog.sql. The problem is that some objects in SYS, mainly schema, are invalid and remain invalid after compilation. What can I do?  Continue Reading

• Necessary to take a cold backup of a temporary tablespace?

  Is it necessary to take a cold backup of a temporary tablespace? According to me it is, but when we shut down the database the SMON process then cleans up the temporary tablespace also?  Continue Reading

• Easy way to import public synonyms?

  Using export/import to update a development database, I normally do a schema (user) import. Is there an easy way to import public synonyms?  Continue Reading

• How many types of backups are there?

  How many types of backups are there in Oracle? Also, how to take a backup from the Oracle database if I want to format the system?  Continue Reading

• Query for locally managed vs. dictionary-managed tablespace

  What is the query to find out if the tablespace is locally managed or dictionary-managed?  Continue Reading

• Limitation of IN clause in SQL

  Is there any limitation of the "IN" clause in SQL, e.g., it does not take more than 1,000 parameters as its input in parentheses?  Continue Reading

• Setting up Employee Supervisor Hierarchy in Oracle Purchasing

  How to set up an Employee Supervisor Hierarchy in Oracle Purchasing? What is the setup to be followed? How is the document to be routed for approval?  Continue Reading