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

1
vote
2answers
2k views

Using transactions in sqlplus

I'm coming to oracle from postgresql. In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction: dbpool.production=> begin; BEGIN ...
2
votes
0answers
77 views

Replacement for Pragma Restrict references

Up to Oracle 11.1 you could mark a package as only reading from the database by adding PRAGMA RESTRICT_REFERENCES( DEFAULT, WNDS); This has now been deprecated. I would like to enforce read only on ...
1
vote
2answers
192 views

Unit of COST in an Oracle execution plan

Is there a unit for cost in an Oracle execution plan? I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? What does this number stand for? Is ...
0
votes
1answer
1k views

Oracle Duplicate Database with no archived logs

I am trying to perform a duplicate operation from one server to another (testing disaster recovery) on oracle 11g. I am very much still learning about Oracle. The system is not in production, it is ...
0
votes
1answer
151 views

Results of SAMPLE clause using a DBlink between 2 Oracle databases not randomised

I'm using the SAMPLE clause to retrieve random rows from a table. It works fine except when the table is from another database. I'm using a database link between 2 Oracle 11g databases (11.2). This ...
0
votes
0answers
210 views

ORA-26097: unsupported conversion for column SYS_NC0000500006$ (from type 58 to type 23)

I have an Oracle Enterprise Edition as a source and taken the dump using the oracle data pump(expdp) and the dump is imported using (impdp) to an Express Edition of Oracle. The import was working ...
2
votes
2answers
2k views

Permissions needed to call DBMS_DATAPUMP from within package

I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a ...
0
votes
2answers
249 views

Oracle: How to split records across days

I have data with two timestamp columns startdate and enddate. The data is produced continuously everyday and some records are such that the startdate is one day and the enddate is the nextday as it ...
0
votes
1answer
487 views

Oracle 11g r2: Cannot create partition on timestamp field

I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this. CREATE ...
1
vote
0answers
49 views

reduce the cost of maintaining a materialized view? [closed]

how to reduce the cost of maintenance a materialized views? and when I selected between maintaining by recompiling and maintaining by refresh?
1
vote
1answer
14k views

ORA-01034: ORACLE not available in oracle 11g

I installed oracle 11g on ubuntu 14.04 and i started lister and the message is as below. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 10-OCT-2014 14:20:02 Copyright (c) 1991, 2011, Oracle. ...
0
votes
0answers
41 views

Did another application use an Oracle block?

One of our production Oracle databases got hit with a corrupt block. Looking at the hex dump, the first part of the block seems to contain Microsoft Windows Event Log and Windows Registry information. ...
0
votes
1answer
56 views

Oracle 11g user created with Create session but able to update the table?

I created a readonly user with only permission granted was create session and read only privilege, using the link: http://stackoverflow.com/questions/7502438/oracle-how-to-create-a-readonly-user Now ...
1
vote
1answer
42 views

Does trigger “see” all changes done in the transaction it was fired from?

Let me elaborate on the title. Suppose we have two tables, t1 and t2. t2 has a trigger t2_trgon it. Same transaction inserts/updates data in t1, then does insert/update in t2. t2_trg is fired and ...
3
votes
1answer
151 views

What is the use of Archivelogs?

Hi I am new to Oracle Database . Using Oracle 11g R2 I want to know the purpose of archive logs created at archive log destination, how they are helpful in database backup. Suppose I am having ...
1
vote
1answer
78 views

How do you capture the output message from a successful MERGE statement when using it in a procedure?

Sorry if this sounds like a rather simple question.. When using the console, normally the statement outputs something similar to X records merged How do I capture this output into an out variable ...
0
votes
0answers
92 views

Oracle Support Identifiers?

Right now i am not working in any company.How to get oracle Support Identifiers?
0
votes
0answers
2k views

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor at the time of switchover using data guard broker

I have a oracle data guard setup with nodes - one as primary and other as physical standby. I also, have configured the data guard broker on it. The problem is when I tried to switch over , I just ...
0
votes
0answers
110 views

