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

4
votes
1answer
34k 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
7k 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
369 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
4k 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
599 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
352 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
248 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 ...
2
votes
1answer
178 views

Suggestions for SQL query repair tool or tips on building one?

Not sure if this is ontopic so I figure I would ask here and if its not liked then we can close it. Our hosting provider for a legacy application that I inherited is no longer supporting Oracle 10 ...
3
votes
2answers
6k views

Move an Oracle database by moving the raw data files

I have a large Oracle database (~300 GB) and I need to move a copy of the database to a new environment in a short period of time. I was wondering if there is a way to just pick up the raw files ...
0
votes
0answers
554 views

Oracle Flashback Archive aka Total Recall in MySQL [closed]

What would you suggest as a replacement system to achieve the same functionality of Oracle Flashback Archive aka Total Recall in other DBMS, in general or specifically in MySQL or MariaDB? ...
2
votes
1answer
905 views

Oracle Enterprise Manager Jobs Failing Initialization

The Enterprise Manager jobs on our RAC system recently started intermittently failing initialization with the following error: Error evaluating parameter sources/security checks/locks ORA-06508: ...
0
votes
1answer
1k views

Error during the installation of oracle 11gxe

When I tried to install Oracle 11g XE into my machine (Windows XP media center edition, version 2002 SP3) I'm getting an error as 0x80040702 Description: Failed to load DLL:chkportDll2 I've ...
2
votes
2answers
3k views

Is there any doc listing standard <=> Oracle character sets correspondencies?

I'm looking for a table listing Oracle and "standard" character sets, for example: Oracle Standard ------------ ---------- WEISO98859P1 iso-8859-1 AL32UTF8 utf-8 etc. etc. Does ...
5
votes
1answer
1k views

Are there performance benefits to turning off archivelogging? Is it wise?

The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could ...
4
votes
1answer
12k views

Connection to an oracle db from another computer

I have installed Oracle 11g to an ubuntu, I can connect to DB using sqlplus from the localhost, But I cannot connect from another computer, I have configured my tnsnames.ora as followed; ELIF = ...
3
votes
1answer
4k views

when to use embedded PL/SQL gateway with Oracle APEX?

Oracle Application Express (APEX) requires one of the following three options for an HTTP server: (1) Oracle HTTP Server (with mod_plsql), (2) Oracle Application Express Listener, or (3) an embedded ...
8
votes
5answers
23k views

PL/SQL: best way to count elements in an array?

Given this: DECLARE TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; MY_ARRAY T_ARRAY; V_COUNT INTEGER; I would like to do: BEGIN -- ... some code filling the MY_ARRAY ...
0
votes
2answers
3k views

Oracle 11g setting for backwards compatibility? [closed]

Edit: Please close this topic. Unfortunately, this issue can't be resolved as it turned out the question is very off-base to begin with. The actual issue was completely unrelated to the question I ...
10
votes
3answers
29k views

How do I find my current SCN?

Given any version of Oracle: How do I find my current SCN? What is the maximum possible SCN?
1
vote
1answer
1k views

How do you monitor specific SQL statement of a specific user ?

We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's. I spoke with them a few times but it seems that their queries are causing high loads and probably ...
1
vote
1answer
2k views

At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run. I guess that it ...
3
votes
2answers
1k views

Efficiently finding distinct values

I have a number of tables with the primary key (month, year, number) and differing cardinalities differ somewhat. For the tuple (month, year) the history doesn't go back very far, this will probably ...
2
votes
1answer
1k views

Oracle 11g production database copy to USB drive

What is the correct commands for backing up a production Oracle 11g database (while still running) to an external USB drive or different directory. I am new to Oracle and but have experience with SQL ...
2
votes
1answer
2k views

PL/SQL : query numeric data type by scale?

Wish to select rows with number fields having a certain amount of data after the decimal eg select number from table where number.scale > 4; Is this possible to do directly, or must I cast to ...
8
votes
2answers
8k views

