Ask the Experts

Ask the Experts

• Nth Saturday of the month

  Using the date functions, can I find out whether a particular date is the 1st, 2nd, 3rd, 4th or 5th Saturday of a month? Our leave rules have changed recently and I have to update my system to reflect full working on 1st, 3rd and 5th Saturdays, and ...  Continue Reading

• "Select star" with column reformatting in Oracle

  A comment on your "select star" article: If you are using Oracle, the obvious solution is to use alter session. Then all date columns will be displayed in this format.  Continue Reading

• Better performance with varchar2(1) or number(1)?

  Since Oracle table structures do not support a boolean datatype, I am faced with a choice (make it a varchar2(1) or number(1)), with contents of Y,N or 1,0. Which provides better performance?  Continue Reading

• Removing carriage returns at the end of clob fields

  I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to make this work?  Continue Reading

• Database too large for import/export method of migration

  We are currently running Oracle 9iR2 on Win2000 Server with multimaster replication configured, which includes two sides. Our company now wants to migrate to RHEL4 with the same Oracle version. The database is too large for import/export. Please ...  Continue Reading

• ORA-27101 error after moving DB to new domain

  I have an Oracle DB (8.1.7.0.0) on an old NT domain. The rest of the domain has been moved to Windows 2003 AD, and today I disjoined the Oracle server from the old domain and joined it to the new domain. As you might expect, when I tried to access ...  Continue Reading

• Unable to enter database with scott/tiger

  I have a problem with installing Oracle9i on Windows XP. When I give username and password as scott and tiger, I cannot enter the database. I get a "TNS PROTOCOL" error. Even while installing, it says SID already exists.  Continue Reading

• Installing Oracle 8i on Windows XP Professional

  How do I install Oracle 8i on Windows XP Professional?  Continue Reading

• Installing 10g for 64-bit Solaris on 32-bit Solaris

  Can you tell me if I can install Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for Solaris SPARC (64-bit) onto a Solaris 8 (32-bit) OS?  Continue Reading

• Backing up a table in Oracle 9i Database Server

  How to take a backup of a table in Oracle 9i Database Server?  Continue Reading

• Backing up online redo log files?

  Under the heading "What files need to be backed up under 9i for a cold backup?" you wrote, "The only files you missed in your question are the online redo log files." RMAN itself does not back up the online redo log files. And in most cases they do ...  Continue Reading

• What is the recovery window in RMAN?

  What is the recovery window in RMAN?  Continue Reading

• Third-party tools for RMAN backups

  I have seen many companies use third-party tools for RMAN backups, mostly Veritas Netbackup for Oracle on Solaris. Could you please tell me the difference between RMAN use through that tool and RMAN use by itself with the Unix prompt?  Continue Reading

• "Resource busy" error when truncating table

  I am inserting records into a sample table at the same time that I'm giving the TRUNCATE TABLE command in another session. I got the "resource busy" error, so I killed the session. But I got the same error. How can I truncate the table?  Continue Reading

• Exporting 10g dump into 8i

  Can we export an Oracle 10g dump into Oracle 8i?  Continue Reading

• ORA-1452 error after export/import

  I'm exporting an Oracle DB between two servers (Intel platform) in order to change HW to improve performance. I did a full export dump with no problem and after that I did an import on new HW. Import was finished successfully with warning; I ...  Continue Reading

• Exporting a table to ASCII-delimited format

  What's the fastest way to export an Oracle table to ASCII-delimited format?  Continue Reading

• Export/import on table with clob datatype column

  How to export and import a table that has a clob datatype column? I am getting errors during export and import.  Continue Reading

• Possible to recover datafile without backup?

  Please tell me if we can recover a datafile that doesn't have any backup and the database is in noarchive mode. Is it possible to recover that datafile?  Continue Reading

• Can we restore archive logs to a cold backup?

  I would like to implement a backup strategy involving a nightly cold backup. I would also like to run the database during the day in archive log mode so that I will be able to recover my database up to the point of failure. There isn't much ...  Continue Reading

• Importing 8i dump file to 9i

  How can I import data from an Oracle 8i dump file to the 9i database?  Continue Reading

