All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

3
votes
1answer
2k views

Use sqlldr control file to automatically create several tables in Oracle

I have a client who has a requirement that I load data from csv files into an Oracle database. Part of the requirement is that I use sqlldr and provide a control file to facilitate loading this data. ...
1
vote
2answers
6k views

Can anyone explain to me the concept “incarnation” in Oracle database in an easy-to-understand way?

The Oracle documentation describe incarnation as: incarnation A separate version of a database. The incarnation of the database changes when you open it with the RESETLOGS option, but you ...
2
votes
1answer
991 views

Oracle Active Session Pool Questions

what is oracle active session pool? is it activated by default? what is the default maximum active session that there is per user?
1
vote
1answer
300 views

What information from v$ views should be included when asking questions about Oracle Problems?

I was asking more information about Operating System. Then I wondered if an generic sql can give this information? Following things come to my mind. SELECT banner from v$version; SELECT LOG_MODE ...
2
votes
1answer
431 views

Oracle Stopping - Can't Login All Of A Sudden -

I'm having a problem with Oracle, it happens randomly around once in three weeks or once a month. The instance that i'm using suddenly hangs, and no logins can be established to it.It won't accept ...
12
votes
2answers
75k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
1
vote
1answer
6k views

Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper

I have following example query. variable pStartDateBegin VARCHAR2(10); variable pEndDateFinish VARCHAR2(10); begin select '01-01-2000', '30-11-2011' into ...
1
vote
1answer
7k views

Oracle Script For Getting Long Active Sessions

Is there any script that you could provide for v$session? I seem to be encountering an issue in which my oracle active sessions are filled up. This is when I do SELECT * FROM V$SESSION WHERE STATUS = ...
2
votes
1answer
2k views

Differing results from gv$session

The following query returns no results, yet it seems like it should. SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID'); In my 11.2.0.2 Standard Edition RAC environment the above ...
5
votes
1answer
1k views

How do I have Oracle ASM scan for and repair uncorrectable ECC errors (bad sectors)?

I'm running Oracle ASM (11gR2) with normal redundancy on SATA disks (WD Raptors). This is on a physical standby database, if it matters. Occasionally, disk sectors develop read errors (ECC failures). ...
5
votes
2answers
17k views

What is Key Preserved Table concept?

I read in the Oracle Documentation about key-preserved table in Updating Join Views Section. However, I didn't find any simple to way understand it. I hope to receive some simple conceptual details ...
3
votes
2answers
13k views

Improve BLOB Writing Performance in Oracle 11g

I have an application that writes a lot of rows into a table that contains a blob column. The average size of the blob is 3k bytes. Is there any buffer that i can resize to improve inserts into this ...
1
vote
1answer
196 views

Copying a table while maintaining consistency in Oracle

We have a PL/SQL stored procedure that is called on user demand which takes a copy of another table (ie. drop index > delete (not truncate) > insert > create index) and we've noticed the odd race ...
3
votes
2answers
7k views

How is localhost resolved by Oracle listener?

When I o an Oracle test installation on a Windows server accepting mostly the defaults, I end up with a listener.ora like LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = ...
3
votes
2answers
6k views

How to move the ownership/copy a table from one user to another?

