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

0
votes
1answer
1k views

Force number precision in a view with Union

I am using Entity Framework and Oracle. Oracle's ODAC seems to have a known bug where data of type NUMBER is cast to Int64 by Entity Framework. I thought a view would assist me but CREATE OR REPLACE ...
2
votes
1answer
277 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
5k 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 ...
1
vote
2answers
8k 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
2answers
1k 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 ...
3
votes
1answer
496 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 ...
2
votes
1answer
995 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 ...
2
votes
1answer
1k views

Recover database until cancel without prompt?

The statement: RECOVER DATABASE UNTIL CANCEL prompts once to select the archive log file. Is there a way to pre specify the value so as to be able to use it in a script without prompt? I have ...
1
vote
0answers
5k 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 ...
1
vote
0answers
2k views

ORA-01460 during Data Pump Import

While migrating data from Oracle 11.2.0.1 RAC to Oracle 11.2.0.3 RAC using Data Pump, the import failed with ORA-01460: ORA-39097: Data Pump job encountered unexpected error -12801 ORA-39065: ...
3
votes
2answers
228 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
1k 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)
1
vote
2answers
5k views

Run Unix command using PL/SQL

Is it possible to run a Unix command using a query in Oracle? I want to run simple commands (like df -h) using a query. Is this at all possible or am I wasting my time? I don't want to use a ...
2
votes
2answers
897 views

Why does my query run faster after I force full table scan?

I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this. SELECT ...
1
vote
2answers
1k views

Why Oracle 11g installs in folder C:\app\<name_of_user>

Just curious, now Oracle 11g installs by default in folder C:\app\ Why? Before, Oracle was installed by default in folder C:\oracle... Why now is created "app" folder?
1
vote
1answer
200 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 ...
0
votes
2answers
615 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
10k 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 ...
2
votes
2answers
22k 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 ...
2
votes
1answer
469 views

Upgrading from 10.2g to 11g by incrementals

I know it's possible to generate a full rman backup in a 10.2g database, move it to another server containing 11g, restore it there and finally do an upgrade. Since I have to do this to a large ...
1
vote
1answer
828 views

Can't create archived redo logs

Oracle 11.2.0.3 in Windows Server 2008 64 bits. I try to set up archive mode in a database: C:\Users\Admin>SET ORACLE_SID=BDSPROD C:\Users\Admin>SQLPLUS /nolog SQL*Plus: Release 11.2.0.3.0 ...
0
votes
1answer
2k views

Error when installing Oracle 11.2.0.3 in Windows Server 2008 R2 64 bits

I downloaded Oracle 11.2.0.3 installer: p10404530_112030_MSWIN-x86-64_1of7.zip p10404530_112030_MSWIN-x86-64_2of7.zip p10404530_112030_MSWIN-x86-64_3of7.zip p10404530_112030_MSWIN-x86-64_4of7.zip ...
1
vote
1answer
451 views

Oracle Enterprise Manager font problem, some text is displayed in Chinese and with squares

In Oracle Enterprise Manager 11g database control in the following section: Setup->Monitoring Templates->"Choose template name and click Edit button"->Policies tab Texts are displayed in ...
0
votes
2answers
13k views

Simple query with date type requires explicit conversion to_date of date

My question is, I have a table which contains a column called myDateColumn (for example) which is of the type date: I can confirm desc myTable contains this line: myDateColumn NOT NULL DATE ...
2
votes
2answers
599 views

Oracle symmetrical one-way replication for cheap HA: unfashionable or technically unfeasible?

We have two smallish unrelated databases on two big Oracle Enterprise servers. Instead of buying more licenses and servers for high-availability replicas, can we replicate database B to server A and ...
3
votes
1answer
2k views

Network design considerations for Oracle Database Appliance

With the introduction of Oracle Engineered Systems the DBA is moved somewhat closer to infrastructure design decisions, and expected to at least have some opinions of the network design requirements ...
3
votes
1answer
5k views

Add an additional listener to Oracle Database Appliance

