Ask the Experts

Ask the Experts

• Questions about Oracle 9i to Oracle 10g upgrade

  We are using Oracle 9i Database on a server with Oracle 9i Client on nodes with third-party software. When we upgrade our 9i server to 10g, can we connect to 10g from our existing 9i client nodes?  Continue Reading

• ORA-12535: Operation times out when trying to connect to database

  Since we moved a server, the outside PC cannot connect to the database. I can perform a successful ping to the server but my operation times out (ORA-12535) when I try to TNSPing.  Continue Reading

• How to migrate Oracle 10g to RAC cluster on Linux

  What's the best way to migrate a single-instance Oracle database, version 10.1.0.3, on Tru64 to a RAC cluster, version 10.2, on Linux?  Continue Reading

• Unable to connect to management server

  I am trying to manage my database through the Enterprise Manager Console from a remote machine, but when I try to open it an error generates: "VTK-1000 UNABLE TO CONNECT TO THE MANAGEMENT SERVER."  Continue Reading

• Impact of Oracle 8i to Oracle 10g migration on scripts

  We are going to migrate from Oracle 8i to Oracle 10g on Unix. Will the migration cause any impact on our Pro*C scripts and Unix shell scripts?  Continue Reading

• DBUA throws "not a valid xml file" error during upgrade

  While trying to run the DBUA to upgrade an Oracle 9.2 database to 10.2 I get the error "/oracle/SND/102_64/cfgtoollogs/dbua/SND/upgrade2/upgrade.xml is not a valid xml file" and the DBUA does not run.  Continue Reading

• What should my global database name be?

  When I type hostname it returns me this localhost.localdomain. So what should be my global database name if SID is ora9i?  Continue Reading

• ORA-12546 error installing 32-bit Oracle on 64-bit Windows XP

  Can I install 32-bit Oracle 8.1.17 on Windows XP 64-bit? It fails when the Database Configuration Assistant is 90% complete with an "ORA-12546: TNS permission denied" error.  Continue Reading

• Apply redo logs to update changes made during hot backup

  There was user activity during a hot backup for Oracle 10g. How do I apply redo logs to update the changes made in the database?  Continue Reading

• Migrate Oracle database from Windows to Solaris

  I would like to migrate a Windows Oracle 8.1.7 database over to an Oracle 8.1.7 instance on Solaris. What would be the best method to do this?  Continue Reading

• ORA-1157 error with Oracle 8i database restore

  I have backed up an Oracle 8i database (Windows-based) with Brightstor software. I restored it and I get an ORA-1157 error.  Continue Reading

• Configure Oracle Data Guard

  Now my company has decided to implement Oracle Data Guard. How do I configure it?  Continue Reading

• No such file or directory error

  I have written a make file to compile a proc code, it's giving the following error: "/9.2.0.1/bin/proc: error while loading shared libraries: libclntsh.so.9.0: cannot open shared object file: No such file or directory."  Continue Reading

• "Delete from emp" without "where" condition

  What's happening in the redo logs when I do a "delete from emp;" without a "where" condition?  Continue Reading

• Remotely manage Oracle 8i via Windows Terminal Services

  Is it possible to remotely manage Oracle 8i via Windows Terminal Services?  Continue Reading

• How to attach Oracle database backup files to existing server

  My client provided backup files of the Oracle database. I know how to import from normal database backups (.dmp) but am still ignorant of attaching data files to the existing server.  Continue Reading

• Difference between candidate key and composite key

  What is the difference between candidate key and composite key?  Continue Reading

• Oracle UDP protocol or Veritas LLT for interconnect traffic?

  If you had Veritas SFORAC 4.1 MP1 and were running Oracle 10g, would you use Veritas LLT protocol for interconnect traffic or Oracle's prefered UDP protocol?  Continue Reading

• Distinct values of more than one column

  How do I see distinct values of more than one column in a single SQL command?  Continue Reading

• LEFT OUTER JOIN without using LEFT OUTER JOIN

  Can I get the same result as LEFT OUTER JOIN without using LEFT OUTER JOIN?  Continue Reading

• Update two columns with one UPDATE command

  How to update two columns in a table with one update command?  Continue Reading

