Ask the Experts

Ask the Experts

• 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

• 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

• 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

• Difference between candidate key and composite key

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

• Update two columns with one UPDATE command

  How to update two columns in a table with one update command?  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

• Inserting multiple rows in SQL

  How to insert multiple rows of data using insert into command?  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

• 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

• Redo log file filled up

  What to do when the current redo log file or datafile file gets filled up?  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

• 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

• Composite keys and normalization

  What is composite key and why do we use it? Why go for normalization?  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

• 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

• 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

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

  I'm getting "ORA-12801: error signaled in parallel query server P004."  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

• 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

• Was OPatch applied successfully?

  How to know that OPatch was applied successfully or not on a 9.2.0.7 database?  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

• 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

• 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

• Measuring query response time in Oracle

  How can I measure query response time in Oracle 9i and Oracle 10g?  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

• 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

• Installing Oracle 9i client on Windows 2003 server

  Can I install Oracle Client 9i on Windows 2003 Server?  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

• 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

• 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

• 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

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

• 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

• Exporting from Oracle to Excel

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

• Importing .dmp file to database

  What is the simplest way to import a .dmp file stored on my local M/C to the database?  Continue Reading

• Invalid GROUP BY SQL query

  I have a table that captures events with the following columns. I want to produce a summary report with a single row entry for each unique source||event combination.  Continue Reading

• Reading SPFILE

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

• Optimize SQL query on table with 50 million records

  We have a table with about 50 million records. We are executing a query that takes one or two seconds to complete in Oracle 10g, but we need it to take less than half a second.  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

• What is a recursive relationship?

  What is meant by a recursive relationship?  Continue Reading

• Invalid stored procedures

  When running an application that is connected to an Oracle server/database and trying to add rows or make a call to the DB, I am getting various errors that the stored procedures are invalid.  Continue Reading

• Rule-based vs. cost-based optimization

  Please tell me the difference between rule-based and cost-based optimization of SQL queries, in detail.  Continue Reading

• One column LIKE another column

  Can one ask for the content of a field in a like condition?  Continue Reading

• COUNT(*) or COUNT(1)

  How do you specify difference between count(*) and count(1) in a select statement? Which is best to use and when?  Continue Reading

• Finding size of files in BLOB datatype

  I have a table named DOCUMENT with a BLOB datatype for storing files. I want a query to find the size of each file stored in that BLOB object.  Continue Reading

• Copying database structure without data

  I want to take the structure of my database, but not the data. Is it possible?  Continue Reading

• What is CPU parse overhead?

  What is CPU parse overhead? What should be done to improve server performance if it is high?  Continue Reading

• Encountered the symbol "ELSE" when expecting one of the following

  I am a beginner to Oracle. I am getting this error: "ORA-06550: line 21, column 1: PLS-00103: Encountered the symbol "ELSE" when expecting one of the following:"  Continue Reading

• Differences between tables and views

  What are the differences between tables and views?  Continue Reading

• Import of .dmp file from 64-bit to 32-bit machine

  I am trying to import an Oracle .dmp file generated on a 64-bit machine onto a 32-bit machine, but I am getting an import error. How can I perform the import?  Continue Reading

• Expression is of wrong type

  This is a function to check whether the given date is a holiday or not. When I execute this, I am getting an error: "ORA-06552: Statement ignored ORA-06553:PLS-382: Expression is of Wrong Type." What is the problem?  Continue Reading

• Fiscal year in Oracle SQL

  I am trying to do counts for multiple fiscal years. I can do this for calendar years, but have been unable to figure it out for fiscal years.  Continue Reading

• GROUP BY multiple columns

  My table structure is empid, category, col1, col2, amount. I would like to get the results as total of amounts for the col1 and col2 combinations, with a particular category.  Continue Reading

• Sum of sales for two years, by month

  I need a query that will give me the following result for a particular region. I need to get the sum of sales value for two years for one particular region, and group by months.  Continue Reading

• Sorting a clob column

  I'm trying to sort a column type asc as well as desc. I'm able to sort the clob column desc but not able to sort it asc. Can you please help me out?  Continue Reading

• Maximum number of sessions exceeded

  When I run a program through my application server, I get the following error: "ORA-00018:maximum number of sessions exceeded."  Continue Reading

• What are DDL and DML?

  What are DDL and DML and their commands?  Continue Reading

• ORA-03113 when query retrieves many records

  If my query retrieves more than 1,000 records, I get the following error: "java.sql.SQLException: ORA-02068: following severe error from <Database Name>, ORA-03113: end-of-file on communication channel."  Continue Reading

• ORA-00980: synonym translation is no longer valid

  While importing an export backup of an Oracle8i database into Oracle9i, it completes with an error at the time of creating a trigger. The message is: "PL/SQL:ORA-00980: synonym translation is no longer valid."  Continue Reading

• ORA-12518: TNS:listener could not hand off client connection

  When I ran 300 virtual users to connect to the Oracle DB using the TPC-C Job, I got the error: "Unable to attach to a server in logon().ORA-12518: TNS:listener could not hand off client connection. Could not logon to the server. "  Continue Reading

• Command prompt disappearing on 10g install

  I downloaded the 10g software. When I click setup.exe, the command prompt disappears and no installer is started! Is this a bug?  Continue Reading

• Migrating Oracle 8i DB from AIX to Windows

  I want to migrate Oracle 8i Database from IBM AIX to a Windows platform. I have the data dump.  Continue Reading

