The second and terminal release of Oracle Corporations Oracle 11g database product. 11.2 was released for Linux on September 1st, 2009. New features added include RAC One Node, Improved data compression ratios (up to 20x), Edition Based Redefinition, Recursive Subquery Factoring, Instance Caging, ...

learn more… | top users | synonyms

4
votes
2answers
637 views

Migrating to Oracle Flashback for Historization on Oracle 11g

Our application built using Oracle 11g requires that we keep historic data. The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically ...
8
votes
3answers
3k views

I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created?

I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the ...
0
votes
1answer
2k views

Upgrading 10g database to 11g database

Is doing a full data export (expdp) from an Oracle 10g (10.2.0.1.0) database and then doing an import of that dump file (impdp) into an 11.2.0.2 database a valid and acceptable upgrade path from 10g ...
1
vote
1answer
885 views

Oracle Client vs Oracle Instant Client on Linux!

to have the following setup on a Linux box: SQLPlus client library (oci.dll)!!!! ORACLE_HOME is set for users is Oracle Instant Client enough or I need to have the Oracle Client installed on the ...
1
vote
1answer
14k views

Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future?

I wanted to create a job that will run tomorrow at noon and recur every day at noon, so I executed this script: DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what ...
3
votes
1answer
717 views

In a two-server dataguard setup, can I store the recovery catalog in the same database?

We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, ...
9
votes
2answers
2k views

Oracle Shutdown Method

Shutting down a database before doing an upgrade or a patch can be done several ways. shutdown immediate; or shutdown abort; startup restrict; shutdown immediate; or shutdown abort; startup ...
3
votes
2answers
2k views

Oracle 11gR2 expdp table converted to CSV from Linux

With Oracle expdp, I can export the tables I want with expdp INCLUDE=TABLE:table1,table2,table3 I need a way to convert this into CSV on a linux machine. I've seen Windows tools but don't know ...
2
votes
1answer
3k views

How to create oracle linked server in oracle server itself?

Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit). I have access to ...
0
votes
2answers
2k views

How do I manage column order when using PL/SQL's %ROWTYPE records?

I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like: DECLARE TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE; l_data ...
2
votes
1answer
371 views

Error creating linked database to oracle

CREATE DATABASE LINK myLINK CONNECT TO username IDENTIFIED BY password USING 'PROD'; OPT= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.6.1.5)(PORT = 1521)) ) ...
13
votes
4answers
3k views

What is the significance of a semicolon at the end of SQL*Plus commands?

Some statements like create table, insert into etc take a semicolon at the end: CREATE TABLE employees_demo ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name ...
0
votes
1answer
263 views

How do oracle date time work?

I have following where condition in query. What the time is between T1 and T2 means it includes or excludes the boundary. Also between T1 and T1 means what?I am confused as both returns result. ...
3
votes
1answer
822 views

Oracle explain for cascading delete is wrong

My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it. If I hit the explain button on a statement like this ...
3
votes
2answers
2k views

Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance

I am somewhat confused by these terms in the init.ora file: db_domain db_name instance_name plus the database name which is provided to the create database command. I do not completely understand ...
4
votes
2answers
444 views

Oracle build_plan_xml visualizer

Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
2
votes
1answer
1k views

SQL on Mac OS X

I'm looking for something like "Oracle database express" to be used on a mac. I hope this question won't offend anyone though. I'm absolute beginner on sql. Your suggestions are most welcome.
5
votes
3answers
803 views

Gathering information to tune oracle row prefetching

I'm trying to determine a value for the number of rows an oracle client will prefetch (i.e. OCI_ATTR_PREFETCH_ROWS, exposed as setFetchSize in JDBC) that will minimize the number of server round trips ...
5
votes
2answers
165 views

Product of n Rows

From this data (assuming the number of rows is not known in advance): with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4) select * from q1; /* C1 -- 2 1 4 1 */ I ...
6
votes
4answers
841 views

Does an IOT guarantee order in a select?

We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1). The priority does not matter ...
2
votes
1answer
495 views

Oracle: UTL_RAW.BIT_OR(2,1) gives me 2 instead of 3. Why?