I've been tasked with doing some tidying of our database. I'm not a DBA expert so here I am. (We don't have a DBA expert either). We have a user Old_User and another user New_User. Old_User owns a ...
3
votes
1answer
792 views

Oracle AQ: History Table for Queue Table

Is there a way to create a history table for a queue table, that will store all the events of the queues, for a defined period of time ?
2
votes
2answers
982 views

Logical schema development and migration tool for different physical schema available including triggers and stored procedures?

We are planning to support for our application another rdbms (oracle). At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have schema ...
1
vote
1answer
1k views

How to create an UTC index from a 'DATE' typed column with Oracle?

I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone. How do I create an index on the column ...
2
votes
2answers
1k views

Oracle 10g XE GUI

I've designed a database on Oracle 10g xe (Windows Platform) and I want to export the tables I created in a sql file. I've googled all night and didn't find anything. I also tried to install a Windows ...
5
votes
1answer
2k views

Oracle 10g express database link to Oracle 8i

I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
10
votes
1answer
12k views

Limit the degree of parallelism (DOP) available to any query

On Oracle Exadata (11gR2), we have a relatively beefy database. cpu_count is 24 parallel_server_instances is 2 parallel_threads_per_cpu is 2 We noted, through observation in Oracle Enterprise ...
4
votes
1answer
6k views

Good video tutorials/series for learning Oracle? [closed]

I am a better learner with visual content. Therefore I actively seek video tutorials when I start learning a subject. For oracle below links are my finds. I would like some additions to these. ...
4
votes
1answer
1k views

Find out if two rows came from same transaction in Oracle

I am using Oracle 11.1 and would like to figure out if two rows in one table where inserted by the same transaction. Is there an easy sql statement to map a row to an transactions id? The table is an ...
2
votes
1answer
70 views

Oracle 10g Administration

Redo log files are not multiplexed in our database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer ...
12
votes
3answers
15k views

Script out Oracle DDL in an automated fashion

Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention. I know of DBMS_METADATA.get_ddl(), but have found that the ...
1
vote
1answer
1k views

ORA-00449: background process 'MMAN' unexpectedly terminated with error 7446

One of our Oracle servers went down rather badly last night, and we have been unable to bring it online since. Attempting to startup each instance results in: SQL> Connected to an idle instance. ...
2
votes
2answers
6k views

WITH using INDEX hint in Oracle

I have a fairly complicated query. I have written two different query to accomplish my goal. First one is using WITH, other one is using global temporary tables. First One: WITH A ( KNO .. ) , ...
2
votes
1answer
6k views

What operating systems are certified for Oracle 11gr2 RAC?

How can this information be found?
2
votes
1answer
1k views

Which packages are required to install Oracle 11.2.0.3 on Solaris 11?

Where can I find information on which packages are required to install Oracle 11.2.0.3 on Solaris 11? The documentation only mentions Solaris 10. I tracked down the packages required for Solaris 10, ...
2
votes
2answers
11k views

How can I list all user-owned functions?

Is there a way to quickly list all the current user-owned functions in Oracle? Something along the same lines as this, which lists the current user's tables select * from user_tables;
4
votes
2answers
4k views

Where to start learning to become a successful Oracle DBA [duplicate]

I am IT Graduate with good knowledge on SQL and C. I have done most of the SQL programming on Microsoft SQL Server in my academics. But i want to become Oracle DBA. Could you please suggest me a ...
5
votes
1answer
3k views

Calculate months between two dates

For a University course I've been given the following task Suppose that we have an employee table like (first_name, last_name, salary, hire_date, ...). There is regulation which states that every ...
4
votes
2answers
4k views

Oracle Enterprise Manager read-only access for non-administrative users

Can access to OEM be restricted to the point where you can allow a non-admin user access to it but they can't modify any objects but can still view performance data, alerts, sessions, etc? I am a ...
3
votes
1answer
9k views

SQL*Loader-605: Non-data dependent ORACLE error occurred

As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error. Data is (are?) in .txt file and its size is 95GB. Here are some more details ...
0
votes
1answer
73 views

How to select rate for all selected modules and all selected years no matter they got rate for that year or not

How to write a SQL to achieve the "goal" section? Select rate for all selected modules and selected years no matter they got rate for that year or not. The years and modules will be input by users, ...
1
vote
1answer
2k views

Creating an index in a temporary tablespace

I am trying to create an index for a relatively large table (80M rows). First I got an ORA-01652 error, then I searched in SO and found a related question. However, trying to implement the steps ...
2
votes
2answers
3k views

“ORA-00942: table or view does not exist” when deleting from a very real table in the user's schema

I have a table that definitely exists in the logged-in user's schema (therefore I own the table) and is not a view/synonym/anything wacky. I can select from it, but when I try and either insert, ...
2
votes
1answer
15k views

Oracle 11g Listener not working

The listener on my Oracle 11g instance is no longer working. I tried to start it but that doesn't work. There are no Linux processes for the listener: > lsnrctl start LSNRCTL for Linux: Version ...
1
vote
2answers
917 views

Oracle Linked Server query error

We have a problem with a query from a SQL Server 2008R2 - 64bit to an Oracle Linked Server. We use the Oracle 64-bit driver for Windows. When we query a certain table we get the following error: ...
5
votes
1answer
1k views

What is Sql Server Clustered Index in Oracle terminology

A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on. If you look up with a name Ozgur you start O then z etc. I would like a simple explaination for this ...
2
votes
3answers
709 views

RAM Impact on Oracle database operations

We are seeing drastic change in the amount of time taken for db operations depending on ram size. The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into ...
1
vote
1answer
80 views

RAM Impact on database operations [duplicate]

Possible Duplicate: RAM Impact on Oracle database operations We are seeing drastic change in the amount of time taken for db operations depending on ram size. The same content when ...
0
votes
1answer
601 views

Oracle: Extract Graphical Database Representation [duplicate]

Possible Duplicate: Oracle Creating ER Diagrams and Data Dictionaries How can I extract a graphical (schema) representation of an Oracle database (tables and their relationships; with ...
3
votes
1answer
528 views

Oracle Materialized View

I need your help with a issue on Oracle Materialized View. I created the Materialized View using the following command: CREATE MATERIALIZED VIEW SNAP_TEST TABLESPACE tbs NOCACHE LOGGING NOPARALLEL ...
16
votes
2answers
86k views

Oracle: Quick way to list all database links

The title says it all, is there a way to quickly list all the current database links in oracle? Something along the same lines as this, which lists the current user's tables: select * from ...
1
vote
2answers
4k views

Confused about the Oracle CONTROL_FILES initialization parameter

This is the explanation of this parameter from Oracle. This is the description of the description in the explanation. From the documentation we know that this parameter is not modifiable (Modifiable: ...
3
votes
2answers
271 views

Recovering from Oracle host OS disk failure (options)?

If an Oracle host OS has a complete disk failure and the host can be restored (assuming from a recent clone of the host OS disks) from backup, is it possible to then point the host to the existing ...
7
votes
2answers
3k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
12
votes
1answer
10k views

What are the benefits of packages over standalone procedures and functions?

Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.
4
votes
2answers
28k views

Determine Oracle session client character set?

I know how the database characterset (NLS_CHARACTERSET in select * from v$nls_parameters;) and the client character set (the client environment setting NLS_LANG) interact. What I can't find out ...