Ask the Experts

Ask the Experts

• 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

• 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

• 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

• 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

• Should sequence numbers be used as primary keys?

  Should sequence numbers be used as primary keys?  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Difference between a primary key and a surrogate key

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

• Select only non-null columns

  Is there a way to pull only non-null fields from a query?  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

• Difference between "SYSADM" and "SYSDBA"

  What are the differences between "SYSADM" and "SYSDBA"?  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

• 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

• 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

• 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

• 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

• 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

• 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

• Difference between subquery and join

  When comparing subqueries and joins, which one is faster?  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

• DISTINCT with GROUP BY

  Could you give us a situation where both DISTINCT and GROUP BY are required?  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

• 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

• 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

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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• "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 [email protected] and then type ...  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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
  			
  
  		

• 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

• 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

• 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

• 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

• Fatal NI connect error

  I have the following error in my Oracle server: "Fatal NI connect error 12638." I don't understood why because it was working so well before, but today it sent that error.  Continue Reading

• Migrating from Solaris to Linux

  We need to migrate our Oracle 9.2 database from Sun Solaris to Red Hat Linux. Our datafiles are stored on EMC's SAN. Do we have to use export and import even though we use a centralized storage?  Continue Reading

• Installing 32-bit and 64-bit Oracle on same machine

  I have an application that does not support 64-bit Oracle. It needs to run on 32-bit Oracle. Can I install two Oracle engines (32-bit and 64-bit) on the same machine? If so, how can it be done?  Continue Reading

• Database crash during hot backup

  Our Oracle 8.0.4 database crashed while doing a hot backup with CA BrightStor. Now one of our databases will not start. Event viewer shows it starting and then shutting down again. Our dbaora7.log shows that all tables and control files ...  Continue Reading

• Three consecutive months in fiscal year

  I have a conveyance_claim table with claim date and type, one record per month. I have to check in financial year (April to March) if three continuous months have claims of the same type. How can I find it?  Continue Reading

• Multiple instances on a single database

  Is it possible for Oracle to have parallel servers (single database, multiple instances)? Is it impossible to have the inverse (that is, multiple databases, single instance)?  Continue Reading

• VARCHAR2 vs. NUMBER

  What is the correct circumstance to store a numeric string as VARCHAR2 vs. NUMBER? The response I have received before is to store all numeric strings as VARCHAR2 unless the intention is to manipulate the field mathematically; but no reason has ...  Continue Reading

• Concatenating XML fragments

  Using XMLElement and XMLForest I have created several XML "strings." In a final XMLElement select statement with XMLForest I want to concat the earlier strings within the output of the final statement. Can concat be used within XMLElement or ...  Continue Reading

• Ranking within a group

  How can I get the rank of a certain column without using the rank() function, just using a simple SQL query?  Continue Reading

• Finding the SCN in the Alert Log

  The Oracle documentation says you can examine the alert.log to find the SCN of the event and recover to a prior SCN. But I was unable to find any SCN in the Alert Log except the time at which I dropped my testing tablespace. Please help.  Continue Reading

• Removing blank lines from a text file

  I want to upload a .txt file containing records in an Oracle table with the UTL_FILE package. There are blank lines between two records in many places, and with the script I'm running, whenever the procedure encounters a blank line it comes out of ...  Continue Reading

• Receiving ORA-313 and ORA-312 errors when trying to open database

  I have a cold backup of my Oracle 9i database. Recently, I tried to start it up but the database could not be opened. I decided to use the cold backup to restore the database, but the problem remained.  Continue Reading

• Comparing current and previous row values

  My SupplyCosts table contains a list of supplies and the cost associated with that supply for every month. I want to select only the months where the cost has changed and the previous cost. SELECT DISTINCT doesn't work because I need the month ...  Continue Reading

• How to UNION tables that are not union-compatible

  Is there any way to UNION two tables that are not union-compatible?  Continue Reading

• "TNS:connection closed" error

  I created a database link to link two Oracle databases. When I queried the remote database, I got "Error: ORA-12537: TNS:connection closed." How can I resolve this?  Continue Reading

• Clearing the rollback segment from a tablespace

  I have a rollback tablespace that shows max bytes of .30 out of 500M, with 499.69M used. I have taken my database down twice to clear the rollback segment from this tablespace and it won't go away. Is there a way to free up the space other than ...  Continue Reading