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

learn more… | top users | synonyms

3
votes
1answer
101 views

Global command to destroy all disconnected data files in Oracle 11g

Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database? Here is the scenario: Every user-defined user and user-defined tablespace was deleted from an ...
0
votes
1answer
99 views

How can I launch SQL Developer?

I recently installed Oracle 11g R2 on a Windows 8.1 machine, but for some reason SQL Developer does not seem to launch easily. What specific steps do I need to take in order to launch a working SQL ...
1
vote
1answer
304 views

How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file?

How do I import data into a new installation of oracle 11g r2 using a .dmp file and a .par file? Here is what I have done so far, on a Windows 8.1 devbox: 1.) Open command line as administrator 2.)...
1
vote
0answers
48 views

Fetching user info from many databases at one go

I have a requirement to fetch all the schema available in 100+ Oracle instances running in RHEL environment. All the schema information such as username, roles assigned and is to be dumped in one ...
0
votes
0answers
104 views

From RAC to Standby RAC With Active Dataguard

I have oracle database 11.2.0.3 with RAC feature , with 2 node on oracle linux 6.4 : ol6-112-rac1 and ol6-112-rac2 I want to create active data guard with RAC feature from this database , these ...
4
votes
2answers
116 views

Selecting from random amount of tables with identical structure

I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software. For simplicity let's say this software stores address ...
0
votes
1answer
196 views

Cannot startup Oracle due to ORA-15041

I'm troubleshooting an Oracle instance that won't startup due to ORA-15041. The trc file shows this error: Failed to create file '+FRA_DATA' (file not accessible?) ORA-19816: WARNING: Files may ...
0
votes
1answer
72 views

how to write code for logon/logoff triggers in oracle?