• Execute SQL statement from table in other schema

  I want to execute SQL statements that use other schema tables from my stored procedure. The stored procedure is in one schema and the table is in the other schema. I am getting the "ORA-01031: insufficient privileges" error.  Continue Reading

• ORA-04082: NEW or OLD references not allowed in table level triggers

  I am calling a stored procedure from a trigger. It shows the error: "ORA-04082: NEW or OLD references not allowed in table level triggers."  Continue Reading

• ORA-28500: Attempt to access a column with unsupported name length

  In one table there is a column name longer than 30 characters. I don't select this column but I get this error: "ORA-28500."  Continue Reading

• Inserting multiple rows in SQL

  How to insert multiple rows of data using insert into command?  Continue Reading

• How many schemas per application?

  We have a legacy system with three applications, each application with its own database. Should we create one schema for each application or one schema for all three?  Continue Reading

• Which datafile contains my table?

  How to know which datafile contains my table?  Continue Reading

• View for total users and default tables in Oracle database

  How to check the total users and default tables that Oracle provides when we create a new database?  Continue Reading

• What is a hash value?

  What is a hash value in Oracle?  Continue Reading

• Composite keys and normalization

  What is composite key and why do we use it? Why go for normalization?  Continue Reading

• Recover database using backup controlfile until cancel

  What happens when we run "recover database using backup controlfile until cancel;"? When do we use this command?  Continue Reading

• Check current SCN in control file

  How to check current SCN or header in the control file?  Continue Reading

• Backup and recovery strategy for very large databases

  I have two very large databases and I'm looking for a good backup/recovery strategy. One is an OLTP Oracle 9.2.0.5 10TB database and the other is an Oracle 10.2 data warehouse that is 12TB.  Continue Reading

• Add a column in a specific position in a table

  I have created one table with three columns. I want to drop one column and add the column back to the same table in the same location.  Continue Reading

• Migrate Oracle Database 9i from Solaris 9 to Solaris 10

  How to migrate Oracle Database 9i from Solaris 9 in server A to Solaris 10 in server B?  Continue Reading

• Redo log file filled up

  What to do when the current redo log file or datafile file gets filled up?  Continue Reading

• IMP-00009 and IMP-00028 errors during import

  I'm trying to import a dump file and I have the errors "IMP-00009: abnormal end of export file" and "IMP-00028."  Continue Reading

• More about ORDER BY for a specified sequence

  How could I sort a query result according to a specific order (not ascending or descending) that I will be supplying from the values of the column to be sorted?  Continue Reading

• ORA-01801 error in RAC environment

  We are getting the ORA-01801 error with one stored procedure in a RAC environment.  Continue Reading

• Migrating to Oracle RAC from single-instance database

  How do I migrate from a single-instance database to a RAC cluster?  Continue Reading

• Dedicated vs. shared server configuration

  What is the difference between dedicated and shared server configuration?  Continue Reading

• Totals from a UNION query

  I'm trying to retrieve a count of rows using a union, but for some reason I get two rows returned with different counts.  Continue Reading

• Convert minutes to HH:MM:SS format

  I need a SQL query to convert 130 minutes into HH:MM:SS format.  Continue Reading

• Checking for URL to administer Oracle 10g database

  How to check for the URL (Web interface) to administer an Oracle 10g database?  Continue Reading

• RAC or Data Guard for 2TB application with 30 users

  We have a 2TB application with approximately 30 users. Do I need 10g RAC or Data Guard for HA for this application?  Continue Reading

• Useful SQL commands for new Oracle DBA

  I am a new Oracle DBA and I am very poor in Oracle SQL commands. Can you provide me with a list of useful SQL commands?  Continue Reading

• What is the SCN?

  I don't understand the concept of SCN (system change number). Is it alloted to each DML or DDL we fire or to a commited transaction?  Continue Reading

• ORA-01092 error at startup

  I'm getting the error "ORA-01092. Oracle instance terminated. Disconnection forced."  Continue Reading

• Script to revoke access from user

  I'm trying to write a script to revoke all access from any given user. I've done the following so far, but can't figure out all the errors -- can you help?  Continue Reading

• ORA-12801: error signaled in parallel query server P004

  I'm getting "ORA-12801: error signaled in parallel query server P004."  Continue Reading

