Troubleshoot and Solve Oracle Problems - Page 30

Viewing queries in v$sqlarea

I am having a problem viewing the queries in v$sqlarea. I have a procedure (p1) with lots of insert, select, update and delete statements. I am calling procedure p1 from a test procedure p2 inside a loop to have a load test for p1. But when I try to... Continue Reading

Why I love VIEWs

I have one table consisting of agents and their clients and policies. One agent can have more than one client, and one client can have more than one policy. How can I select just 25 clients per agent? Continue Reading

High System% usage

I have observed a high System% usage, around 38%, by Oracle 9i. The 'strace' tool didn't give any conclusive data. Could you suggest a way to identify the reason for the high System% usage? Continue Reading

Alternative solution to BITMAP index

I have two Oracle queries. When I do Explain Plan on these queries, they both use Index on col2 and not col1. Query1 is fast. Query2 takes over three minutes! This is the problem. I created BITMAP Index on col1 and both queries started working fast,... Continue Reading

Integer or CHAR(1) primary key?

I have a table containing customers and an additional table defining types of customers. There's a relationship between them defining the types of customers but integer type PK/FK for CUSTOMERS_TYPES/CUSTOMERS is used. Is it clever to use CHAR(1) PK... Continue Reading

APIs in Oracle Enterprise Manager

I would like to know if there are any APIs available in Oracle Enterprise Manager to find out details of a process. For example, if I know that my SAP program is running slow on my Oracle database, I would like to pass the process ID or program ...Continue Reading

TNS protocol adapter error

I'm installing Oracle9i using the typical installation, and when I try to log in through SQL*Plus I'm getting the message "TNS protocol adapter error." Do you know why I'm getting this error?Continue Reading

Data partitioning and surrogate keys

