All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

0
votes
1answer
260 views

Oracle scheduler max_jobs_Slave_processes is set to null, how many will be allocated?

Executing the following query against my database: select value from dba_scheduler_global_attribute where attribute_name='MAX_JOB_SLAVE_PROCESSES'; yields a value of NULL. My question is what ...
2
votes
1answer
767 views

How to speed up a query with a lot of subqueries

I have a query which uses a series of functions to return the status of each document for a loan. SELECT loan_number, borrower_name, get_application_status(loan_number, ...
2
votes
1answer
371 views

Oracle start database with incorrect password

I have a question regarding starting oracle database with in correct sys password. I have provided incorrect password for sys user and I am still able to start database and execute statements. So ...
3
votes
1answer
9k views

How to connect multiple users/schemas in Oracle 11g?

I'm almost new user to databases, so I think I'm doing something wrong. I have the database created. I created 3 users, 1 it's an Admin, 2 are just users. I created 2 roles, 1 for the Admin and 2 ...
2
votes
2answers
154 views

Hierarchy query with 3 tables

I am not able to understand how to achieve a hierarchy accessing 3 tables. That is, I have Table1, Table2 and Table3 where: Table1 (ID_table1, name_c, size) Table2 (ID_table2, ID_table1, name_l, ...
1
vote
2answers
12k views

How high can I set undo retention on Oracle 11gR2

Is it viable to set a very high undo retention, as to allow flashback queries several weeks back in time? Naturally, enough space must be available in the undo tablespaces to contain the amount of ...
0
votes
3answers
3k views

Query returning data from two rows of a table in a single row

My table looks like this: ID APPLICATION OTHERVALUE 0001 subapp SubvalueA 0001 mainapp MainValue1 0002 subapp SubvalueB 0002 mainapp MainValue1 0003 ...
3
votes
1answer
5k views

sql*plus 11.2 client connection to oracle timing out on Linux

One of our DBAs is seeing connection issues with his sql*plus client. Whenever he connects to the Oracle v10g server, the connection seems to time out for no apparent reason. We tried checking ...
1
vote
1answer
9k views

User can't stop Oracle listener (TNS-01190)

I have Oracle 10g installed on RedHat Enterprise Linux 5. I have 2 users that use Oracle: uid=501(user1) gid=502(group1) groupes=500(oinstall),501(dba),502(group1) and uid=500(oracle) gid=500(...
0
votes
3answers
3k views

ORA-00905: missing keyword when trying to create a synonym

I have a problem when I try to create a synonym like this: CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM; Why does this happen and how can ...
10
votes
3answers
4k views

Why do I have to select from the dual table?

This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL). select 'abc' abc, 1 def;...
0
votes
2answers
2k views

RMAN Recovery In Oracle 11g : Backup Database To Tape

On issuing this command: RMAN> backup device type sbt tablespace <tablespace_name>; we are getting the following error: ORA-19554: error allocating device, device type: SBT_TAPE, device ...
1
vote
1answer
581 views

DB stored procedures: how do they help increase security in web systems?

The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
0
votes
1answer
54 views

Oracle scheduler chain priority in data warehouse applications

The scenario is as follows: We have a data warehouse type application that relies on the Oracle job scheduler and a Chain. This chain is responsible for generating materialized views (caches) that ...
2
votes
1answer
1k views

column precision exceeded. ORA-01438 is so unmeaningful

I am not sure if this is a question to StackExchange DB Admin group. please let me know if its not. Let's suppose you have a Java batched prepared statement writing data to database. If one of the ...
1
vote
2answers
553 views

How to isolate general Oracle 10g issues through SQL

Background: I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
3
votes
1answer
788 views

Wait time differences in Oracle

I'm trying to track the different wait time classes for current Oracle sessions but I must be misunderstanding something. When I compare the sum(time_waited)/1000 for a given session in gv$...
2
votes
1answer
2k views

Why is DBA_SEGMENTS presenting twice the amount of data than the DBA_TABLES?

I tried to compute the audit log table usage using the following steps: Truncate all the audit log table data (i.e. AUD$) Perform test case that inserts and update data, also delete statement but it ...
2
votes
1answer
2k views

Oracle: Long delay opening connections

I am getting a very long delay (about 2 minutes) opening initial connections on my 11g2 development server. Sometimes I get ORA-28547: connection to server failed, probable Oracle Net admin error and ...
1
vote
3answers
5k views

Instance Startup Modes & Shutdown Modes

So, I know that there are 3 modes of startup modes available for the instance which is NOMOUNT, MOUNT, and OPEN. What I want to know is in NOMOUNT mode it is stated that the instance started but still ...
14
votes
4answers
131k views

How do I generate a tnsnames.ora file to connect to an Oracle Database?

I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find. Do I need to generate the tnsnames.ora file myself? If so, where do ...
1
vote
1answer
3k views

Unable to install 64-bit Oracle 10g Release 2 ODAC 10.2.0.3 for Windows x64 on 64 bit Windows Server 2008 R2

I need to connect an application to Oracle. In the installation guide for that application it says that it requires 64 bit version of Oracle Data Access Component,more specifically 10.2.0.3 . I have ...
1
vote
1answer
2k views

Java application unable to access Blob stream in Oracle after upgrade to 11g

This issue below might be an application problem, but I cannot seem to rule out a poor database setup, I was hoping to get some insights into potential database issues. I am maintaining a legacy Java ...
1
vote
0answers
8k views

Could Oracle ORA-08103 'object no longer exists' error be caused by size of undo_retention parameter?

Had a once-a-day long running data warehousing procedure that I had written that was failing about 50% of the time. Logs were showing ORA-08103 'object no longer exists'. ORA-12012: error on auto ...
3
votes
3answers
4k views

Best practice for a DBA to login to a database

I've read that a DBA never should login to a database as system user. That makes me wondering... how should a DBA login to a database? Should he/she create a DBA account and use it for login purposes? ...
2
votes
1answer
830 views

Restore database from individual datafile backups

I had a script that backs up all the individual datafiles like: RMAN> BACKUP AS COMPRESSED BACKUPSET DATAFILE 'datafile<1>.dbf' So i have backupsets of all the datafiles. I'm confused as ...
1
vote
1answer
1k views

How to create sql query results in columns from rows?

From the following tables: store(store_id, ...) {1 ..., 2 ...} two stores film(film_id, ...) inventory(inventory_id, film_id, store_id) rental(rental_id, rental_date, inventory_id, return_date, ...) ...
6
votes
2answers
1k views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
3
votes
2answers
263 views

Periodic Oracle restarts…intentionally

Taking on a new dba role for an Oracle 11gR2 2 node RAC, I've been told that about 9 times a year, in schedule with other software releases, we intentionally shutdown and restart the database. It ...
0
votes
2answers
3k views

Difference between chained and migrated rows

What is the difference between chained and migrated rows? Also what query should be used to display these rows? (I'm using Oracle 11gR2 database)
6
votes
2answers
28k views

How to set an Oracle user's default schema?

I created a few new users in Oracle. However, when running sqlplus, they all need to fully qualified the table names in query. What's the best way to set a default schema for these new users?
1
vote
1answer
241 views

Pitfalls of creating a foreign key constraint on a system table?

I have a number of tables which hold metadata about the tables and packages in the system. This is intended to be a resource for analysts and documentation so it is not used to create data. One of ...
1
vote
3answers
349 views

Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server

SELECT A.Field1 FROM Table1 A INNER JOIN Table1 B ON A.Field2 = B.Field2 AND A.Field3 = B.Field3 AND A.Field4 = B.Field4 AND B.Field1 = "string1" AND B.Field2 = "string2" AND A.Field5 = (SELECT ...
0
votes
2answers
1k views

Row level restore with Oracle Recovery Manager (RMAN)

We're approaching the end of 2012 and I still find myself relying on database exports to maintain the ability to do row level restore operations. While this is a proven method, it sure would be nice ...
1
vote
1answer
15k views

Oracle 11gR2 archive log destinations

I want to add a second archive log destination to an Oracle 11gR2 RAC database with ASM, the idea is that I will then have some redundancy if the primary storage should fail. Archive log is already ...
3
votes
2answers
39k views

How to store single result in variable and reuse it in a query (Oracle)?

I have a query like SELECT id FROM xyz WHERE ...; which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a ...
19
votes
5answers
106k views

How to see list of databases in Oracle?

Is there an equivalent to MySQL SHOW DATABASES statement? Is it possible to find databases in a cluster? i.e. databases present on the network on some other system? Could I analyze the files present ...
2
votes
1answer
8k views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
5
votes
1answer
2k views

Does UPDATE INDEXES work for local indexes?

I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed): CREATE TABLE table1 (col1 INT, col2 INT) PARTITION BY LIST(col1) ( PARTITION p1 VALUES(1), PARTITION ...
2
votes
3answers
462 views

How can I identify and fix bottlenecks in an Oracle11gR2 import

I've recently been tasked with moving our Oracle 11g database from a linux CentOS desktop to a windows 2008 server. Given I've not done much behind the scenes stuff with oracle before I'm a bit out of ...
5
votes
2answers
26k views

Oracle Enterprise Manager 11g dbconsole “Internet Explorer cannot display the webpage”

The dbconsole is configured and started, but when I navigate to it, Internet Explorer shows the page stating that "Internet Explorer cannot display the webpage". This is the error that means that IE ...
4
votes
2answers
4k views

Hibernate Query Running Slow

I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end. This query is taking anything from ...
3
votes
2answers
8k views

Oracle Procedure to Enable/Disable all constraints

I have this nice piece of code: begin for i in ( select constraint_name, table_name from user_constraints where constraint_type ='R' and status = 'ENABLED' ) LOOP execute immediate ...
1
vote
1answer
172 views

oracle how to call procedure

This is very basic question, but I couldn't find an answer here... So I have this very useful procedure without any parameters: CREATE OR REPLACE PROCEDURE DISABLE_TRIGGERS IS v_statement VARCHAR2(...
0
votes
1answer
2k views

Find ID columns with null value in oracle database

I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database Any suggestion?
14
votes
2answers
13k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
2
votes
1answer
1k views

oracle merge operation hangs (or loops); query tuner claims it's a cartesian product operation

I have a Merge query (open to better suggestions) that seems to cause the server to hang. (I'm not sure what it's doing, but it pegs the CPU and never completes) When I run it through the query ...
1
vote
1answer
2k views

Replicating data from Oracle to MySQL

I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
7
votes
2answers
24k views

sqlnet.ora or tnsnames.ora changes require reboot?

Anytime you change Oracle's sqlnet.ora or tnsnames.ora files, does the system require a reboot? In my instance, I only have the Oracle client installed on the machine I'm referring to, but out of ...
1
vote
1answer
2k views

Oracle Imp Dump Full

Can I create a new instance in a new server and import a dump file which was made with the command exp full=y etc? If it is possible do I need to recreate tablespaces and users before the import? If ...