how to make oracle db instance and em dbconsole running manually/?

I installed oracle11gr2 (x64) on vmware oracle linux (x64). When I finished installing, I'm sure that I executed the init scripts as root: /app/oraInventory/orainstRoot.sh ...
2
votes
1answer
7k views

Connected to an idle instance; trouble creating DB

I tried to connect to my database and got Connected to an idle instance: C:\>sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 22 19:07:34 2014 Copyright (c) 1982, 2010, ...
0
votes
2answers
176 views

composite index vs normal index

I need to index some tables and for that i found that there are different kinds of indexing. I am kind of clueless on their proper place of usage. Should i create index on all the fields that want ...
1
vote
0answers
122 views

Adding custom meta data to a table in oracle

I'd like to know if there's a possibility to add some custom metadata to a oracle database table. The problem at hand is the following: I'm importing a table from another DB. It might happen that I ...
0
votes
3answers
178 views

Replacing the results of a row

I'm working with Microsoft Reporting Services 2008. In our database, we have a small group of confidential students that need to be taken into account for several queries. If they are a confidential ...
0
votes
1answer
2k views

how can I insert a long string into a blob using an insert statement?

ORA-06502: PL/SQL: numeric or value error: raw variable length too long ORA-06512: at "SYS.UTL_RAW", line 224 the datatype is BLOB here's my insert statement insert into system_value (type, ...
1
vote
1answer
99 views

Run stored procedures from specific package/with specific tag at lower priority

I have this requirement to run "Maintainance Jobs" for a web application. These jobs will just call a stored procedure from a set of specific packages. Running these jobs can consume quite a bit of ...
2
votes
3answers
488 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 ...
0
votes
0answers
138 views

Selectively load data using Oracle 11g r2 External Table Preprocessor

I am using the Preprocessor feature in Oracle 11g r2 to load several data files in a table. However, the raw files seem to have certain rows that shouldn't be imported with the rest. For example: ...
0
votes
0answers
76 views

How to import data into Oracle 11g r2 daily

