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

0
votes
1answer
461 views

Oracle 11g install on Debian Wheezy does not start

I successfully installed Oracle 11g R2 on my laptop for evaluation, by mixing various sources of documentation. I saw the Oracle daemons after installation. But I have a few problems : After a ...
1
vote
1answer
639 views

Data Migration from Oracle to SQL Server [duplicate]

If you need to do a data migration from an Oracle database to SQL server, what approaches and technical solutions are the best practice? Our database has about 100 million rows and a total of 52 ...
5
votes
1answer
160 views

Sudden increase in log_file_sync waits

I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion. Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
1
vote
0answers
161 views

Performance Hit when upgrading to Oracle 11gr2

I am experiencing the same symptoms as noted in this question which was closed and does not have a satisfactory answer. Original Question We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to ...
0
votes
1answer
117 views

Enable Oracle Database options in an existing database

I want to enable Oracle Real Application Testing option in my existing database, but I couldn't find the way to do that using Oracle Universal Installer (OUI) or Database Configuration Assistant ...
0
votes
0answers
189 views

How to clone a database from one environment to a database on another environment?

OS: RHEL DB: Oracle 11g version 11.1.0.7.0 I am trying to clone a database on one environment (environment A) to another database on a separate environment (Environment B). Environment B's database ...
4
votes
2answers
1k views

How to remove all hidden parameters from Oracle Database

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended? SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ...
2
votes
1answer
99 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 ...
0
votes
1answer
751 views

User with dba privs can't login to APEX, but can from SQL*Plus

I'm using Oracle 11gR2 XE. A user with dba privileges can't log in to the APEX interface, but can from the command line with sqlplus. I just created user: CREATE ROLE administrators; GRANT dba TO ...
0
votes
0answers
136 views

Duplication Oracle Database to remote host

I have two scenarios. 1 ) Let's say , I have the same file system and same SID on both nodes. I've created any directories necessary for start the duplicate database. First I used command below. ...
2
votes
2answers
311 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 ...
1
vote
1answer
634 views

NLS_TIMESTAMP_FORMAT changing automatically and randomlly

I am using Oracle 11gR2. My nls_timestamp_format is changing randomly from dd-mon-rr to yyyy/mm/dd. Does anyone have any clue why this is happening?
0
votes
1answer
1k views

How to purge logs by using Flashback feature in Oracle Database

I’ve configured flash recovery area for Oracle Database. My question is: how can I delete Flashback Database log?or is there any retention policy for that? 2- After I’ve enabled archive log mode and ...
0
votes
1answer
126 views

Efficient way to perform approximated search?

I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record. As it happens ...
0
votes
3answers
1k views

Exporting schema data (tables, permisions) into another schema on same database

I want to take data from one schema schema1 and put it into schema2 this should be quite easy to do but i have searched through the oracle documentation and can't find a way to do it.
0
votes
1answer
233 views

How to clean up duplicate objects in SYS/SYSTEM