How to retrieve foreign key constraints data

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema. I've found this, but does not gives all ...
1
vote
2answers
2k views

Dropped table not showing up in recycle bin

I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it . What's the ...
3
votes
1answer
9k views

Oracle 11g hunting down the deadlock, maybe foreign key?

I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
3
votes
3answers
11k views

Oracle 11g SYSDBA login

I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use SQLPLUS "/ AS SYSDBA". But I want to ...
0
votes
1answer
221 views

ORA-04020 on Oracle SE1 (11.2.0.1)

We've been experiencing some deadlock issues on our Oracle SE1 (11.2.0.1) database server (Red Hat 5/64bit) Seeing ORA-04020 errors in the logs. Is going to 11.2.0.2 the only way to resolve the ...
3
votes
3answers
614 views

What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One?

We are currently using Oracle Standard Edition One running on two physical database server. If I wanted to get higher availability, what's the most cost effective way or cheapest way to achieve this? ...
3
votes
2answers
2k views

Copying Oracle user without dump file

I'm looking for a way to copy an Oracle user and its data to a new one without dump file. Google search brings a tip by Steve Karam, which seems to be exactly what I want. However the page is not ...
1
vote
0answers
63 views

What patch(es)/feature(s) to Oracle provide the column avg_hard_parse_time on v$sqlstats? [closed]

I have some profiling scripts which work in some environments, but not others; the odd thing is that it doesn't seem terribly consistent across versions, so I'm guessing there's some patch or feature ...
3
votes
2answers
7k views

How to free space after deleting the millions of rows from a table

I performed a delete operation on my table . It deleted around 10 millions of rows from it How do I free the space associated with the deleted data. I used the query: alter table tablename ...
3
votes
1answer
4k views

Oracle: Read from CLOB column line by line and insert to the table

I have CLOB column, and wanna read its data line by line to insert it in another table. Now let's create the same situation that I have: Data in a Table MyTable_1 OWNER | TABLE_NAME | SQL ...
4
votes
1answer
102 views

Relationship between number of transactions and redo generation

If I execute some SQL statements over many transactions instead of just committing once after all statements, will this have an effect on the amount of redo log generated? And hence on the size of the ...
7
votes
3answers
2k views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
1
vote
1answer
131 views

Can a Database request automatically remove itself from the queue after a delay?

I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made ...
3
votes
1answer
2k views

Oracle11g password expiry issue

I have a user which has a default profile in oracle db. I want to reproduce the following warning ERROR: ORA-28002: the password will expire within X days In dba_profile I set following values. ...
-3
votes
1answer
297 views

Steps to create instance, parameter file , password file and database [closed]

_Hi everybody _i wanna all steps to create instance, parameter file , password file and database _manually in oracle 10g _i hope from all to help me
0
votes
1answer
155 views

oracle local partition index

I have a table like this: CREATE TABLE employees (employee_id NUMBER(4) NOT NULL, last_name VARCHAR2(10), department_id NUMBER(2)) PARTITION BY RANGE (department_id) (PARTITION employees_part1 ...
6
votes
3answers
3k views

Why performance degradation with query hint FIRST_ROWS(1000)? [closed]

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input. In case the user supplies very unselective arguments, the query takes a lot of ...
1
vote
2answers
2k views

oracle global index vs local index on a partitioned table

I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this ...
5
votes
1answer
1k views

How will Oracle fill a datafile added to a tablespace?

I have a tablespace with one datafile which is 50% used. If I add a new datafile to the tablespace - will the first datafile continue to be used until it reaches full capacity and only then will ...
7
votes
2answers
60 views

Oracle 10g: Is it possible to designate a undo point?

I was wondering if it is possible to designate a point in time, then later "roll-it-back" to that state (undo DDL and DML changes). Maybe using a record of changes and then executing the reverse? Does ...