Ask the Experts

Ask the Experts

• 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

• Importing tables with integrity constraints

  An Oracle user asks why they are receiving errors when importing a table with integrity constraints.  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 two instances of Oracle on one server

  An Oracle user asks what kind of performance issues may occur when running two instances of Oracle on the same server.  Continue Reading

• Solving the ORA-1650 error message

  Oracle expert Brian Peasland explains how to solve the ORA-1650 error message.  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

• Cleaning a temporary tablespace

  A SearchOracle.com member asks, "I have an Oracle 10g database with its temporary tablespace full. Is there any way to clean this tablespace?"  Continue Reading

• Clone database vs. standby database

  A SearchOracle.com member asks, "Can you tell me the difference between clone database and standby database? "  Continue Reading

• Using rule-based optimization to optimize a query

  A SearchOracle.com member asks, "Can I have a detailed explanation of how and when the 15 rules of Rule Based Optimization are used when optimizing a query?"  Continue Reading

• Value of Oracle Certified Professional (OCP) exam in landing entry-level job

  A SearchOracle.com member asks, "Will taking the OCP Exams be beneficial to me in getting a DBA or Database Designer job?"  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 important is ERP knowledge?

  Oracle expert Michael Hillenbrand explains how important ERP expertise is when starting a career in Oracle.  Continue Reading

• Using Oracle Streams

  Oracle expert Harish Harbham explains how Oracle Streams can be used for replication.  Continue Reading

• How to convert a SQL Server 2005 database to Oracle 10g

  Expert Harish Harbham answers the question, "How do you convert SQL Server 2005 database into 0racle 10g?"  Continue Reading

• How to create a mediated schema

  A SearchOracle.com member asks, "I have two different relational schemas with me. Can I get an example as to how to integrate them and create a mediated schema?"  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

• Deleting a multiplexed control file

  A SearchOracle.com reader asks, "What will happen to my database if I delete a multiplexed control file?"  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

• How to call a procedure in a procedure

  A SearchOracle.com reader asks, "How do I call another procedure p2 from p1?"  Continue Reading

• Allocating multiple datafiles in a tablespace

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

• Using the EXPLAIN PLAN command

  Oracle expert Brian Peasland explains how using the EXPLAIN PLAN command will help to execute an insert statement.  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

• Retrieving row numbers with SQL

  A SearchOracle.com member asks, "How do you retrieve row numbers along with the data?"  Continue Reading

• Clustered computing vs. grid computing

  A SearchOracle.com member asks, "What are the differences between grid and clustered computing?"  Continue Reading

• Speed of low-cost query

  A SearchOracle.com member asks why a low-cost query has a slower speed than expected.  Continue Reading

• Oracle 11i vs. Oracle 11g

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

• What is the best book for Oracle certification?

  A SearchOracle.com member asks, "What is the best book for Oracle certification?"  Continue Reading

• Can I change the import order of tables in a dump file?

  A SearchOracle.com member asks, "Can I change the order in which a table gets imported in a dump file?"  Continue Reading

• How to perform an incremental level 0 backup

  Our Database Backup and Recovery expert explains how to perform an incremental level 0 backup in RMAN.  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

• How to recover archive logs in Standby database

  A SearchOracle.com member asks, "In Standby database, if some archive logs are missing (could not be transferred), then how do you recover?"  Continue Reading

• Excess redo logs in tablespace backup mode

  Our Database Backup and Recovery expert answers a question about excess redo logs.  Continue Reading

• Counting NULL columns

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

• SQL to count values of a status code

  Our SQL expert answers a question about counting values of a status code.  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

• What are the different date formats in SQL?

  A member asks our SQL expert: "What are the different date formats? What is a timestamp? Please give me one example."  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

• Importing from Oracle 8i to Oracle 10g

  Can I use the following command to import into an Oracle 10g R2 database? What will happen to my sys and system objects? Will they have the 8i objects?  Continue Reading

