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

learn more… | top users | synonyms

2
votes
1answer
2k views

Is there a way to explicitely have a not-null column in a view

If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table: create table tq84_t ( col_1 number, col_2 varchar2(15) not null ); create view ...
0
votes
1answer
4k views

Oracle: list user-created tables in the SYS schema

I need to delete all tables (hundreds) in the SYS schema that someone accidentally created with sqlplus. Looking in dba_tables and dba_objects, I don't see a way to distinguish these tables from pre-...
0
votes
2answers
12k views

ORA-01031 while creating a view as DBA

I'm trying to create a view under my DBA schema on Oracle XE. I already did that on the productive database and it worked with a user with a lot lesser privileges than the DBA role. But now I always ...
1
vote
1answer
1k 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 ...
0
votes
1answer
2k views

How to Drop tables being a SYS user

I am not being able to drop my tables using : drop table <table_name>; command. It says "the table is referencing primary keys of some other tables as foreign keys." , and when I tried to ...
4
votes
1answer
3k views

How to determine Oracle LOB storage footprint?

With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space. Timing is fairly easy to profile but what's ...
0
votes
2answers
604 views

Fatal NI connect errors

I'm wondering if there are any auditing processes I can enable to see what is causing these errors, like sql statements being run, or any other metadata that I could use to track down the source of ...
1
vote
2answers
865 views

fighting filter (nested loops) execution plan from Oracle

My query is: SELECT category, count(*) FROM large_data_table t WHERE (t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 1)) OR (t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 2)) GROUP BY GROUPING ...
1
vote
2answers
8k views

Run Unix command using PL/SQL

Is it possible to run a Unix command using a query in Oracle? I want to run simple commands (like df -h) using a query. Is this at all possible or am I wasting my time? I don't want to use a ...
1
vote
2answers
2k views

oracle tablespace extending

While under massive write (insert and create table as) load, when Oracle RAC needs to extend tablespace, all write activity gets blocked for however long it takes it to finish extending (tens of ...
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 roles....
0
votes
1answer
503 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
3k 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
185 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
1k 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 ...
2
votes
1answer
73k 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
953 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 database....
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
432 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
9k 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
688 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 ...
0
votes
0answers
928 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
165 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
9k 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 product(...
2
votes
1answer
746 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
591 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
19k 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
3k 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
25k 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
38k 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
202 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
460 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
770 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
293 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
885 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 (DBCA)...
0
votes
0answers
684 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
11k 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 ...