I have a sql query like below to check duplicate objects in SYS/SYSTEM. select OBJECT_NAME, OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME||OBJECT_TYPE in (select OBJECT_NAME||OBJECT_TYPE from ...
0
votes
0answers
83 views

one vs two column index difference when doing JOIN query?

Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query : SELECT * FROM alfa JOIN beta on beta.id = ...
0
votes
1answer
96 views

Dynamic where condition in a stored procedure

I am using Oracle 11g. I am trying to get the table data with this procedure: CREATE OR REPLACE PROCEDURE test(p_table IN varchar2) IS v_sqltxt varchar2(4000); BEGIN v_sqltxt:='select count(*) from ...
1
vote
0answers
82 views

need the most efficient way to handle unique constraint errors

I am looking for the most efficient way to solve this situation: We are an Oracle shop. I need to load data from one table to another. My source table, which contains 30 million rows, has a person ...
1
vote
2answers
2k views

How to set Proxy Settings when installing Oracle 11g on Windows 7 Ultimate Edition?

I have downloaded the file - "win32_11gR2_database_1of2.zip" from oracle site. While installing Oracle 11g, I find this window in the first place: What to do? What values should I put on the ...
1
vote
1answer
425 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
0answers
2k views

impdp failing with ORA-01031: insufficient privileges

I'm trying to import a dmp file provided by our dba to my dev database. I've been able to export the contents to a SQLFILE and the contents look ok. I tried importing the schema and it kept on failing ...
0
votes
1answer
1k 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: ...
0
votes
2answers
270 views

Oracle database uses wrong parameter file

When I try to startup my Oracle database I get the following error: LRM-00109: could not open parameter file '/opt/app/oracle/product/11.2.0/db_1/dbs/initjicdbaex.ora' The /dbs directory in my ...
0
votes
0answers
61 views

file extension of the file types cdump, incident, trace

I need to know the file extension of the file types cdump, incident, trace, alert. Actually I want to create files of the above types manually using mkfile command. There are no existing files of ...
1
vote
1answer
2k views

grant execute on utl_file to user in Oracle 11g XE

sqlplus connect <sys>/<pwd> grant execute on utl_file to <user> connect <user>/<pwd> DESC utl_file then i get: ORA-24372: invalid object for describe How can I access ...
9
votes
2answers
316 views

Why is a “non-single-group group function” allowed in a subselect but not on it's own?

Why does the first query not fail with the same error as the second: with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual) select sum(units) from (select product_id, ...
0
votes
1answer
526 views

Error AWR Operation failed: CATPROC not valid when creating database with dbca

I have a fresh installation of Oracle 11g enterprise edition on CentOS 6, both 64 bits. I have installed Oracle and so far everything looks fine. I have already added an started a listener. The ...
0
votes
0answers
176 views

Can't change Oracle sqlnet.ora runtime in the client program?

I am trying to setup Kerberos authentication for my Oracle database and I have a situation where I need to maintain different versions of sqlnet.ora for each kerberos user connection. My application ...
1
vote
1answer
879 views

Registry error while trying to install Oracle 11g on Win 2008 R2

I am new to Oracle and trying to install Oracle database 11g Enterprise Edition on Windows 2008 Server R2 but getting this error all the time... It gives me error when updating registry key ...
5
votes
1answer
303 views

What must be in place to validate a XMLTYPE against a schema?

I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the ...
0
votes
1answer
114 views

List partitioning or filtering a non-partitioned table, which incurs lower cost?

Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed. Set 1: ...
4
votes
2answers
3k views

Shrinking the SYSTEM tablespace in Oracle

Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table. We have truncated SYS.AUD$ but the datafile is still very big (~30G). Resize doesn't work because the file contains used data ...
1
vote
0answers
2k views

Oracle 11g R2 on CentOS 6.3, and Net Configuration Assistant

this is my first time to install Oracle 11g R2 on Linux. I made all instructions in Oracle documentation. But in the installation process, the Net Configuration Manager is failed. I continued the ...
0
votes
0answers
106 views

No network ACL, yet my user can successfully connect to external server

This one's had both me and our dba scratching our heads. We have an Oracle 11.1.0.7.0 instance, DEV, with a user who can successfully connect to our ldap server, e.g.: DECLARE l_session ...
1
vote
2answers
156 views

Expand sparse table with self outer join on distinct values

I have this: g1 | g2 | x ---------+-----------+----- New York | Monday | 210 New York | Tuesday | 258 Chicago | Monday | 30 Chicago | Wednesday | 25 LA | Thursday | 40 ...
1
vote
1answer
757 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 ...
3
votes
1answer
423 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 ...
5
votes
2answers
289 views

Defining constraints in `CREATE TABLE` statements

Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
5
votes
1answer
293 views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
2
votes
1answer
2k 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
2answers
262 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 ...
3
votes
1answer
2k views

How to find the schema name of a data pump dmp file?

I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line: impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp ...
1
vote
1answer
712 views

Alternative tools to export Oracle database to SQL Server? [closed]

I've got an Oracle database that I need to export (schema and data) to SQL Server. I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient ...
2
votes
1answer
649 views

Specify Server for DBMS_Scheduler Job in Policy Managed RAC

A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from. I know that with an Admin managed database this could be done by creating a service that ...
1
vote
1answer
190 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 ...
2
votes
1answer
1k 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
292 views

Blocking incoming database links

Let's say there is an Oracle database A and another Oracle database B. Our database is A. Is there a way to block database links from B to A?
3
votes
2answers
9k 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 ...
2
votes
0answers
580 views

Oracle 11g R2 - same query sometimes fast, sometimes slow

I have a simple update query on a table with about 100 rows in it: UPDATE RESULTS_DATA SET IMAGE_BLOB=:myBlob WHERE MEASUREMENT_INDEX=:mIndex AND BATCH_ID=:batchId AND SYSTEM_ID=:sysId ...