• Exp/imp for complex cross-platform migration and upgrade

  I have to migrate data from Oracle 7, Oracle 8i, Oracle RAC 9i to Oracle 10g on different operating systems. Can I do it with the exp/imp utility?  Continue Reading

• How to back up archive log files in RAC

  I have two-node Oracle 10g RAC with ASM on an AIX system. I want to take a backup of my archive log files from node 1.  Continue Reading

• One datafile not accessible

  I did a dbverify through Brtools. It ended with one particular datafile not accessible.  Continue Reading

• Latest transaction if no recent prior transactions

  I would like to select the latest customer record only if there are not any prior transactions for the previous 180 days. How do I do this in SQL?  Continue Reading

• Three ways SQL can count rows by type

  I want to get the individual Type field data and their count in SQL.  Continue Reading

• SQL to select only certain times within a date range

  I have a field in my table which stores dates and times in datetime format. I want to get the data from 10 p.m. to 6 a.m. between 1st July and 31st July. What's the best way to get this data?  Continue Reading

• How to start multiple instances in Oracle 10g

  I want to know how to start multiple instances in Oracle 10g. I have created two databases using DBCA and I have one database server.  Continue Reading

• Varchar or number for better performance?

  Which one is better for performance as a unique index, varchar or number?  Continue Reading

• LAST_OPER_TYPE column in v$sga_dynamic_components

  Why does the LAST_OPER_TYPE column in v$sga_dynamic_components always contain the value INITIALIZING for the "DEFAULT 16K buffer cahce" row?  Continue Reading

• Unable to view Oracle tables in NetBeans

  I am very new to databases (Oracle 9.2 installed) and my problem is I'm not able to view tables in NetBeans 5.5 even after establishing the connection with the database via NetBeans.  Continue Reading

• Moving from rollback segments to undo tablespace in Oracle 10g

  We are using rollback segments in an Oracle 10g database. We would like to move from using rollback segments to the undo tablespace.  Continue Reading

• Where to download Oracle 8.1.7 ProC compiler media

  I need the 8.1.7 ProC compiler. We do not have the media. Tried 8.1.5 ProC and it did not work. Can you direct me to sources that may have the media for purchase/download?  Continue Reading

• How to move tables from system tablespace to user tablespace

  I want to move a few tables from the system tablespace to a user tablespace. I have some data on the tables, and I also have some user privileges. I want to retain both.  Continue Reading

• ORA-12560 error with Oracle 10g Instant Client

  I installed Oracle 10g Enterprise Edition on one computer, and the 10g Instant Client on another. When I try to connect from the other computer with I get an ORA-12560 (TNS protocol adaptor error).  Continue Reading

• Number of rows in multiple tables

  I need a query which will give the tablename and the count of the number of rows. Is it possible?  Continue Reading

• Stringing together columns with UPDATE SQL

  Is it possible in T-SQL or PL/SQL to string together the values in a column grouped by another column?  Continue Reading

• Large data migration from XML database to Oracle 10g

  We are planning to migrate 3 TB of data from Oracle 9i XML DB to Oracle 10g. What do you recommend?  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

• Stored procedure to autotransfer data between Oracle servers

  How to make a stored procedure to autotransfer updated data from a remote server to a central server?  Continue Reading

• Transparent Gateways between Oracle database and DB2

  I am searching for an easy way to fetch and insert data into/from DB2. Can you give me more information on Transparent Gateways?  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

• How to post backup to remote server with RMAN

  I need to take a backup of the local server and post that backup to the central server via Internet with RMAN. I can back up the database and connect to the remote database. But I can't find how to post the backup to the remote server.  Continue Reading

• IMP-00017 error message with Oracle import

  I am doing an import and encountered this error message: "IMP-00017: following statement failed with ORACLE error 910."  Continue Reading

• How to export data by date in Oracle

  Is there a way to export data by date?  Continue Reading

