Ask the Experts

Ask the Experts

• 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 prevent a SQL injection attack in Oracle

  Oracle security expert Brian Fedorko explains how to prevent a SQL injection attack in Oracle.  Continue Reading

• How to use string functions to make an SQL join

  SQL expert Rudy Limeback explains how to use string functions to make an SQL join using only a portion of a column value.  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

• How to use SQL's POSITION function with substrings

  Expert Rudy Limeback explains with an example how to use the SQL POSITION function with substrings.  Continue Reading

• An SQL solution for a customer order homework problem

  How do you select a max value in an SQL customer order table? Learn the answer here.  Continue Reading

• Using SQL date functions to get totals for last three days

  How can you use SQL date functions to get totals for the last three days in a table? Find out in this expert tip.  Continue Reading

• Using CASE in the SQL ORDER BY clause

  Read about why one Oracle user is getting an error when trying to use CASE in the SQL ORDER BY clause.  Continue Reading

• How to rebuild a database to change Oracle block size

  Oracle expert Brian Peasland explains how to rebuild an Oracle database in order to change the block size from 4K to 8K.  Continue Reading

• How to rebuild a built-in Oracle package body

  Oracle expert Brian Peasland explains what commands to use in order to rebuild a built-in Oracle package body.  Continue Reading

• UNIQUE index vs. PRIMARY KEY constraint to rebuild Oracle indexes and constraints

  An Oracle user asks whether it's redundant to use both a UNIQUE index and a PRIMARY KEY constraint to rebuild Oracle indexes and constraints on a very large table.  Continue Reading

• How to fix drop table and truncate table commands frozen in Oracle 10g

  Read Oracle expert Brian Peasland's explanation of why drop table and truncate table commands may be frozen in Oracle 10g.  Continue Reading

• Defining Oracle host string vs. host name

  Read expert Brian Peasland's explanation of the difference between a host String and Host Name in Oracle.  Continue Reading

• What's the difference between an SQL inner join and equijoin?

   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 the SQL date function to find aggregate totals by month

  Read SQL expert Rudy Limeback's solution for using an SQL date function, EXTRACT, to find aggregate totals by month  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 return multiple values for THEN clause in an SQL CASE expression

  Read this tip from SQL expert Rudy Limeback on how to return multiple values in THEN clause of SQL CASE expression.  Continue Reading

• Tips for derived tables in SQL and using FULL OUTER JOINs

  SQL expert Rudy Limeback gives his strategy for using FULL OUTER JOINs on derived tables in SQL. Read the tip here.  Continue Reading

• How to write an SQL query for two foreign keys to the same table

  A SQL user asks how to write a query for two foreign keys to the same table. Read SQL expert Rudy Limeback's answer here.  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

• What is a staging table, and how is the staging concept used in Oracle?

  Expert Brian Peasland gives an example of what the staging concept is and how to use a staging table in Oracle.  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 configure Oracle DB_BLOCK_SIZE and Solaris operating system block size

  Read this expert explanation of DB_BLOCK_SIZE vs. operating system block size configuration in an Oracle 10g database operating on Solaris 10.  Continue Reading

• How does Oracle instance recovery work and what happens internally?

  Oracle expert Brian Peasland explains how an Oracle instance recovery works and what happens internally during the process in this tip.  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

• Using nested SQL string functions to find ERP customer values in a table

  SQL expert Rudy Limeback explains the value of nested SQL string functions when extracting the ERP customer value for all rows in a table.  Continue Reading

• Can the Oracle database schema be cleared without dropping?

  An Oracle user asks if the Oracle database schema can be cleared without dropping. Read Brian Peasland's answer here.  Continue Reading

• Tips for how to size an Oracle server

  Using the usage patterns on an existing server, Database Design and Architecture expert Brian Peasland explains how to size an Oracle server.  Continue Reading

• Do DDL statements roll back, and why?

  An Oracle user asks if DDL statements roll back and why they would not be written to roll back segments. Read the answer here.  Continue Reading

