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
0answers
648 views

Is it recommended to run a Oracle schema backup when there are active sessions?

I need to backup two schemas in a Production database (Oracle11gR2) that are being accessed by an application and some other systems. These schemas are quite small and exp will be used for the export. ...
0
votes
0answers
134 views

Replicating Excel's CHIDIST function in Oracle 11g

I'm attempting to replicate the functionality of an Excel workbook in Oracle. One of the formulae on the worksheet uses CHIDIST, which "Returns the one-tailed probability of the chi-squared ...
2
votes
1answer
477 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 ...
3
votes
1answer
5k views

How to combine `delete` and `insert` operations in one statement?

for the following data, I'd like to be able to delete some rows and insert others giving the result below. Is this possible with a single statement (eg with the merge statement)? create table ...
1
vote
0answers
432 views

rman 'delete archivelog all’ very slow

On our automated test system we use flashback all the time to reset our Oracle 11g database to a state before a test run. We noticed some of the scripts involved, started to become slower and slower. ...
1
vote
1answer
6k views

Using wm_concat to concatenate rows, but in an order determined by another column

Let's say I have 3 columns: p_id, description, order_by. I am trying to do the following: I would like to concatenate the description for all like p_id values. So we are talking a group by p_id ...
3
votes
1answer
876 views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
0
votes
2answers
60 views

Why is the padding on months in Oracle 9 characters?

I ran two simple sql statements select length(to_char(sysdate, 'fmMonth')) from dual; select length(to_char(sysdate, 'Month')) from dual; The length of the first statement was 4 (It's June) and the ...
1
vote
1answer
392 views

USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)

Does anyone know what triggers the USER_ID field in the log.xml to be populated? The value also exists in the V$DIAG_ALERT_EXT view. I've found by observing the logs that if a temp tablespace fills ...
0
votes
1answer
2k views

Different dates Oracle 11g with TOAD

I have the following queries: SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL; /*this returns 2/1/2049. */ SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL; ...
1
vote
1answer
3k views

Unable to connect to Amazon RDS instance [closed]

I recently created an oracle instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance using Oracle SQL Developer. The (relevant) information I have from Amazon; Endpoint - The ...
2
votes
1answer
373 views

BLOBs storage in a dedicated table

I have a database that has several BLOB columns scattered across multiple tables. Since I am a developer and am looking at making application code writing easier, I wanted to turn these columns into ...
2
votes
1answer
3k views

where is oracle session timezone set?

I have a logging table CREATE TABLE EMAIL_LOGGING ( ID NUMBER(9) NOT NULL, SEND_DATE TIMESTAMP(6) DEFAULT LOCALTIMESTAMP NOT NULL, ...
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 ...
3
votes
2answers
11k views

How to quickly startup/shutdown Oracle 11?

I am wondering what is the quickest way to properly startup/shutdown a Oracle DB daemon (Oracle 11.2, installed on a test machine). I need it for C/C++ programs that use the OCI/Pro*C API. Currently ...
0
votes
0answers
2k views

Oracle 11gR2 DBCA installation failing with TNS Lost contact

I am getting below errors while running DBCA with silent install using response file ORA-12547: TNS:lost contact PRCR-1079 : Failed to start resource ora.ocl.db It's a fresh installation and not ...
0
votes
1answer
84 views

Creating oracle database physical files from migrated DB

I migrated a database from SQL Server to Oracle 11g and there doesn't seem to be any physical files created after migration. The client requires that they receive a copy of the database so they can ...
1
vote
1answer
691 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 ...
2
votes
1answer
3k 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 ...
6
votes
1answer
397 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
233 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
596 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
497 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
4k 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
162 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 ...
3
votes
1answer
631 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 ...
0
votes
1answer
2k 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
373 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
583 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
2k 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
5k 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
129 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
8k 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
855 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 ...
1
vote
1answer
306 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
131 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
5k 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
2k 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
6k 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 ...
1
vote
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: ...
0
votes
2answers
1k 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 ...
1
vote
0answers
106 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
7k 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
654 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
1k 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
352 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
4k 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
565 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
220 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
5k 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 ...