Ask the Experts

Ask the Experts

• All possible combinations of values

  I have a table with a column of datatype number, with values as 1,2,3,4. Now I need all the possible combinations of these values in a query. Can you please help me?  Continue Reading

• Is Oracle case-sensitive?

  I am new to Oracle. Is Oracle a case-sensitive language?  Continue Reading

• Unwrapping a wrapped procedure

  What is the way to unwrap a wrapped procedure?  Continue Reading

• Exporting user database from stored procedure

  Can I export a user database from a stored procedure or SQL command? How?  Continue Reading

• Searching for multiple words

  My Web site developer is sending me the search keyword with '+'. But it only returns the results with the order of entry. What can I do?  Continue Reading

• INFORMATION_SCHEMA primary key

  For knowing the structure of the table, we use DESC tablename. It will not show which column is the primary key. What is the command for knowing the complete structure of the table?  Continue Reading

• Starting over with RMAN configuration

  I was using RMAN to perform my backups and those backed up files were generated on an NFS mounted drive. Now someone removed the NFS mounted drive so my catalog is out of sync. I'd like to start all over on my RMAN configuration.  Continue Reading

• Latest transaction before a specified date

  I have a Transactions table that stores card transaction data. TransactionID is a primary key that gets incremented with every transaction. Transactions don't occur every day. The Balance is adjusted with every transaction and reflected in the ...  Continue Reading

• Using V$ views to determine if backup was successful

  We are hosting 40+ databases which are backed up using archive_logs via RMAN without a catalogue. These files are copied to a hard drive and then backed up to tape. We would like to interrogate each databases's V$ views to determine whether a backup...  Continue Reading

• Backup command suddenly failing

  I am running the following command and continually getting the errors ORA-19504 and ORA-27038. This has worked for months and now does not. There is adequate space and I can write files as Oracle to the location configured. Please advise.  Continue Reading

• Error with VERITAS NetBackup for Oracle

  I am getting the following error in my rman_backup log file. What could be the cause for this error?  Continue Reading

• Recovering database with cold backup and archive logs

  If I do a cold backup using RMAN on my 9.2.0.7 database at 1:00 am today, and I have all the archive logs from 1:00 am to 5:00 pm, can I use the 1:00 am RMAN cold backup to recover my database up to 5:00 pm? How?  Continue Reading

• Updating rows with %rowtype

  Can we update rows with the help of %rowtype?  Continue Reading

• Default password for sys

  What is the default password for sys in oracle 9i?  Continue Reading

• What is an instance?

  What is an instance? Can we open more than one instance in a machine?  Continue Reading

• Becoming a DBA without experience

  I have obtained both Oracle9i Professional (OCP) and Oracle9i Associate (OCA) certificates and have a background in ADA programming. I am a bit confused on how to get experience in this field as every company requires at least two to three years' ...  Continue Reading

• SELECT query sequence of execution

  What is the sequence of steps in which the query gets executed? How is the query executed at the back-end by the database engine?  Continue Reading

• Restricting certain statements on tables

  I need to put some restrictions on my database tables. Say, for example, I want to prevent statements like "select * from table;" but allow statements like "select column from table;". How do I do this?  Continue Reading

• Why only one primary key?

  Why is there only one primary key allowed in a table? Why not more than one?  Continue Reading

• How to query a query

  I am using two functions in a Select statement. Let's say A and B are functions which contain large code, and both functions return a numeric data type only. In the Select statement, I want to subtract those two functions.  Continue Reading

• Size of database in gigabytes

  I'm new to Oracle. How do I find out the total size of a database in gigabytes?  Continue Reading

• Long-running query causes "snapshot too old" error

  I have a production database and I am receiving the "ORA-1555: Snapshot too old" error when a long-running query is executed. I tried changing the UNDO_RETENTION to 40,000 seconds (as the query takes more than 24 hours to complete processing). The ...  Continue Reading

• Fixing internal error code without much downtime

  When I try to remove snapshot SNAP@_BLOB_LOG46 I receive the error "ORA-00600: internal error code." Oracle indicated the only solution is to make one export/drop DB/import. This action, due to the size of the DB, would demand at least five days of ...  Continue Reading