I need to batch load several log data files (comma delimited but they don't have a .csv extension in the file name) once each day into an Oracle database. I'm sure this is a very common use case, but ...
2
votes
2answers
558 views

Oracle Data Pump Import imports no records

When we do an Oracle Data Pump import of our database, it runs without significant errors. However, on one table Oracle reports that 0 records have been imported. Processing object type ...
0
votes
0answers
194 views

How to find materialized view log dependencies over database links

Is there a way to identify materialized views that have used a given MV log, even if the MV is using the log over database link? (Oracle EE 11.2) The problem is that I would like to drop a certain MV ...
1
vote
2answers
208 views

Notification email when deadlock is encountered in Oracle alert log

How to send a notification email when a deadlock is encountered in alert log on any node like the deadlocks encountered below? (an alert any time a deadlock is detected in alert log) Node 1: Mon Sep ...
0
votes
0answers
121 views

Oracle Counters for Windows Performance Monitor exceeding handle quota

On several windows 2008 R2 servers with an oracle 11.2.0.3.0 database installation i see the following warning in the eventlog every couple of days: Windows Management Instrumentation has stopped ...
1
vote
0answers
293 views

PLSQL Compilation error for other users/

I created a plsql procedure that queries the database and dumps the result onto a CSV file. I am an application developer and I have dba rights to my oracle database. I'm able to compile and run the ...
0
votes
3answers
2k views

comparing values between two tables

how to Compare values between two different tables I have two tables as shown below Table 'A' sql_id OPERATION OPTIONS 63kf3an2j0pk SELECT STATEMENT 63kf3an2j0pkc TABLE ACCESS FULL ...
0
votes
0answers
168 views

Query being executed beyond timeout in a transaction Oracle

I have a simple query as below when executed separately the result is coming very quickly. But when is part of the application flow the same query is being shown as in execution status for more than 5 ...
1
vote
1answer
450 views

Stop NLS settings in init.ora getting overridden by environment variable

We have a databases server that is used for development which has hundreds of database instances for different branches/builds of our application. And we needed an special instance that has ...
2
votes
0answers
49 views

Importing data into Oracle 12

I got a set of files from a client which are, as far as I can tell, copies of a database in Oracle 11. Each folder has the following structure: - init<DB>.ora - orapw<DB> - arch - dbf ...
0
votes
0answers
38 views

Oracle Buffer Pool TARGET_SIZE is different from CURRENT_SIZE, but RESIZE_STATE is STATIC

According to the documentation for V$BUFFER_POOL, TARGET_SIZE shows that target size when resizing, but should be the same as CURRENT_SIZE if RESIZE_STATE is STATIC. However, on one of our databases ...
0
votes
0answers
51 views

In PL/SQL what is the usage of row level and column level attributes?

I am new to PL/SQL... What is the usage of row level and column level attributes? Can any one tell me why these are using to access columns of the table? Explain with an example please..
0
votes
1answer
63 views

Optimal way of loading, registering, indexing and querying a huge XMLDB

I've got 35 million highly structured XML documents, with a total size of 500 GB. An XSD is available for this data, and the end goal is to transform the data into an RDB. All fields can be considered ...
0
votes
0answers
101 views

Are there any incompatibilities between Oracle 11 and 12?

I have an application which has been validated on an Oracle 11.2 database. A client has asked me whether they can use Oracle 12c instead as that is what their IT department would prefer. Is there an ...
0
votes
3answers
530 views

Delete all the data from all tables

I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets. These are 47 tables in total. I tried the following: Delete ...
0
votes
1answer
105 views

oracle tablespaces

I export db from user which is related on tablespace USER on local PC and import to user which is related to tablespace DATATAB on server....my question is: will data be stored in DATATAB tablespace ...
1
vote
1answer
3k views

Oracle error 12154 while importing DMP file

My Setup I'm running Windows 7 64-bit, using Oracle DB 11g R2 for Windows 64-bit. I have Oracle SQL Developer Release 3.0 (4.0.2.15.21) as the GUI for my database. Question I followed the ...
1
vote
1answer
1k views

Oracle 11g varchar2 datatype with date and time as YYYY/MM/DD-HH24:MI:SS:FF3

I am working with a column 'LastUPDATE' in a database that is datatype of varchar2. This column is listed as YYYY/MM/DD-HH24:MI:SS:FF3 format. Example: '2012/09/06-21:17:30:019' My first issue is ...
0
votes
1answer
3k views

How to grant permissions for User defined type in Oracle

This is probably easy but i can't seem to figure it out. I created my user-defined type as CO_ADMIN. However, the application using the type runs using GS_USER database account. I have granted ...
0
votes
0answers
396 views

Oracle 11.2 Explain Plan takes too long

I'm using Oracle 11.2 SE on a 16GB Xeon E3 Linux machine as well as 11.2 XE on a 8GB i7 Win8 machine: SELECT banner FROM v$version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL ...
0
votes
1answer
45 views

Sub query with not equal

Sub query with not equal table_1 col1 SV0362 RU0177CSPG NO0124CSPG VN3582 AT9923CSPG BZ0324 PE0309 AS4293EXPT ML0331OMKT table 2 colA OO56128665 OO56128665 OO56128665 OO56128665 US0170 US0170 ...
1
vote
0answers
417 views

sql query takes too long to execute [closed]

I am having trouble with this query. It takes 10 hours to execute. WITH CONSUM AS ( SELECT C.ID_MEASURER,C.ID_MEASURING_POINT,C.CONSUM_TYPE FROM GCGT_RE_CONSUM_H_E C WHERE C.DAY ...
0
votes
0answers
76 views

Is it possible to copy mounted database without RMAN?

I need to make a copy of production database for development. For some reason I can't use RMAN. My question is: can I copy standby database while its in mounted state and recovery is canceled? Of ...