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
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 ...
1
vote
1answer
134 views
Can I recover an overwritten incident trace file?
I was looking at incident trace file in oracle database but some how that file got overwritten. As of now I have ecrep_ora_26467_i49966.trm but trhe corresponding .trc file is lost. is it possible to ...
2
votes
1answer
121 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
2answers
186 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 ...
0
votes
1answer
522 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
...
3
votes
1answer
782 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 ...
0
votes
2answers
822 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
...
3
votes
1answer
294 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
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 ...
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, ...
1
vote
1answer
95 views
Does Oracle server installation include client components?
I installed Oracle personal edition on a Windows Server 2008 virtual machine for testing purposes. My intention is to test Linked server functionality with SQL Server. Does Oracle Server installation ...
3
votes
2answers
197 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
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
...
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 ...
...
9
votes
1answer
384 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 ...
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 ...
7
votes
1answer
123 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 = ...
5
votes
1answer
468 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 ...
0
votes
0answers
240 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 ...
3
votes
1answer
90 views
Using Inteval Datatype to store result of calculation
I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.
3
votes
2answers
315 views
Help to understand explain plan in Oracle
I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
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 ...
-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 ...
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 ...
5
votes
1answer
135 views
how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server
How can I compare two Oracle database servers which are on two different machines?
We need to ensure that the new server is setup correctly.
For example, one machine is running Oracle 10g on ...
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.
...
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 ...
4
votes
2answers
582 views
Updating rows conditionally with a gapless ascending value
A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
3
votes
1answer
992 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 ...
6
votes
4answers
316 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 ...
1
vote
1answer
342 views
Reverse engineer Oracle database in visio
Trying to reverse engineer tables added into a database but when i connect, I get strange Chinese symbols with a lot of apex stuff.
I dont see anywhere the tables that i added, what am i doing ...
2
votes
1answer
530 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
...
2
votes
2answers
2k views
How to convert postgresql dump files to oracle dump files?
How can I convert postgresql dump files to oracle dump files? If anyone has experience in this, or knows of an open source tool I can use, please share it with me.
6
votes
3answers
274 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 ...
0
votes
2answers
986 views
Oracle database link to MySQL database on RedHat Linux
I have an Oracle 11g database on a RedHat server and also have MySQL database on another RedHat server. I want to create a link to the MySQL server from the Oracle which allows me to execute queries ...
4
votes
1answer
200 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
347 views
ora-07202 when creating standby database
I am pretty new to Oracle database, and I am trying to set up a high availability between two PCs.
The first PC runs Windows server 2008 R2, and the second is on Fedora.
As far as I have read, with ...
0
votes
0answers
104 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 ?
...
1
vote
2answers
6k 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 ...
2
votes
2answers
3k views
Comparing date and timestamp variables in a where clause in a procedure
I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
2
votes
1answer
117 views
Thoughts about Oracle backup procedure for 11.2 (beginner question)
Two days ago we "found out" that our old, not taken care of, horribly neglected, Oracle DB server was extremely important to our business.
The only person who knew anything about this server was ...
2
votes
0answers
188 views
Ports necessary for Oracle 11g Replication across a firewall?
We have two Oracle 11g Enterprise Edition servers that need to replicate data via Oracle streams. However, the two servers are on untrusted domains and connect via a VPN. On end of the connection is ...
3
votes
1answer
52 views
INDEX on a table where I'm only filtering on a row_number()
I've got the following table (scaled down to a MWE):
Fiddle here.
CREATE TABLE weekly
( yearnum int,
weeknum int,
location varchar2(5),
weekly_count int,
weekly_sum int);
INSERT INTO weekly ...
1
vote
1answer
757 views
How do I find out the “redo log file max size” in Oracle 11R2
I'm working with Oracle 11R2 and I want to see the max size of my redo log files (I didn't install Oracle so I'm not aware of the configuration).
I have searched the Internet but all I have found ...
3
votes
1answer
568 views
How far should bind variables go in Oracle?
I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time.
Now, I have a quite firm ...
4
votes
3answers
4k views
Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes
Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
2
votes
1answer
542 views
Drop user did not reduce size of tablespace file
I have a development environment in which I frequently copy a certain schema.
When I'm done with the copy, I will use OracleEM to drop that user/schema.
My problem is that this does not seem to ...
4
votes
1answer
211 views
Dodgy causes of ORA-12549?
I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error.
Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" - ...
2
votes
3answers
143 views
Oracle hint failure
When I run the PL/SQL block after the CREATE statement, I receive the duplicate key error I would expect if I did not add the hint. Any ideas to why the hint does not work?
CREATE TABLE Sandbox.Clue
...