• ORA-1115 error during export

  I got this error while I was doing an export. Is this a problem with the object or is the data file corrupt? How can I fix this? Could the file size be the problem?  Continue Reading

• Restoring hot backup from machine 1 to machine 2

  I have an Oracle hot backup from machine 1, and I want to restore it on machine 2. Can you give me the step-by-step procedure to do this job?  Continue Reading

• Is COMMENT a reserved word in Oracle?

  Is COMMENT a reserved word in Oracle? If so, how can I name a field in a table 'COMMENT'?  Continue Reading

• ORA-00942: table or view does not exist

  We recently did a system refresh from PRD to TEST. After that, our backup failed in TEST with the following error.  Continue Reading

• Recovering crashed database with corrupted undo tablespace

  I have a small Oracle 10g database. The database runs under archivelog mode. Recently my database crashed and it looks like the undo tablespace has been corrupted. How can I recover it?  Continue Reading

• Migrating DB2 UDB database to Oracle on Linux

  In the next couple of weeks I need to migrate from a DB2 UDB database running on an AIX system to Oracle running on Redhat Linux. It doesn't appear that I will be able to use the Oracle Migration Workbench because from the documentation it only ...  Continue Reading

• Oracle listener affected by new Service Pack 2

  I'm on Windows XP and I just upgraded to a new Service Pack 2. It seemed to affect my Oracle Listener. I get ORA-12541 errors now when I try to connect, no matter how I try to connect. This was not happening before and my TNSNAMES.ORA and ...  Continue Reading

• Installed Oracle with wrong default language

  I have installed Oracle 9i Server on client but by default it got installed in Dutch. I want to change it to English. I have tried doing alter system set NLS_LANG option but it did not change the setting. Please advise.  Continue Reading

• Installing Oracle9 on Linux

  I need the step-by-step procedure on how to install Oracle9 on RedHat Linux 9.2 and Suse Linux.  Continue Reading

• "Failure when initializing OCR"

  I am installing Oracle 10g on MS Windows 2003 Server. During the installation I get the following error: "Failure when initializing OCR." Next, the error: "Install program cannot initialize service OracleCSService." I select "retry" and the ...  Continue Reading

• Tools to migrate stored procedures and queries

  What are the tools available to migrate stored procedures and queries from Oracle 8i to Oracle 10g?  Continue Reading

• Moving from RBO to CBO

  Our current application is using Oracle 9.2.0.6 with RBO. We are in the process of strategizing the move to CBO. Can you advise on some good articles and things to be wary of?  Continue Reading

• Configuring iSQL*Plus to run SQL through Internet Explorer

  How to configure the iSQL*Plus environment in Oracle9i on a standalone machine with Windows 2000 Advanced Server so that we can run SQL through Internet Explorer and access the database?  Continue Reading

• Upgrading to Solaris 10 and Oracle 10g

  Currently we are using Oracle 9i EE on Solaris 8 and it has been almost five years. Now we are planning to change the server to Solaris 10 and Oracle 10g SE. How do I migrate my data to the new server?  Continue Reading

• Migrating PeopleSoft app from Sybase to Oracle

  I'd like to know if there are published procedures for migrating a PeopleSoft application from a Sybase database to Oracle. One would think that Oracle would hurry to provide such migration paths, but I cannot find any information.  Continue Reading

• Best Windows OS for Oracle8i

  Can I install Oracle 8i on Windows XP Professional? If yes, then what are the minimum requriements needed (for a home PC)? What are the steps for installation? Which is the best OS for Oracle8i, Windows XP Professional, Windows 2000 Professional or ...  Continue Reading

• Upgrading SAP and Oracle DB

  We planning to upgrade SAP 4.6 B to 4.7 C; for that we have to upgrade our DB first. Our DB is Oracle 8.0.6. We have to upgrade to 9.1. Please provide us with the steps and guide us with some relevant documents.  Continue Reading

