Ask the Experts

Ask the Experts

• 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

• 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

• 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

• 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

• Add or drop inactive online redo log files

  Can we add or drop (inactive) online redo log files at the open stage?  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

• How to export data by date in Oracle

  Is there a way to export data by date?  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

• 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

• 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

• 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

• Integrity constraints and referential integrity

  What is the difference between integrity constraints and referential integrity?  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

• 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

• 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

• 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

• 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

• 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

• How to find Oracle database instance size

  How to find out the Oracle database instance size (BG + SGA)?  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

• 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

• Recovery with missing init.ora file

  If the initsid.ora file is missing how should we recover it?  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 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

• 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

• 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

• Difference between static and dynamic registrations

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

• How to create an index using a procedure in Oracle

  How can we create an index using a procedure?  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

• 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

• 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

• 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

• The MAX of two MAX values in SQL

  Can I get the max of two columns into one column in SQL?  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

• 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

• 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

• 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

• 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

• 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

• Using /var/opt/oracle/oratab

  Can I use /var/opt/oracle/oratab to specify listener information?  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

• 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

• 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

• How to increase SGA_MAX_SIZE in Oracle

  How to increase SGA_MAX_SIZE in Oracle? I got the error "ORA-03113: end-of-file on communication channel" during startup.  Continue Reading

• Simple SQL query takes longer than usual

  A simple SQL query is taking a longer time than it usually takes. How do we need to check to find out why it is taking so long?  Continue Reading

• UPDATE only rows which match another table in SQL

  I have a SQL query where I am trying to update a column in a table from data in another table. This works fine when I try to update all the records in the first table; however, Is it possible to update only the missing data?  Continue Reading

• SQL to calculate days to next status change

  I would like to find out how long a particular customer order has been on hold in SQL.  Continue Reading

• Remove fragmentation of tables and indexes in Oracle

  How to remove fragmentation of tables and indexes in Oracle when tablespaces are in LMT and DMT?  Continue Reading

• Hint in view code to force use of index

  I have a view that joins many related tables. The view is quite slow when run wide open. Can I put a hint in the view code to force the use of an index?  Continue Reading

• Is my database using SPFILE or PFILE?

  How do I know whether my database is using SPFILE or PFILE?  Continue Reading

• Closing connections to Oracle database takes too long

  Closing connections to my Oracle database takes up to 40 seconds. We have several apps that open a connection, grab some data, then close the connection. However the apps are often timing out because closing the connections take so long.  Continue Reading

• How many database instances can be created?

  How many database instances can be created on an Oracle server with an installation drive containing 15 GB free space?  Continue Reading

• Limit on CLOB datatype in Oracle 10g

  In Oracle 10g, does the 32K limit on the CLOB datatype still apply?  Continue Reading

• ORA-04030: out of process memory when trying to allocate bytes

  When I run a multi-threaded process in Oracle Apps 11i, it fails with the error "ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,pl/sqlvc2)."  Continue Reading

• Parallel processing causing performance problems

  Our largest tables are defined with "parallel degree nn" in Oracle 9.1. As a consequence, almost all of our queries that run against these tables invoke parallel queries and spawn many, many parallel slaves.  Continue Reading

• ORA-01940: cannot drop a user that is currently connected

  I got an error message while dropping a user: "ORA-01940: cannot drop a user that is currently connected."  Continue Reading

• ORA-03297: file contains used data beyond requested RESIZE value

  I have a datafile that is 16GB. I have moved indexes out and want to shrink the file, but get the error: "ORA-03297: file contains used data beyond requested RESIZE value."  Continue Reading

• ORA-604 and ORA-4021 errors

  We are having some problems that lead me to guess that the ORA-604 at SQL level 1 either causes or is caused by the ORA-4031, and then when the query creating the ORA-604 error at SQL level 2 causes the ORA-4021 lockup and that is when I start ...  Continue Reading

• Oracle does not maintain order of inserted records

  I need to have records in table B sorted with regard to the primary key. But Oracle has the problem of not maintaing the order in the way records were inserted.  Continue Reading

• Employees with the same first and last names in SQL

  How do I SELECT a list of employees who have the same last and first name in SQL?  Continue Reading

• "Table or view does not exist" error with DESC command

  On one of the production databases, when I select table_name from dba_tables I get names of 372 tables. But when I use Desc command on most of those tables, I get an error message, "Table or view does not exist."  Continue Reading

• Autoextend vs. adding new datafile

  I have one tablespace which is almost 90% full. Should I autoextend or to add a new datafile?  Continue Reading

• ORA-12535 error connecting to local database through VPN

  I have installed Oracle 9.2.0.1 on my local computer. When I try to connect to the installed local database, I get an error: "ORA-12535:TNS operation timed out."  Continue Reading

• Using multiple Oracle homes to apply a patch

  How do you use Oracle Enterprise Manager without a database or does this require Opatch from the command line?  Continue Reading

• Patch to upgrade to Oracle 10.2.0.3

  I installed Oracle 10.2.0.1 client on Sun Solaris 5.9. Is there any patch to upgrade this 10.2.0.1 to 10.2.0.3?  Continue Reading

• Columns in the SELECT not in the GROUP BY

  I have an SQL query where I am using the SUM function with the GROUP BY clause. I need to select a couple more columns from the table without adding those columns in the GROUP BY clause.  Continue Reading

• Oracle Universal Installer doesn't start

  I'm having a problem while trying to install an Oracle 10g database on Windows Server 2003 via Remote Desktop. When I run setup.exe from the CD, the "please wait" command prompt comes up and the Oracle Universal Installer doesn't start.  Continue Reading

• RunInstaller: execute permission denied

  When installing Oracle 10g on an HP-UX machine, it gives an error, "runInstaller: execute permission denied."  Continue Reading

• SQL*Plus not working after Oracle 10g installation

  I have successfully installed Oracle 10g on Linux Enterprise Edition 4 but after that the SQL*Plus command is not working.  Continue Reading

• When will support for Oracle 9i be discontinued?

  When will general support for Oracle 9i be discontinued?  Continue Reading

• All employees under a given manager

  I want to display all the employees who report to a person. My data is like a tree structure.  Continue Reading

• Oracle Universal Installer throwing error

  I've installed a dual-booting OS running Windows XP and Windows 2000 Server on two partitions. The Oracle Universal Installer is throwing an error.  Continue Reading

• Running out of space for backups, archive logs

  The Oracle database is growing and the physical disk is out of space. Can I move the archive log folder?  Continue Reading