• Methods to quicken update of 30-column table

  I am updating 80% of the rows in a 30-column (6 VC [4000] and a blob) table. I have the STD 10% pctfree set up. I am getting fair throughput, but have been tasked with making it faster. So far, I haven't been able to improve on my original ...  Continue Reading

• Initialization parameters for tuning a Peoplesoft app on Oracle 10g

  We are starting to upgrade to Oracle 10g and support a PeopleSoft application on AIX5L. Which initialization parameters do you consider important for tuning purposes?  Continue Reading

• Finding length of numeric datatype field

  I want to find the length of a numeric datatype field in my table. How can I find it?  Continue Reading

• Reading WSID from back-end

  We're using the following string to connect to the Oracle DB Server through the OraOLEDB Driver. We want to read WSID from this string in the back-end session for the purpose of auditing using triggers. Is there any means of reading this information...  Continue Reading

• Can I install the Oracle 8i client and the Oracle 10g client on Windows XP?

  Can I install the Oracle 8i client and the Oracle 10g client on Windows XP?  Continue Reading

• Exporting multiple tables in Oracle 9i Unix-HP

  How I can export multiple tables (3000 of them, actually) in Oracle 9i Unix-HP? I created three files (1000 tables in each file) with starting table names of A to H, I to Q and R to Z. How can I assign each file to export?  Continue Reading

• How does the data dictionary on the SGA get updated?

  How does the data dictionary get updated on the SGA once the instance is shut down? I can understand the fact that the data buffer cache goes through checkpoints updating datafiles; however, as far as I have seen, the data dictionary on the shared ...  Continue Reading

• Does DBMS_OUTPUT.PUT_LINE affect performance?

  Is it possible that the DBMS_OUTPUT.PUT_LINE will affect performance?  Continue Reading

• Making a select statement faster

  I have created a table, Party, and in it there are two main fields, party_code and party_name, and around 2,500 records are available in it. I passed a select statement to select party_name and party_code, but it is too slow in Oracle. What do I ...  Continue Reading

• Select * from loc

  Suppose you have a table which has only two columns, namely locid (not primary) and place. In Oracle, which query should I run to get the output as above? Firstly I don't want to use any advanced feature of Oracle 9i. Is it possible using plain SQL ...  Continue Reading

• Copy a row to the same table

  I want to insert a new row in a table with the same row in the same table with a different primary key value. I think I can do it using a temporary table but is there any other way?  Continue Reading

• ORA-01092 error while executing database creation command

  I have successfully installed Oracle9i on RedHat 9.2.0. While executing the database creation command, I got the error "ORA-01092: Oracle instance terminated, disconnection forced."  Continue Reading

• Multiple tablespaces or one tablespace with multiple datafiles?

  Is it better to have multiple tablespaces than one tablespace with multiple datafiles? I'm on a Windows platform so I try to keep my datafiles to a 2GB max. Is it not a performance penalty to have only one tablespace with many datafiles for all the ...  Continue Reading

• Avoiding Oracle errors

  How can I avoid the errors ORA-01501, ORA-00200, ORA-00202, ORA-27038 and OSD-04010?  Continue Reading

• Which Oracle products are best for our large Web-based application?

  We would like to develop a big Web-based application for our business, which can be accessed by around 10 million people with good performance, stability and easy maintenance. We are expecting huge database connections. We would like to use Oracle ...  Continue Reading

• How to insert Arabic data in Oracle?

  How to insert Arabic data in Oracle 9i?  Continue Reading

• ORA-12514 error when connecting through SQL*Plus

  I get the error "ORA-12514: TNS listener could not resolve service_name in connect descriptor" when I try to connect from the command line with SQL*Plus. When I try to connect to the database with other tools (e.g., Toad ), everything works just ...  Continue Reading

• Count consecutive years

  I am struggling with writing a query to count consecutive years from the current year. I do not want to use a cursor. There is no requirement for the dates to be 365 days apart, just that they have different years. Any thoughts would be appreciated.  Continue Reading

