Ask the Experts

Ask the Experts

• Counting rows in a UNION query

  How can I get the total count of the number of records returned, not duplicate, from the union of two queries which return the same set of data but using different where clause?  Continue Reading

• Foreign keys in a composite primary key

  Is it possible to use one of the attributes of a composite primary key as a foreign key?  Continue Reading

• Non-zero values for SHARED_POOL_SIZE and JAVA_POOL_SIZE

  I am getting an ORA-04031 error. I have a two-node 10g RAC cluster. If I gave both SHARED_POOL_SIZE and JAVA_POOL_SIZE values (thus overriding the defaults) how would this affect the running of the CLUSTER? Does a CLUSTER require these values to be ...  Continue Reading

• Determining which table and column a field is stored in

  Is there a way in Oracle Applications 11.5.9 using RDBMS 9.2 to place the cursor on a particular field in a window and be able to determine which primary Oracle table and column name that field is stored in?  Continue Reading

• Taking full backup with Toad

  Can you please tell me how to take a backup of a database? I am using Toad, but Toad has options to take each component (like tablespace, table data) backup separately while I need the backup of the whole database. Can you send me the script, if we ...  Continue Reading

• Joining tables without common columns

  How do I join two tables without common columns and display any two columns, all records in a corresponding table?  Continue Reading

• Purpose of CLUSTER_DATABASE parameter

  We have a two-node RAC database. Recently we switched our production database from no archive log mode to archive log. We changed the parameter CLUSTER_DATABASE=FALSE on one of the instances before converting and reverted back. Could you tell me ...  Continue Reading

• Combine multiple lines of text into one

  I have a table that I created from a flat file. I have thousands of records. What is my best approach to normalize this? I would like to keep the multiple lines of text, but put them into one field.  Continue Reading

• ORA-14450 error with ALTER TABLE

  When executing (from SQL*Plus) ALTER TABLE event_work MODIFY(user_pid VARCHAR2(22 BYTE)) we get this error: "ORA-14450: attempt to access a transactional temp table already in use."  Continue Reading

• Running multiple instances and databases on one server

  How to run multiple instances and mulitiple databases on a single server? Sometimes I have faced an interview question like "How many databases you are handling?" Please clarify.  Continue Reading

• Problem dropping materialized views

  I have an issue with dropping materialized views. I've been trying to DROP an MV which is defined as REFRESH ON DEMAND and there are no locks anywhere. A DROP statement sits there and never returns.  Continue Reading

• Query to select CLOB field

  I want a simple query to get the output of a clob type field in Oracle. I have inserted text ("this is a test for clob") in the clob field. How to select this?  Continue Reading

• Creating table without primary key

  I would like to create a table without a primary key. Does Oracle (10g) use the rowid as the default primary key in this situation?  Continue Reading

• "Invalid hex number" error with update trigger

  I am trying to write a before update trigger which will fire only when we update the password in the users table. The password column is encrypted in the users table. The keys are stored in users_keys. It is showing an error: "ORA-01465: invalid hex...  Continue Reading

• 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

• Unwrapping a wrapped procedure

  What is the way to unwrap a wrapped procedure?  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

• 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

• 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

• What is an instance?

  What is an instance? Can we open more than one instance in a machine?  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Calling a procedure inside another

  How to call a procedure p1 inside p2?  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

• 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

• System vs. object privileges

  What is the difference between system privileges and object privileges 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

• 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

• 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

• Exporting selected procedures and functions in Oracle

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

• Key roles of database administrator

  What are the key roles of a DBA?  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

• Writing procedure or function inside trigger

  Is it possible to write a procedure or function inside a database trigger?  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

• Difference between two dates in hours

  How to calculate the difference between two dates in hours?  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• ORA-12528 error

  I am getting the error "ORA-12528: TNS:listener: all appropriate instances are blocking new connections."  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

• Command to view stored procedure

  Which command is used to view a stored procedure?  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

• Moving column values from one table to another

  I have two tables wherein I have to get a string of values from one column of one table and pump the retrieved information into a new column in the other table. How do I do this?  Continue Reading

• Finding tables with BLOB field

  How to find the tables from a schema which have at least one BLOB field?  Continue Reading

• Database migration from DB2 to Oracle

  What are the steps of a database migration from DB2 to Oracle?  Continue Reading

• ORA-14400: Inserted partition key does not map to any partition

  What advice can you give me about this error message?: "ORA-14400: Inserted partition key does not map to any partition."  Continue Reading

• Inserting a BLOB in an Oracle table

  I am using a French front end to connect to an Oracle database back end. Everything is working well except for BLOBs (I have images in one of the tables). I can insert, update and delete all other items. Can you give me the SQL routine to insert a ...  Continue Reading

• Storing image file in table

  How can I store an image file such as a .jpg or .bmp in a table? If it is to be stored outside the database and called from SQL, how can it be done? Also tell me how should I do this in PL/SQL.  Continue Reading

• Moving Oracle folder to external hard drive

  I have PeopleSoft installed locally on my laptop and it uses Oracle 9i as its database (also local on the C drive). I want to move the oradata folder to my external hard drive to free up space and leave everything else. How can I accomplish this?  Continue Reading

• Executing procedure with in/out parameter

  How to execute a procedure or package from the SQL prompt where the procedure has an in/out parameter in Oracle?  Continue Reading

• Extracting from LONG datatype column

  I have a table with a column specified as the LONG datatype. When selecting data I get the following error: "ORA-00932: INCONSISTENT DATATYPE." Is there a quick way to extract this information?  Continue Reading

