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

2
votes
2answers
4k views

Oracle aggregate function performance

Can Oracle be smart about aggregate functions, such as MIN(), MAX(), and (AVG)? My testing shows that it seems to be surprisingly stupid. I have the following query: SELECT COUNT(userId), AVG(age), ...
3
votes
2answers
4k views

Oracle Trace File Interpretation: enq: TX - index contention

Background I've got an application which is doing a large number of concurrent inserts into an Oracle table. The Top Activity view is reporting a lot of index contention and I am attempting to reduce ...
11
votes
3answers
5k views

How could sequence.nextval be null in Oracle?

I have an Oracle sequence defined like so: CREATE SEQUENCE "DALLAS"."X_SEQ" MINVALUE 0 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 0 NOCACHE NOORDER NOCYCLE ; ...
3
votes
2answers
29k views

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Couple of days back, I execute shutdown on my DB but it took very long time so I shutdown my m/c. After restarting my machine, I get this exception when I try to connect to my database using TOAD. I ...
40
votes
4answers
362k views

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database local_SID = (DESCRIPTION = (ADDRESS ...
2
votes
1answer
23k views

how to Grant Permission on a table to a user on the other database in oracle

Hi I have a table in my schema to be accessed from a user from Another Oracle databse.. I am trying with GRANT all on a.table_name to test@o11db; here a is my Schema and Test is another schema in ...
2
votes
2answers
2k views

How can Oracle use a Range Scan here?

I have a Customer table, with the columns "Name", "Location" and "DateMovedIn" (amongst others), which contains c. 20,000,000 rows. On this table, I have the index I_LOC_NAME_DATE, such that: Column ...
4
votes
2answers
6k views

oracle distinct doing sort

I'm getting poor performance from DISTINCT. The explain plan indicates that it is doing SORT (GROUP BY) which doesn't sound right. I would expect some kind of HASH aggregation to produce much better ...
2
votes
2answers
15k views

ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

I got that error when I tried to execute this query: select * from lc_history2 lc where lc.view_name <> 'LC Main' and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc ...
2
votes
1answer
191 views

Oracle SecureFile - DBFS

If I create a table that has a SecureFile column, does it mean that all the LOBs put into the column are automatically saved as files? In other words, DBFS and SecureFiles go hand in hand mandatorily ...
1
vote
1answer
1k views

Is there a tool that can check the availability of an Oracle 11g database on a remote machine?

I'm configuring the boot sequence on a Linux box, and one of the startup applications is dependent upon an Oracle instance being available on a different box. I would like a bash scriptable tool to ...
4
votes
3answers
4k views

Dropped tables are not going to Recycle Bin

To start, I am a student and just getting my feet wet with Oracle. Quick history Virtual Box XP running Oracle express with SQL Developer. Created a user and tablespace for myself. (Key here is ...
5
votes
1answer
3k views

minimum privileges required for a user

I am configuring a 10g Express database on Windows 2003 that will be the back end for a public web server. There are many other things to do to harden the database but a good place to start is user ...
2
votes
1answer
1k views

Alter in PL SQL Loop

I just started learning PL/SQL an hour ago... I'm trying to set profile settings dynamically... I select distinct profile names from dba_users and I'm trying to run an alter profile command for each ...
2
votes
2answers
2k views

Reusing index in constraints

I'm trying to understand how Oracle determine if the index can be used for constraint. For example, the following works : create table temp11 (id int not null, val varchar2(10), CONSTRAINT PK_temp11 ...
10
votes
2answers
17k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
1
vote
1answer
349 views

Why are Oracle processes only using half the SGA set?

I have an Oracle set up on a remote server that's maintained by a third party. Recently it became corrupt and we replaced it with a new instance. Since then it's been running slower when performing ...
3
votes
1answer
7k views

How can I delete a corrupted table space?

Problem: On an Oracle 11.2g DB; We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager). ...
2
votes
1answer
941 views

Write to different schema with own tablespace from another account

Say I have created these two accounts: CREATE USER u1 IDENTIFIED BY "&&u1Password" DEFAULT TABLESPACE u1tbs TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON u1tbs QUOTA 256M ON ...
13
votes
2answers
1k views

forensically deleting/updating data

I have a need to forensically remove data from oracle. If I just delete it, my understanding is the data will still actually be in the data file until that space is reused. I'm not concerned about the ...
1
vote
1answer
280 views

Oracle block usage

I am wondering about a weird deadlock and after checking all other components I am considering it to be a INITRANS problem in Oracle 11.2. Now three questions arose: Is there some way of analyzing ...
4
votes
2answers
2k views

Oracle 11g see transaction or session start time

I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records? I've got a transaction id "TX-1234-abcd", a ...
6
votes
3answers
10k views

Trouble killing sessions then immediately dropping users in Oracle 11g XE

I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call. Here is what I have so far: DECLARE cursor c is select * ...
4
votes
1answer
591 views

Isssue with Database Links converting OEM Job to DBMS_Scheduler Job

We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a ...
8
votes
2answers
21k views

How do I find an object that doesn't seem to exist in the objects table?

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
3
votes
2answers
33k views

Error ORA-12541: TNS:no listener

Whenever I try to start the listener (I just have only one Oracle database) I get the following: LSNRCTL> status Connecting to ...
2
votes
3answers
948 views

Oracle OLAP option

I'm considering giving a try to the Oracle OLAP option. Can anyone share their experiences with it? I didn't find much discussion/blog activity on the subject. In particular, I'm interested in ...
0
votes
1answer
73 views

Replicating Results of Queries on Oracle

I wan to setup a database such that its data come from queries run on a remote database. Basically the source database is very slow and we have no control over it. So, I want to create a second ...
2
votes
1answer
2k views

Oracle SQL count occurances of character in VARCHAR

Is is possible to count the number of occurrences of a character in a VARCHAR in oracle? Something that would allow me to determine '11001110' has 5 ones?
0
votes
1answer
1k views

ORA-01031 Insufficient Privileges Raised From Within Function

When I run a CREATE USER statement within Sql Developer, the user is created just fine. When I try to call a function which performs CREATE USER (followed by a bunch of other statements) I get the ...
2
votes
1answer
206 views

SQL from/where or join?

I was wondering performance wise what would be faster a join or a from and where query. I am trying merge and update query but performance is terrible?
6
votes
2answers
5k views

efficient way to poll database?

I have a Linux CentOS server hosting an Oracle 11.2 database, which communicates with an application server, which communicates with a client (a Flex application). I'll try to describe the pseudo-code ...
5
votes
1answer
2k views

Does DBMS_STATS.AUTO_CASCADE ever work?

According to the documentation, "Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics are to be collected or not." But it seems like AUTO_CASCADE will always ...
2
votes
2answers
1k views

How to compare performances of two databases

There are two database servers and I want to compare their performances. Both have Windows operating system and and Oracle (10.2.0.4 and 11.0.2) database. Both are OLAP databases with 15-20 users ...
4
votes
4answers
986 views

How are foreign key indexes helpful?

How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
2
votes
1answer
20k views

SQL Developer cannot connect to Oracle database, yet sqlplus can!

I am having a problem connecting sql developer to my Oracle 10g database. sqlplus is able to connect fine, but when I try to open the connection in sql developer, I get 'ORA-12514 TNS does not know ...
1
vote
1answer
54 views

Database partition efficiency

Let's assume that you have a table that partitions on CATEGORY and SUBCATEGORY. Also assume that every subcategory can be mapped to a category, i.e. subcategory are proper subsets of categories. ...
4
votes
1answer
35k views

Oracle SQL create view privileges

This is more of a theoritical question but I need help ASAP. Here it is: With the appropriate use of SQL queries, grant as system administrators the required access privileges to a database user ...
7
votes
3answers
5k views

ORA-04031: unable to allocate bytes of shared memory (“”,“”,“”,“”)

I am receiving this error in a development database. It happens on some specific queries (simple inserts and deletes via .NET application). The database is used by one user, and error happens ...
3
votes
2answers
19k views

Oracle11.2g post-installation problem on Oracle Linux kernel

[question copied from stackoverflow] After installation of Oracle 11.2g in silent mode on the Oracle Linux kernel, I have the following error: [u01/app/oracle/product/11/bin] $ sqlplus sqlplus ...
0
votes
1answer
8k views

Oracle SQL for left outer join to rownum = 1 of another query?

Background I have a query joining many tables together, the chief of which is a workorder table. I also have a table called WOStatusHistory, which stores every status for every workorder. A diagram ...
2
votes
3answers
1k views

do oracle redo logs need to be copied when all other data is copied to a new disks?

I have to migrate all data for an Oracle server from the present SAN to a new SAN. It is an 11g database in production on a Linux server. Database software is on local disk but all data (including ...
0
votes
3answers
2k views

Oracle returns different results when executing a sql with bind variable or with a fix value

The following two statements (strongly simplyfied) returning a different result: with a fix value for rownmun: select * from table where rownum <= 12; with bind variable for rownum: select * ...
2
votes
1answer
371 views

Rman not replacing logs with same name on networked drive

Historically, rman saved logs by replacing files of the same name so that the most recent set was always there, and logs from backup of older than the most recent set simply went away. Rman is ...
2
votes
1answer
5k views

RMAN & Archivelog Deletion Policy confusion

Never been able to fully grasp the notion of delete input/delete input all when backup up Archivelog with rman. Tied to the same issue is the configure Archivelog deletion policy to backed up X times ...
3
votes
2answers
2k views

How does name resolution work in Oracle 10g?

In Oracle 10g (10.2.0), there are 4 types of names resolution: Easy Connect(10g added this new method) Local naming Directory External naming It is said that if Easy connect is used by the ...
4
votes
1answer
600 views

Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch?

I'm installing my first RAC and I was wondering if I should install CRS 10.2.0.1 (we are still at 10g for compatibility reasons) then patch to 10.2.0.5 before I install ASM (then patch) and finally ...
1
vote
5answers
1k views

Frequent or Infrequent Commit

I'm trying to run an update query but still taking to long I have a commit in it. Is it better to set the commit to a higher value (5000) or lower (1)? I have to update 15 million records in a table ...
1
vote
1answer
357 views

Oracle, Stored and External procedures

Could someone give me a brief explication about these two mechanisms in oracle : stored and external procedures. Are they distinct ? Are they differ when we integrate in some language (Java, C++ or C ...
5
votes
2answers
251 views

Implementing uniqueness with Oracle

For example, say I have something like CREATE TABLE COMPANY(id int not null primary key, ...); CREATE TABLE DEPARTMENT(id int not null primary key, company_id int not null, CONSTRAINT ...