• Getting 'Ora-01034 Oracle not available' error message

  After installing Oracle 8i on Windows XP and rebooting the system (when I start SQL Plus and enter user ID and password), I received the error message, "Ora-01034 Oracle not available." Why is this happening?  Continue Reading

• Triggers vs. stored procedures in Oracle Forms

  In regards to triggers vs. stored procedures, does this apply to Oracle Forms deployed over the Web as well?  Continue Reading

• Purging data in Oracle 9i

  Can you please briefly explain data purging? What technology or method can I use to purge data in Oracle 9i?  Continue Reading

• Concatenate values separated with commas

  I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any function that could do that.  Continue Reading

• Memory allocation for better Oracle performance

  My question is regarding the memory allocation for Oracle 10g on HP Itanium server having RAM of 8GB. How much memory should I allocate to DB_CACHE_SIZE, LARGE_POOL_SIZE, SHARED_POOL_SIZE, SORT_AREA_SIZE and PGA_AGGREGATE_TARGET for better ...  Continue Reading

• How to free up more space for Oracle?

  I have a barcode application running on a Sun platform with Oracle9i. Oracle is 92% full. I have space in other locations. Is there any way to relocate/resize?  Continue Reading

• Select empname and managername

  What is the query to select empname and his managername from an emp table?  Continue Reading

• Using export/import process to clone to data warehouse

  I work for a university and we have built a data warehouse and two data marts for archived data. Currently we are using an export/import process to clone our online system to our test data warehouse. We are trying to determine if we should do ...  Continue Reading

• Standard vs. Enterprise Editions of Oracle

  What's the difference between Standard Edition and Enterprise Edition?  Continue Reading

• Difference between function, procedure and trigger

  What is the difference between a function, a procedure and a trigger? I know the basic differences, but I would like to know the answers based on the performance tuning aspect.  Continue Reading

• How to install Oracle9i on Windows 2003 Server?

  How to install Oracle9i on Windows 2003 Server?  Continue Reading

• Concerns about quarterly security patches

  My question revolves around the quarterly security patches. I have over 300 9.2.0.4 databases. We just started creating 9.2.0.6 databases with an eye toward going to 10.2 in the first quarter next year. Should I upgrade my existing 9.2.0.4 ...  Continue Reading

• Error: Not enough disk space

  I did a clean installation of W2K Server with SP2. Now when I'm trying to install Oracle9i, I'm getting the following error message: "Oracle Universal Installer: Error in writing to directory E:Docume~1Admini~1Locals~1TempOraInstall. Please ensure ...  Continue Reading

• "TNS:Could not resolve Service name" error

  I've installed Oracle9i in Windows XP, and when I try to connect through SQL*Plus it gives me an error: "TNS:Could not resolve Service name." I connected using scott and tiger. I even tried through svrmgrl in the command prompt. There I get a ...  Continue Reading

• Best way to upgrade 8.0.5 to 8i or 9i

  What is the best possible way to upgrade an Oracle 8.0.5 database to Oracle 8i or 9i?  Continue Reading

• How to lock a particular row?

  How to lock a particular row in Oracle?  Continue Reading

• Cause of ORA-10902 error

  I am trying to create a database on Oracle 9i Server running on Windows 2000 Advanced Server with OLE DB 8.1.7.3.0. While using the Database Configuration Assistant, I am getting this error: "ORA-10902: Oracle Instance Terminated." What would be the...  Continue Reading

• How do I start the listener service?

  I installed Oracle Developer 9i DS in the C: drive and Oracle8 SQL for NT on the D: drive on a Windows operating system. When I start the Forms Developer and compile it, I get the message "FRM-10142, start the oracle listener." Please tell me how I ...  Continue Reading

• Invalid objects after compilation

  I have successfully installed Oracle 10g on Windows XP. After that, I ran the file catalog.sql. The problem is that some objects in SYS, mainly schema, are invalid and remain invalid after compilation. What can I do?  Continue Reading

• Easy way to import public synonyms?

  Using export/import to update a development database, I normally do a schema (user) import. Is there an easy way to import public synonyms?  Continue Reading

