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

• 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

• 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

• 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

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

• 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

• 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

• 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

• 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

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

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

• 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

• 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

• 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

• 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

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

• 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

• 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

• How does the Oracle LGWR write to online redo log files?

  Learn how the Oracle LGWR writes both uncommitted and committed transactions from the log buffer to the online redo log files in this tip from Oracle expert Brian Peasland.  Continue Reading

• How to restore a dump file with the Oracle SHOW=Y option

  Oracle expert Brian Peasland explains how to restore a dump file with the Oracle SHOW=Y option before precreating tablespaces.  Continue Reading

• How to perform Oracle database recovery with a corrupt online redo log

  Learn how to perform Oracle database recovery of an Oracle 9i database with a corrupt online redo log with this SYS from Oracle expert Brian Peasland.  Continue Reading

• How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table

  Learn how to use the V$SEGMENT_STATISTICS view to find the most accessed table in an Oracle database with this query from Oracle expert Brian Peasland.  Continue Reading

• How to use the Oracle Database Upgrade Assistant (DBUA)

  Oracle expert Brian Peasland explains in what scenarios to use the Oracle Database Upgrade Assistant and more about the Oracle DBUA in this expert tip.  Continue Reading

• Can I have a single Oracle 11g RAC instance across multiple databases?

  Expert Brian Peasland explains whether you can run multiple Oracle databases in a single Oracle 11g RAC instance and how this differs with MySQL and SQL server databases.  Continue Reading

• How to perform an Oracle 8i to 10g migration

  Oracle expert Brian Peasland explains how to perform an Oracle 8i to 10g migration when moving to Oracle 10g R2 with these step by step migration tips.  Continue Reading

• Understanding the data archiving definition

  What is data archiving? Learn the data archiving definition, including the two main data archiving benefits and reasons for using data archiving tools, in this tip from an Oracle expert.  Continue Reading

• How to use RMAN CONVERT to migrate from Solaris to Oracle 11g RHEL

  Learn how to migrate Oracle from Solaris to Oracle 11g RHEL with the RMAN CONVERT command in this tip from an Oracle expert.  Continue Reading

• How to calculate Universal Power Unit license requirements

  Oracle licensing expert Scott Rosenberg explains how to calculate Universal Power license requirements using server processor speed in this expert response.  Continue Reading

• Understanding different Oracle development license strategies

  Oracle licensing expert Scott Rosenberg explains how to plan your Oracle development license strategies, such as how to use the OTN development license, in this expert tip.  Continue Reading

• Oracle error 6550 may mean incorrect Oracle export version

  Oracle expert Brian Peasland explains why you might get an Oracle error 6550 when using the wrong Oracle export version in this tip.  Continue Reading

• How to use DBMS_CRYPTO package for Oracle password encryption/hashing

  Oracle security expert Brian Fedorko explains how to perform Oracle password encryption and Oracle password hashing with the DBMS_CRYPTO package in this tip.  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 avoid invalid objects in Oracle and modify during off hours

  Oracle expert Brian Peasland explains how to avoid invalid objects in Oracle and if it is possible to modify objects such as packages and views during working hours.  Continue Reading

• Traditional table vs. Index Organized Table (IOT) in Oracle

  Don't know whether to use a traditional table or an Index Organized Table (IOT) in Oracle? Expert Brian Peasland explains the difference.  Continue Reading

• How to use the UNION operator in Oracle to join two similar tables

  Want to use the UNION operator in Oracle? Learn how to do so to join two different tables with the same number of columns and column names.  Continue Reading

• RMAN convert command to migrate Oracle from Big Endian to Little Endian

  Want to migrate Oracle from Big Endian to Little Endian? Learn how to do so with the RMAN convert command and Transportable Tablespaces in this expert tip.  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

• How to export triggers in an Oracle export command

  Are your Oracle triggers not importing after an Oracle export? Learn why in this tip from expert Brian Peasland.  Continue Reading

• How to recreate an Oracle index in a new schema with the CREATE command

  Learn how to recreate an Oracle index in a new schema using the CREATE command with this sample SQL code from an Oracle expert.  Continue Reading

• Do I need to buy an Oracle license for a development database?

  Oracle licensing expert Scott Rosenberg explains when it's required to buy an Oracle license for a development database  Continue Reading

• How to trim the Oracle listener log in Unix/Linux

  Want to trim your Oracle listener log? Oracle expert Brian Peasland explains how to do so in Unix/Linux servers without bouncing the listener.  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

• ORA-12514 error when connecting to the Oracle database through Toad

  Are you receiving the ORA-12514 error when connecting to Oracle through Toad? Learn how to solve this Oracle error.  Continue Reading

• Solving the ORA-00904 error: invalid identifier in Oracle

  Learn about ORA-00904, an invalid identifier error in Oracle, and how to solve it with this tip from Oracle expert Brian Peasland.  Continue Reading

• How to solve an Oracle import error

  Oracle expert Brian Peasland explains how to solve an Oracle import error received after taking a table export and importing it into Oracle 10g.  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

• Working with substitution variables and using EXECUTE IMMEDIATE in PL/SQL

  PL/SQL expert expert Dan Clamage gives sample code for using EXECUTE IMMEDIATE with substitution variables 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 check SQL query construction with the Mimer Validator

  Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.  Continue Reading

• How to use an SQL CASE expression

  Read an example of an SQL case expression from our SQL expert Rudy Limeback.  Continue Reading