I have written the code for a logon/logoff triggers. Logon is working but the logoff trigger is created with an error. Please give some suggestions. Logging table: create table logon_off_audit( ...
1
vote
2answers
74 views

Varchar2 datatypes are longer than expected

When I run the following script : create table tbl1 ( col1 varchar2(30), col2 varchar2(50), col3 number(4,2) ); desc tbl1; alter table tbl1 modify (col1 varchar2(35)); desc tbl1; drop table tbl1; ...
1
vote
2answers
116 views

TNS errors using Oracle instant client

I'm struggling to get my Oracle application working. I'm using the Oracle 11.2.0.4.0 instant client with a C++ application that is compiled using Visual Studio 2010. The machine is running Windows ...
0
votes
1answer
169 views

SGA size not decreasing after reducing memory_target (Oracle 11.2.0.1)

I have an Oracle 11.2.0.1 database using dynamic memory allocation with parameter MEMORY_TARGET. When I decrease it with SCOPE=BOTH, the size of the SGA doesn't seem to decrease along with it. I have ...
0
votes
0answers
54 views

oracle update statement is too slow

I have oracle database 11.2.0.4 on oracle linux 6.4 I have configure huge page on this OS . After 5 months of my projects , when i want to update any tables their have over 20,000,000 records , this ...
2
votes
2answers
78 views

Performance improvement after table analyze

I used a function that contains 3 tables a,b, and c. Table a has an index ind1 on two columns. I call a stored procedure that contains code and gets data using the above function. I analyzed my ...
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 ...
0
votes
1answer
69 views

Error ORA-12505 when trying to access a newly installed instance of oracle-11g express

I encounter problems installing a local instance of oracle express on my Windows 7 - SP1 computer. I did a basic installation, as administrator (run as 'administrator' on setup.exe). Then I tried to ...
1
vote
1answer
38 views

ORA-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair during AWR report creation

When I create an AWR Report, the following error occurs: ORA-13506 Cause: An attempt was made to perform operation with an invalid Start/End Snapshot Pair. What does this mean, and what ...
1
vote
0answers
115 views

Is it possible to resize a blob image?

I've got a table with an image blob field in it, which stores jpg files. As those jpg files are shown in a web page, I would like to resize them in order to spend less bandwidth when my web page call ...
1
vote
1answer
98 views

What is the purpose of %ORACLE_HOME%/dbs directory in Windows Environment?

I see some files in there like an init.ora and opctrn11.h, but what is the purpose of this directory, if we use %ORACLE_HOME%/database to store the p/spfile instead?
1
vote
2answers
77 views

Serialize :new in trigger

I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new. What I am looking for ...
0
votes
0answers
52 views

Oem 11g download link

Is the only way to get the following softwares from OTN by making service request? If suppose I dont have an OTN Account then how can I get it? Download Oracle Enterprise Manager Grid Control 11g ...
1
vote
0answers
65 views

Oracle Database Change Notifications only sent every 15 minutes, not immediately

I'm registering a listener for Oracle Database Change Notifications through JDBC (using the EclipseLink implementation of JPA, Oracle 11.2.0.3, Oracle JDBC thin driver 11.2.0.4). I'm getting ...
0
votes
2answers
107 views

Database not able to startup showing an error in reading control file

On Windows Server 2008 R2, when I am trying to startup Oracle 11g database it shows an error: SQL> startup ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size ...
2
votes
2answers
96 views

In Oracle what is the difference between STDEV and STDEV_SAMP functions?

We have a query that should return NULL when doing standard deviations in groups that have only 1 row. The normal STDDEV function returns zero. However I found another function called STDDEV_SAMP ...
1
vote
1answer
80 views

Oracle automatically unmount

I have a weird issue in my Oracle database, after 10 - 15 minutes of not using it (Not performing any query, insert on SQL Developer tool) then it will say ORA-27101: shared memory realm does not ...
0
votes
0answers
52 views

CDC Service for Oracle by Attunity Stops with Status Code ABORTED

We use Oracle 11gR2 as our OLTP layer behind our ERP product and we want to capture changes on SQL server for DWH use. In a new VM i make a clean install of SQL Server 2014 with SP1 (DB Engine and ...
1
vote
0answers
30 views

Diagnosing Oracle query problems by a non-DBA [closed]

I have a query (insert) run on Oracle 11gR2 (inside a stored procedure). It goes something like this: execute immediate 'truncate table global_temp_table'; insert into global_temp_table select /*+ ...
0
votes
0answers
33 views

ROUND function behaviour with dates

My question is about rounding the week. My understanding is when rounding y week oracle will round to same day of the week as the first day of the month. Does this mean if the first day of the month ...
0
votes
0answers
51 views

Oracle - compressed subpartitions in uncompressed table

I am working with Oracle 11.2. I have table which on subpartition level has in DBA_Tab_subPartitions column COMPRESSION set to ENABLED for all subpartitions. But on partition level in ...
0
votes
2answers
81 views

Can Oracle databases start automatically when Windows machine is booted/rebooted?

In one of my client machines every time Windows starts we have to start the Oracle database manually. Is there a solution/ command to start the database automatically when the machine is started/...
0
votes
0answers
61 views

How to concatenate using cast(collect()) in Oracle DB?

My current query: select distinct mb.BP_TYPE,mb.ABS,mb.BLD_SEQ_CODE, mb.BPCODE||'~'||(select LISTAGG(mbp.PROCEDURE,' ') within group(order by mbp.LINE,mbp.SLN1,mbp.SLN2) from MIS_BS_BB_PROC mbp ...
1
vote
1answer
72 views

sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows

While i am trying to connect with with database by the command sqlplus /nolog than the error message is coming "sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows" ...
0
votes
1answer
39 views

Oracle connection through visual studio

I installed oracle client 11.2.0 version in my machine and also installed Oracle data access providers for visual studio.I also installed Oracle Data Access 11.2.0.3.20. I am trying to connect to the ...
1
vote
0answers
71 views

Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

I have the following scenario: Oracle 11.2.0.3 A 3rd party application, that requires cursor_sharing = TRUE A table (T2310) with a indexed field (C1000000161) that has no duplicated values Result ...
1
vote
3answers
77 views

What procedure can be used to change system statistics in a production environment

System statistics aren't required but highly recommended by Oracle. From my own experience, it produced a huge improvement on the total user IO waits of our production environment when first ...
3
votes
2answers
436 views

Database not able to startup showing control file is inconsistent with another

Our windows server 2008 R2 is shutdown due to power failure. When I am trying to startup Oracle 11g database its shows an error. SQL> startup ORA-01081: cannot start already-running ORACLE - shut ...
0
votes
0answers
54 views

Does RECO always resolve in-doubt transactions ? (Oracle)

I am working on an application which uses a Oracle 11gR2 database in order to fetch and store data. The application is written in C/C++ and some Pro*C, which takes care of everything related to the ...
0
votes
1answer
139 views

Top Activity in OEM reports that dbms_scheduler jobs run as the job_creator user?

The top activity graph in Oracle Enterprise Manager was reporting that certain activity was being performed by certain users at odd hours of the night. Upon researching, this activity was because the ...
0
votes
0answers
39 views

Oracle Enterprise Manager 11g - restricting tablespace access

This may be a complete newbie question but I need help nonetheless. I have an administrator assigned to a default tablespace with his quota set to zero on all other tablespaces yet find that when ...
3
votes
1answer
61 views

Running / Accumulative Sum with Reset - Oracle

I am trying to accumulate a sum, but the sum accumulated cannot be positive. So far i got the following: Select t.caccserno,t.Reportingmonthyear,t.Amount,t.cycleage from temp_UnpaidInterest t ...
0
votes
1answer
192 views

Migrate/Export DB from Oracle 11g Enterprise to Oracle 11g Express

So I'm a DBA newbie and I've got an Oracle Enterprise server running in production with the following information under All Reports -> Data Dictionary Reports -> Version Banner, under "...
0
votes
1answer
62 views

How do I iterate over 1000 records in a particular order

I am writing a procedure to migrate two tables into different tables. This procedure will have two cursors. One to fetch the parent records and then second cursor to fetch the details for each parent ...
0
votes
1answer
198 views

Problem using sample schemas by Oracle in Oracle APEX

I am new to Oracle and Oracle APEX. I have been practicing to make Oracle APEX database application using "hr" schema of the database. Before doing so, I unlocked the schema, along with schemas "scott"...
0
votes
0answers
79 views

Possible cause of ORA-00600 on dbms_xplan.display?

I'm trying to see the execution plan information for queries. I can see some statistics after I set autotrace on. Also, I can check from plan_table. However, most tutorials or blogposts suggest ...
0
votes
3answers
400 views

lsnrctl is not working on Ubuntu 14.04

I have managed to install Oracle 11g on Ubuntu 14.04 through lots of pain. I managed to install it in the end but listener does not seem to be working and even responding any command. My /home/...
0
votes
0answers
38 views

One Listener for two Oracle Versions

I've got an dev machine with Oracle 10 and 11 running (on separate users) So I want to use only one Listener (the new from 11). I deleted the Listener of Oracle10 and created one on Oracle11. After ...
0
votes
1answer
38 views

Is it possible to generate workload for hammerora (hammerdb for oracle) from AWR tables?

I'm trying to generate workload from our production databases but rather than running a trace file in production, I would prefer using the AWR tables since we have a license for it. Is it possible? ...
0
votes
1answer
141 views

Oracle migration unable to create indexes

I want to move the contents of a database instance to another using the expdp and impdp utilities. The source instance is version 12.1.0.1.0 and the destination is 11.2.0.1.0 so I modified the expdp ...
1
vote
2answers
98 views

Testing on Logical Standby Database

Primary DB: 11.2.0.3 Enterprise Standby DB: 12.1.0.2 Enterprise I've found several articles on making changes on a Physical Standby DB, but I've been having difficulty locating how to enable user ...
1
vote
2answers
56 views

Fetching recent/historical queries run on a database from an application

I'm getting some database errors from an application related to unique constraints however none of the records have any duplicate values so I want to check what query was run that produced the error. ...
0
votes
1answer
75 views

ORA-12154 when PLSQL Developer 11 is trying to connect to database

Hi i have troubles when i'm trying to connect my PLSQL Developer to Oracle 11g R2 Express Edition. I created a new folder in c:\Oraclex86, in that folder I installed the oracle 11g R2 Client for x86 ...