• Necessary to take a cold backup of a temporary tablespace?

  Is it necessary to take a cold backup of a temporary tablespace? According to me it is, but when we shut down the database the SMON process then cleans up the temporary tablespace also?  Continue Reading

• Query for locally managed vs. dictionary-managed tablespace

  What is the query to find out if the tablespace is locally managed or dictionary-managed?  Continue Reading

• Taking an Oracle backup with Visual Basic

  I am developing a project using VB6.0 and Oracle 8i and am facing problems with backup and recovery. If an error occurs then I have to uninstall Oracle and I lose all the data from the database. I am taking my backup in MS Access, but I want to take...  Continue Reading

• How many types of backups are there?

  How many types of backups are there in Oracle? Also, how to take a backup from the Oracle database if I want to format the system?  Continue Reading

• OCI model for backup

  Can you tell me more about the OCI model for backing up my database?  Continue Reading

• Limitation of IN clause in SQL

  Is there any limitation of the "IN" clause in SQL, e.g., it does not take more than 1,000 parameters as its input in parentheses?  Continue Reading

• Setting up Employee Supervisor Hierarchy in Oracle Purchasing

  How to set up an Employee Supervisor Hierarchy in Oracle Purchasing? What is the setup to be followed? How is the document to be routed for approval?  Continue Reading

• Database objects becoming invalid

  We are using Oracle Database. In our client site, we have 500 users using the database at one time. Sometimes the database objects become invalid automatically while doing developments.  Continue Reading

• Receiving ORA-12541 error trying to connect through Forms/Reports

  I get the ORA-12541 error message when I try to connect through Forms6i and Reports6i Builder. I've started the listener. I have no problems accessing the database through SQL*Plus. I've checked the listener and tnsnames files and everything ...  Continue Reading

• How to replicate an Oracle database to SQL Server?

  I am trying to find the best solution for having a database replicated from Oracle to SQL Server without creating an Oracle instance in our machine. This is not a migration; the Oracle database will continue to be used as is, but reporting is to be ...  Continue Reading

• How to create a database link with an OS Authenticated user?

  How to create a database link with an OS Authenticated user?  Continue Reading

• Oracle RAC to consolidate silo databases

  I have silo databases for housing several applications. Each has its own application server and database server and its own storage. I was thinking of using Oracle RAC to consolidate all these silo applications. What are your thoughts, and are there...  Continue Reading

• Importing data between Oracle 9i and SQL Server 2000

  I have SQL Server 2000 and Oracle 9i running on my system. Can you please suggest some tool by means of which I can import data from Oracle 9i into SQL Server and vice versa?  Continue Reading

• How to solve ORA-04076 error?

  I am facing the ORA-04076 error. I googled the error but was unable to understand it. What exactly do I need to do to solve this? I am using Toad.  Continue Reading

• Problem with quick generation of archive log files

  I am using Oracle 9i release 2 with patch 5 as my production database in archive log mode. Yesterday users complained that performance are going down. I checked the database and found that archive log files are generating very quickly, nearly two ...  Continue Reading

• Scripts for backing up Oracle objects

  I want to write scripts which will give me backups of Oracle 9i objects, e.g., tables, etc. How can I write them? Or can I get such scripts free online?  Continue Reading

• Keeping data recovery site active

  My current environment is 9i release 2 on AIX 5.3. I have an OLTP database with heavy trans rate, average 300 transactions per sec. The application type is online banking, responsible for buying and selling stocks online to thousands of users via ...  Continue Reading

• Developing a performance monitoring tool

  I want to develop a performance monitoring tool for Oracle. What are the important parameters I should consider? This tool must be independent of any platform. Which language would you suggest to use?  Continue Reading

• How to import Excel data into an Oracle DB?

  How to import Excel spreadsheet data into an Oracle database (10g)?  Continue Reading

• Performance probem with INSERT statement

  I am facing a performance problem with an INSERT statement inserting records in one transaction table. The table has around 20 columns and all are NOT NULL columns. It has seven indexes and many constraints. There are many concurrent inserts ...  Continue Reading

