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

• What happened to the Oracle PULA software licensing contract?

  Word of a purported Oracle Perpetual Unlimited License Agreement slipped out last year, but nothing has been heard since. Oracle's silence on the PULA is all about the cloud, experts say.  Continue Reading

• What's the biggest trap for users in an Oracle licensing agreement?

  The biggest Oracle licensing trap isn't in how you use the vendor's software, but in the license agreement itself and how easy Oracle makes it for customers to start out noncompliant.  Continue Reading

• How do Oracle on-premises and Oracle cloud licensing differ?

  Software licensing expert Keith Dobbs explains how Oracle's cloud focus has affected its licensing policies -- and how Oracle Cloud at Customer could change things for on-premises users.  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

• Dynamic query failing with a single date condition

  A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query.  Continue Reading

• Managing Oracle connections from third-party apps

  Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.  Continue Reading

• Perfect the Oracle RMAN duplicate 10g command

  One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.  Continue Reading

• Doing SQL arithmetic with SELECT instead of arithmetic operations

  Learn how to do simple SQL arithmetic operations without using the arithmetic operators.  Continue Reading

• Managing SQL parent table-child table relations

  Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.  Continue Reading

• Three important facets of Oracle Hyperion project implementation

  When implementing an Oracle Hyperion project, a company must be aware of technical, management and roadmap issues.  Continue Reading

• Using SQL SELECT and SQL UNION ALL statements to sort numbers

  Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures.  Continue Reading

• How to copy Oracle Database 9i to a new server

  Expert Brian Peasland explains to one puzzled reader how to copy Oracle Database 9i from one production server to another.  Continue Reading

• Confusion around PL/SQL to_date and to_char functions

  One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values.  Continue Reading

• Why won't TNS listener connect with Oracle Database 11g 32-bit?

  One reader asks why his 64-bit TNS listener won't connect with 32-bit Oracle Database 11g.  Continue Reading

• Where to look for Oracle DBA learning materials

  Readers ask SearchOracle.com expert Brian Peasland where they can find learning materials for becoming a better Oracle database administrator.  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

• Inconsistent data type error in Oracle Database 9i

  A reader asks SQL expert Daniel Clamage about an inconsistent data type error he's getting on a query running in Oracle Database 9i.  Continue Reading

• Oracle licensing on Oracle Database Standard Edition

  One reader asks two questions about licensing Oracle Database Standard Edition.  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

• Oracle third-party support has benefits, drawbacks

  Expert Al Marmero describes the pluses and minuses of Oracle third-party support. Cost, quality and potential litigation are all key considerations.  Continue Reading

• Partitioning and Oracle licenses

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

• Understanding Oracle Exadata Smart Scan

  Expert Shyam Varan Nath explains what Oracle Exadata Smart Scan does and how it can improve query performance on Exadata.  Continue Reading

• Examining mobile applications for J.D. Edwards

  Oracle recently announced some new mobile apps for J.D. Edwards. Expert Al Marmero talks about their benefits and potential drawbacks.  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

• Improve the Media Recovery Process (MRP) in Oracle Data Guard

  One reader asks expert William Cullen why the Media Recovery Process (MRP) in Oracle Data Guard shouldn't be used in multi-node Real Application Clusters (RAC) environments.  Continue Reading

• Working with binary large objects (BLOBs)

  One reader asks how to create and retrieve a binary large object (BLOB), and SQL expert Karen Morton gives some comprehensive advice and tips.  Continue Reading

• Creating a virtual SQL table for date values

  One reader asks how to use SQL to track his company employees' absentee days, and expert Dan Clamage suggests creating a virtual table.  Continue Reading

• Oracle SQL: Using the analytic rank function

  Expert Karen Morton explains how to use a simple analytic rank function to sort through a group of golf scores.  Continue Reading

• Oracle licensing with logical partitions (LPARs)

  One reader asks about what Oracle licensing his organization must have if they have an environment with two LPARs with one active node.  Continue Reading

• Itanium's core factor affects Oracle DR licensing

  One reader asks about licensing Oracle Database 11g on the company's Itanium-based HP servers, and a discussion on Oracle's processor core factor table ensues.  Continue Reading

