Ask the Experts

Ask the Experts

• 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

• Setting up multi-master replication in Oracle 10g

  Could you please let me know the procedure to set up multi-master replication in Oracle 10g?  Continue Reading

• What is SOA?

  What is SOA and what are its benefits?  Continue Reading

• Consecutive dates within a range

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

• Reading text file from URL

  I need to read a fixed-length text file from a URL rather than one residing on the file system.  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

• What is OTN?

  What is OTN? Where can I find those documents?  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

• ORA-00942 error during export

  When exporting data from our database, I get the ORA-00942 error. Why?  Continue Reading

• Best way to precreate database before import

  What is the best way to precreate the database on the new box before you import the db to it?  Continue Reading

• Using NDS or dynamic SQL in update trigger

  I am trying to use NDS or dynamic SQL in an update trigger to extract the value of the :new and the value of :old for a given column.  Continue Reading

• CURRENT OF with a RETURNING clause

  I am using Toad for Oracle to create PL/SQL. I get an ORA-00933. Can I do a CURRENT OF with a RETURNING clause?  Continue Reading

• Hardware requirements for RMAN

  What are the hardware requirements for implementing RMAN? Do we need a separate PC?  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

• Backing up archive files via RMAN

  I tried to back up the archive files via RMAN, but I got an error from RMAN. What should I do to correct the filename RMAN wants to back up?  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

• Determining which execution plan is better

  How do you determine that one execution plan is better than another?  Continue Reading

• CASCADE CONSTRAINTS clause for dropping tables

  In your answer to drop all tables with one command you overlooked the relationships between tables. Therefore you would need to add on a CASCADE CONSTRAINTS clause. Would you not?  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

• Including OS files in cold RMAN backup

  During a cold RMAN backup can I include OS files? The app is a document management app that has docs in the database and on an OS file system.  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

• Migrating database to new storage box

  We have got new EMC storage and want to migrate our Oracle database (300+ GB) to the new storage box.  Continue Reading

• Calling Web service from Oracle vs. .Net

  What are the advantages of calling a Web service from Oracle rather than from .Net? Are there any performance benefits?  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

• InitSID.ora processes for cluster servers

  How do I calculate the processes in my initSID.ora for three cluster servers? What should be the criteria for the sessions, as well?  Continue Reading

• Benefits of RAC in large systems environment

  In a large systems environment where multiple CPUs and memory are dynamically available to be deployed for a database server, what are the benefits of a RAC implementation?  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

• Database vs. relational database

  What are the differences between a database and a relational database?  Continue Reading

• Using Teradata and Oracle Forms to build application

  I want to build an application using Teradata as the DB source and Oracle Forms 9i as the front end of the application.  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

• ORA-02248 error while trying to import

  I want to migrate from Oracle 8.05 to Oracle 9i. I exported the database in 8.05 and then tried to import it in Oracle 9i. I am getting the following error: "ORA-02248." Plesae help me solve my problem.  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

• 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

• Creating new tablespace in oradata folder

  In my Oracle 10g database installation, the oradata folder is at the same level as that of the Oracle 10g database folder. I have no access to this folder. I have another oradata folder inside the Oracle 10g database folder. I have access to it., ...  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

• Clone database with exp/imp on Linux

  Is it possible to clone an Oracle 10g database from Linux (R3 update4) server to IBM Blade Server under Linux AS4 with exp/imp?  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

• RAC node scaling in DSS workloads vs. OLTP

  What are your experiences with RAC node scaling in DSS workloads vs. OLTP? Also, isn't the primary bottleneck in scaling out the overhead of the cachefusion lock management protocols, not the actual interconnect?  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

• Exact contents of redo logs

  I want to know the contents of the redo logs. Do redo logs contain exact data inserted by users or instructions to redo transactions in case of failure?  Continue Reading

• What is an outer join?

  What is an outer join? How is it advantageous over an inner join?  Continue Reading