• Patch to upgrade from 9.2.0.1.0 to 9.2.0.7.0

  Currently we have Oracle 9i (9.2.0.1.0) on Windows 2003 Server and we would like to upgrade to 9.2.0.7.0. Which patch do I have to apply and how to apply the patch?  Continue Reading

• Oracle Migration Workbench for DB2 V8

  When will Oracle Migration Workbench support DB2 V8? Seems like a great tool but only supports up to V7.2.  Continue Reading

• Patch for upgrading to 9.2.0 on Windows NT

  I am using Oracle 8.0.6 with Windows NT. Now I'm required to upgrade Oracle to 9.2.0. Can you explain to me which patch set I should use to upgrade my database to 9.2.0?  Continue Reading

• IP address of host cannot be determined

  Every time I try to install Oracle 10g on Windows Server 2003, I get an error. It says it's thrown when the IP address of a host cannot be determined.  Continue Reading

• Does Oracle architecture differ by version?

  Is Oracle architecture different for different versions (8i/9i)? If yes what are the basic differences?  Continue Reading

• Calling a procedure inside another

  How to call a procedure p1 inside p2?  Continue Reading

• What is the alert log?

  I'm using Oracle 10g. My friend told me to go to the alert log and review the error messages. What is an alert log in Oracle? What is the use and purpose of this file? How to review this file and where exactly is it located?  Continue Reading

• DISTINCT applies to all columns in the result

  Could you tell me what are the effects of using the DISTINCT keyword before a group of different column names in a SELECT statement?  Continue Reading

• Insufficient privileges error connecting as sysdba

  I am doing a DR test for my production SAP database. I restored the datafiles and redo log files. I get an insufficient privileges error when I try to connect as sysdba.  Continue Reading

• Importing .dmp file via command prompt

  How to import the .dmp file in Oracle 9i (in the command prompt)?  Continue Reading

• The WITH clause

  The UNION of three tables yields multiple rows of the same product_id with different prices and supplier names, and some with no item info. Can we generate a query to return a distinct set of records based on product_id with the cheapest price and ...  Continue Reading

• Rows locked by which user

  I am trying to find out which rows are being locked by which user. I have two users: user1 is doing a select * from emp where empno=7369 for update nowait; and user2 is trying the same query and of course he will get an exception, resource is busy. ...  Continue Reading

• Table locking in Oracle 10g

  One of my customers is using Oracle 10g (10.2.0.2) with Weblogic 8.1 SP5. For some unknown reason, one of the tables is locking up and not releasing itself for a long time. Are there any known issues in Oracle 10g regarding tables locking up? This ...  Continue Reading

• Alternate way to alter table

  I created a table with 10 columns and have about 100,000 records in it. I want to alter the structure of the table. Currently Oracle has the alter table add columns option and it works fine. I want to do this in a different way. I want to export the...  Continue Reading

• Finding unused indexes

  I want to find out the unused indexes on a particular table, because most of the indexes are not used by the queries which are written based on the indexes.  Continue Reading

• Deleting a duplicate in a table

  How to delete a duplicate in a table?  Continue Reading

• Restoring files after OS crash

  I have Oracle 8i running on Windows 2000 Server. My OS crashed. After reinstalling the OS (without formatting) I reinstalled Oracle 8i after renaming the earlier Oracle home directory. All my .dbf, .log, .ctl, etc. files are intact. Can I restore ...  Continue Reading

• Exporting selected procedures and functions in Oracle

  How can we export selected procedures/functions/packages in Oracle?  Continue Reading

• System vs. object privileges

  What is the difference between system privileges and object privileges in Oracle?  Continue Reading

• Calling package within package

  I have a package abc which has a procedure XYZ. There is another package abc2 which has a function def. Can I execute abc.xyz in function def? All the above packages are in the same schema with the same owner.  Continue Reading

• Parsing, converting and loading image with XML

  I have to parse an image from XML. The image is in base64 encoded form. I have to parse the image, convert it to binary and load it in a blob in an Oracle database.  Continue Reading

• Unwrapping encrypted procedure in Oracle

  How to unwrap a procedure that is encrypted using the wrap utility in Oracle?  Continue Reading

