Oracle Database 11g Release 2. Please also tag oracle for search purposes.

learn more… | top users | synonyms

2
votes
2answers
5k 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
1answer
943 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
3answers
1k 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
151 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
371 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
240 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) ...
2
votes
3answers
3k views

How to handle big data update based on excel spreadsheet

Scenario Select-based data is exported from DB to Excel spreadsheets. Some manual work is done on the excel files (filling missing data). Changed data from excel files is updated back to DB. ...
2
votes
2answers
288 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
14k 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
1answer
2k 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 ...
2
votes
2answers
26k 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
704 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
14k 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
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 MIN(TRUNC(...
2
votes
1answer
4k 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
16k 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
1k views

understanding ORA-01792 maximum number of columns in a table or view is 1000

We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error: ORA-01792: maximum number of columns ...
2
votes
1answer
45k views

Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server

I installed 11g on Centos VM on my mac.I have everything up and working on server.I have included following command I ran on cenos. $lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production ...
2
votes
1answer
16k views

Connecting to SQL Server using Oracle SQL Developer

I am using oracle sql developer to manage my oracle database. I recently added third party database jdbc drivers to be able to connect to my new MySQL and SQL Server installations. I need help with ...
2
votes
1answer
269 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
669 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 DECODE(...
2
votes
1answer
4k 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
4k 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
2k 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
896 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
25k 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
8k views

How to convert postgresql dump files to oracle dump files? [closed]

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.
2
votes
1answer
595 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
503 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 ...
2
votes
2answers
1k views

How to compare performances of two databases

There are two database servers and I want to compare their performances. Both have Windows operating system and and Oracle (10.2.0.4 and 11.0.2) database. Both are OLAP databases with 15-20 users ...
2
votes
1answer
1k 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 ...
2
votes
1answer
3k views

How do I create a database+tablespace+user in Oracle 11gR2?

Good evening, I'm trying to figure out how to create a database+tablespace+user in Oracle 11g. I've tried some tutorials, but nothing seems to work. I cannot find Database Configuration Assistant ...
2
votes
1answer
164 views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1,...
2
votes
2answers
297 views

sqlplus (Oracle XE) doesn't start on CentOS 7

I followed the steps of this document: https://docs.oracle.com/cd/E17781_01/install.112/e18802/toc.htm#XEINL121, but sqlplus doesn't start. Even if I used the command: "sqlplus /nolog". The other ...
2
votes
1answer
1k views

du of directories in a diskgroup in Oracle ASM

In Linux, you could run the below on a directory to find the size of each sub-directory: du -sh * When you're in asmcmdin the +FRA disk group for example, how can I retrieve the size of each ...
2
votes
1answer
4k views

Cannot remotely connect to oracle express

I have installed Oracle Express in a oracle-6.5-x64 virtual machine (let's called it host). The installation went fine, I am able to connect and run commands using SQLPlus from the host. Now I'm ...
2
votes
1answer
3k views

ORA-00922 encountered in Oracle 11gR2 installer

I've just run the installer for Oracle 11gR2 on a Windows Server 2008 (64-bit) OS. I encountered the "ORA-00922: missing or invalid option" error at the last stage of installation when it was doing ...
2
votes
1answer
746 views

Rebuild an index while it is being referenced in Oracle

I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent ...
2
votes
1answer
16k views

Completely uninstall Oracle server on Linux

I have searched lot in Google for how to uninstall Oracle on Linux, but I can't figure it out. I think that I need to reinstall the server because I get a lot of errors when I try to startup the ...
2
votes
1answer
2k views

Changing a materialized view column's length after a change in the base table?

We changed the underlying table column size (length). So the materialized view which is built on that table throws an error now since the MV column still has the old size. This is an Oracle 11gR2 ...
2
votes
1answer
19k views

Expanding system tablespace

I need to add new data file into the system tablespace because its running out of space. Are there any precautionary measures I should consider since this is a production environment?
2
votes
1answer
18k views

Oracle DBCA utility: “Listener is not up or database service is not registered with it”

I'm a software engineer with some experience on SQL Server but a newbie on Oracle. I'm attempting to run dbca on a remote linux machine to reconfigure a database that was initially configured to idle ...
2
votes
2answers
6k views

Setting NLS params in logon trigger don't work in oracle 11g

We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
2
votes
3answers
3k views

Installing Oracle 11g on Ubuntu 11.10

Does anyone know to how to install Oracle 11g on Ubuntu 11.10?
2
votes
1answer
488 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
2
votes
1answer
2k views

Differing results from gv$session

The following query returns no results, yet it seems like it should. SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID'); In my 11.2.0.2 Standard Edition RAC environment the above ...
2
votes
1answer
549 views

Decision tables in the database: a valid solution?

I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases. I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish this....
2
votes
2answers
6k views

getting error installing oracle 11gR2: cannot access node

I'm installing Oracle 11.2 on dedicated hosted server (centOS 5.5). I've gone through the Oracle Quick Installation guide diligently. I believe I've set everything up correctly, although I'm not a DBA ...
2
votes
1answer
28 views

SQLPLUS Default Column Widths

For almost all my databases, when I run a SELECT statement, the length of the column displayed is sized to the width of the data being queried. However, I have two databases (Dev and Prod for the ...