In designing a database with Oracle partitioning in mind, would it be advisable to create surrogate keys or is it better to remain with the original modelled primary key and create data partitions on the unique key (which is the modelled primary key...Continue Reading

"Oracle not available" error

I have just installed Oracle9i on Windows XP, but when I start SQL*Plus, I get the following errors: ORA-01034: "Oracle not available" and ORA-27101: "Shared memory realm doesn't exist." Please tell me how and where I should make necessary changes ...Continue Reading

Logging in to a test database

I have a test database that I am trying to refresh from another database. When attempting to import the data, I forgot to truncate the tables. I am now trying to log in to the database as both SYS and SYSTEM and I am getting a 1031 error, "invalid ...Continue Reading

Configuring Oracle RAC on a single node

Can you tell me how to configure Oracle RAC 10g, 9i on UNIX and Linux on a single node?Continue Reading

Installing Oracle DBMS on Solaris servers

Where can I find information about installing Oracle DBMS on Solaris servers? I would like a detailed step-by-step approach.Continue Reading

Proof of installed security patches

We are being audited by our internal security group and I have to prove that I have installed Oracle security patches from Alert #68. How do I prove that these patches were installed on Unix and Windows servers?Continue Reading

RMAN connection error

I have just installed Oracle 9.2 on Red Hat Enterprise Linux. Everything is working perfectly except RMAN. When I write "rman" and press enter it hangs. If I write "rman connect target /" it gives an error saying "can't open connect," though my ...Continue Reading

Connecting to 10g database after rebooting server

I have installed a 10g Oracle database on a Windows 2003 server. After the server is rebooted I am unable to connect to the database. Can you please help me to resolve this?Continue Reading

Temporarily renaming files for testing purposes

Am I at risk if I temporarily rename the /etc/oraInst.loc file so I can put up a separate Oracle home for upgrade testing purposes? I can't fool with the "real" test/dev/prod instances which all are on the same set of executables.Continue Reading

8i and 9i databases on same server

I have an Oracle8i database on a UNIX server. Now I want to install Oracle9i on the same server, so 9i and 8i will co-exist on it. Would you give me instructions on how to configure the system before the installation of 9i so that the 8i database ...Continue Reading

Checking for corrupt blocks

At what stage will DB_BLOCK_CHECKING be performed? Is it while reading the blocks into the cache or at some other stage?Continue Reading

Editing object-type data on the Enterprise Manager Console

Is it possible to insert/edit object-type data on the Oracle 9i Enterprise Manager Console? I am only able to view/edit standard relational data but not data defined by object types.Continue Reading

Choose the right RAID level

Use these quick guidelines to decide what RAID level you should use for different components of an Oracle installation on Windows.Continue Reading

Database returning duplicate records

I have a problem getting correct results from my database. My application is completed about 80%, but now when I want to make my reports I find that my database design is not correct (returns duplicate records).Continue Reading

Importing an Excel file into Oracle 10g

I need to import an Excel file into Oracle 10g. How do I do it?Continue Reading

Deleting a user from a tablespace

I have a deleted user but I am getting its name again in TOAD. If I try to delete it from my tablespace, I get an error that says "cannot drop a user that is currently connected." Please help.Continue Reading

Loading PDF files using SQL*Loader

How do I load a PDF file into an Oracle table using SQL*Loader?Continue Reading

Is full backup mandatory after changing parameters?

We have a production instance of size 1.3 TB (archive log) and need to increase the db_files soft parameter in the parameter file. Is it mandatory to take the full backup of the database after changing the parameter and recreating the control ...Continue Reading

Pre-creating tables in 9i

I am creating a new 9i database and then pre-creating the tables, importing the user data from an 8i database. I would like to know if I should have tablespaces created with different uniform size. Once I pre-create the table, I also have the ...Continue Reading

Connecting to an Oracle database through .NET

How do I connect to an Oracle database using Visual Basic/.NET?Continue Reading

Converting to LEFT OUTER JOIN syntax

I have a query where the last table security_role_authorization needs to be rewritten using a left join somehow so that if there is no authorization level for a security role it will still show the security roles.Continue Reading

Generating week dates

I have two dates: 10th May 2005 and 23rd June 2005. I have to display the weeks for those dates. Can you please help me out in writing a SQL query for this kind of result?Continue Reading

Migrating 8i databases to 9i

We have a few Oracle8i (8.1.7.4) databases, and we are going to upgrade them to 9i (9.2.0.5) in the next few days. What are the different ways to migrate the 8i DBs to 9i and is one better than the other?Continue Reading

Using DBUA to upgrade

I am trying to upgrade a 9i database to 10g on an IBM-AIX platform. While using the DBUA utility, I was informed that it could not create the SYSAUX tablespace even though there was a ton of space. Has anyone had any success in using DBUA to do an ...Continue Reading

Database Upgrade Assistant error

I have Windows 2000 and Oracle 8.1.7 database installed on it. Recently I installed Oracle 9.2 on it and I ran the Database Upgrade Assistant, and it threw a ORA-12560 error on the first of six steps. I changed the Oracle home to both Oracle ...Continue Reading

Importing export file after 9i upgrade

I am in the process of upgrading from Oracle8i on Windows NT to 9i Rel2 on Windows Server 2003 running on separate boxes. I have installed 9i on my Windows 2003 server and created the necessary tablespaces. Can I now simply import the entire full ...Continue Reading

Oracle 9i login error

After installation of Oracle 9i on Windows XP, when I try to log in using "scott/tiger," it's not logging in and displaying the message "Enter user-name," and for the third time it's coming out of SQL. Error: "ORA-12560." I am not entering any ...Continue Reading

Running Oracle9i in 32-bit mode

I'm trying to upgrade an 8i database to 9i on an AIX machine but it is 32 bit. Can I run 9i on AIX 5.1 in 32-bit kernel mode? If so, how do I get it installed, linked and working?Continue Reading

Starting database automatically

Why does my Oracle Database (8i) not start automatically when my server (2003 enterprise) starts up? I have to execute the command to start my database each time when I start my server.Continue Reading

Oracle vs. Access

We use Access as a database. Somebody suggested we use Oracle instead of Access. I cannot find any comparison or white paper that can explain the difference of Oracle vs. Access. I have to justify the change to an Oracle database to upper ...Continue Reading

Upgrading to Oracle 9i from Oracle 7

Can I upgrade from Oracle 7 to Oracle 9i without going to Oracle 8?Continue Reading

Changing server mode from dedicated to shared

If we change the server mode from dedicated to shared, can the response time of end users improve? The database was initially in dedicated mode, but there are multiple users connecting to the same. What can be the effects on the existing database? ...Continue Reading

Performance tuning: The limits and limitlessness of time

Columnist Craig Shallahamer discusses a subtle aspect of analyzing response times for Oracle performance tuning: the relationship between service time and queue time and how that relates to the operating system.Continue Reading

Creating a performance tuning document

I have been assigned the task to create a performance tuning document which will help other DBAs in sizing the database and tuning performance. If you have a template document it would help me a lot.Continue Reading

Changing parameters for better performance

I have a very intensive application--4,500 transactions per second. I would like to change the table/indexes storage in order to get better performance.Continue Reading

Setting Oracle parameters

We are running an Oracle 8.1.7 database on a Windows 2000 Advanced Server, quad processor IBM server. We get reports of data access and performance speed issues. Somebody mentioned to me that we could manipulate some settings in our INIT.ORA file....Continue Reading

Too many open files error message

I am seeing the following error message in my log files and I was wondering if you could point me in the right direction in resolving this.Continue Reading

Increasing listener and process settings

We have an Oracle9i database on Unix Solaris 5.9 with three instances processing millions of records. We often receive the "timeout" message for one or more jobs. The timeout cause and action suggest that we increase the listener ...Continue Reading

Performance of MERGE statements

Has anyone analyzed the performance of a MERGE statement vs. separate INSERT and UPDATE statements? I have several MERGE statements taking over an hour every night and would like to improve on that.Continue Reading

Two schemas on a single database

Suppose I have 2 schemas/users on a single database (s1 and s2) and s1 executes procedure prc1 in s2 which updates the table t1. Also imagine both s1 and s2 have the table t1 (same structure). Which table will be updated?Continue Reading

Controlling bandwidth used by XML

XML seems to require more bandwidth for moving data than either text files, standard sql*net connections or even db_links. What would you recommend for controlling the amount of infrastructure bandwidth consumed by use of XML?Continue Reading

Changing the tablespace for an XML schema

When I register an XML-Schema with dbms_XMLSchema.registerSchema, are the tables created into the default tablespace? Is it possible to change the tablespace for the tables?Continue Reading

Using XML and Oracle together

I have been an Oracle DBA for the past eight years but never had a chance to work on XML and Oracle combined. I would be grateful if you could let me know where to start looking to learn more about the advantages of using XML and Oracle together.Continue Reading

Generating XML from stored procedures

What are the best ways to generate XML from stored procedures? I have tried using SQLX with version 9i and it works pretty nice. I would like to know if you can recommend any other ways of doing it and suggest which approach has to be taken in what ...Continue Reading

Rolling back Oracle XSU

Some of the Oracle XSU that I create contains several inserts/updates and I would like the opportunity to rollback if just one of those inserts/updates fails but this seems impossible as I only get the aforementioned int and not Exceptions thrown ...Continue Reading

CLOB string too long

When you concatenate, Oracle treats a clob as a varchar2 and then, at over 4000 characters, the string becomes too long to handle. Do you have any ideas to get around this so that I can have larger HTML pages?Continue Reading

Stored procedures in concatenated XML

I've got a flash client that expects to get data in XML format from a call to a HTTP service. Right now I've got Oracle HTTP (Apache) installed that calls PL/SQL stored procedures. The stored procedures use HTP.P calls to embed data from SELECT ...Continue Reading

MetaBase scripting for the Oracle data warehousing DBA

The OMB scripting language is a extension of TCL and is an attractive alternative to the do-it-yourself approach to ETL management for the Oracle data warehousing DBA.Continue Reading

Problem solve