Ask the Experts

Ask the Experts

• 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

• 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

• Using Undo Tablespaces vs. rollback segments in Oracle 9i, 10g

  Expert Brian Peasland explains how Undo Tablespaces and Undo Segments can be used an alternative to rollback segments in Oracle 9i and 10g.  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

• Which backup strategy should I use for my Oracle 10g database?

  Database Backup and Recovery expert Brian Peasland explains how multiple backup strategies exist to backup an Oracle database.  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

• 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

• Can I use Oracle FTP for importing data from Oracle 11g into 10g?

  Database Backup and Recovery expert Brian Peasland explains why the Oracle FTP utility cannot be used for importing data from Oracle 11g into 10g.  Continue Reading

• How to achieve zero downtime for maintenance on the Oracle database

  Learn how to achieve zero downtime for maintenance on the Oracle database, and learn which maintenance operations need to be performed offline vs. online in this tip from database Backup and Recovery expert Brian Peasland.  Continue Reading

• Is Linux certification necessary for my job in Oracle?

  Expert Michael Hillenbrand discusses whether Linux certification is necessary for a job in Oracle.  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

• The SQL REPLACE function

  SQL Expert Rudy Limeback answers an Oracle user's question about the REPLACE function.  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

• ORA-06550 error when importing a 10.2 dmp file into a 9.2 database

  An Oracle user asks Database Backup and Recovery expert Brian Peasland why they are getting the ORA-06550 error error when importing a 10.2 dmp file into a 9.2 database.  Continue Reading

• Using exp and imp for Oracle Database when switching platforms

  Database Design and Architecture expert Brian Peasland answers a reader's question about importing a dump of an Oracle Database from a previous Windows machine to a Linux machine.  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

• Does making an Oracle datafile READ ONLY after a backup change the file?

  An Oracle user asks if making a datafile READ ONLY after a backup changes the file.  Continue Reading

• Cold backups after SHUTDOWN ABORT

  An Oracle user asks if a cold backup can be performed after closing the database with SHUTDOWN ABORT.  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

• Defining RTO, MTBF and MTTR

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

• Managing temporary tablespace growth in Oracle

  An Oracle user asks Database Backup and Recovery expert Brian Peasland about managing temporary tablespace growth.  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

• Receiving the ORA-01652 error with no specified tablespace

  An Oracle user asks why they are receiving the ORA-01652 error but there is no tablespace specified in the error.  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

• Remote database administration: What to look for in a provider

  Managing an Oracle shop expert Michael Hillenbrand talks about his experiences with remote database administration.  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

• ORA-06550 error when exporting a database

  An Oracle user asks why they may be getting an error when exporting a database.  Continue Reading

• Querying complex derived tables

  An Oracle user asks SQL expert Rudy Limeback how to run a query when the inner query results come from five different tables.  Continue Reading

• Connecting tables in a database

  SQL Expert Rudy Limeback gives advice on finding information on primary and foreign keys to connect tables in a database.  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

• Changing a NULL column to NOT NULL

  An Oracle user asks SQL expert Rudy Limeback how to repopulate a table after modifying the table to NOT NULL.  Continue Reading

• SQL string functions

  SQL expert Rudy Limeback explains how to use string functions.  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

• Can I add multiple datafiles and make use of all the temporary datafiles at the same time?

  An Oracle user asks: Can I add multiple datafiles and make use of all the temporary datafiles at the same time?  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

• How to use the OPatch Utility in Oracle

  Expert Brian Peasland explains how to use the OPatch Utility to find the CPU patch level applied to the particular database.  Continue Reading

• The USING BACKUP CONTROLFILE clause in Oracle commands

  An Oracle user asks how a command with the USING BACKUP CONTROLFILE clause works.  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

• Normalizing a crosstab table

  SQL expert Rudy Limeback explains how to use a UNION query to normalize a crosstab table.  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

• How does Oracle identify which password file to use?

  An Oracle user asks how the database identifies which password file to use.  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

• Moving a table with a LOB column

  An Oracle user asks if anything different has to be done when moving a table with a LOB column.  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 implement a ref cursor called in a stored procedure

  Expert Brian Peasland explains how and when to implement a ref cursor called in a stored procedure.  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

• Why am I getting the ORA-00381 error when initializing the parameter?

  An Oracle user asks why they are getting the ORA-00381 error when initializing the parameter.  Continue Reading

• Performance issues in Oracle 9i

  An Oracle user asks if upgrading their database to 10g will help solve performance and space management issues in Oracle 9i.  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 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

• Export dump with files larger than 2 GB

  An Oracle user asks how to create an export dump with files larger than 2 GB.  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

• Failed stored procedure through db link in Oracle

  An Oracle user asks why a stored procedure through db link from Oracle 10g to 8i is failing.  Continue Reading

• Creating db links from Oracle 10g to 8i

  An Oracle user asks about creating db links from Oracle 10g to 8i.  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

• 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

• 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

• Error during RMAN backup

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

• Can I drop a column in SYS schema?

  An Oracle user asks if they can drop a column in SYS schema.  Continue Reading

• Moving Oracle binaries

  An Oracle user asks if it is OK to move binaries that are on a local disk to SAN.  Continue Reading

• SGA sizing in Oracle

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

• Converting text data to an Oracle table without Oracle's SQL*Loader utility

  Oracle expert Brian Peasland explains when to use SQL Developer.  Continue Reading

• Oracle block size during import

  An Oracle user asks if there will be any problem with block sizes during import.  Continue Reading

• How to encrypt RMAN backups

  An Oracle user asks if it is possible to encrypt the backup in the Oracle 9i without using other application tools.  Continue Reading

• Entity-Attribute-Value constraints

  SQL expert Rudy Limeback explains the entity-attribute-value model.  Continue Reading

• A running total

  SQL expert Rudy Limeback explains the importance of a sequencing column.  Continue Reading

• SQL to update the header records?

  A SearchOracle.com user asks if SQL can be used to update the header records.  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

• RDBMS composite key definition

  Oracle expert Brian Peasland explains the terms composite key and problem space in RDBMS.  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

• Redo log file problems

  Expert Brian Peasland explains why it is important to make sure online redo logs are multiplexed.  Continue Reading

• Can I resize SGA while the database is up and running?

  An Oracle user asks if the SGA can be resized while the database is up and running.  Continue Reading

• Manual upgrade vs. export and import

  An Oracle user asks if they should do a manual upgrade or use the export/import utility to their new database.  Continue Reading

• Can I move a SYSTEM tablespace when the database is running?

  An Oracle user asks if they can move a SYSTEM tablespace when the database is running.  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

• Maximum limit Oracle DBMS datafiles

  Oracle expert Brian Peasland discusses how to control the maximum number of datafiles in a database.  Continue Reading

• Changing the Database Identifier (DBID)

  Oracle expert Brian Peasland explains why you never have to change the DBID.  Continue Reading

• What is the ORA-03113 error?

  Oracle expert Brian Peasland explains how the ORA-3113 error means a network connection to the database was severed.  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

• What are the differences between database migration and database upgrades?

  An Oracle user asks about the differences between database migration and database upgradation.  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

• 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