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, ...
0
votes
0answers
9 views
Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop
I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials. I was hoping to install Oracle Express Edition on my Windows 7 laptop to get ...
4
votes
2answers
77 views
Multiple JOIN of 6 tables via 5 database instances
I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
0
votes
1answer
25 views
Where I can find the tnsnames.ora file for my Oracle Windows client? [duplicate]
I installed Oracle client for my Windows 7 machine. I can connect to a remote Oracle database using Toad for Oracle using the direct method (giving host, port and service name for the connection).
...
1
vote
2answers
28 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
...
0
votes
1answer
63 views
How to keep an Oracle 11g instance lean?
As a Mac user I run a local Oracle Enterprise Linux (OEL) Developer Day VM that houses an Oracle 11g instance for development/unit testing. I frequently refresh the data in the schemas (sometimes ...
0
votes
1answer
65 views
Why Oracle SQL Developer does not need Oracle client?
I installed Oracle SQL Developer from Oracle's web page. It seems to work without a Oracle client installed on my computer. How is that possible? I also installed Toad for Oracle and it seems to ...
0
votes
1answer
39 views
Can we create a table in specific datafile - Oracle 11g R2?
I am buying a new server than has two HDs:
300 GB with 15000 rpm.
600 GB with 10000 rpm.
I am thinking to allocate the datafiles in both hard disks. and put the less used tables in the data files ...
0
votes
0answers
35 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
44 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 ...
1
vote
1answer
30 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
116 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
28 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 testrun. We noticed some of the scripts involved started to become slower and slower. In ...
3
votes
1answer
79 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
27 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 ...
0
votes
1answer
30 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
72 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;
...
0
votes
0answers
38 views
Migrating form Oracle to Sybase fails due to missing functions
I generated the DDL from Oracle to Sybase, created the tables with no issues even migrated most tables into Sybase using insert into, however when came to the time functions I get errors:
Function ...
0
votes
1answer
63 views
Unable to connect to Amazon RDS instance
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
39 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 ...
1
vote
1answer
54 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,
...
0
votes
1answer
66 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 ...
0
votes
2answers
104 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
114 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
56 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 ...
0
votes
1answer
184 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
48 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
64 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
73 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
27 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
59 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 ...
2
votes
2answers
80 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
60 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
79 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
39 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
125 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
136 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
90 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
55 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
116 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
49 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
50 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
33 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
51 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 ...
0
votes
2answers
152 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
81 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
384 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
160 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
67 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
37 views
What value should oracle.install.db.config.starterdb.memoryLimit be set to in the Oracle response file for a silent installation?
What happens when oracle.install.db.config.starterdb.memoryLimit is not set in the Oracle response file for a silent installation?
I noticed that when recording the response file for a silent Oracle ...
0
votes
0answers
35 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 ...