• Definitions of checkpoint, PMON and SMON

  What is a checkpoint? What are PMON and SMON?  Continue Reading

• Backup strategy for 1TB database

  I work for a small firm with less than 400GB of data which is backed up on a daily basis. What is the best backup strategy if the firm expands its database to 1TB?  Continue Reading

• How long to export 10TB data from 9i to 10g?

  How long will it take to export data from Oracle 9i to 10g (about 10TB data)?  Continue Reading

• tnsping timing out when trying to connect

  I installed Oracle 10g server on a Dell desktop. I have no trouble seeing the database instance on the server. But when I try to connect with the laptop, tnsping times out.  Continue Reading

• Difference between migration and upgrade

  I believe this is a not a good response to the following question. The manual does not specifically or categorically answer the question proposed. Migrate/export (duplicate copy) is really different from the concept upgrade (replace Oracle version).  Continue Reading

• Was OPatch applied successfully?

  How to know that OPatch was applied successfully or not on a 9.2.0.7 database?  Continue Reading

• Patches for upgrading from Oracle 9i release 1 to release 2

  I have Oracle 9i release 1 and now I want to upgrade to release 2. Where can I download the required patches for upgrading?  Continue Reading

• Database Configuration Assistant gets stuck on step 8

  I am using Oracle 9.2i on Windows XP. In creating a new database using "Database Configuration Assistant" I get stuck on step 8 of 8 -- after clicking the finish button, nothing happens.  Continue Reading

• Closest Saturday less than or equal to a date given

  I need to find the closest date that is a Saturday less than or equal to a date given by a user.  Continue Reading

• Determining size of table in Oracle database

  What is the query to determine the table size of a database in Oracle?  Continue Reading

• Number of columns in primary and foreign keys

  Why must the number of columns in primary keys match the number in foreign keys?  Continue Reading

• ORA-25143: default storage clause is not compatible with allocation policy

  When I set extent management to local I'm getting this error message: "ORA-25143: default storage clause is not compatible with allocation policy." How can I resolve this problem?  Continue Reading

• ORA-01702: a view is not appropriate here

  I'm creating an index on a view using Oracle 9.2.0. I get the error ORA-01702 (a view is not appropriate here).  Continue Reading

• Measuring query response time in Oracle

  How can I measure query response time in Oracle 9i and Oracle 10g?  Continue Reading

• Sorting data in varchar2 field

  I have a database field defined as varchar2. Some of the records are numeric and some are alphanumeric. How do I sort out the data?  Continue Reading

• ORA-12545 error message at login

  When I tried to log in to a database on Oracle 9i I received an error message, "ORA-12545."  Continue Reading

• Installing Oracle 9i client on Windows 2003 server

  Can I install Oracle Client 9i on Windows 2003 Server?  Continue Reading

• Database too large for exp/imp migration

  I am migrating an Oracle 8i database to Oracle 10g. The DB is too large for imp/exp. Can I create a standby database on 8i first, switch to production and then migrate to 10g?  Continue Reading

• How to apply patch to Oracle 10g on Linux?

  I want to apply an Oracle patch to 10g on a Linux box. I downloaded the patch but have no clue how to proceed.  Continue Reading

• First or third Monday of the month?

  Given a date that falls on Monday, how can I know if it is the first or third Monday of the month?  Continue Reading

• Using Oracle for the first time

  I have Oracle on my system but I don't know how to start with it. After installing it, when I open SQL it asks for my username, password and host string. I don't want to give all these.  Continue Reading

• Migrating database from Solaris to Linux

  What is the procedure to migrate an Oracle database from a Solaris machine to Red Hat Linux 9?  Continue Reading

• Importing Oracle 9i dump file into Oracle 8

  I have an Oracle 9i dump file I need to import (backwards) into Oracle 8. How would this be achieved?  Continue Reading

• Levels for incremental backups in RMAN

  We want to plan incremental backups on Oracle 9i with RMAN. How do I detemine what levels of incremental backups to use?  Continue Reading

• Lost the pupbld.sql file

  I lost the pupbld.sql file. How do I create an Oracle 10g database without the file?  Continue Reading

• Can't connect to Oracle after running alter statement

  After running an alter statement and restarting the system I cannot connect to Oracle. It gives me the errors "ORA-01034: ORACLE not available" and "ORA-27101: shared memory realm does not exist."  Continue Reading

