Ask the Experts

Ask the Experts

• Oracle LEFT JOIN vs. LEFT OUTER JOIN: What's the difference?

  Get an explanation of the different types of Oracle joins, including whether there's really a difference between the LEFT JOIN and LEFT OUTER JOIN syntax in PL/SQL.  Continue Reading

• What is the difference between a database engineer, architect and administrator?

  Expert Michael Hillenbrand explains the difference between a database engineer, a database architect and a database administrator.  Continue Reading

• For Oracle disaster recovery, check your physical standby databases

  Database expert Michael Abbey explains what's needed to ensure that your Oracle physical standby databases are vetted and work properly.  Continue Reading

• How to escape the no-win Oracle SLA as a DBA

  Escaping the no-win Oracle SLA as a DBA is all about tying technology to business, according to two Oracle Database experts.  Continue Reading

• You don't need Oracle DB 12c Heat Map to prioritize, compress data

  PayPal Database Engineer John Kanagaraj says an AWR workaround lets you prioritize and compress data in earlier ODB versions.  Continue Reading

• Does Oracle database technology make sense for your company?

  Two Oracle database experts explain why it's crucial for companies to make a specific business case for every technology feature they wish deploy.  Continue Reading

• Three tips for using Oracle insert syntax to insert multiple rows

  If you're looking for insert syntax to insert multiple rows in Oracle, read these tips from three of our community members.  Continue Reading

• Calling stored procedures inside user-defined functions in Oracle Database

  In this expert answer, Brian Peasland explains how to call a stored procedure inside user-defined functions in Oracle Database.  Continue Reading

• Solve a PL-SQL error that creates dynamic tables

  Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically.  Continue Reading

• Hyperion upgrade creates issues with migrating Essbase data

  One reader asks how to properly migrate Essbase data when upgrading from Hyperion 9.3.1 to 9.3.3 and then 11.1.2.1.  Continue Reading

• Partitioning and Oracle licenses

  One reader asks expert Scott Rosenberg from Miro Consulting about partitioning Oracle software licenses.  Continue Reading

• Making Monday the start of the week in Oracle SQL

  One reader asks how to set up a report in Oracle SQL so that Monday is the first day of the week.  Continue Reading

• Copy Oracle Database but with no data

  One reader asks how he can make a copy of his Oracle Database but not have any data in it, for test purposes.  Continue Reading

• How to add disk to ASM diskgroup with two-way mirroring

  Learn how to add disk to ASM diskgroup with two-way mirroring and how to specify a failgroup in this tip from Oracle expert Brian Peasland.  Continue Reading

• Do I need to pay extra for licenses for an Oracle Partitioning Option?

  Do you have an Oracle Partitioning Option installed in your database? Learn whether you need to pay extra for licensing and how to be compliant in this tip from an Oracle licensing expert.  Continue Reading

• When to use double quotes in Oracle column to avoid invalid identifier

  Are you seeing an invalid identifier? Learn when to use double quotes in Oracle column names to avoid this in this tip from PL/SQL expert Dan Clamage.  Continue Reading

• How to decrypt an Oracle password using John the Ripper and checkpwd

  Learn how to decrypt an Oracle password with Oracle password crackers like John the Ripper and checkpwd in this tip from an Oracle security expert.  Continue Reading

• How to use the CREATE SESSION command to track Oracle database logins

  Oracle security expert Brian Peasland explains how to use the CREATE SESSION command in an Oracle audit table to track Oracle user database logins.  Continue Reading

• Can I download DBCA for Oracle Express Edition?

  Understand how to install the DBCA in Oracle 10g Express Edition and why you cannot simply download DBCA in Oracle in this expert tip.  Continue Reading

• Can I create multiple schemas in Oracle for one user?

  Want to create multiple schemas for one user in Oracle? Learn whether it's possible here.  Continue Reading

• How to concatenate rows into a single CLOB in PL/SQL

  PL/SQL expert Dan Clamage explains how to concatenate rows into a single CLOB in PL/SQL.  Continue Reading

• Using the SQL GROUP BY clause for counting combinations

  Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause  Continue Reading

• How to solve an ORA-1555 error in the Oracle testing database

  Expert Brian Peasland explains to a SearchOracle.com reader asks why the ORA-1555 error is showing in the Oracle testing database but not in production  Continue Reading

• How to sort an SQL UNION query with special ORDER BY sequence

  SQL expert Rudy Limeback explains how to sort an SQL UNION query using a special ORDERY BY sequence.  Continue Reading

• Using an SQL SELECT statement from a non-existing table

  SQL expert Rudy Limeback explains how to formulate a query using an SQL SELECT statement from a non-existing table.  Continue Reading

• Using LEFT OUTER JOIN query to get zero row counts in SQL

  An SQL expert explains how using a LEFT OUTER JOIN query can retrieve zero row counts in SQL.  Continue Reading

• How to create an SQL CHECK constraint for two letters

  SQL expert Rudy Limeback explains how to create a SQL CHECK constraint for two letters when trying to write a query to retrieve data from two tables.  Continue Reading

• How to disable Oracle constraints and triggers during an import in 10g

  An Oracle user asks expert Brian Peasland if constraints and triggers can be disabled on an Oracle import in Oracle 10g and how to complete this task.  Continue Reading

• How to return a zero in SQL instead of no row back for a select count

  Want your row to return a zero instead of getting no row backs for some combinations when doing a select count in SQL? Find out how here.  Continue Reading

• Business Analyst vs. Data Architect: Who should do the source-to-target mapping?

  An Oracle user asks who should do the source-to-target mapping in a company--the Business Analyst or Data Architect.  Continue Reading