• Sum of all percentanges for a customer must be 1

  My table is as follows: Preference (login:str(10), symbol:str(20), percentage:real) : (PK: (login,symbol)), where percentage is a fraction of one unit for the corresponding mutual fund designated by symbol. The sum of all percentages (fractions of ...  Continue Reading

• ORA-12545 error with Form Builder in Developer 10g

  I have already worked once with the Form Builder from Oracle Developer 10g, which I have installed on my computer. But today, when I try to connect to the database, I get the error "ORA-12545: Connect failed because target host or objet does not ...  Continue Reading

• Steps to implement RAC

  How can I implement a RAC application in Oracle Database in my system?  Continue Reading

• NIC teaming for interconnect

  We have a three-node 9.2 cluster on Windows 2000 that we will be moving onto new hardware to be a two-node 9.2 cluster on Windows 2003, and I was interested in your thoughts on NIC teaming for the interconnect, as I haven't found much at all out ...  Continue Reading

• Transaction failover effectiveness

  How effective ia transaction failover? If I have session-specific variables set in my session on node 1, are they maintained and accessible from node 2 if node 1 goes down? (i.e., v$session.client_info, mode, action, temp_tables, etc.)  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

• Declaring same variable twice in a package

  Recently, I tried declaring and initializing the same variable twice with different values each time in a PL/SQL package. To my surprise, it got compiled without any errors. I tried the same in a procedure and got a complier error.  Continue Reading

• Replacing hostname with IP address

  I have installed Oracle 10g and am able to connect to the db without any errors. But when I replace the hostname with the IP address of my machine in the listener.ora file, I am getting an error: "ORA-12514: TNS: listener does not currently know of ...  Continue Reading

• Where is directory in database?

  I have created a directory as below but I want know where the directory is stored in the database.  Continue Reading

• Migrating Oracle applications to open source database

  Can you provide some insight into the claim that you can migrate most Oracle-based applications to an open source database unchanged? This sounds too easy.  Continue Reading

• Is Oracle 8.0.6 compatible with AIX 5.2?

  I did an export from an Oracle 8.0.6 / AIX 4.3.3 platform and would like to import on a new AIX 5.2 server (still Oracle version 8.0.6). Is Oracle 8.0.6 compatible with AIX OS 5.2?  Continue Reading

• Migrating Oracle9i database from Solaris to AIX

  We have a 300 GB database running on Oracle9i release 2 on a Solaris platform. We want to migrate the database on IBM AIX server Oracle9i rel2 on a HACMP envoirment. Please guide me on the steps involved in doing this, the downtime required for the...  Continue Reading

• Erased too many registry keys with deinstall

  I had installed Oracle 10 XE on my computer, but I deinstalled it. Doing so, I think I erased more registry keys than necessary, because when I tried to reinstall it, Windows showed the message: "error: 1612 The installation source for this product ...  Continue Reading

• Faster way to migrate to 10g on Linux

  I have to migrate a 9i database with a size of 700 GB on AIX to a 10g database on Suse Linux. With export/import it lasts for a disproportionately long time. Is there a reasonable alternative?  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

• Installing Oracle 10g on Solaris 10

  Would you please explain how to install Oracle 10g on Solaris 10?  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

• Accessing database in dual boot system

  I have a dual boot system on a Windows platform. The first OS is Windows 2000 Server and the second OS is Windows XP. I have installed the Oracle 9.2 database in Windows 2000 Server and it is working fine. I want to access the same database in ...  Continue Reading

• Minimum requirements for Oracle 8i on Windows XP

  What are the minimum requirements for Oracle 8i to run on a PC with Windows XP?  Continue Reading

• Could not resolve service name

  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 it gives me ...  Continue Reading

• Foreign keys in a composite primary key

  Is it possible to use one of the attributes of a composite primary key as a foreign key?  Continue Reading

• Counting rows in a UNION query

  How can I get the total count of the number of records returned, not duplicate, from the union of two queries which return the same set of data but using different where clause?  Continue Reading

• Does RAC scale horizontally for OLTP?

  Does Oracle RAC scale horizontally for a high-transaction OLTP database? There is an ongong debate that it can only scale horizontally to a max of four to five servers without generating deadlocks and bottlenecks in the interconnect.  Continue Reading

• Faster way to parse codes into table

  I am working on a large text file. Each row has codes and values that I must parse into a table using DMBS_LOB.INSTR and DBMS_LOB.SUBSTR. I found those methods really slow. Is there a faster way?  Continue Reading

• Configuring Log Miner in Oracle9i

  I have an Oracle9i database on a Unix platform, and I want to configure Log Miner.  Continue Reading

• Data dictionary cache

  I have a doubt about the data dictionary cache. I have to know where the information is stored within the data dictionary and what the table name is.  Continue Reading

• ORA-12699 when logging on as domain user via client

  I'm encountering the error "ORA-12699: Native Service Internal Error" while connecting to the Oracle 10g database server using Oracle client. This happens when I log on as a regular domain user, but doesn't arise if I log in as administrator.  Continue Reading

• List of databases installed on local machine

  I am using Oracle 10g. How can I see the different databases installed on my local machine? Also I want to see a list of tables in a particular database. I want to use SQL*Plus to get this information.  Continue Reading

• Non-zero values for SHARED_POOL_SIZE and JAVA_POOL_SIZE

  I am getting an ORA-04031 error. I have a two-node 10g RAC cluster. If I gave both SHARED_POOL_SIZE and JAVA_POOL_SIZE values (thus overriding the defaults) how would this affect the running of the CLUSTER? Does a CLUSTER require these values to be ...  Continue Reading

• Full import/export from database with different name

  How do I make full import into a database using full export from a database with a different name?  Continue Reading

• Taking full backup with Toad

  Can you please tell me how to take a backup of a database? I am using Toad, but Toad has options to take each component (like tablespace, table data) backup separately while I need the backup of the whole database. Can you send me the script, if we ...  Continue Reading

• Joining tables without common columns

  How do I join two tables without common columns and display any two columns, all records in a corresponding table?  Continue Reading

• Determining which table and column a field is stored in

  Is there a way in Oracle Applications 11.5.9 using RDBMS 9.2 to place the cursor on a particular field in a window and be able to determine which primary Oracle table and column name that field is stored in?  Continue Reading

• Purpose of CLUSTER_DATABASE parameter

  We have a two-node RAC database. Recently we switched our production database from no archive log mode to archive log. We changed the parameter CLUSTER_DATABASE=FALSE on one of the instances before converting and reverted back. Could you tell me ...  Continue Reading

• Memory needed for RMAN backups

  I am thinking of using RMAN as the backup and recovery procedure. I want to know how much memory it will use for backup per channel. I want to know if I need to upgrade memory before using RMAN.  Continue Reading

• Recovering lost datafile

  How can you recover a datafile which is lost and is not in your backup?  Continue Reading

• Performing full backup without all archivelogs

  I support an Oracle 10g database on an AIX server and some of the older archivelogs were mistakenly deleted before they were backed up. I want to take a full backup that I can restore the database to this point if there's a failure of some sort. ...  Continue Reading

• Creating an RMAN catalog

  I have created the RMAN database and the rman/rman user. I created the tablespace. Not too clear about the catalog. Do I need to run a script or package to create a catalog? Once the catalog is created, what are the next steps?  Continue Reading

• Full export with sys and sys object errors

  I made a full export on an Oracle 9i database and created a database on a different machine with a different name. When I tried to make the full import I had lots of errors, especially at the sys and sys objects. What should I do?  Continue Reading

• What is normalization?

  What is normalization?  Continue Reading

• Combine multiple lines of text into one

  I have a table that I created from a flat file. I have thousands of records. What is my best approach to normalize this? I would like to keep the multiple lines of text, but put them into one field.  Continue Reading

• Steps to use SQL Developer

  Please let me know how I can get my hostname, service name and the port number to be able to run SQL statements and do other things on Oracle SQL Developer.  Continue Reading

• Running multiple instances and databases on one server

  How to run multiple instances and mulitiple databases on a single server? Sometimes I have faced an interview question like "How many databases you are handling?" Please clarify.  Continue Reading

• Moving table with indexes to another tablespace

  Do you know how to move an Oracle table to another tablespace along with its associated indexes? I am having trouble with a LOB moving from tablespace A to B.  Continue Reading

• Equivalent of verify_function in 10g

  Can you please tell me what the verify function in Oracle 10g is, similar to verify_function, which is used in 9i?  Continue Reading

• Setting up audit trail in Oracle 9i

  How do I set up an audit trail in Oracle 9i on Windows 2000, to log all database accesses/updates, in some audit table within the database?  Continue Reading

• Querying a SQL Server database from Oracle

  Is there something analogous to a linked server (in SQL) in Oracle? Can I query a SQL Server database from Oracle?  Continue Reading

• Rollback segment datafile deleted

  Somebody deleted the rollback segment datafile from the server. The database cannot be started again. We get "ORA-01545 rollback segment 'RBS01' specified not available." Sometimes, it will throw a ORA-00600 error.  Continue Reading

• Problem dropping materialized views

  I have an issue with dropping materialized views. I've been trying to DROP an MV which is defined as REFRESH ON DEMAND and there are no locks anywhere. A DROP statement sits there and never returns.  Continue Reading

• ORA-14450 error with ALTER TABLE

  When executing (from SQL*Plus) ALTER TABLE event_work MODIFY(user_pid VARCHAR2(22 BYTE)) we get this error: "ORA-14450: attempt to access a transactional temp table already in use."  Continue Reading

• Query to select CLOB field

  I want a simple query to get the output of a clob type field in Oracle. I have inserted text ("this is a test for clob") in the clob field. How to select this?  Continue Reading

• Identifying standby database

  If someone configured a physical standby database, how can I identify by seeing the primary database initialization parameter that one is the standby database?  Continue Reading

• Oracle instance architecture

  What is the difference between Oracle architecture and Oracle instance architecture?  Continue Reading

• "Invalid hex number" error with update trigger

  I am trying to write a before update trigger which will fire only when we update the password in the users table. The password column is encrypted in the users table. The keys are stored in users_keys. It is showing an error: "ORA-01465: invalid hex...  Continue Reading

• Creating table without primary key

  I would like to create a table without a primary key. Does Oracle (10g) use the rowid as the default primary key in this situation?  Continue Reading

• All queries with DB link return zero rows

  I created a link between two Oracle DBs, DB1 and DB2. While querying from DB2 to DB1 using the link, the query is returning zero rows. For any table, any query this is the result. What could be the problem?  Continue Reading