• Can I install Oracle 8i on Windows 2003 Server?

  I was not able to install Oracle 8i on Windows 2003 Server. Is it compatible with this operating system?  Continue Reading

• Receiving ORA-12560 error trying to create database

  I tried to create a database with the Database Configuration Assistant in Oracle 10g. It got to the point where it creates the scripts, runs the batch file and creates the necessary folders, but then it stops right away with an ORA-12560 error.  Continue Reading

• How to resolve ORA-04030 error?

  I am getting an ORA-04030 error, "out of memory cannot allocate nn bytes." Do I need to change the pga_aggregate_target parameter?  Continue Reading

• Can Oracle 9i be installed on Windows 2003 Server Standard Edition?

  Can you install Oracle 9.2.0.6.0 on Windows 2003 Server Standard Edition? I read once that you need the Enterprise Edition to install Oracle 9i.  Continue Reading

• Verifying patch level of Oracle instance

  How do you verify what patch level an instance of the Oracle RDBMS is on?  Continue Reading

• ORA-600 error and slow server response after upgrade

  We upgraded from 9i to 10g on a data warehouse database. Since then we are facing a lot of problems, such as the ORA-600 error and slow server response.  Continue Reading

• Datatype of ID field for very large database

  While designing a database for a very large organization, where the data can reach terabytes and where records need to be kept for years in the database, what type of ID field should be used?  Continue Reading

• Query to delete duplicate records in column

  We have a table with duplicate records for columnA, and we want to make columnA the primary key. For this we need a query to delete the duplicate records so that columnA will have only unique values.  Continue Reading

• Why isn't Oracle user-friendly?

  Working with SQL Server is simple. Oracle is an enterprise database and powerful but why has it never been user-friendly?  Continue Reading

• Need Oracle9i ODBC driver

  Oracle 9.2 was installed on my PC. I need the Oracle9i ODBC driver to configure a client, but I can only find JDBC drivers. How can I load the ODBC driver? Do I need to uninstall Oracle?  Continue Reading

• Need Oracle 8.1.7 Enterprise Edition CD for re-installation

  We need to re-install an old version of Oracle (8.1.7 Enterprise Edition) but can't locate the CD anywhere.  Continue Reading

• Physical memory needed for 10TB database

  We are looking to install a new Oracle 9i database which will be around 10TB. I'm looking for a good rule of thumb on the physical memory needed for a DB of that size.  Continue Reading

• Considerations before upgrading to Oracle Applications R12

  What are the main things to know before upgrading to Oracle Applications R12?  Continue Reading

• TNS error when performing tnsping

  I can ping the server on which my database is installed, but when I do tnsping <db_name> I get the following error: "TNS-12535: TNS:operation timed out."  Continue Reading

• Upgrading Oracle 8i to 9i on same server

  I need to upgrade Oracle 8i to 9i on the same server by full database export, but 8i should still be there after upgrade.  Continue Reading

• PL/SQL procedure to load CSV file into database table

  I have a control file and a CSV file. I need help writing a PL/SQL procedure which can load the Excel file into the database table specified in the control file.  Continue Reading

• Syntax error while running Oracle 10g installer

  While running the Oracle 10g DB installer on Sun Solaris, I am getting this syntax error.  Continue Reading

• Using index to avoid full table scan

  My query is taking too long to run. How can I use an index to reduce cost?  Continue Reading

• Exporting from Oracle to Excel

  How to export table data from a database to an Excel sheet?  Continue Reading

• Long query with where clause causes error

  I am getting the "ERROR at line 1: ORA-03113: end-of-file on communication channel" error message whenever I run a very long query having a 'where' clause.  Continue Reading

• SYSMAN account locking up

  I have a sysman account that keeps locking up every minute or so. What do I do?  Continue Reading

• Migrating to a different Endian format

  You say to precreate the database and tablespace on Solaris and use the imp utility to import all tablespaces from the Windows host. What about Endian format?  Continue Reading

• Creation of lksid file in Linux

  Why is an lksid file automatically created when you create a pfile to create a database in Linux?  Continue Reading

• Reading SPFILE

  Can we read directly from SPFILE what parameters are defined?  Continue Reading