Ask the Experts

Ask the Experts

• 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

• How to replicate an Oracle database to SQL Server?

  I am trying to find the best solution for having a database replicated from Oracle to SQL Server without creating an Oracle instance in our machine. This is not a migration; the Oracle database will continue to be used as is, but reporting is to be ...  Continue Reading

• How to create a database link with an OS Authenticated user?

  How to create a database link with an OS Authenticated user?  Continue Reading

• Database objects becoming invalid

  We are using Oracle Database. In our client site, we have 500 users using the database at one time. Sometimes the database objects become invalid automatically while doing developments.  Continue Reading

• Receiving ORA-12541 error trying to connect through Forms/Reports

  I get the ORA-12541 error message when I try to connect through Forms6i and Reports6i Builder. I've started the listener. I have no problems accessing the database through SQL*Plus. I've checked the listener and tnsnames files and everything ...  Continue Reading

• Problem with quick generation of archive log files

  I am using Oracle 9i release 2 with patch 5 as my production database in archive log mode. Yesterday users complained that performance are going down. I checked the database and found that archive log files are generating very quickly, nearly two ...  Continue Reading

• Sequence of WHERE and ORDER BY clauses

  In this query, I can see that the WHERE clause takes place before the ORDER BY. Is there a way I can reverse this? My objective is to first sort the rows and then to pick the first one in the sorted rows.  Continue Reading

• List of Oracle apps APIs?

  I need the whole list of Oracle apps APIs that are used for inventory and purchasing order modules.  Continue Reading

• Difference between cluster and grid

  What is the difference between a cluster and a grid? Is it a conceptual difference or a technical difference?  Continue Reading

• Difference between RBO and CBO

  I do not have a clear idea about SQL tuning (Rule Based vs. Cost Based Optimizer). Could you explain the difference between RBO and CBO?  Continue Reading

• Moving archive log files from primary to standby database

  I have the following scenario with an Oracle 10g Data Guard database. All the archive log files from the primary database are moved to standby. One of my tasks is to make sure that the moved log files will not get applied immediately to the standby ...  Continue Reading

• Multiple rows with same value in one column

  I'm doing a search from one table and my goal is to show only the rows with the same value in one of the columns. I am only interested in seeing the rows for all the emp_no that shows more than once.  Continue Reading

• Should sequence numbers be used as primary keys?

  Should sequence numbers be used as primary keys?  Continue Reading

• How do I recover deleted data without writing over current data?

  I am trying to restore a database where data was inadvertently deleted. I am recovering the deleted data from a backup. My question is how can I merge the two together without writing over my current data?  Continue Reading

• A CASE expression inside a COUNT function

  Could you please check my CASE query? I just want to include all statuses that were performed by a person across the row, but it seems the query repeats for every row with each status.  Continue Reading

• Archive log files generating quickly

  Yesterday users complained that performance was going down; after than I checked in the database and I found that archive log files are generating very quickly, nearly two archive log files in a minute with a size of 100 MB each. But normally it ...  Continue Reading

• Operation hanging during installation

  I am trying to install 9.2.0.1 on Windows 2003. It succesfully installed Oracle and performed the Net Configuration. When it creates the database through DBCA and goes to option COMPLETING DATABASE CREATE and tries to "clone the database," the ...  Continue Reading

• Difference between "SYSADM" and "SYSDBA"

  What are the differences between "SYSADM" and "SYSDBA"?  Continue Reading

• Difference between a primary key and a surrogate key

  What is the difference between a primary key and a surrogate key?  Continue Reading

• ORA-0658 error at run time

  All the objects in the database are valid, but at run time I am getting error "ORA-0658 PL/SQL: could not find program unit being called." I selected select * from all_objects where status='INVALID' and got no rows.  Continue Reading

• Counting rows in a LEFT OUTER JOIN

  I have a question regarding outer joins. I want to show a list of all retailers in our database and a count of their orders. I came up with a query that shows all retailers that have at least one order and the count, but it won't show retailers ...  Continue Reading

• Way to recover a database without backup?

  We have just lost a tablespace on our live database (looks like someone ran the command "drop tablespace $$$ including contents"). The tablespace (GSIF) is gone! But we can see the datafile. Is there a way of recovering the tablespace with its ...  Continue Reading

• Select only non-null columns

  Is there a way to pull only non-null fields from a query?  Continue Reading

• Two tables with same value as primary key

  Can any two tables having the same column value as primary key refer to the other table by defining its primary key as a foreign key?  Continue Reading

