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

learn more… | top users | synonyms

1
vote
1answer
35 views

most effective architecture to apply mulitple functions

I have a table that is the only source for a second table. Currently the second table is created using a CTAS (create table as select) statement that pulls in all the columns from the first table and ...
2
votes
1answer
2k views

oracle blocking sessions

I am trying to investigate some concurrency issues on Oracle 11 RAC. So far the most helpful tool I have is "blocking sessions" view of the Enterprise Manager, which tells me which query is blocking ...
4
votes
2answers
1k views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 ...
0
votes
1answer
478 views

Oracle cluster top activity class “cluster”

When under high load (many queries in parallel) our Oracle RAC gets in a state where most of the activity is reported as "cluster" by Enterprise Manager. It stays like that for 30 minutes to a couple ...
0
votes
2answers
3k views

Difference between chained and migrated rows

What is the difference between chained and migrated rows? Also what query should be used to display these rows? (I'm using Oracle 11gR2 database)
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
181 views

How would you track DB storage use and then extrapolate it?

I need to track the size of various DBs we have, then see it's growth rate so that we can guess where it will be at certain times. I'm using Oracle 11g first, but will eventually be applying this to ...
4
votes
2answers
993 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 ...
1
vote
1answer
68k 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). ...
2
votes
1answer
937 views

Flashback feature in Oracle 11g?

I am using Oracle 11gR1. As a part of a project, I have to execute multiple update statements by disabling a few constraints (triggers and check constraints) which could result in a corrupted ...
2
votes
2answers
2k 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 ...
1
vote
1answer
423 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 ...
1
vote
1answer
8k 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
667 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 ...
2
votes
2answers
6k views

Setting NLS params in logon trigger don't work in oracle 11g

We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
4
votes
2answers
2k views

Oracle 11g see transaction or session start time

I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records? I've got a transaction id "TX-1234-abcd", a ...
3
votes
4answers
4k views

Is it safe to delete dummy tables Oracle 11g XE

i created a database schema in 11g express edition, however there are some APEX tables and some dummy data... Is it safe to delete them? also i would like to know what does apex means and why they ...
0
votes
0answers
898 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
164 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 ...
3
votes
1answer
8k 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 ...
2
votes
1answer
700 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 ...
4
votes
1answer
557 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
17k views

How do I find out the “redo log file max size” in Oracle 11R2

I'm working with Oracle 11R2 and I want to see the max size of my redo log files (I didn't install Oracle so I'm not aware of the configuration). I have searched the Internet but all I have found ...
3
votes
1answer
1k 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
63 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
3k 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; ...
2
votes
1answer
4k 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, ...
3
votes
1answer
9k views

How to connect multiple users/schemas in Oracle 11g?

I'm almost new user to databases, so I think I'm doing something wrong. I have the database created. I created 3 users, 1 it's an Admin, 2 are just users. I created 2 roles, 1 for the Admin and 2 ...
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 ...
7
votes
1answer
2k views

Generate XML using Oracle XML schema

I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA. I have created tables using the XMLTypes generated. I can create XML using XMLQuery and if I understand correctly, ...
4
votes
2answers
23k 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 ...
6
votes
2answers
37k views

How do you get the explain plan?

Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.
0
votes
1answer
88 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 ...
2
votes
1answer
198 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 ...
6
votes
1answer
451 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
1answer
764 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
5k 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 ...
1
vote
0answers
283 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
853 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
672 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 ...
1
vote
1answer
10k 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 ...
4
votes
2answers
7k 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
2answers
654 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 ...
0
votes
0answers
489 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. ...
0
votes
3answers
12k 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
7k 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
1k 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
346 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
142 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
8k 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 ...