Why am I getting 2 instead of 3 when I perform BIT_OR(2,1)? SELECT UTL_RAW.CAST_TO_NUMBER ( --> decimal: 3? nope, 2 UTL_RAW.BIT_OR( --> binary: 11? ...
4
votes
3answers
4k views

Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns

Here's a table create table tq84_virtual_test_without ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 number ); with the rule that col_5's value is the sum of the other ...
2
votes
1answer
388 views

Oracle ash report

Recently, i have been handed over with an ash report from DBA. To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
2
votes
2answers
204 views

Overhead of DBMS_Profiler

If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is ...
2
votes
1answer
358 views

Oracle Stopping - Can't Login All Of A Sudden -

I'm having a problem with Oracle, it happens randomly around once in three weeks or once a month. The instance that i'm using suddenly hangs, and no logins can be established to it.It won't accept ...
3
votes
1answer
5k 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). ...
1
vote
1answer
240 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 ...
2
votes
0answers
461 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 ...
2
votes
2answers
2k views

Materialized views use older dbms_job instead of scheduler?

Very new to this topic - using 11g. When I create a materialized view, a matching job (not a scheduler job) is created. But it's my understanding that dbms_job is being replaced by dbms_scheduler. ...
2
votes
2answers
790 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 ...
2
votes
1answer
302 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 ...
5
votes
1answer
858 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 ...
3
votes
1answer
7k 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
526 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? ...
0
votes
1answer
200 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 ...
1
vote
2answers
2k views

how to verify that next sequence value can be used as a new primary key

I have many tables with a trigger attached like this BEFORE INSERT ON BRULES ...snip... if inserting and :new.ID is NULL then SELECT BRULES_SEQ.nextval into :new.ID FROM DUAL; end if; Due to ...
2
votes
1answer
927 views

Repartition Primary Key Index online in Oracle

I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the ...
2
votes
1answer
3k views

Oracle DB hanging after upgrade to 11.2.0.3

The database is hanging randomly (with some leniency I would say almost each hour). Top session is: select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0 or select ...
1
vote
1answer
531 views

Oracle 11gR2 - Limits on Materialised View replication between databases

We are about to deploy a 3rd party product running on Oracle 11gR2. The product allows very generic text searches. We'd like to mitigate the performance impact of the searches. Our current design is ...
3
votes
1answer
2k views

Installing 10g client next to 11g database on same computer

I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit I want to import data from warehouse which has Oracle 10.2.0.4.0 installed. When I try to import impdp usr/pass@WAREHOUSE TABLES=TABLE ...
2
votes
1answer
421 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
2
votes
1answer
1k views

Differing results from gv$session

The following query returns no results, yet it seems like it should. SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID'); In my 11.2.0.2 Standard Edition RAC environment the above ...
5
votes
1answer
2k views

Oracle 10g express database link to Oracle 8i

I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
2
votes
1answer
438 views

Decision tables in the database: a valid solution?

I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases. I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish ...
1
vote
0answers
414 views

how to move a oracle datafile to another location [closed]

It was different if database is archivemode or not, There are two ways are posted below http://ozcan-yildirim.blogspot.com/2011/11/oracle-veritabaninda-datafile-nasil.html#more or ...
6
votes
4answers
885 views

How do I sync instance A to instance B without losing any of the data already on instance B?

Like I said in a comment below, I'm no DBA and I'm still in a learning process. Here's my scenario : I have an Oracle server running a couple schemas (let' call it Source). What I would like to do ...
3
votes
1answer
6k views

SQL*Loader-605: Non-data dependent ORACLE error occurred

As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error. Data is (are?) in .txt file and its size is 95GB. Here are some more details ...
2
votes
1answer
700 views

How to configure so the Oracle wallet password can be authenticated with a web based Oracle application?

Does anyone know how to configure it so that the Oracle wallet password can be authenticated with a web based Oracle application? I’m trying to make secure LDAP connections with an Oracle web based ...
1
vote
2answers
7k views

How to connect to a local server in Oracle SQL Developer like SQL*Plus with no TNSlistener?

How can I use SQL Developer to connect to a local Oracle 11g server? I didn't enable TNS Listener, but SQL*Plus works well. @update1: I copied all *.jars from ...
1
vote
1answer
178 views

Oracle bitand inconsistent behaviour

I'm working on a legacy database using bitmaps for searching. I'm using the bitand function to find bitmaps that match a given bitmask but I'm getting the wrong results: the bitand between two numbers ...