• Using inline view to better query joined tables

  One reader asks how to best query data from two joined Oracle tables.  Continue Reading

• Oracle license requirements in disaster recovery scenarios

  One reader asks whether they need an Oracle license to back up their database to tape.  Continue Reading

• SQL query for sorting through ID columns

  A reader asks Oracle expert Karen Morton how to query a group of rows based on their text columns.  Continue Reading

• Avoiding and fixing mutating table errors

  PL/SQL expert Daniel Clamage tells one reader why her merge statement is creating a mutating table error.  Continue Reading

• Oracle temporary tablespace too large

  One reader asks how he can adjust a query to prevent his temporary tablespace file from getting too large.  Continue Reading

• Mirroring in Oracle RAC and Data Guard

  A reader asks a question about mirroring on Oracle RAC using Data Guard.  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

• Oracle RAC and distance limitations

  One reader asks what the Oracle RAC distance limitations are for two environments being run in sync.  Continue Reading

• Object privileges in Oracle database security

  A reader asks a question about the GRANT OPTION as it relates to object privileges in Oracle database security.  Continue Reading

• Oracle perpetual license policies

  One reader asks about his company's Oracle software licenses that they want to drop support on, saying that Oracle wants them to discontinue product use altogether.  Continue Reading

• Changing file paths in a recovered Oracle database

  Expert Brian Peasland helps a reader figure out how to successfully remap the file path for a database that had to be recovered from a dead server.  Continue Reading

• Oracle Exadata and SGA caching

  One reader asks Oracle Exadata book co-author Rick Greenwald about the SGA caching dynamic in Exadata.  Continue Reading

• Exadata, hybrid columnar compression, and inserts

  One reader asks Oracle Exadata book co-author Rick Greenwald about compression on row-insertion on Exadata.  Continue Reading

• Cache Fusion for RAC on Exadata

  One reader asks whether Cache Fusion for Oracle RAC is implemented differently on Oracle Exadata compared to other platforms.  Continue Reading

• Purchasing extra licenses despite no software modifications

  Oracle licensing expert Scott Rosenberg answers a reader's question about their adoption of Oracle E-Business Suite.  Continue Reading

• The problem with row-by-row processing in Oracle Database

  One reader asks what's wrong with his script, and Oracle expert Karen Morton explains the problem with row-by-row processing.  Continue Reading

• Most valuable skill: DBA, PL/SQL or Oracle Forms

  Want to know what skill -- DBA, PL/SQL or Oracle Forms -- is most valued in the IT industry today? Hear from SearchOracle expert Dan Clamage on the topic.  Continue Reading

• Creating lookup tables that support a drop-down menu for categories

  Want to create tables to support drop-down menus for categories in PL/SQL? Learn how in this tip from PL/SQL expert Dan Clamage.  Continue Reading

• Restoring to a backup instance on a backup server for a disaster recovery plan

  In this Oracle database and backup recovery tip, expert Brian Peasland helps a reader restore a backup instance on a backup server in order to develop a disaster recovery plan.  Continue Reading

• Pulling data from a text file to query

  In this SQL tip, expert Karen Morton explains how one can pull data from a text file into a usable format so that it's available for an SQL query.  Continue Reading

• Using the CPU_Count parameter in Oracle partitioning

  Are you allowed to use the CPU_Count parameter in Oracle partitioning? Learn whether this is acceptable in this Q&A from Oracle licensing expert Scott Rosenberg.  Continue Reading

• How can I identify data files with active Oracle I/O?

  Can you find the data files with active Oracle I/O without affecting the application? Oracle expert Brian Peasland answers this question and offers solutions in this tip.  Continue Reading

• Can I use special characters in Oracle table names?

  Learn why you may want to avoid using special characters in Oracle table names when they also have meaning in utilities or SQL scripts in this tip from Oracle database expert Brian Peasland.  Continue Reading

• Performing an Oracle application backup: What is the best method?

  Trying to perform an Oracle application backup? Learn about your options for Oracle application backups, including putting schemas in tablespaces and using exp/imp, in this tip from Oracle database expert Brian Peasland.  Continue Reading