• What is the VARCHAR2 datatype?

  May I please have a description of the VARCHAR2 datatype? This datatype is used in our catalogue database and I cannot find an explanation that will provide a clear understanding.  Continue Reading

• What are rollforward and rollback operations?

  I read that when an instance is terminated with SHUTDOWN ABORT and then restarted, SMON will come into the picture and ensure database consistency. In this process it performs rollforward and rollback operations. Please explain to me what is meant ...  Continue Reading

• Advantages and disadvantages of table partitioning

  What is table partitioning and what are its advantages and disadvantages? Also, what is bulk collection? What are hints?  Continue Reading

• How to view a list of stored procedures through SQL*Plus?

  How can I get a list of stored procedure objects from SQL*Plus? How can I view the source of the stored procedures?  Continue Reading

• Can I put two primary keys in one table?

  Can I put two primary keys in one table?  Continue Reading

• How to estimate database size in Oracle

  What are the main considerations for estimating the size of an Oracle database? Are there any rules of thumb? How to estimate the size and number of datafiles, redo log files, control files and archived redo log files?  Continue Reading

• Migrating a 9i database on HPUX to 10g on Linux x86

  How do you migrate an Oracle 9i database on HPUX to an Oracle 10g on Linux x86?  Continue Reading

• Difference between subquery and join

  When comparing subqueries and joins, which one is faster?  Continue Reading

• Way to identify which row is being locked

  In Oracle 9i, is there a way to identify the specific row being locked in a particular table? I have seen much code that identifies the object being locked and by whom, but I need to be able to identify the row.  Continue Reading

• Using date as a primary key

  If I select the date as a primary key, what problems could I face in the long term?  Continue Reading

• Is my table in the buffer pool keep?

  I created a table using the command 'create table t (no number)' followed by an alter, 'alter table t storage(buffer_pool keep).' There are 100 rows in this table. If I query v$db_object_cache, I do not find this table as kept. However if I query ...  Continue Reading

• WHERE EXISTS ( SELECT NULL... ) revisited

  In a previous post, you wrote that for an EXISTS clause, it doesn't matter if you use SELECT 'text,' SELECT 1, or SELECT NULL. I have at some point been told that using SELECT NULL for an EXISTS should allocate less memory than selecting a value. ...  Continue Reading

• DELETE WHERE NOT EXISTS

  I have two tables, A1 and A2, and they both have a column called SSn. How will I delete a row from table A2 that doesn't exist on A1?  Continue Reading

• DISTINCT with GROUP BY

  Could you give us a situation where both DISTINCT and GROUP BY are required?  Continue Reading

• Listener not responding

  We are running Oracle 10.1.0.3 on Sun OS 5.9 and we have been facing serious problems for the last month. Sometimes our listener stops responding. If I check the status of the listener, it shows successfully. But if I reload or start the listener ...  Continue Reading

• ORDER BY ordinal position

  For this query, what does the last line "order by 4,2,3" mean?  Continue Reading

• Extracting dump files into text format

  I have a set of Oracle 7i dump files, but my institute does not have Oracle Database. I managed to extract a few of the files into .txt format using nxtract.exe, but it is not extracting if the number of rows is greater than 5000. Could you please ...  Continue Reading

• Using dblink to access a different server

  I was trying to use dblink to access a different server. I created a dblink and then I tried to run the insert into a table command (trying to add rows to the remote server). I got the following error: "ORA-28030: Server encountered problems ...  Continue Reading

• Importing a trigger from another schema

  I'm facing a problem. Some of the triggers are deleted from one schema, so I want to import a trigger from another schema. Is it possible in Oracle?  Continue Reading

• Cloning a database without changing DBID

  I have Oracle 8.1.7 running on a Windows 2000 server. I want to clone the database without changing the DBID. Please help me with this. Thank you.  Continue Reading

• What is the solution of the ORA-03121 error?

  What is the solution of the error "ORA-03121: No interface driver connected. Function not performed"?  Continue Reading

• Is it necessary to set ORACLE_SID when creating a database?

  Is it necessary to set ORACLE_SID for creating a database? How will it identify SID? Is it through the instance_name in the initsid.ora file? How is the password file helpful?  Continue Reading

• Cloning a production database into a development server

  I want to clone my production database into my development server. Both are the same Oracle version but different OS versions. Now I have created a new instance of SKBDBSVR and edited all the environment settings. I'm receiving errors.  Continue Reading

• Creating a partition table based on day of the week

  I need to create a partition table based on the day of the week. Do I use list partition for this? Where do I get the full syntax diagram for the partition clause? Do I have to use one of the columns in the table as a partitioning column?  Continue Reading