• ORA-03001 error with dynamic query

  I am using a dynamic query (with the help of EXECUTE IMMEDIATE) in the function and within the dynamic SQL I am calling another function created by me. But when I execute the function I am getting an error: "ORA-03001 unimplemented feature."  Continue Reading

• Foreign key referencing unique key in parent table

  How do I create a foreign key from the child table that references a UNIQUE key in the parent table? I have a table named employee with a field emp_id. I want to add an entry in the table every 10 minutes if the max value of emp_id is 10. After 10 ...  Continue Reading

• Unix script to create database export via crontab

  I need to find a Unix script that will run via crontab and create a full database export. The script I have attached runs OK via ./export.sh in a Unix shell (about two hours and is 22 GB in size), but when I run it via a crontab job, it takes 18 odd...  Continue Reading

• Configuring ASM instance in Oracle 10g

  I need to know the steps involved in configuring an ASM instance for an Oracle 10g Rel2 database.  Continue Reading

• Opening recovered database with UPGRADE option

  I have a backup of an Oracle 8i database which I needed to restore on Oracle 10g Server. I created a new database on the server, placed all the datafiles and redo logs and then re-created the controlfile and did a recover database using the backup ...  Continue Reading

• Command to open database with missing datafile

  Can you tell me the command(s) or procedure to open a database that has a missing datafile due to a media failure (external device)? The database is in noarchivelog mode, there's not any backup and the version is 10g Release 2. It doesn't matter if ...  Continue Reading

• ORA-01555 error when exporting table with BLOB column

  I think that I have a data problem. Exporting a table with a BLOB column, I get an ORA-01555 error even though I can export a like-sized table, 1.6 million rows, that has a long raw column containing similar data, .pdf files. How can I figure out ...  Continue Reading

• ORA-00932 using length function

  An article on this Web site said the length function could tell me the length of a long datatype column. However, in SQL*Plus I received the following error: "ORA-00932: inconsistent datatypes: expected NUMBER got LONG."  Continue Reading

• Bad application performance on Oracle RAC

  We are deploying a new vendor-supplied application. It has never been tested or deployed in a RAC environment. We are deploying on a RAC environment. We are seeing some serious performance issues. Are there any specific checks to see if the ...  Continue Reading

• Changing passwords for seeded applications accounts

  I have inherited an Oracle 7.3.4 database with 10.7 char applications running on it. The default passwords were never changed for the seeded applications accounts. How do I safely change these passwords?  Continue Reading

• Displaying users/schemas with password from Administrator

  How do I display all the users/schemas with a password from the Administrator? The results should display passwords in character form.  Continue Reading

• Key roles of database administrator

  What are the key roles of a DBA?  Continue Reading

• ORA-28500 with database link

  I created a database link from Oracle 10g to MS SQL Server. In one table there is a column name larger than 30 characters. I don't select this column but I get this error: "ORA-28500: Attempt to access a column Address_OutputMedium_Caption_ID with ...  Continue Reading

• Correct use of CLOB datatype

  I have a database design where CLOB datatypes are being used in place of VARCHAR2 datatypes. The VARCHAR2 datatypes are not large. I believe this was done was to use ConText indexes for Web page searches. I need some ammunition regarding the use or ...  Continue Reading

• PL/SQL ignores ASCII null characters

  I am trying to send an escape sequence to a dot matrix printer. But the sequence has embedded ASCII null characters (i.e., chr(0)), and PL/SQL seems to ignore the first null and all sequence bytes after. Why is this and what is the workaround?  Continue Reading

• Writing procedure or function inside trigger

  Is it possible to write a procedure or function inside a database trigger?  Continue Reading

• Oracle stored procedure to return multiple rows

  I want a stored procedure in Oracle, which will return multiple rows. What should be the code? I also want to call this stored procedure from VB.NET and display this record in this platform. In the DBMS_SQL package, what does NATIVE stand for?  Continue Reading

• Datepart function in Oracle

  I want to use the datepart function to get the month of my variable. Is this allowed in Oracle or am I off on the syntax?  Continue Reading

• Multiple Oracle instances in Unix

  How to create multiple instances in a Unix system?  Continue Reading