• Buffer deadlock in statspack report

  Why do we have a "buffer deadlock"? I see this deadlock in the statspack report for Oracle 10.0.1 (Solaris system) associated with DBSNMP schema.  Continue Reading

• Do indexes affect performance of updates and inserts?

  I have three indexes for Table A. These indexes are helpful when I perform select statements, especially for SQL involving GROUP BY. Will these indexes affect the performance on updates and inserts for Table A?  Continue Reading

• Syntax for username commands in Forms 6i

  I need to issue the "alter user username account unlock" and "alter user username password expire" commands inside Forms 6i. What is the correct syntax?  Continue Reading

• Error on UTL_FILE

  When I run a stored procedure this errors comes out: "PLS-00905: object RPT.UTL_FILE is invalid." Do you know what utl_file package is, and how I can fix the above problem in Oracle? Do you know how to determine if it is a privilege issue, fix a ...  Continue Reading

• Error with BRTOOLS

  Oracle 10.2.0.2 on SAP WEB AS 7.00 generates an ORA error when starting the BRTOOLS.  Continue Reading

• "Invalid block type" message during 9i installation

  After installation of Oracle9i on Win2003 Server (NTFS) I'm not able to complete the database creation. At 41% it is showing "invalid block type."  Continue Reading

• TNSListener service not starting

  My TNSListener service is not starting when I start my laptop. When I first downloaded 10g I was able to connect to the local DB and other test and development DBs via Toad and SQL*Plus. But for the last two to three days I am having problems ...  Continue Reading

• Migrating data to legacy system as functional consultant

  What are the steps to be followed when migrating data from a legacy system as a functional consultant? What are the important interface tables that should be kept in mind?  Continue Reading

• Restricting users from connecting through SQL*Plus/Toad

  I want to restrict selected users from connection through SQL*Plus and Toad but they can connect using the Oracle Developer application.  Continue Reading

• Command to increase size of datafile

  How can I increase the size of a datafile in any tablespace (what is the command)? How can I be sure that I have increased the right file size in the right schema?  Continue Reading

• Errors with select query on dblink

  I created a dblink, but when I execute the select query, it throws the following errors: "TNS: could not resolve service name" or "TNS: no listener." Can you help?  Continue Reading

• Importing table structure only to new schema

  How do I import the table structure only to another schema with a different tablespace for data, as well as for constraints?  Continue Reading

• Ratio for SGA and database size

  What is the ideal general ratio between SGA and the database size for good performance?  Continue Reading

• Inserting special characters in varchar2

  Please let me know how to insert the text "mehra & co" into the varchar2 datatype in SQL in 9i.  Continue Reading

• Calling stored procedure from VB.net

  I would like to know how I can call an Oracle stored procedure from VB.net. How will I pass an array variable to that stored procedure?  Continue Reading

• Using SQL to find the number of Sundays in a month

  I want to find out how many Sundays come in any month in SQL.  Continue Reading

• Identify column names by table names when using SELECT *

  I am joining multiple tables in a select statement and I want to add the tablenames to the column names so I can trace its origin. How can I do this and still use the *?  Continue Reading

• Query-only responsibility in Oracle Apps

  Regarding the possibility of creating a query-only responsibility in Oracle Apps, what's the type of privileges or DB roles that particular DB account should have associated with it, in order for my query-only responsibility to work properly?  Continue Reading

• Storing CLOB as .csv file

  I need to fetch a CLOB field from a database and then move the fetched content to a .csv file. Can you please help?  Continue Reading

• Sending CLOB through PL/SQL

  I have a VB front end and I want to send a CLOB datatype in an Oracle 9i database through PL/SQL.  Continue Reading

• How does SMON know to perform a crash recovery?

  How does SMON determine that it has to perform a crash recovery?  Continue Reading

• Copying selected tables from one user to another

  I want to copy only selected table structures from one user to another user in Oracle.  Continue Reading

• Designing a generic "Product" table

  I work in a product-based company. I am planning to build a generic data mart for all our future customers. we need to design our "Product" table generically, so that it can accomodate any type of products. Please suggest what is the best way to ...  Continue Reading

• What information do redo log files contain?

  What do redo log files contain? As far as the definition of redo log files, it is written that they contain changes made to the database. My question is what type of changes, as I know the datafiles contain all the committed transactions.  Continue Reading

• Checkpoints and RBA in Oracle

  How many types of checkpoints are there in Oracle? What are they? Also, what is RBA in Oracle?  Continue Reading

• "Divisor is equal to zero" error

  I am getting an "ORA-01476: divisor is equal to zero" error on a insert statement that does no dividing. Can you tell me why?  Continue Reading

• "No rows selected"

  When I select all rows from dba_tablespaces the content column shows me the entry of the temporary_data column as temporary and the rest as permanent. When I select from v$tempfiles or dba_temp_files the result is "no rows selected." What is the ...  Continue Reading

• Database automatically shutting down and restarting

  My 10g DB keeps shutting down and starting automatically on my Windows 2003 machine. This is new behavior and I don't believe any new changes have been made to this machine.  Continue Reading

• Necessary to back up UNDO tablespace?

  As a secondary backup solution, we decided to go with hot backups, using ALTER TABLESPACE ... BEGIN BACKUP. I am doing this for all permanent tablespaces, thus excluding temp and undo. My question is, is it nessecary to back up the UNDO tablespace, ...  Continue Reading

• Importing Oracle data from Unix to Windows

  I want to import data in Oracle on Windows. The export (dump) file is taken from Oracle running on Unix. Please advise.  Continue Reading

• Update a substring of a column value

  How do I update the substring of a string value? I tried the above in TOAD and I got "ORA-00927: missing equal sign."  Continue Reading

• Function is nested too deeply

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

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

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

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