Ask the Experts

Ask the Experts

• ERD for running Oracle database schema

  How can I get the full ERD (entity relationship diagram) for a running Oracle database schema?  Continue Reading

• Testing migration from Oracle 8i to Oracle 9i

  How do I know that a migration from Oracle 8i to Oracle 9i has been done successfully? Is it possible to import a .dmp file exporting from Windows 2003 Server to a server running RedHat Linux?  Continue Reading

• Insufficient privileges error when creating stored procedure

  I need to create a stored procedure in my schema using Toad but it gives me the error "Insufficient privileges."  Continue Reading

• How to migrate from Oracle 8 to Oracle 10g with exp/imp

  We have to migrate from Oracle 8.1.7 to Oracle 10.2.0 using the exp/imp utility. Please let us know the checklist.  Continue Reading

• Tool for data migration from SQL Server to Oracle

  I'm doing data migration from SQL Server 2000 to Oracle on Linux. Should I use SQL*Loader and Oracle Workbench or another tool?  Continue Reading

• IMP-00010 error with import from 10g to 9i

  I have an Oracle 10g dump file which I need to import to Oracle 9i. When I try to import I am getting the error "IMP-00010: not a valid export file, header failed verification."  Continue Reading

• Was incremental backup differential or cumulative?

  Is there a way to tell whether an incremental backup was done in a differential or cumulative manner in RMAN?  Continue Reading

• How to export table from one Oracle database to another

  I need to export (using the Oracle exp utility) a table from one Oracle database to another Oracle database.  Continue Reading

• Difference between Oracle Forms and E-Business Suite

  I took an Oracle Applications Developer course covering SQL, PL/SQL, Forms 6i and Reports. Most jobs seem to want 11i Applications or E-Business Suite. Are these variations of Oracle Forms?  Continue Reading

• SQL for five days ago, excluding weekends

  I need the date which is five days before the current date. For business logic it should exclude Saturday and Sunday.  Continue Reading

• SQL query for all Fridays in a year

  How to get all Fridays for year 2007 in SQL?  Continue Reading

• ORA-01422 error when procedure returns more than one row

  I have the following Oracle procedure that returns one or more rows depending upon the input into the SQL statement. When there is greater than one row returned I get the ORA-01422 error.  Continue Reading

• Should column names in UNION queries match?

  This question is regarding the restrictions on the usage of the UNION operator. Shouldn't the names of the columns in the select queries match?  Continue Reading

• Performance goals for DBAs

  Do you have any suggestions on how to measure DBA job performance and provide meaningful goals for performance management of DBAs?  Continue Reading

• Difference between logging mode and nologging mode

  What happens if I put my tablespace in logging mode vs. nologging mode?  Continue Reading

• Oracle 10g study materials

  Can you provide me with some study materials about Oracle 11g?  Continue Reading

• How to find all USER_OBJECTS in database?

  How can I get all the USER_OBJECTS in the database?  Continue Reading

• What is a parameter file?

  What is a parameter file?  Continue Reading

• Becoming a DBA with no experience

  I took an Oracle training camp (one week) five years ago and I have no practical experience. I am returning to a community college in Dallas which offers a DBA certificate and an Oracle associate certificate. What else can I do to better my ...  Continue Reading

• Storing documents in BLOBs vs. external FTP server

  We were "sold" by Oracle consultants on the advantages of storing all our documents into BLOBs in the database instead of the external FTP server method we were using. We now find that you cannot trap an update to a BLOB field in a trigger.  Continue Reading

• How to find Oracle database instance size

  How to find out the Oracle database instance size (BG + SGA)?  Continue Reading

• Index on table that is truncated/repopulated daily

  Since I truncate and repopulate the data in my table every day, is the index of much use?  Continue Reading

• Calling procedure inside another procedure in anonymous block

  I have a procedure p1 which holds another procedure p2. I want to call this p2 procedure in an anonymous block.  Continue Reading

• Running a script without user's password to Oracle database

  Is there any way to run a script .sh (Unix) or .bat (Windows) automatically or manually without including the password of the user that connects to the database or entering manually that password?  Continue Reading

• ORA-12801 error while loading seed data

  I am getting this error while loading seed data: "ORA-12801: error signaled in parallel query server P012."  Continue Reading