• RMAN catalog backup: One catalog vs. multiple catalogs

  Database Backup and Recovery expert Brian Peasland explains why the best approach is to have one Recovery Catalog for all databases.  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

• CASE expressions in the ORDER BY clause

  SQL expert Rudy Limeback discusses using a CASE expression in the ORDER BY clause.  Continue Reading

• SQL to select rows 1000 through 3000 in a table

  An Oracle user asks if they can use SQL to select rows 1000 through 3000 in a table.  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

• How to restore a database using RMAN to Point in Time

  An Oracle user asks how to restore a database using RMAN to Point in Time.  Continue Reading

• Partitioning a table in Oracle to improve performance

  An Oracle user asks if partitioning a table will improve performance.  Continue Reading

• User vs. schema in Oracle

  An Oracle user asks what the difference is between a user and a schema.  Continue Reading

• Is there any tool to migrate data from DB2 to Oracle?

  Expert Brian Peasland talks about the Oracle Migration Workbench, which can migrate data from DB2 to Oracle.  Continue Reading

• Defining RTO, MTBF and MTTR

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

• Update a specific column in a field or row?

  An Oracle user asks if SQL can be used to update a specific column in a field.  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

• Which normal form is used most?

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

• Why am I getting the ORA-03113 error when trying to refresh a materialized view?

  An Oracle user asks expert Brian Peasland why they are getting the ORA-03113 error when trying to refresh a materialized view completely.  Continue Reading

• ORA-27101 error: shared memory realm does not exist

  An Oracle user asks why their machine is abnormally terminating and they are receiving the ORA-27101 error.  Continue Reading

• Hot backup when Oracle Database is up

  An Oracle user asks Database Backup and Recovery expert Brian Peasland the following question: "What is the procedure for doing a hot backup when the database is up?"  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

• Checkpoint vs. commit process

  An Oracle user asks if there is a relationship between CKPT and commit in Oracle.  Continue Reading

• How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?

  An Oracle user asks expert Brian Peasland: How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?  Continue Reading

• Can I resize the SYSTEM tablespace?

  An Oracle user asks if they can resize the SYSTEM tablespace.  Continue Reading

• Oracle DBA interview questions

  A SearchOracle.com user asks what questions they can expect in an interview for the position of a DBA.  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

• Benefits of a 3NF level of normalization

  An Oracle user asks expert Brian Peasland his general opinion regarding normalizing data in an Oracle database.  Continue Reading

• In Oracle, does the number of columns in a table affect the performance during SELECT/INSERT/UPDATE?

  An Oracle user asks if the number of columns in a table affect the performance during SELECT/INSERT/UPDATE.  Continue Reading

• How to move data faster with a database link

  Oracle expert Brian Peasland explains how to move data across a database link to speed up an import.  Continue Reading

• How can I solve the ORA-4030 error?

  An Oracle user asks how to solve the ORA-4030 error: out of process memory .  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

• How to get history of modified data from Oracle databases

  An Oracle user asks how to get the history of data modified from an Oracle database.  Continue Reading

• What is the difference between a production DBA and a developer?

  Oracle DBA Michael Hillenbrand explains the difference between a production DBA and a developer.  Continue Reading

• Can I export an Oracle database from UNIX and import to Windows?

  An Oracle user asks if they can export an Oracle database from UNIX to Windows.  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

• Correlated update in Oracle

  SQL expert Rudy Limeback explains how a query is meant to be a correlated update.  Continue Reading

• How DDL and DML commands work in Oracle

  Expert Brian Peasland explains how DDL and DML commands work in Oracle.  Continue Reading

• Why am I getting the ORA-01195 error when issuing a recover command?

  An Oracle user asks why they are seeing the ORA-01195 error when issuing a recover command.  Continue Reading

• Recreating advanced queuing objects during database upgrade

  Expert Brian Peasland explains how solving the ORA-28031 error will enable an Oracle user to recreate queue tables.  Continue Reading