• "Insufficient privileges" error

  I tried to log on as SYSDBA and received "ORA-01031: insufficient privileges." I am using Oracle 8.0 on Windows NT. How I can resolve the above issue?  Continue Reading

• Sequence of WHERE and ORDER BY clauses

  In this query, I can see that the WHERE clause takes place before the ORDER BY. Is there a way I can reverse this? My objective is to first sort the rows and then to pick the first one in the sorted rows.  Continue Reading

• The three smallest numbers

  How do I find the three smallest numbers from a column of numbers?  Continue Reading

• COMMIT after deleting X records

  I would like to know if there is an automatic way or command to do a COMMIT after deleting X records. If I have 1 million records to delete, what is the best way to delete and keep good perfomance?  Continue Reading

• Barcode printing in Oracle

  Is their a simpler way to perform barcode printing in Oracle other than developing a print routine?  Continue Reading

• List of Oracle apps APIs?

  I need the whole list of Oracle apps APIs that are used for inventory and purchasing order modules.  Continue Reading

• Difference between cluster and grid

  What is the difference between a cluster and a grid? Is it a conceptual difference or a technical difference?  Continue Reading

• What is the max number of sessions that Oracle 8i supports?

  I want to know the maximum number of concurrent Oracle sessions that Oracle 8i could support. I know that it depends upon the parameters, e.g., LICENSE_MAX_USERS, defined in the initSID.ora file. But what is the maximum sessions that I could get a ...  Continue Reading

• Query for five most recent dates

  I have a table that I want to query by a char date field. I want to get the five most recent dates. So far using MAX in a subquery I can get one row. How do I get the rest?  Continue Reading

• How does Oracle 8i differ from Oracle 8?

  What does the "i" stand for in Oracle8i? I know it is an advanced version of Oracle 8 for Internet-based applications. But I don't know how it actually differs in applications. I know even with Oracle 8 we can connect through the Internet to the ...  Continue Reading

• What does the dump file consist of?

  When I export a dump from a database, what does the dump file consist of? Are all the views, triggers, procedures and packages in the dump file?  Continue Reading

• Why does RMAN generate less redo than user-managed backup?

  Why does online backup using RMAN generate less redo in contrast to user-managed online backups that are performed by issuing the ALTER TABLESPACE BEGIN BACKUP command?  Continue Reading

• How do I recover deleted data without writing over current data?

  I am trying to restore a database where data was inadvertently deleted. I am recovering the deleted data from a backup. My question is how can I merge the two together without writing over my current data?  Continue Reading

• Problems with exp utility after migration

  I have migrated an Oracle 8i DB to a new build of Oracle 9i by importing and exporting the dump file from the 8i DB. Everything works fine apart from the exp utility. If I try and export "users" I get these errors: "Exp-00056:ORACLE error 31600 ...  Continue Reading

• Select another row if first choice is not available

  I have a table with two columns, col1 and col2. I would like, for a given col1, to return the record whose col2 is CM, but if col2 as CM is not available, then return the record whose col2 is ML. Can this be achieved using an SQL query?  Continue Reading

• Should sequence numbers be used as primary keys?

  Should sequence numbers be used as primary keys?  Continue Reading

• Multiple rows with same value in one column

  I'm doing a search from one table and my goal is to show only the rows with the same value in one of the columns. I am only interested in seeing the rows for all the emp_no that shows more than once.  Continue Reading

• Difference between RBO and CBO

  I do not have a clear idea about SQL tuning (Rule Based vs. Cost Based Optimizer). Could you explain the difference between RBO and CBO?  Continue Reading

• How to change the Oracle SYS password

  Is there a way to change the SYS password? Recently my SYS account crashed. I get either "You are no longer connected to Oracle" or "You have no sufficient privileges" every time I try to log on as SYSDBA.  Continue Reading

• Moving archive log files from primary to standby database

  I have the following scenario with an Oracle 10g Data Guard database. All the archive log files from the primary database are moved to standby. One of my tasks is to make sure that the moved log files will not get applied immediately to the standby ...  Continue Reading

• What is a surrogate key?

  What is a surrogate key in a table?  Continue Reading