After deployment of Oracle Database Appliance with OAK 2.3.0 the standard listeners are configured for the public network on bond0. That includes SCAN listeners and one VIP listener per node. This ...
2
votes
0answers
739 views

Where can I find a syntax reference for Oracle SQL Developer's “Generate DB Doc” function?

Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
5
votes
1answer
21k views

Importing schema to new or different tablespace

Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated? As an example, I have exported BLOG_DATA from OLDDB, where ...
1
vote
0answers
528 views

Restoring Oracle prod backup to test server with new database name

I need to restore an Oracle production database (aaprod) to a test server that already is home to a test database (aatest) under a new name (bbtest). I have a cold backup of the production database ...
4
votes
2answers
748 views

Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance

This is as a result of this question. So I'm working with a query that looks like this: select count(*) from table1 where col1 = 123 and col2 = '1'; The problem is this: col2 is defined as a ...
12
votes
1answer
2k views

Commit vs Fast Commit vs Commit Cleanout in Oracle Database

I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases. Many sources confuse these terms and do not explain ...
7
votes
1answer
158 views

Cardinality is so far off from reality

I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2. select count(*) from table1 where col1 = ...
3
votes
3answers
403 views

Why does the query cost change so much and how to prevent it

The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that: The query has an structure like: SELECT ... a lot of stuff ... ...
3
votes
2answers
573 views

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

I have something that looks like this (SQL Fiddle here): Date Location Value 1/1/2012 Baltimore 36 1/1/2012 Houston 36 1/1/2012 Chicago 55 2/1/2012 Baltimore 49 ...
1
vote
1answer
560 views

What are the things need to be considered while creating tablespace?

I am creating temporary/permanent tablespace on Oracle. As I am going to create an application that is transactional there will be traffic. I bumped into concept called tablespace in oracle. What kind ...
5
votes
1answer
1k views

Trying to copy a schema using impdp

I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool. I've setup a database link back to itself and the link works fine. Then I'm running this: impdp username/password@instancename ...
1
vote
0answers
135 views

Use of hash functions in group by?

I'd use a listagg() to generate a list of components a certain customer has and compare it to another list generated the same way somewhere else to detect inconsistencies. The problem is that ...
1
vote
1answer
7k views

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

What is the syntax for a stored procedure that outputs a refcursor, a varchar2, and a number? I would like to output all three in the result, for the purpose of displaying count, and a message which ...
1
vote
1answer
220 views

INDEX Creation Advice Oracle

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id. The attributes date, and entity_id would make a unique pair. ...
-3
votes
1answer
376 views

Import an Oracle dump file using a second server

I want to import a dump file into an Oracle server, SRV1. Due to space constraints on SRV1, I cannot copy the dump to the server and then import it. I copied the dump to another Oracle server, SRV2 ...
0
votes
1answer
8k views

Moving a 10g database to 11g with rman backup

Following Doc ID 369644.1, I'm trying to restore a 10g database into a 11g database ( version 11.2.0.1.0), which is in other server. What I did on 11g server: - Generate full rman backup of 10g ...
2
votes
1answer
1k views

Oracle 10gR2 to 11gR2 upgrade - performance hit [closed]

We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and ...
4
votes
1answer
6k views

How can I view what NLS settings are active for other session?

I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the ...
5
votes
2answers
2k views

How to install Oracle 10g Express and 11g Enterprise on same machine

I have to continue maintaining an Oracle 8i database that receives information (about 20 Inserts and Updates a day) from a 9i database. I am upgrading the 9i database to 11g which does not support ...
6
votes
4answers
656 views

Can this business logic be enforced by a conditional database constraint?

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
8
votes
3answers
2k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
2
votes
1answer
3k views

Why Is oracle ignoring my index?

This query is not too complex: select trunc(created_date, 'MONTH') as created_date, al.op_name, al.region, al.alarm_type, COUNT(1) as total_new, SUM( (SELECT ...
4
votes
1answer
508 views

How to I interleave logs from two different Oracle tables?

I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's ...
0
votes
0answers
189 views

How to load XML data into an oracle db table ?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ? ...