• SGA sizing in Oracle

  An Oracle user asks about a discrepency in SGA sizing.  Continue Reading

• Error during RMAN backup

  An Oracle user asks why they are receiving an error when conducting an RMAN backup.  Continue Reading

• SQL to round up to nearest thousand

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

• How can I find statistics on total memory usage and database connections?

  An Oracle user asks How to find statistics on total memory usage and database connections.  Continue Reading

• How do you create a link between two databases inside a stored procedure?

  An Oracle user asks how to create a link between two databases inside a stored procedure.  Continue Reading

• Modifying SYS password in a RAC environment

  An Oracle user asks how their SYS password may be affected when doing "alter user sys identified by pwd;" on one node after OS authentication.  Continue Reading

• Defining Real Application Clusters (RAC) vs. Storage Area Network (SAN)

  Oracle expert Brian Peasland explains the differences between an RAC and SAN.  Continue Reading

• Why am I getting the ORA-02019 error?

  An Oracle user asks why they are getting the ORA-02019 error when trying to create a link between databases.  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

• The last transaction date every month

  SQL expert Rudy Limeback explains how to display the last date of the record of every month.  Continue Reading

• How to archive and decommission a database

  An Oracle user asks the best way to archive and decommision a 5GB Oracle 7.3 Database.  Continue Reading

• File permissions when doing an RMAN backup

  Oracle expert Brian Peasland explains why an error message may be related to file permissions.  Continue Reading

• Taking export for all schemas

  An Oracle user asks what syntax to use when taking full export of a database without data and for all schemas.  Continue Reading

• Converting Long Raw to Blob

  Expert Greg Williams explains when and how to convert Long Raw to Blob using PL/SQL.  Continue Reading

• What is the difference between a relational database and a repository?

  An Oracle user asks the difference between a relational database and a repository.  Continue Reading

• Using the DRIVING_SITE hint

  Oracle expert Brian Peasland explains how the DRIVING_SITE hint can help an optimizer to run a remote query on either the local or the remote system.  Continue Reading

• Running a Monday-Friday report to include weekend data

  SQL expert Rudy Limeback explains how to run a Monday-Friday report to include weekend data.  Continue Reading

• Return non-matching rows from both tables

  An Oracle user asks how to return only non-matching rows when joining two tables.  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 transfer data from Oracle to MS SQL Server

  SOA and Integration expert Harish Harbham explains how to transfer data from Oracle to MS SQL Server.  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

• Allocating multiple datafiles in a tablespace

  Oracle expert Brian Peasland explains how Oracle allocates multiple datafiles in a tablespace.  Continue Reading

• Using SQL to get counts of counts

  Expert Rudy Limeback explains how to use SQL to get counts of counts in an Orders table.  Continue Reading

• Oracle 11i vs. Oracle 11g

  Expert Michael Hillenbrand explains the difference between Oracle 11i and Oracle 11g.  Continue Reading

• Where is the dump of an export located in an Oracle 9i export?

  A SearchOracle.com reader asks, "Where is the dump of an export located in an Oracle 9i export?"  Continue Reading

• Can I use SQL*Plus to remove archived redo logs?

  Our Database Backup and Recovery expert answers a SearchOracle.com member's question about using SQL*Plus to remove archived redo logs.  Continue Reading

• Counting NULL columns

  Our SQL expert explains how to count null columns in a row.  Continue Reading

• PL/SQL function as part of a WHERE clause

  If I have a statement that calls a PL/SQL function as part of the where clause...  Continue Reading

• Full name column from last name and first name

  Our SQL expert answers the question: "How do you create a default value of a column that is the concatenated value of two other columns? For example, I have a column for last_name and a column for first_name, and want to create a column for the ...  Continue Reading

• Row numbers over partitions in SQL

   Continue Reading

• Counting a row's NULL columns

  How can I count the number of columns in a row that are not null in SQL?  Continue Reading

• How many redo log files?

  How can I know how many redo log files are enough for a database that receives 65,000 transactions per minute and how large should they be?  Continue Reading