Ask the Experts

Ask the Experts

• "Delete from emp" without "where" condition

  What's happening in the redo logs when I do a "delete from emp;" without a "where" condition?  Continue Reading

• No such file or directory error

  I have written a make file to compile a proc code, it's giving the following error: "/9.2.0.1/bin/proc: error while loading shared libraries: libclntsh.so.9.0: cannot open shared object file: No such file or directory."  Continue Reading

• How to attach Oracle database backup files to existing server

  My client provided backup files of the Oracle database. I know how to import from normal database backups (.dmp) but am still ignorant of attaching data files to the existing server.  Continue Reading

• LEFT OUTER JOIN without using LEFT OUTER JOIN

  Can I get the same result as LEFT OUTER JOIN without using LEFT OUTER JOIN?  Continue Reading

• Difference between candidate key and composite key

  What is the difference between candidate key and composite key?  Continue Reading

• Update two columns with one UPDATE command

  How to update two columns in a table with one update command?  Continue Reading

• ORA-04082: NEW or OLD references not allowed in table level triggers

  I am calling a stored procedure from a trigger. It shows the error: "ORA-04082: NEW or OLD references not allowed in table level triggers."  Continue Reading

• Inserting multiple rows in SQL

  How to insert multiple rows of data using insert into command?  Continue Reading

• What is a hash value?

  What is a hash value in Oracle?  Continue Reading

• View for total users and default tables in Oracle database

  How to check the total users and default tables that Oracle provides when we create a new database?  Continue Reading

• Redo log file filled up

  What to do when the current redo log file or datafile file gets filled up?  Continue Reading

• Add a column in a specific position in a table

  I have created one table with three columns. I want to drop one column and add the column back to the same table in the same location.  Continue Reading

• Backup and recovery strategy for very large databases

  I have two very large databases and I'm looking for a good backup/recovery strategy. One is an OLTP Oracle 9.2.0.5 10TB database and the other is an Oracle 10.2 data warehouse that is 12TB.  Continue Reading

• Recover database using backup controlfile until cancel

  What happens when we run "recover database using backup controlfile until cancel;"? When do we use this command?  Continue Reading

• Check current SCN in control file

  How to check current SCN or header in the control file?  Continue Reading

• IMP-00009 and IMP-00028 errors during import

  I'm trying to import a dump file and I have the errors "IMP-00009: abnormal end of export file" and "IMP-00028."  Continue Reading

• Composite keys and normalization

  What is composite key and why do we use it? Why go for normalization?  Continue Reading

• Convert minutes to HH:MM:SS format

  I need a SQL query to convert 130 minutes into HH:MM:SS format.  Continue Reading

• Dedicated vs. shared server configuration

  What is the difference between dedicated and shared server configuration?  Continue Reading

• Useful SQL commands for new Oracle DBA

  I am a new Oracle DBA and I am very poor in Oracle SQL commands. Can you provide me with a list of useful SQL commands?  Continue Reading

• Totals from a UNION query

  I'm trying to retrieve a count of rows using a union, but for some reason I get two rows returned with different counts.  Continue Reading

• ORA-12801: error signaled in parallel query server P004

  I'm getting "ORA-12801: error signaled in parallel query server P004."  Continue Reading

• Script to revoke access from user

  I'm trying to write a script to revoke all access from any given user. I've done the following so far, but can't figure out all the errors -- can you help?  Continue Reading

• tnsping timing out when trying to connect

  I installed Oracle 10g server on a Dell desktop. I have no trouble seeing the database instance on the server. But when I try to connect with the laptop, tnsping times out.  Continue Reading

• Was OPatch applied successfully?

  How to know that OPatch was applied successfully or not on a 9.2.0.7 database?  Continue Reading

• Database Configuration Assistant gets stuck on step 8

  I am using Oracle 9.2i on Windows XP. In creating a new database using "Database Configuration Assistant" I get stuck on step 8 of 8 -- after clicking the finish button, nothing happens.  Continue Reading

• Difference between migration and upgrade

  I believe this is a not a good response to the following question. The manual does not specifically or categorically answer the question proposed. Migrate/export (duplicate copy) is really different from the concept upgrade (replace Oracle version).  Continue Reading

• Backup strategy for 1TB database

  I work for a small firm with less than 400GB of data which is backed up on a daily basis. What is the best backup strategy if the firm expands its database to 1TB?  Continue Reading

• Definitions of checkpoint, PMON and SMON

  What is a checkpoint? What are PMON and SMON?  Continue Reading

• ORA-25143: default storage clause is not compatible with allocation policy

  When I set extent management to local I'm getting this error message: "ORA-25143: default storage clause is not compatible with allocation policy." How can I resolve this problem?  Continue Reading

• Measuring query response time in Oracle

  How can I measure query response time in Oracle 9i and Oracle 10g?  Continue Reading

• ORA-01702: a view is not appropriate here

  I'm creating an index on a view using Oracle 9.2.0. I get the error ORA-01702 (a view is not appropriate here).  Continue Reading

• Installing Oracle 9i client on Windows 2003 server

  Can I install Oracle Client 9i on Windows 2003 Server?  Continue Reading

• First or third Monday of the month?

  Given a date that falls on Monday, how can I know if it is the first or third Monday of the month?  Continue Reading

• Migrating database from Solaris to Linux

  What is the procedure to migrate an Oracle database from a Solaris machine to Red Hat Linux 9?  Continue Reading

• Can I install Oracle 8i on Windows 2003 Server?

  I was not able to install Oracle 8i on Windows 2003 Server. Is it compatible with this operating system?  Continue Reading