• How to use a hierarchical query to generate ROWNUMs using PL/SQL

  Want to generate numbers from SQL? Learn why using a PL/SQL hierarchical query may be a better solution than using a data dictionary view in this tip from PL/SQL expert Dan Clamage.  Continue Reading

• Minimize database downtime when moving an Oracle DB to a new server

  Learn how to minimize database downtime when moving your Oracle 10gR2 database from a Windows 2003 32-bit server to a Windows 2008 64-bit server, using the Database Configuration Assistant, in this tip from an Oracle database expert.  Continue Reading

• Do I buy an extra license for an OAS to Fusion Middleware 11g upgrade?

  Learn whether you need to buy an extra license when performing an upgrade from Oracle Application Server (OAS) to Oracle Fusion Middleware 11g for Forms and Reports in this tip from Oracle licensing expert Scott Rosenberg.  Continue Reading

• Licensing Dev and QA environments with Oracle SE in production

  Do you need extra licenses for Dev and QA environments when you have Oracle Standard Edition (SE) in production? Learn about licensing Dev and QA environments in this tip from Oracle licensing expert Scott Rosenberg.  Continue Reading

• How to perform incomplete recovery to overcome Oracle database error

  Learn how to perform incomplete recovery in Oracle to overcome an error after restoring with RESETLOGs in this tip from Oracle expert Brian Peasland.  Continue Reading

• Understanding rollback in Oracle following SHUTDOWN IMMEDIATE

  Oracle expert Brian Peasland explains the concept of rollback in Oracle, and explains how Oracle rollback works when a SHUTDOWN IMMEDIATE is issued.  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

• Understanding licensing for Oracle database failover

  Oracle database failover nodes may need to be licensed on their own. Learn why and how to tell what qualifies as “failover” in this tip from Oracle licensing expert Scott Rosenberg.  Continue Reading

• Do I have to license non-production databases supporting Oracle EBS?

  Do I have to license non-production databases for Oracle EBS? Learn the answer from licensing expert Scott Rosenberg, who explains why you might have to license all database instances for EBS.  Continue Reading

• Does Oracle support non-certified products?

  Does Oracle support non-certified products and non-certified products? Learn more about what certified means in this tip from Oracle expert Brian Peasland.  Continue Reading

• Oracle backup and recovery strategies: Export dump vs. RMAN

  Should you use an export dump or RMAN in your Oracle backup and recovery strategies? Learn about the importance of employing both in this tip from Oracle expert Brian Peasland.  Continue Reading

• Using SQL Automatic Workload Repository to find Active Session History

  SQL expert Karen Morton explains how to use the Automatic Workload Repository (AWR) in SQL to find the Active Session History (ASH) of a session.  Continue Reading

• How to license a two-node, Oracle mirrored environment

  Do you need to license both nodes in an Oracle mirrored environment? Learn the answer in this tip from Oracle licensing expert Scott Rosenberg.  Continue Reading

• How fingerprint scanning works with Oracle 9i security

  Interested in using fingerprint scanning in Oracle 9i? Learn about Oracle 9i security and how it works with a biometric scanner in this tip from Oracle security expert Brian Fedorko.  Continue Reading

• How to use a CASE statement in SQL to change gender values in a column

  Learn how to use a CASE statement in SQL to convert all values in a column from female to male or male t female in this tip from SQL expert Karen Morton.  Continue Reading

• Using the Oracle CEIL function to round to nearest five thousandth

  The Oracle CEIL function can be used to round to the nearest five thousandth. Learn how to do this in this tip from SQL expert Karen Morton.  Continue Reading

• What is the required skill set for an Oracle RAC DBA?

  Interested in becoming an Oracle RAC DBA? Learn about the necessary skill set and what kind of knowledge is necessary to work with Oracle RAC in this tip from an Oracle ACE director.  Continue Reading

• How to use the SQL MAX function to sum the values of a column

  SQL expert Karen Morton explains how to use the SQL MAX function to sum the values of one column based on comparison with another in this expert tip.  Continue Reading