• Importing dump files from Unix to Windows

  I am new to Oracle 10g on Windows. How to import dump files taken for Oracle 10g under Unix? Can anyone provide steps or direct me?  Continue Reading

• Populate a calendar table

  I want to create a table with five columns (weekdayNumber, weekdayName, date, weekdayBeg, weekdayEnd). How can I create and generate data automatically?  Continue Reading

• Another good FULL OUTER JOIN example

  I have an OrderTable, with columns OrderDate and Advance, and a SaleTable, with columns SaleDate and Advance. Now I want to show all records from both tables and sum the two advance fields where the dates match.  Continue Reading

• Creating test cases for upgrade

  We are upgrading Oracle and will want to create test cases to verify that the upgrade has not adversely affected the data or the applications that access this data.  Continue Reading

• Migrating from Linux/Solaris to Windows and vice versa

  I need to migrate from Oracle on Linux or Sun Solaris to Oracle on Windows 2000 and the other way around. What are the issues with the migration?  Continue Reading

• Creating a database clone from a cold backup

  I have a cold backup of my Oracle 9iR2 Patchset 4 database running on a Windows 2000 server. I have to create a database clone from this backup onto another Windows 2000 server box. Can you please provide a step-by- step procedure to do so?  Continue Reading

• Retrieve data related by composite key

  How to retrieve related data which has a composite primary key and corresponding foreign key in the other multiple tables?  Continue Reading

• Cause of ORA-06550 error

  I'm getting an ORA-06550 error with submessage PLS-00103, which is objecting to the word 'INSERT.' I initially thought this would be a code issue but when I backed the database up and imported it to a standard Oracle-built database the problem went ...  Continue Reading

• RMAN vs. dbverify utility

  You have mentioned the use of the dbverify utility to check for errors in a datafile. Can we not also use RMAN to detect corrupt blocks and also to spot bad spots on disks? If we can use RMAN to cover both the above cases, what is the advantage/...  Continue Reading

• Compare values in consecutive rows

  I have a set of data in PostgreSQL with reading_timestamp as one of the columns and level as another column. I need to sort these data in ascending order (based on reading_timestamp) and then compare the value of level in two consecutive rows. If ...  Continue Reading

• The row with the latest date

  I have a database of millions of rows of trades. I want to retrieve the trade record of the latest buy trade. This would have the latest TRADE_DATE and the BSCODE = 'B.' How do I specify the latest TRADE_DATE?  Continue Reading

• Converting a column from VARCHAR2 to DATE

  I need to convert a date column created using data type VARCHAR2 into data type DATE. Is it possible?  Continue Reading

• Cause of "snapshot too old" error

  I am querying the database to retrieve around 6,000 rows and processing them one by one. In the meantime I get the "snapshot too old" error. Can you please help me resolve this error and also tell me the root cause of this error?  Continue Reading

• Foreign key referencing only part of composite primary key

  If you refer a composite primary key of a master table, you have to refer all the fields of the composite primary key. But how to partially refer the composite primary key in a child table?  Continue Reading

• Calculate amount percentages

  I have a table with two columns, ID and amount. I need to calculate percentage amount allocated to an ID.  Continue Reading

• Oracle treats empty strings as NULL

  This is with reference to "NULL can be your friend." You suggest to use COALESCE function and say that "What this COALESCE expression does is substitute an empty string whenever foo is NULL, so that the resulting value will be found in the IN list....  Continue Reading

• Exporting a column from a table for later import

  I want to export a column in a table and import it later. I have faced a situation where I had to export only four colunms in a table containing 20 columns.  Continue Reading

• User session blocking other sessions

  If there is a user session that is blocking other sessions and processes from accessing the Oracle database, how can we overcome this problem? Could you please explain with examples, by using a SQL command if you can?  Continue Reading

• Syntax for increasing SYSTEM TABLESPACE size

  Patch Notes 7.4.1.1 say to increase SYSTEM TABLESPACE to minimum 10 MB, but don't give syntax. Can you help me increase the SYSTEM TABLESPACE size to 100 MB?  Continue Reading

• Specifying a non-empty directory

  I am having trouble installing Oracle 10g. When I reach the prompt to SPECIFY FILE LOCATION WINDOW, where I enter path=/export/home/u01/app/oracle and then press OK, I see a WARNING window that says "You have specified a non-empty directory to ...  Continue Reading