• Levels for incremental backups in RMAN

  We want to plan incremental backups on Oracle 9i with RMAN. How do I detemine what levels of incremental backups to use?  Continue Reading

• Why isn't Oracle user-friendly?

  Working with SQL Server is simple. Oracle is an enterprise database and powerful but why has it never been user-friendly?  Continue Reading

• Query to delete duplicate records in column

  We have a table with duplicate records for columnA, and we want to make columnA the primary key. For this we need a query to delete the duplicate records so that columnA will have only unique values.  Continue Reading

• Need Oracle 8.1.7 Enterprise Edition CD for re-installation

  We need to re-install an old version of Oracle (8.1.7 Enterprise Edition) but can't locate the CD anywhere.  Continue Reading

• PL/SQL procedure to load CSV file into database table

  I have a control file and a CSV file. I need help writing a PL/SQL procedure which can load the Excel file into the database table specified in the control file.  Continue Reading

• Exporting from Oracle to Excel

  How to export table data from a database to an Excel sheet?  Continue Reading

• Importing .dmp file to database

  What is the simplest way to import a .dmp file stored on my local M/C to the database?  Continue Reading

• Reading SPFILE

  Can we read directly from SPFILE what parameters are defined?  Continue Reading

• Optimize SQL query on table with 50 million records

  We have a table with about 50 million records. We are executing a query that takes one or two seconds to complete in Oracle 10g, but we need it to take less than half a second.  Continue Reading

• Invalid GROUP BY SQL query

  I have a table that captures events with the following columns. I want to produce a summary report with a single row entry for each unique source||event combination.  Continue Reading

• Creation of lksid file in Linux

  Why is an lksid file automatically created when you create a pfile to create a database in Linux?  Continue Reading

• What is a recursive relationship?

  What is meant by a recursive relationship?  Continue Reading

• Invalid stored procedures

  When running an application that is connected to an Oracle server/database and trying to add rows or make a call to the DB, I am getting various errors that the stored procedures are invalid.  Continue Reading

• Rule-based vs. cost-based optimization

  Please tell me the difference between rule-based and cost-based optimization of SQL queries, in detail.  Continue Reading

• One column LIKE another column

  Can one ask for the content of a field in a like condition?  Continue Reading

• COUNT(*) or COUNT(1)

  How do you specify difference between count(*) and count(1) in a select statement? Which is best to use and when?  Continue Reading

• Finding size of files in BLOB datatype

  I have a table named DOCUMENT with a BLOB datatype for storing files. I want a query to find the size of each file stored in that BLOB object.  Continue Reading

• Copying database structure without data

  I want to take the structure of my database, but not the data. Is it possible?  Continue Reading

• What is CPU parse overhead?

  What is CPU parse overhead? What should be done to improve server performance if it is high?  Continue Reading

• Encountered the symbol "ELSE" when expecting one of the following

  I am a beginner to Oracle. I am getting this error: "ORA-06550: line 21, column 1: PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:"  Continue Reading

• Differences between tables and views

  What are the differences between tables and views?  Continue Reading

• Import of .dmp file from 64-bit to 32-bit machine

  I am trying to import an Oracle .dmp file generated on a 64-bit machine onto a 32-bit machine, but I am getting an import error. How can I perform the import?  Continue Reading

• Expression is of wrong type

  This is a function to check whether the given date is a holiday or not. When I execute this, I am getting an error: "ORA-06552: Statement ignored ORA-06553:PLS-382: Expression is of Wrong Type." What is the problem?  Continue Reading

• Fiscal year in Oracle SQL

  I am trying to do counts for multiple fiscal years. I can do this for calendar years, but have been unable to figure it out for fiscal years.  Continue Reading

• GROUP BY multiple columns

  My table structure is empid, category, col1, col2, amount. I would like to get the results as total of amounts for the col1 and col2 combinations, with a particular category.  Continue Reading

• Sorting a clob column

  I'm trying to sort a column type asc as well as desc. I'm able to sort the clob column desc but not able to sort it asc. Can you please help me out?  Continue Reading

• Finding the highest COUNT in a GROUP BY query

  I want to get the maximum of totalcontracts from the retrieved data using a select statement.  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

• 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

• System vs. object privileges

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

• Exporting selected procedures and functions in Oracle

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

• Difference between two dates in hours

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

• Consistent vs. inconsistent backup

  What are consistent and inconsistent backups?  Continue Reading

• ON DELETE RESTRICT

  I would like to know where we use DELETE RESTRICT?  Continue Reading

• ORDER BY a specified sequence

  I'm trying to create an SQL query with the IN clause where I give the IDs to match. Now I want the order of these records to be the same as I give in the IN clause. But it's not happening.  Continue Reading

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

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

• Recovering schema(user) using RMAN level 0 backup

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

• Difference between RMAN and traditional backup methods

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

• Standard vs. Enterprise Editions of Oracle

  What's the difference between Standard Edition and Enterprise Edition?  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

• 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 a primary key and a surrogate key

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

• Difference between "SYSADM" and "SYSDBA"

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

• Can I put two primary keys in one table?

  Can I put two primary keys in one table?  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Compare last year sales to this year sales

   Continue Reading

• Alternative to LEFT OUTER JOIN?

   Continue Reading

• How do I copy an Oracle DB from one server to another?

   Continue Reading

• Different ways to back up a database

  There are several ways to back up a database, including schema, table and full exports, as well as hot and cold backups. Here are descriptions of each method.  Continue Reading

• Assertions

   Continue Reading

• Purpose of roll forward and roll backward

  What are rolling forward and rolling backward in backup and recovery?  Continue Reading

• ORA-12801 and ORA-01652 errors on running query

   Continue Reading

• PL/SQL to display all tables

   Continue Reading