• How to use GROUP BY GROUPING SETS to find books with multiple authors

  SQL expert Karen Morton explains how to use GROUP BY GROUPING SETS in an SQL query to find all books with more than one author in your books database.  Continue Reading

• Understanding foreign key column naming conventions

  Want to know how to name the foreign key column in your table? This tip from Oracle PL/SQL expert Dan Clamage will help you understand the best way to name a foreign key column.  Continue Reading

• Using the PL/SQL input parameter and forward slash in a PL/SQL program

  Learn how to use the PL/SQL input parameter and forward slash in a PL/SQL program to avoid an error message in this tip from PL/SQL expert Dan Clamage.  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

• What are the best PL/SQL books?

  Read Oracle expert Dan Clamage’s recommendations for PL/SQL books that allow readers to gain a basic understanding of PL/SQL, along with other places to find PL/SQL resources.  Continue Reading

• Can I run Oracle 9i on a Windows 64-bit O/S?

  If you want to migrate Oracle 9i to a Windows 64-bit O/S environment, learn in this expert tip why you may want to install Oracle 10g or 11g first.  Continue Reading

• How to run an Oracle database backup script during off hours

  Want to run an Oracle database backup script during off hours? Learn how with these samples scripts from an Oracle expert.  Continue Reading

• How can I find similar values in two Oracle tables in PL/SQL?

  Looking to find similar values in two Oracle tables in PL/SQL? Learn about how to do so with this tip from Oracle PL/SQL expert Dan Clamage.  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 avoid duplicates of Oracle delimited codes in CLOB column

  Learn how to avoid duplicates of Oracle delimited codes in a CLOB column by scanning the column in this tip from Oracle PL/SQL expert Dan Clamage.  Continue Reading

• Can I copy table data with unlike primary keys from Table1 to Table2?

  Oracle PL/SQL expert Dan Clamage explains two methods for copying table data from table1 to table2 when the tables have different primary keys in this expert tip.  Continue Reading

• How to use iterative scans to create hierarchical queries in SQL

  PL/SQL expert Dan Clamage explains how to use iterative scans to create hierarchical queries in SQL that are database agnostic.  Continue Reading

• How to list rows with special characters in PL/SQL

  Want to learn how to list rows with special characters in PL/SQL? Read this tip from our PL/SQL expert for advice on how to write a PL/SQL query using the translate, char or trunk functions.  Continue Reading

• How to use a stored procedure to fetch from ref cursor in Oracle

  Oracle expert Dan Clamage explains how to use a stored procedure to fetch from a ref cursor in Oracle in this tip.  Continue Reading

• Why can't I install Oracle 9i on Windows 2003?

  Having trouble installing Oracle 9i on Windows 2003? Learn why in this tip from Oracle expert Brian Peasland.  Continue Reading

• How to use SQL Plus to export Oracle data from more than one table

  Learn how to use SQL Plus to export Oracle data from more than one table with the SPOOL command in this tip from Oracle expert Brian Peasland.  Continue Reading

• How to perform database recovery with a corrupt control file in Oracle

  Learn how to perform database recovery with a corrupt control file in Oracle with these step by step instructions, including how to use the CREATE CONTROLFILE command, from Oracle expert Brian Peasland.  Continue Reading

• How to perform a Big to Little Endian conversion in Oracle 8i

  Learn how to perform a Big to Little Endian conversion in Oracle 8i using either an Oracle upgrade or export/import in this tip from Oracle expert Brian Peasland.  Continue Reading

• How to enable remote Oracle OS authentication with OS_AUTHENT_PREFIX

  Oracle expert Brian Fedorko explains how to enable remote Oracle OS authentication in Windows and Linux, including why you might receive the ORA-010145 error when trying to have an Oracle user identified externally.  Continue Reading

• Why am I receiving Oracle memory allocation errors?

  Are you receiving Oracle memory allocation errors? Learn why you may be having memory allocation problems in Oracle in this tip from Oracle expert Brian Peasland.  Continue Reading

• How to execute DML in an Oracle trigger with EXECUTE IMMEDIATE

  Read how to execute DML in an Oracle trigger with the EXECUTE IMMEDIATE statement in this tip from Oracle expert Brian Peasland  Continue Reading