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

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 ...
3
votes
1answer
3k 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 ...
3
votes
1answer
37 views

loss of all redologs scenario

I'm having this question regarding a restore scenario in RMAN: A database is running in Archivelog mode. Full RMAN hot backups are taken, redologs are backed up with RMAN to tape. The application ...
3
votes
1answer
166 views

Oracle Data Pump Import with running advanced replication

In our system we have a running advanced replication between two databases(11g release 2) and want to use pump import to import a backup on one site. However the imported data is not replicated to the ...
3
votes
1answer
2k views

Why is add timestamp column with default not null so slow in Oracle 11g?

In Oracle I need to add a new column with type timestamp with time zone to a large table (80 million rows). I cannot have more than 30 - 60 seconds of downtime when I run it in production. This ...
3
votes
1answer
118 views

connection issue / Oracle Express 11g in windows7 32bit

I have an issue with Oracle Express 11g in windows7 32bit. Everytime when I try to conncet to the database I get this error message. How can I fix it please!
3
votes
1answer
348 views

Oracle Datapump: modify fully qualified objects when cloning a schema

I am confronted with a problem for which I have not found an elegant solution: I need to clone a schema within the same database, so I use datapump's remap_schema import parameter. The problem is ...
3
votes
1answer
59 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 ...
3
votes
1answer
2k views

Unable to create a tablespace, unusual error

I'm having difficulty creating tablespaces with Oracle 11gR2 on a fiber attached disk array. Permissions are correct and there is ample space available. I can create tablespaces on other mounts that ...
3
votes
1answer
523 views

Is there a way to allow a proxy connection to use passed authentication in a public database link?

I think the answer to this is no, but I could be wrong. If nothing else it might be nice for others to be aware of this limitation of proxy connections.
3
votes
2answers
791 views

FileWatcher Troubleshooting Options

I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't ...
3
votes
1answer
649 views

How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?

I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job. I have a remote Oracle 11g R2 database on unix and a local one on Windows. I read that you can install the oracle ...
3
votes
0answers
2k 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 ...
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 ...
3
votes
0answers
834 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
0answers
2k 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 ...
3
votes
0answers
491 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 ...
3
votes
2answers
2k views

Oracle imp crashes without error

I have Oracle 10g XE database locally. I export schema with something like exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp Exp is 10.2.0.1.0 version - so is the DB. Than I take ...
3
votes
3answers
1k views

Increase Oracle buffer cache gets slower

So we're contemplating increasing our Oracle 11gR2 buffer cache. They said they tried it in the past and things appeared to get worse - so they undid the change. They, however, have no idea why ...
2
votes
2answers
3k 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?
2
votes
2answers
6k views

Why can't we specify group functions in Oracle in the where clause?

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
2
votes
3answers
359 views

How does a database use the SYS schema even when we change the password?

From the thread What is the difference between sys and system accounts in Oracle databases?, I understand that sys schema is supposed to be used only by the database. But still an administrator can ...
2
votes
3answers
3k views

How to automate Oracle DB installation (like a 'hands-free' install)?

I installed the Oracle 11g2 'standard' edition using the following zip-files: linux.x64_11gR2_database_1of2.zip linux.x64_11gR2_database_2of2.zip I used the installation guide by Oracle which I ...
2
votes
1answer
858 views

Flashback feature in Oracle 11g?

I am using Oracle 11gR1. As a part of a project, I have to execute multiple update statements by disabling a few constraints (triggers and check constraints) which could result in a corrupted ...
2
votes
1answer
6k 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
2answers
4k views

In Oracle, how to globally enable parallel execution?

In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?
2
votes
3answers
490 views

Storing file into oracle db

I'm developing a java web-app for the acquisition of contracts, and that have to receive some file from an external system. For each new contract i recieve some information about the new customer and ...
2
votes
1answer
121 views

Oracle Table Design, Storing Small Values

In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm ...
2
votes
1answer
295 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 ...
2
votes
1answer
127 views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...
2
votes
2answers
277 views

Does RMAN in Oracle only copy the archive logs? What happens to the active redo log in case of failure?

If RMAN copy copies the archived redo logs when they are full, what happens to the current redo log in the event of a failure (hardware failure, etc.)? Isn't there always an active redo log that is ...
2
votes
2answers
9k views

Dynamic Oracle Pivot_In_Clause

I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this: WITH PIVOT_DATA AS ( SELECT * FROM ( SELECT USERNAME, GRANTED_ROLE ...
2
votes
2answers
6k views

Granting privileges to dbms_crypto

I'm trying to grant proper privileges for using DBMS_CRYPTO. Whenever I call it from package it gives me error: PLS-00201: identifier 'DBMS_CRYPTO' must be declared User already has privileges: ...
2
votes
2answers
20k 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
4answers
535 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 ...
2
votes
2answers
12k views

SGA_MAX_SIZE and SGA_TARGET

We recently increased sga_max_size to 4g from 2g but didnt increase sga_target, is it ok not to increase sga_target?? reason we wanted to increase sga is to reduce wait time on sequential reads in our ...
2
votes
1answer
2k views

oracle blocking sessions

I am trying to investigate some concurrency issues on Oracle 11 RAC. So far the most helpful tool I have is "blocking sessions" view of the Enterprise Manager, which tells me which query is blocking ...
2
votes
1answer
3k views

can I replace nulls in a PIVOT with zeroes?

I am using the PIVOT function in Oracle and am curious if I can replace the null values with zeroes? I know I can wrap the entire query in another SELECT and then use COALESCE on the values, but I am ...
2
votes
2answers
1k 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 ...
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 ...
2
votes
1answer
10k views

How to display a number with fixed number of digits before the decimal?

I am looking for result 0123.56 and the following query should have worked. SQL> select to_number(123.56,'0999.99') from dual; select to_number(123.56,'0999.99') from dual * ERROR ...
2
votes
1answer
156 views

Does a DDL in Oracle affect other transactions?

I understand that DDL statements in Oracle have an implicit commit before and after the statement, which obviously affects the current transaction. However, does a DDL statement in any way affect ...
2
votes
2answers
593 views

Duplicating a Database with RMAN

I would like to duplicate to the same server Oracle Database with RMAN. I've configured flash recovery area for Oracle Database. My database is using SPFILE as shown below. SQL> SELECT ...
2
votes
1answer
3k views

how to create and insert into a table in an anonymous block

I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table. When the block compiles, it gives me an ORA-00942: table or view does not exist ...
2
votes
1answer
2k views

Can you have multiple Oracle ACL files?

I have an application which needs to send emails through Oracle triggers. I understand that in Oracle 11g you must have an ACL file to grant certain schemas access to the network. My plan was to ...
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 ...
2
votes
2answers
713 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 ...
2
votes
1answer
18k views

How do I grant execute permission to an Oracle database user?

I'm trying to call a Java function from Oracle database that does the following: Process p = Runtime.getRuntime().exec("..."); When I try to run it, I get the following error: ORA-29532: Java call ...
2
votes
1answer
521 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? ...
2
votes
1answer
418 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 ...