• Why am I receiving the ORA-12535 error when using a remote database?

  An Oracle user asks why they are receiving the error ORA-12535: TNS: operation timed out when using a remote database.  Continue Reading

• Defining RTO, MTBF and MTTR

  Database backup and recovery expert Brian Peasland explains the terms RTO, MTBF and MTTR.  Continue Reading

• Which normal form is used most?

  SQL expert Rudy Limeback explains what normal form is used in most database projects.  Continue Reading

• Using BETWEEN with DATETIMEs in SQL

  Expert Rudy Limeback is asked if SQL can be used to retrieve data between two dates, including the two dates.  Continue Reading

• SQL query for co-authored books

  SQL expert Rudy Limeback gives advice for writing a query that involves a number of tables.  Continue Reading

• LEFT OUTER JOIN to a MIN/MAX row

  An Oracle user asks SQL expert Rudy Limeback how to use a LEFT OUTER JOIN with a MIN/MAX condition.  Continue Reading

• SQL for hourly totals for the last 48 hours

  SQL expert Rudy Limeback explains to an Oracle user how to user asks how to generate datetimes or use left outer join to get hourly totals for the last 48 hours in SQL.  Continue Reading

• What is the difference between an instance and mounting in Oracle?

  An Oracle user asks what the difference is between an instance and mounting.  Continue Reading

• Import on one table from dump file

  An Oracle user asks if it is possible to do an import on one table from a large dump file.  Continue Reading

• How do I retrieve second transaction for each customer?

  An Oracle user asks how to write a query to retrieve the second transaction for each customer.  Continue Reading

• SQL to round up to nearest thousand

  SQL expert Rudy Limeback explains how to use the CEILING function.  Continue Reading

• How to drop tablespace with missing datafile

  Oracle expert Brian Peasland explains how to drop a tablespace with a missing datafile and recreate the tablespace.  Continue Reading

• Select from one table based on condition in another

  SQL expert Rudy Limeback explains how to select from one table based on condition in another using an inner join in Oracle.  Continue Reading

• How to use DISTINCT on just one column

  SQL expert Rudy Limeback explains how to use DISTINCT on just one column.  Continue Reading

• What is the difference between obsolete and expired status?

  Oracle expert Brian Peasland explains the difference between obsolete status and expired status.  Continue Reading

• Add or drop inactive online redo log files

  Can we add or drop (inactive) online redo log files at the open stage?  Continue Reading

• LOB tables missing with exp/imp move of schema

  I have a problem when moving a schema using exp/imp. After import, the tables with LOBs are missing.  Continue Reading

• Query on dblink returning ORA-12545 error

  I have a dblink between Oracle 9i and Oracle 10g. When I execute this query sometimes I get the result, but sometimes I get the error "ORA-12545."  Continue Reading

• Restore database from backup without control files

  If you just had to restore from backup and do not have any control files, how would you go about bringing up this database?  Continue Reading

• Integrity constraints and referential integrity

  What is the difference between integrity constraints and referential integrity?  Continue Reading

• Difference between driving table and driver table in Oracle

  I need to know the difference between the driving table and driver table in Oracle.  Continue Reading

• Use HAVING with CASE to count specific instances

  I'm trying to construct a SELECT statement that will return people that have NO live courses running at the moment.  Continue Reading

• SQL query for all Fridays in a year

  How to get all Fridays for year 2007 in SQL?  Continue Reading

• Performance goals for DBAs

  Do you have any suggestions on how to measure DBA job performance and provide meaningful goals for performance management of DBAs?  Continue Reading

• How to create an index using a procedure in Oracle

  How can we create an index using a procedure?  Continue Reading

• What is the difference between left outer join and (+) outer join?

  What is the difference between left outer join and (+) outer join?  Continue Reading

• The MAX of two MAX values in SQL

  Can I get the max of two columns into one column in SQL?  Continue Reading

• Obtaining COUNTs for males and females in one SQL query

  For my table the gender column has only two values; F and M. I would like to do a count of both genders in one SQL query.  Continue Reading

• Using GROUP BY to collapse rows

  Can I get the following with one select statement in SQL?  Continue Reading

• How to increase SGA_MAX_SIZE in Oracle

  How to increase SGA_MAX_SIZE in Oracle? I got the error "ORA-03113: end-of-file on communication channel" during startup.  Continue Reading

• SQL to calculate days to next status change

  I would like to find out how long a particular customer order has been on hold in SQL.  Continue Reading

• UPDATE only rows which match another table in SQL

  I have a SQL query where I am trying to update a column in a table from data in another table. This works fine when I try to update all the records in the first table; however, Is it possible to update only the missing data?  Continue Reading

• Employees with the same first and last names in SQL

  How do I SELECT a list of employees who have the same last and first name in SQL?  Continue Reading

• Columns in the SELECT not in the GROUP BY

  I have an SQL query where I am using the SUM function with the GROUP BY clause. I need to select a couple more columns from the table without adding those columns in the GROUP BY clause.  Continue Reading

• When will support for Oracle 9i be discontinued?

  When will general support for Oracle 9i be discontinued?  Continue Reading

• What should my global database name be?

  When I type hostname it returns me this localhost.localdomain. So what should be my global database name if SID is ora9i?  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

• 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

• Check current SCN in control file

  How to check current SCN or header in the control file?  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

• 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

• Definitions of checkpoint, PMON and SMON

  What is a checkpoint? What are PMON and SMON?  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

• 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

• 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

• 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

• 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

• 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

• Steps to implement RAC

  How can I implement a RAC application in Oracle Database in my system?  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

• Grid or RAC?

  Which is better, RAC or 10g Grid? When to use both or either of these? What are the steps to install 10g Grid? Does it require a special license?  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

• 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

• 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

• 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

• 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