• IMP-00003 and ORA-01435 errors with import

  I am trying to import a dump file to an Oracle database, but am getting the following error.  Continue Reading

• How to install Oracle 10g on Sun Solaris 9

  I am trying to install Oracle 10g on a Sun Solaris version 9 platform with required patches. What are the steps?  Continue Reading

• ORA-01033 error after UPS failure with Oracle 10g

  Recently my machine got shut down from a UPS failure when I was in the middle of a transaction with Oracle 10g. When I restarted my machine and tried to log in, I got the "ORA-01033: Initialization or shut down in progress" error.  Continue Reading

• Recovery with missing init.ora file

  If the initsid.ora file is missing how should we recover it?  Continue Reading

• Can I import into Oracle 9i with imp.exe version 10g?

  Is it OK that I try to import data into Oracle database version 9i with imp.exe version 10g?  Continue Reading

• Does RMAN shut down the database during level 0 backups?

  After going through scripts, I see that the level 0 does not shut down and restart the database during the backup. Does RMAN handle that or do these commands need to be added?  Continue Reading

• How to recover if incremental backup fails

  If somehow only Tuesday's backup did not run or failed, then can I use the whole week's backup to restore and recover the database? In this situation what would be the backup strategy?  Continue Reading

• How to migrate 300GB Oracle database from Z/OS to AIX

  I need to migrate the database (Oracle 8.1.7.4) on IBM Z/OS to Oracle 10g on IBM AIX Server. The size of the database is 300GB. Please let me know the best way to migrate.  Continue Reading

• How to import/export Oracle dump file from Windows to Solaris

  Can you give me the command prompt to import and export a dump file from Windows 2003 to Sun Solaris?  Continue Reading

• SQL FAQ: Common SQL questions, part 2

  This three-part answer addresses some of the most frequently asked questions about SQL. Part two looks at some common SQL homework questions.  Continue Reading

• SQL FAQ: Common SQL questions, part 3

  This three-part answer addresses the most frequently asked SQL questions. Part three looks at the more complex common questions.  Continue Reading

• SQL FAQ: Common SQL questions, part 1

  This three-part answer addresses some of the most frequently asked questions about SQL. Part one covers novice questions.  Continue Reading

• How to become a successful Oracle DBA?

  I want to be an Oracle DBA in the near future. I have an information systems background and a basic understanding of SQL and Oracle.  Continue Reading

• Table space required for .pdf files

  We are storing .pdf files into the blob datatype. So how much tablespace size do we need to create?  Continue Reading

• Is a full outer join possible in Oracle?

  Is a full outer join possible in Oracle? I thought I had to join two outer join queries with a UNION statement.  Continue Reading

• Best practice for tablespaces for large tables

  What is the best practice for large tables in a database -- separate tablespaces for each big table or put all big tables in one tablespace?  Continue Reading

• ALTER USER command not working

  I used the ALTER USER command to restrict a user not to use a table. I got the message "system altered" but the user can still create the table.  Continue Reading

• Select on Oracle table takes hours

  A user complains that every time he tries to access a table (select only), it takes more than two hours to get the results. There are no DML operations performed on the table.  Continue Reading

• How to improve performance of column update

  I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four hours and counting. What can I do to improve this performance?  Continue Reading

• Can I use TKPROF in Oracle 10g?

  I have always used TKPROF to do SQL tuning in previous versions of Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2?  Continue Reading

• Difference between static and dynamic registrations

  What are the differences between static and dynamic registrations in Oracle?  Continue Reading

• Completing RMAN backups before batch stream

  We need to complete the RMAN backup process in less than 10 minutes as we cannot start the nightly batch stream unless the backup is done.  Continue Reading

• How to create an index using a procedure in Oracle

  How can we create an index using a procedure?  Continue Reading

• Does Oracle overwrite rollback information for uncommitted transactions?

  If the undo_retention parameter is set to 900 sec and after that period if unexpired (uncommitted) data is overwritten by other transactions in some undo segment, then how does rollback work?  Continue Reading

• How to clone Oracle 10g database on different server

  I'm using Oracle Database 10g on Windows 2003 Server. I want to create a clone database on a different server.  Continue Reading