• What is the Oracle inventory directory?

  My basic question is about the Oracle inventory directory. What are the global inventory and local inventory? While installing Oracle software through OUI, what kind of information will be stored in the inventory? What is the use of inventory?  Continue Reading

• "TNS: Operation timed out" error

  I have installed Oracle 9.2.0.1.0 on Windows XP sp2 and installed all latest XP security patches updates. My problem: I can connect to my instance (gali) if I type conn scott and type tiger (password) but if I type conn scott@gali and then type ...  Continue Reading

• LEFT OUTER JOIN on more than two tables

  Is it possible to perform LEFT OUTER JOIN in more than two tables? If possible, please give me details.  Continue Reading

• LEFT OUTER JOIN with ON condition or WHERE condition?

  I would like your explanation of the difference between a LEFT OUTER JOIN ON condition and a WHERE condition.  Continue Reading

• The difference between NOT IN and NOT EXISTS

  What's the difference between NOT IN and NOT EXISTS in SQL? Which is better to use in SQL queries for the performance aspects?  Continue Reading

• Copying a LONG RAW column into another tablespace

  How can I copy a table with a LONG RAW column from one into another tablespace? It´s important to know that it is not allowed to change the LONG RAW datatype.  Continue Reading

• Updating parent and child records at the same time

  We have an application that updates a child record and based on that change there is a change to the parent table's status_id column. There is a trigger that is supposed to handle this, but we are getting deadlocks because we are trying to update...  Continue Reading

• Inserting an apostrophe in PL/SQL

  In PL/SQL, I cannot insert an apostrophe and I cannot retrieve data with apostrophes. If I select these columns it gives me the error "Quoted string not properly terminated." How can I correct this?  Continue Reading

• ORA-04030: Out of process memory

  How to avoid the ORA-04030 error? It is out of process memory when trying to allocate 2457618 bytes. Please tell me what system parameters need to be changed.  Continue Reading

• How long does a rollback take?

  How long does a rollback take to be done? I tried to delete 42 million records in a table and waited for three hours. After that I had to kill the script to do a truncate. But I am still getting ORA-00054.  Continue Reading

• Deleting a duplicate row in a table

  How do you delete a duplicate row in a table?  Continue Reading

• Use of decimal in NVL function

  I would like to ask about the NVL function. Is there any difference between these?  Continue Reading

• Handling a large trace file

  Once monthly we run a report of which we have nearly 100,000 records. It takes a long time and the performance is very slow. When I run the SQL trace for the session, the size of the trace file becomes very big. Please help me to solve this issue.  Continue Reading

• Deleting expired backups on tape

  I have many backups of 10 different databases taken with RMAN with archiving enabled, currently put to TSM (Tivoli) tapes. What is the best way to delete all expired backups and flag the repository?  Continue Reading

• Errors during import of a dump file

  I'm new to Oracle and I have the following problem. When I try to do an import of a DMP file, I get this error message and I can't do the import. What do I have to do to solve this?  Continue Reading

• Performing a hot backup with RMAN

  How can I do a hot backup using RMAN?  Continue Reading

• LEFT and RIGHT joins, INNER and OUTER joins

  I have been searching the Internet for definitions, and perhaps some examples, of table joins. More specifically, what is the difference between a LEFT and RIGHT join, and an INNER and OUTER join?  Continue Reading

• Design recommendations for FACT TABLE and DIMENSIONS

  We are in the phase of warehouse designing. What are the recommendations and restrictions for FACT TABLE and DIMENSIONS?  Continue Reading

• Ways to speed up a full export

  I have a database exclusively for ESRI SDE consisting mainly of imagery (approximately 2 terabytes and growing). Currently a full expdp takes 12+ hours. Are there any possible parameters and/or options to expedite a full expdp in a shorter timeframe?  Continue Reading

• Using OS Authentication

  I believe that we have a configuration somewhere (Oracle?) that allows Unix user account "ORAsid" to connect to the database as "OPS$ORAsid" without requiring a password. Is this true? Could this be changed (to requiring a password to get into the ...  Continue Reading

• Converting from CHAR to DATE datatype

  As a time value, I used the CHAR datatype and need to convert to a DATE value while manipulating/computing. How do I use the DATE datatype instead?  Continue Reading

• Input/output errors

  I have the following error:

  ERROR at line 1:
  ORA-01115: IO error reading block from file 2 (block # 2772)
  ORA-01110: data file 2: '/home5/oracle/oradata/lrec_ts_files/lrec.dbf'
  ORA-27091: skgfqio: unable to queue I/O
  ORA-27072: skgfdisp: I...
  				 Continue Reading