• Can I drop a deleted datafile without dropping the tablespace?

  If a tablespace has two datafiles, and one of the datafiles, which is empty, is accidentally deleted, can I drop the deleted datafile without dropping the tablespace?  Continue Reading

• Create database on target server before cloning Oracle 9i database?

  To clone an Oracle 9i database on Windows Server, do you first have to create a database on the target server?  Continue Reading

• Sargable predicates in SQL

  What are examples of sargable or stage 1 predicates and non-sargable or stage 2 predicates? Are they still pertinent in today's SQL?  Continue Reading

• How to create a database schema in Oracle?

  How to create a database schema?  Continue Reading

• What is the System Global Area (SGA)?

  Can you tell me what is SGA and how I manage it? What is the importance of it?  Continue Reading

• Functions of PFILE, SPFILE and INIT.ORA

  What is the difference between the PFILE, SPFILE and INIT.ORA, and what is the function of each?  Continue Reading

• Difference between RECYCLEBIN and 'DROPPED' column

  What is the difference between the RECYCLEBIN and the 'DROPPED' column in Oracle 10g?  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

• Default values for kernel resources in Oracle

  Where can I find information about default values for the kernel resources?  Continue Reading

• Can I mark a record as deleted in Oracle?

  Is it possible to mark a record as deleted? I have to reuse the deleted record.  Continue Reading

• When were the SYS/SYSTEM passwords last changed?

  How can I find out what date the SYS/SYSTEM passwords have been last changed on?  Continue Reading

• How to find transactions being committed to Oracle database

  How to find the transactions being committed? Which database views help to find this?  Continue Reading

• Business case for Oracle Database upgrades

  How can I make a business case to convince my top management that the time to upgrade all our production Oracle 9i databases to Oracle 10g is now?  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

• How to migrate 1.5 TB Oracle database from Solaris to AIX

  We have a huge database (1.5 TB) on Solaris (Oracle 10g) and we want to migrate it to AIX -- is expdp the best solution?  Continue Reading

• RMAN not working at command prompt

  I am getting an error with RMAN. When I type rman on command prompt, I am not getting any response, no error and no RMAN prompt.  Continue Reading

• Unique constraint violations, "parent key not found" errors

  I exported a schema from a production database and tried to import to the same schema in the test database. The import completed with lots of unique constraint violation and "parent key not found" error messages. How to fix it?  Continue Reading

• How to back up RAC database with RMAN

  How to back up an Oracle RAC database with RMAN?  Continue Reading

• Using RMAN from Oracle client machine

  How to use RMAN in a client machine? I'm getting an error like "RMAN is not recognized as an internal or external command."  Continue Reading

• Integrity constraints and referential integrity

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

• Using connection load balancing with Oracle RAC

  When using XA data sources on a RAC database, the best practice is to set up a singleton service to avoid split branches. Is it still safe to use connection load balancing?  Continue Reading

• Change the order of conditions in the WHERE clause

  In the below select query, can the order of the conditions in my WHERE clause change the time of execution?  Continue Reading

• How to create a password file in Oracle 9i?

  How to create a password file in Oracle 9i? What is the exact command and do we need to make any changes in the parameter file?  Continue Reading

• Create data dictionary from existing Oracle database

  I want to create a data dictionary document from an existing Oracle database.  Continue Reading

• Functionality of SYSTEM tablespace in Oracle

  What is the functionality of the SYSTEM tablespace?  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

• 20GB data dictionary causing performance problems

  We have 81 schemas in our development instance! While the schemas are usually relatively small data-wise, this arrangement makes for a SYSTEM tablespace approaching 20 GB.  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

• Online tablespace reorganization in Oracle 9i

  What is the procedure to do online tablespace reorganization in Oracle 9i?  Continue Reading

• Why no integrity constraints in Oracle E-Business Suite?

  Why doesn't Oracle's own E-Business Suite carry any integrity constraints?  Continue Reading