• I'm a data modeler -- can I get a DBA job?

  I'm currently in the database field as a data modeler, but I want to be a DBA. Is it feasible for me to get a DBA job?  Continue Reading

• Can I install Oracle binary files on a SAN?

  You said it is not recommended to install Oracle binary files on a SAN in a RAC environment. What about a non-RAC environment where there is only one Oracle instance and server connected to one SAN?  Continue Reading

• What is the difference between left outer join and (+) outer join?

  What is the difference between left outer join and (+) outer join?  Continue Reading

• How to import comma-delimited text file to Oracle table

  We have to import a text file (delimited with commas) to an Oracle table via our application with PL/SQL.  Continue Reading

• TNS:listener does not currently know of service requested in connect descriptor

  I installed Oracle 10gR2 on my client machine, configured my Net8 and added an ODBC system datasource which I wanted to use in connecting my ASP application to the Oracle 10gR2 Server running on HP-UX, but I keep getting this error.  Continue Reading

• Way to restrict DBA's access to data

  Is there any mechanism to restrict the Oracle DBA to view the application's data other than data vaults?  Continue Reading

• Where to start learning Oracle basics for career

  I'm currently interested in pursuing a career path in database programming or administration. Is it feasible for me to jump right into Oracle basic learning or should I establish a base in another curriculum?  Continue Reading

• What is the line feed character in SQL?

  What is the line feed character in SQL and what is the ASCII value of it?  Continue Reading

• What is the meaning of ER model?

  What is the meaning of ER model?  Continue Reading

• How to dump SQL Server database with Oracle data

  We need to dump a SQL Server 2005 database with the data from an OLTP Oracle 7.2.3 database. Should we pull data from SQL Server or push data from the Oracle DB?  Continue Reading

• Import of database dump takes 13 hours

  I tried to import a database dump onto my Oracle server using the imp script. The import process currently takes 13 hours for a 3 GB dump file.  Continue Reading

• Slow query performance for customer with large data volume

  I have a critical performance issue due to the large volume of data for a specific customer. Whenever customer XYZ's data is being fetched the query takes about half an hour to execute, whereas it fetches the data in seconds for the rest of the ...  Continue Reading

• ORA-03297 when trying to resize tablespace

  When I try to resize the tablespace I get the error ORA-03297.  Continue Reading

• SQL CHECK constraints to validate table inserts

  I have various validation rules to be applied while inserting the records in a table. For example, if the type is 'Tool' the subtype can only be 'Template' or 'Document.' How can I achieve this?  Continue Reading

• The MAX of two MAX values in SQL

  Can I get the max of two columns into one column in SQL?  Continue Reading

• Point-in-time recovery on production database

  I need to do a point-in-time recovery on a production database. What are the steps?  Continue Reading

• Migrate Oracle 10g database from Linux to AIX

  I would like to migrate Oracle 10g on a Linux server (RHEL4) to AIX 5L. I have alreay installed Oracle 10g. What is the best approach?  Continue Reading

• Procedure compiles but does not execute

  When I create a table using an execute immediate statement in the procedure, the procedure will compile but is not executed. Why?  Continue Reading

• What happens if there is no log switch for a long time?

  If a log switch doesn't occur for a long time then what happens? Also, if I committed my transaction and suddenly the power was gone, then what will happen in the database file?  Continue Reading

• ORA-27102 error after increasing memory

  I increased the RAM from 2GB to 3GB, but when I try to increase the sga_max_size I get the following error on startup: "ORA-27102: out of memory."  Continue Reading

• Re-create data dictionary without disturbing database

  When the data dictionary is currupted, how do I re-create it without disturbing the database?  Continue Reading

• Users have unlimited tablespace system privileges

  I recently I found out that the users in production have been given unlimited tablespace quotas and system privileges as a result of which they are creating their own datafiles and allocating as much space they need.  Continue Reading

• Rename a table with import utility in Oracle

  I am trying to import a table in Oracle. The target database contains a table with the same name. How do I rename the table?  Continue Reading

• TNS listener consuming a lot of memory

  Our TNS listener is consuming a lot of memory. I have Oracle 9.2.0.8 on a Unix box.  Continue Reading

