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, ...
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
543 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 ...
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
1answer
686 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.
2
votes
2answers
122 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
1k 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 ...
2
votes
1answer
378 views
Oracle Active Session Pool Questions
what is oracle active session pool? is it activated by default?
what is the default maximum active session that there is per user?
2
votes
1answer
228 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 ...
2
votes
2answers
693 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
634 views
Piplelined Result Set over Database Link Alternatives
Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there. I can think of a few, but I'd rather not lead the question. I ...
2
votes
1answer
417 views
Recover Oracle database after drive loss Interview Question
How would you answer this as an interview question?
The database crashed and you lost a
drive containing one of your control
files and another drive containing an
entire redo log group. You ...
2
votes
1answer
282 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
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 ...
2
votes
1answer
619 views
Can RMAN duplicate be used to “replace” an existing database?
I have an unused database B, not valuable. I'd like to make a copy of database A, but do not want to go through all the manual steps RMAN requires to set up the auxiliary database. Can I use RMAN ...
2
votes
1answer
155 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
2k 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 ...
2
votes
1answer
1k 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 ...
2
votes
1answer
142 views
May I Convert Non RAC db to an RAC DB and add on a Node?
Currently my work env as 1 node none RAC database 11gR2, we are getting another machine (Linux), so I need to add on this node to our currently env and becoming a RAC db. What is the best way/route to ...
2
votes
1answer
2k views
Oracle Wallet Setup ORA-12578
I am having trouble setting up the Oracle Wallet on a new box connecting to a new database. I have added the following lines to my SQLNET.ORA:
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION = ...
2
votes
1answer
923 views
Best methods for ingesting XML into Oracle 11gR2
I have a large Oracle 11g Instance. I now have a requirement to convert large amounts of XML into records in several related tables.
The xml will arrive frequently at irregular intervals. The ...
2
votes
1answer
393 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 ...
2
votes
1answer
51 views
What's the most appropriate NLS_SORT parameter to cover all/most European languages?
I'm developing an Oracle 11g-based application which is likely to be used by clients in many different countries (mostly European). I want the program to change its NLS_SORT so that it's case ...
2
votes
1answer
85 views
Oracle: How to gather stats in a logical standby database?
I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
2
votes
0answers
111 views
Create Oracle ASM Disks Without Installing Oracle
I have been asked to setup workstations (VMs) for an instructor's Oracle 11g R2 Workshop. I am finding the documentation that was downloaded from the Oracle Academy site extremely underwhelming. I am ...
2
votes
0answers
268 views
Why ORA-00932: Inconsistent Types error?
Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2.
The following query raise ORA-00932 error:
SELECT BBB.FIELD1 FROM TABLE0 AAA
JOIN (SELECT * FROM ...
2
votes
0answers
400 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 ...
2
votes
1answer
139 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 ...
2
votes
0answers
267 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, ...
2
votes
1answer
760 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 ...
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 ...
2
votes
0answers
126 views
How to Connect R to Oracle?
I need to connect R to oracle and I have been unsuccessful so far. I downloaded two packages: RODBC & RODM.
This is the statement that I've been using: DB <- ...
2
votes
0answers
613 views
ORA-01017: invalid username/password; logon denied
I've come across the abnormal situation, and can't understand what happens.
I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to ...
2
votes
1answer
227 views
Table Statistics Affect Index Only Query?
We have a query that was getting poor performance. The root of the problem could be reproduced using a simple query accessing only one index to retrieve one column (the indexed column) from eight ...
2
votes
0answers
248 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 ...
1
vote
2answers
945 views
What does 'nls' in nls_date_format stand for?
For example:
alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss';
changes the date format for the session.
What is nls here?
1
vote
2answers
187 views
Factoring out subselect (with clause) ends in ORA-00904
I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again.
This is my base query which works quite well:
select tka.id, tka.name, txt.text as langname, ...
1
vote
2answers
845 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 ...
1
vote
1answer
155 views
Why am I not seeing any benefits of parallel execution when parallel clause is used?
I have these two methods of creating a table and populating with rows. The first one uses a
parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) ...
1
vote
1answer
166 views
Should a table be partitioned before or after an INSERT?
I have a fairly simple question that is proving hard to search for.
I need to create a table that will be partitioned and into which (lots of) data will be loaded.
Is it more efficient to create the ...
1
vote
2answers
266 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
6k views
How do you get the explain plan?
Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.
1
vote
2answers
516 views
Sum values from two tables ORACLE
I want to use two values, in two different tables, to be used as a total in a third table.
For instance:
table accommodation and flight
Add value from acc_price with value from flight_price
Add ...
1
vote
2answers
849 views
“ORA-01950: no privileges on tablespace 'SYSTEM' ” : What error is it?
Earlier I created a user :
SQL>create user suhail identified by password
SQL>User created.
SQL>Grant create session,create table to suhail;
SQL>Grant Succeeded.
but when I login as ...
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.
...
1
vote
1answer
761 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 ...
1
vote
1answer
263 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
372 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 ...
1
vote
1answer
55 views
Delete shared memory segments left over by an Oracle instance
We're using Oracle Enterprise 11rR2 running on Solaris.
How can I delete/remove allocated shared memory segments using ipcrm?
I'm getting this error:
ORA-01041: internal error. hostdef extension ...
1
vote
1answer
88 views
need to use PLSQL to insert distinct rows
I need to take some data from Table A, use some logic, and then insert one or more rows into Table B. I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic ...
1
vote
1answer
65 views
Strange behaviour of a query with order by and rownum on Oracle's Exadata server
I am executing the following query on two different database servers:
SELECT start_date,
end_date,
resort_id
FROM employee_activity
WHERE employee_id = 27
AND start_date < ...