• Difference between instance and session

  What is the difference between an instance and a session?  Continue Reading

• ORA-01116 error

  I have a customer running Oracle 7.3. Can you please help me with the following error?: "ORA-01116: error in opening database file 10."  Continue Reading

• SP2-0734 error during import

  I want to import a .dmp file into my Oracle 9i database, and when I try to do so using the SQL prompt it gives me an error: "SP2-0734: unknown command beginning 'imp pis/pi...' - rest of line ignored."  Continue Reading

• Error with RMAN cold backup

  I am taking a cold backup through RMAN. Yesterday I ran a crosscheck command and got this error. Is my backup method valid?  Continue Reading

• Accessing two cursors at the same time

  Can two cursors be accessed at the same time to open them at the same time?  Continue Reading

• Copying tables and indexes to another tablespace

  I've just read your answer on how to move tables and indexes to another tablespace. I would like to do this as a cloning process so that the tables and indexes stay put in the source but are copied/moved into a target database without having to do a...  Continue Reading

• ORA-12638: Credential retrieval failed

  I'm getting the infamous "ORA-12638: Credential retrieval failed" error message when trying to connect to Oracle 10g Express through the adaptor provided in the .Net Oracle Developer Tools. I'm running Windows XP Pro under a domain account.  Continue Reading

• Creating new user and password in Forms 6i

  In Forms 6i (d2k) I created the userid and password. How to create a new user and password programatically?  Continue Reading

• Consecutive dates within a range

  How do I check if an employee has worked seven consecutive dates within a range?  Continue Reading

• CASE in definition of cursor

  Why isn't CASE supported by PL/SQL in definition of cursor? It doesn't work.  Continue Reading

• Network, hierarchical and relational data models

  What are the differences and commonalities if any between network, hierarchical and relational data models?  Continue Reading

• Wrong number or types of argument in call

  I am calling a stored procedure (named TASKS_DEF) through Java code. This procedure is called by passing 28 arguments. I get the error "PLS-00306 : wrong number or types of argument in call to 'TASKS_DEF'."  Continue Reading

• Can't start up database without error ORA-01110

  I can't get our database to start up correctly. I have tried copying CONTROL02 to CONTROL01 and from CONTROL01 to CONTROL02 but it just always results in another error (ORA-01110).  Continue Reading

• Hardware requirements for RMAN

  What are the hardware requirements for implementing RMAN? Do we need a separate PC?  Continue Reading

• Account queries in Oracle AR Receivables

  We need to generate the below three queries to develop a marketing campaign. All these queries are based on Oracle AR Receivables.  Continue Reading

• How to modify sqlnet.ora file to restrict database access?

  I am currently trying to restrict access to my database through listing the IP addresses with permission. I read that I should modify my sqlnet.ora file. The problem is I do not have this file.  Continue Reading

• Forcing a user to reset password

  How do I force a user to change their password after I have reset it the first time they log on?  Continue Reading

• Paging with multiple columns

  I have a table with a composite key having three or more columns. I want to fetch data in a bundle of 100. How can I include a composite key in my where clause?  Continue Reading

• How to drop all tables from database with one SQL query

  How to drop all tables from the database using one SQL query?  Continue Reading

• Finding the highest COUNT in a GROUP BY query

  I want to get the maximum of totalcontracts from the retrieved data using a select statement.  Continue Reading

• Start dates, end dates and history tables

  I have a database with several tables that use a composite primary key of ID, start date, end date. If I am changing any description on a particular table, I will update the start date and end date, then I will insert the new row. Is there any ...  Continue Reading

• Data warehouse based on operational data model

  When trying to design a data warehouse, we often try to model the database on the operational data model. Are there any guidelines in trying to create a data warehouse based on the operational data model?  Continue Reading

• "Only a DBA can import a file exported by another DBA" error

  I'm getting these errors: "Export file created by EXPORT:V09.02.00 via direct path, IMP-00013: only a DBA can import a file exported by another DBA, IMP-00000: Import terminated unsuccessfully."  Continue Reading

• Recovering system datafile without backup

  How can I recover my system datafile without any backup? I was converting locally to dictionary-managed tablespaces by executing the query exec dbms_space_admin.tablespace_migrate_to_dictionary('ts1'); after some time when I started my db I got the ...  Continue Reading

• Test to check query in Toad

  I want to test to make sure that a query is working in Toad. I would like to pull the top 10 rows, but select top 10, column, column, etc. doesn't seem to run.  Continue Reading

• Fuzzy matching in SQL

  Can you do fuzzy matching with SQL? I want to retrieve a set of results based on how closely they match a certain string.  Continue Reading

• Insert not showing in v$sqlarea

  If a stored PL/SQL procedure is executed, all the DML in the procedure should show up in v$sqlarea, right? My test case shows it's not there. Why don't I see the insert?  Continue Reading

• Database Configuration Assistant not working

  When I click on Database Configuration Assistant nothing happens. I don't get a connection, there's no response. What could be the problem and how can I solve it?  Continue Reading

• FRM-40401: no change to save

  I am entering the following code from Developer but it gives an error, "FRM-40401: no change to save."  Continue Reading

• Practicing with Oracle Database as student

  I am a student who will be taking the second part of database at my college. We will be creating tables and then creating forms and reports and writing PL/SQL from the tables that we create (a project). How as a student can I download Oracle ...  Continue Reading