• NOT EXISTS with a correlated subquery

  I need to compare one field in my table against an entire column in another table. The second table has a list of excluded codes that do not need to be in the first table. How do I do this in SQL?  Continue Reading

• Obtaining COUNTs for males and females in one SQL query

  For my table the gender column has only two values; F and M. I would like to do a count of both genders in one SQL query.  Continue Reading

• Oracle licensing policy for educational institutes

  We are seeking to establish a new Oracle/IT training institute in our area. I have gone through lot of Oracle licensing documentation but couldn't find any information on Oracle licensing policy for educational institutes.  Continue Reading

• Difference between Oracle RAC and Data Guard

  What is the difference between Oracle Data Guard and RAC? What's the difference between a node and an instance?  Continue Reading

• DBA time tracking and change management

  I'm looking for ideas on how to best track and account for the time my DBA team uses. Also, what best practices can you prescribe for implementing a database change management framework?  Continue Reading

• Oracle job advice: ERP or Oracle apps?

  I am Oracle 9i certified but still not getting any job oppurtunities. What should I do, move to ERP package of Oracle or do I have to start with some kind of front-end programming?  Continue Reading

• Error in invoking target install of makefile

  I have been trying to install Oracle 8.1.7 on SUSE Linux 9.0 and got the error: "Error in invoking target install of makefile /opt/oracle/...../*.mk."  Continue Reading

• Errors installing Database Configuration Assistant

  I'm getting errors while installing the Database Configuration Assistant.  Continue Reading

• What to do after hiring an unqualified candidate

  I found someone very qualified who was hired per my recommendation. Another person was totally not qualified but was hired anyway. What do I do next?  Continue Reading

• Using /var/opt/oracle/oratab

  Can I use /var/opt/oracle/oratab to specify listener information?  Continue Reading

• Impact on apps of migration from Oracle 8 to 10g

  We would like to migrate our database from Oracle 8.1.7 to Oracle 10g. We would like to know the impact for our application developed using Delphi and our other components.  Continue Reading

• Interview questions about managing an Oracle database

  I have an interview relating to managing an Oracle database. What types of questions will they ask me?  Continue Reading

• Performing backups with Oracle RAC and ASM

  I am setting up a seven-node RAC environment (10gR2) on Linux and will be using ASM. I plan to set up a Flash Recovery Area (also ASM), but I'm concerned about how I can get the backups off to tape.  Continue Reading

• Using GROUP BY to collapse rows

  Can I get the following with one select statement in SQL?  Continue Reading

• SMON process consuming 100% CPU

  Why does the SMON process periodically consume 100% CPU?  Continue Reading

• The running total self-join

  How to find the cumulative sum without using an analytical function?  Continue Reading

• Syntax for export utility in Oracle 9i

  We are using Oracle 9i and we want to use the export utility to export the database. How do I write the syntax for the exp utility?  Continue Reading

• Matching one column value to part of another in SQL

  How do I pull out all the rows where the description column does not start with its corresponding ID?  Continue Reading

• Clone production database to different OS

  I want to clone my production database into my development server. Both are the same Oracle version but on different OS versions.  Continue Reading

• Import Oracle 9i dump from Windows to Unix

  How to import a dump from a Windows machine to an Oracle 9i Unix server?  Continue Reading

• Recover deleted database from data files, control files

  We deleted a database from an Oracle 10g server. We have all the files. How can I recover my database from the files?  Continue Reading

• What do database architects do?

  What exactly do database architects do?  Continue Reading

• Connect to Oracle 9i through client

  I have Oracle 9i Client and Oracle 10g Client on my machine. When I tried to connect to Oracle 9i through SQL*Plus on my machine, it directly connects to Oracle 10g.  Continue Reading

• Separate database for each client in Oracle 10g

  We need to create a data warehouse in Oracle 10g. Is there a way to create a separate database for each client?  Continue Reading

• Relationship between schema and datafile in Oracle

  In Oracle, is there a clear relationship between the schema and the datafile?  Continue Reading

• See tablespace utilization/database size without DBA privileges

  Can I see the tablespace utilization and the current database size of my database even though the Oracle user account I'm using does not have DBA privileges?  Continue Reading