• PL/SQL code to implement ETL function

  I was looking for some functions and procedures written in PL/SQL code that can implement ETL function. Where can I find this generic code? Who could help me to understand how PL/SQL works?  Continue Reading

• Can I spool a file from a PL/SQL procedure?

  Can I spool a file from a PL/SQL procedure? If not, what is the workaround to get output from Oracle directly into text format?  Continue Reading

• Using BLOB with ctx_doc.filter

  I have a question about ctx_doc.filter. If I have this sentence -- ctx_doc.filter('myindex','1', mklob, TRUE); -- and mklob is the CLOB datatype, this works fine, but if I have mklob as a BLOB, it doesn't. Do you know if Oracle have another ...  Continue Reading

• ORA-04091: table is mutating

  I encountered the error "ORA-04091: table XXXX is mutating, trigger/function may not see it" when I created an after insert trigger. I tried adding PRAGMA AUTONOMOUS_TRANSACTION to the code. The error went away but the table didn't get updated.  Continue Reading

• ORA-02068 with database link

  I have created a database link but I get this error when I try to use the link: "ORA-02068: following a severe error from RTX510 ORA-01034:oracle not available ORA-07429." However the other database is available and running.  Continue Reading

• "Not a valid month" error

  I am trying to perform a partial export of a table. Every day, I want to export just the rows created yesterday. I am using a PL/SQL shell script and executing code in a par file. I get an error: "ORA-01843: not a valid month."  Continue Reading

• Difference between two dates in hours

  How to calculate the difference between two dates in hours?  Continue Reading

• Trigger to track user password changes in table

  I need to create a trigger which tracks user password changes into a table my_table. I have a database user who has alter user privileges and would like him to change users who belong to his group. That is, when the user changes the password for a ...  Continue Reading

• Help building billion-row system

  I need to build a new system with a table that will keep data for 60 days (approximately 1 billion rows). Once the data is there, more data will be loaded (about 10 million rows per day). The system needs to reject and report this new data whenever ...  Continue Reading

• Implementing Virtual Private Database

  Can you please explain VPD (Virtual Private Database)? I don't know this concept. How can I implement this in my 9i database?  Continue Reading

• Unable to flush file data

  I'm getting this error message ("ORA-27052: unable to flush file data") and don't have any idea why I'm getting it. Can anyone help?  Continue Reading

• Importing text file to Oracle table

  We've been using SQL Server, and now we switched to Oracle. We have to import a text file (delimited with commas) to an Oracle table via our application. Our application tool is Microsoft .NET. Is there any way to import a text file in Oracle via ...  Continue Reading

• Two ways to SUM a complex expression

  From the given table I want to derive the count for OOS as a separate field (CntOOS) that gives me the number of times 'OOS' appeared in the above three visits.  Continue Reading

• Restoring an Oracle backup to a new Windows server

  I am a senior SQL Server DBA but brand new to Oracle. I have been given a task by my management to perform a backup from our production server and restore it on a new server to simulate a disaster. Please provide me with a process of restoring just...  Continue Reading

• Taking tablespace out of recovery mode without restoring it

  We have a tablespace that is in recovery mode and therefore it is preventing us from doing a full backup. Is there any way to get a tablespace out of recovery mode without restoring it?  Continue Reading

• ConnString value for dblink syntax

  We have two different Oracle servers and I am trying to connect from one server to another server using dblink. I am using the syntax for creating the dblink as "create database link dblink_name connect to user_name identified by password using ...  Continue Reading

• ORA-12528 error

  I am getting the error "ORA-12528: TNS:listener: all appropriate instances are blocking new connections."  Continue Reading

• Create automated backup for Oracle 9i database

  I want to create an automated backup for my company's database. We have Oracle 9i Enterprise. I also want to save the backup files on another machine, not the one on which Oracle is installed (the server).  Continue Reading

• Sending spool file as mail attachment

  I need to run a select statement every monday evening at 6, and I need a generated spool file to be sent as a mail attachment to multiple users using PL/SQL, not using a cron tab or shell script.  Continue Reading