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, ...
-1
votes
1answer
80 views
How to get how many rows are inserted in ORACLE?
How do I get how many rows are inserted after executing an INSERT statement?
I'm using Oracle 11r2 with MyBatis in Java.
2
votes
0answers
398 views
Oracle high Paging Faults
What are the reasons that lead to terrible high Paging faults.
The following is the topas information and background about the Environment.
OS: AIX 6.1
Softwares: Oracle 11gR2
Paging Space:30720 ...
0
votes
2answers
207 views
How to merge Oracle data
There are two schemas in Oracle11g. For example db1 and db2.
They have same structure of tables and different data to each other.
Now I want to merge data on db1 with data on db2 with no ...
2
votes
1answer
2k views
Installing Oracle 11g XE on Ubuntu 12.04
I have spent the last 6 hours trying to install Oracle 11g on Ubuntu. Obviously, I'm not the only one who has struggled with this, as there is a lot of (often misleading) "documentation" online on how ...
2
votes
1answer
136 views
Database Link from Oracle 10g on Windows over to MySQL database
Didn't see any answers on this subject so hoping for a miracle! :-)
I've got an Oracle 10g database running on Windows Server 2003 and I'd like to create a database link to an external MySQL database ...
0
votes
1answer
192 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
95 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
831 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
823 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
445 views
RMAN Recovery In Oracle 11g : Backup Database To Tape
RMAN> backup device type sbt tablespace <tablespace_name>;
On Issuing this command following is the error which I am getting:
ORA-19554: error allocating device, device type: SBT_TAPE, ...
3
votes
1answer
139 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
279 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
483 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
941 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
523 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
166 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
1answer
145 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)
0
votes
2answers
466 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 ...
1
vote
2answers
262 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
132 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?
0
votes
1answer
107 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
232 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
1k 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
1k 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
120 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
266 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
518 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
122 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
810 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
...
1
vote
2answers
185 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
293 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
775 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
266 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, ...
3
votes
1answer
3k 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
223 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
238 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 ...
9
votes
1answer
382 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
122 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
264 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
196 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
...
0
votes
1answer
156 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 ...
0
votes
0answers
239 views
c#.net Datable to oracle table update
I would like use a datatable of undetermined number of rows from a c#.net app to replace all rows in an existing oracle table. Basically the functionality I am going for is an edit, save function for ...
5
votes
1answer
467 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 ...
1
vote
0answers
89 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
1k 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
118 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
117 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
2k 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 ...
0
votes
0answers
21 views
Drop user is not reclaiming the space [duplicate]
Possible Duplicate:
Drop user did not reduce size of tablespace file
On my machine i have recently imported a dump which is consuming around 22GB Data. And i ran the command
Drop user ...
2
votes
1answer
753 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 ...
