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

6
votes
2answers
5k views

How do I query to pull all data in row based MIN and MAX service date?

I'm very new to Oracle SQL especially in the Pentaho report builder. It lets you do some things and block others. PL/SQL is not available so I must do this in a query. I need to be able to pull ...
6
votes
3answers
2k views

Clustered index in SQL Server vs index organized tables in Oracle

I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how ...
6
votes
2answers
2k views

Does it make sense to consolidate Oracle databases?

I am more experienced with SQL Server and Sybase than Oracle, and understand those products well. I've been asked to look for ways to reduce the server estate running Oracle. I understand that an ...
6
votes
5answers
22k views

Simplified/automated datafile free disk space reclaim

On Oracle version 11g: After Googling, I can't find a simple way to reclaim free space after deleting a table. I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
6
votes
1answer
19k views

Difference between database dump (export) and database backup?

What is the drawback using only EXP or EXPDP command for a database dump (and considering it as a backup) instead of taking backups using BACKUP DATABASE command (via RMAN)? What is a difference ...
6
votes
2answers
1k views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
6
votes
2answers
2k views

Oracle clean up

I have an Oracle database on a VMWare image that seems to be slowly eating all my available disk space. I would like to know if there is a command or script that would perform some kind of clean up of ...
6
votes
4answers
38k views

Oracle startup error (could not open parameter file)

I installed the Oracle 11g server on Ubuntu 12.10. But I can't start the server. When I start up the server, the following error occurs. SQL> startup ORA-01078: failure in processing system ...
6
votes
1answer
3k views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
6
votes
2answers
42k views

How to troubleshoot enq: TX - row lock contention?

I have the following situation. I have RAC. On both nodes there are the locks. On the First Node SID EVENT USERNAME BLOCKING_SESSION ROW_WAIT_OBJ# OBJECT_NAME ...
6
votes
3answers
10k views

Trouble killing sessions then immediately dropping users in Oracle 11g XE

I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call. Here is what I have so far: DECLARE cursor c is select * ...
6
votes
1answer
2k views

What's the difference between a v$ view and its dba_ equivalent?

What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.
6
votes
3answers
25k views

How to connect to a remote Oracle server from Windows machine using PL/SQL Developer?

I understand that I need to configure database address in the TNSNAMES.ORA file but where should I put this file? UPDATE I defined the $TNS_ADMIN variable, created the TNSNAMES.ORA file, added new ...
6
votes
2answers
785 views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
6
votes
2answers
2k views

Identify which parameters were passed to a procedure (Oracle)

Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...
6
votes
2answers
7k views

How do you find where a stored procedure is used (in other stored procedures)

I have a stored procedure which I want to refactor, in a database which has thousands of SPs. Is there a quick way of finding references to that stored procedure in other SPs so I can be sure that I'm ...
6
votes
2answers
13k views

Oracle - Automate Export/Unload of Data

Oracle SQL Developer has an option to export the contents of a query result to various formats (CSV/fixed width/Excel/XML). Is there any way to automate it? If not, what free tools are available ...
6
votes
1answer
16k views

Query execution time in Oracle SQL Developer

I know I can use set timing on to see how long it took to execute a query after the execution is finished. But is it possible to see current execution duration in Oracle SQL Developer while the query ...
6
votes
1answer
98 views

Why are some aggregates treated differently in the RHS of a rule in the model clause?

With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent: select ...
6
votes
3answers
9k views

Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)

I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job; alter system set memory_max_target = 512M scope = spfile; Which I guess ...
6
votes
2answers
676 views

Is it worth partitioning a Table by single value in Oracle?

Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in advance?...
6
votes
2answers
2k views

EXP-00085 warnings while doing full backup

During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings [...] . exporting post-schema procedural objects and actions EXP-00008: ORACLE error 6502 encountered ORA-06502:...
6
votes
2answers
206 views

Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command

I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below: create table test_table1 (col1 nvarchar2(100)); table TEST_TABLE1 ...
6
votes
2answers
8k views

Clean trace files on Oracle 11g

The partition containing my Oracle 11g installation began to fill up with trace files and when I went looking for what are these I've found this disturbing post about how to disable the trace file ...
6
votes
2answers
13k views

How Does Oracle Handle Multiple Concurrent INSERTs Against One Table

I am trying to understand Oracle 11g a little more closely. My question is simple: how does Oracle handle two sessions that are attempting to insert records into a single table at the same time. For ...
6
votes
3answers
3k views

Why performance degradation with query hint FIRST_ROWS(1000)? [closed]

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input. In case the user supplies very unselective arguments, the query takes a lot of ...
6
votes
1answer
2k views

Oracle concatenation of queries and function index usage

I have a problem with something I can't understand about indexes in Oracle 11g. We can create test data with: create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
6
votes
2answers
7k views

Current isolation level in oracle

How to get the current (default) transaction isolation level in oracle?
6
votes
1answer
11k views

Introduce partitions into an existing non-partitioned table

What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this? Especially in cases where there are many ...
6
votes
2answers
188 views

ANALYZE TABLE..VALIDATE STRUCTURE runs forever

So, as I've mentioned before, I am a SQL Server DBA/Consultant who is helping out a customer with their Oracle databases. Mostly, I've been able to figure out what I need to and work through things ...
6
votes
1answer
958 views

sqlplus consuming 99% CPU

Oracle's installed on one server, I'm running sqlplus on a different (application) server. I've recently encountered a problem getting sqlplus to do anything; it simply spins, consuming 99% CPU even ...
6
votes
2answers
116k views

How to resolve ORA-01031: insufficient priviledges error on Windows installation?

I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database ...
6
votes
3answers
5k views

Is it possible to see the (completed) SQL Statements executed by a specific session?

Is it possible to see a list of all past SQL statements executed by a specific session (or number of sessions)? For example, the following query gives me all currently executing statements from all ...
6
votes
3answers
7k views

ALTER SYSTEM DISCONNECT SESSION doesn't work

Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't. I login as: sqlplus system/[password] ...
6
votes
1answer
7k views

Is there a timeout for a query over database link?

Edit/Preface: This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really ...
6
votes
2answers
2k views

Making the Oracle optimizer use a virtual column to find out about a partition

I have a large table that has three columns like this: "START_DATE" DATE, "START_VALUE" NUMBER(10,7) "START_DATE_VALUE" NUMBER(18,7) GENERATED ALWAYS AS ( (extract(YEAR FROM START_DATE) * ...
6
votes
2answers
5k views

efficient way to poll database?

I have a Linux CentOS server hosting an Oracle 11.2 database, which communicates with an application server, which communicates with a client (a Flex application). I'll try to describe the pseudo-code ...
6
votes
2answers
3k views

I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer

When trying to use the SQL Tuning Advisor from SQL Developer I get this error. Tuning advisor was working some days before.
6
votes
1answer
2k views

DBMS_Resource_Manager.Calibrate_IO Usage

DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter. The documentation says this value should be set to the... Approximate number of physical disks in ...
6
votes
1answer
404 views

Oracle Support Tips & Tricks

How can My Oracle Support (Metalink) be used effectively? What are your secret hints to this system? What saved you the most time?
6
votes
1answer
3k views

MySQL or Oracle Associate Certification?

I have 15+ years in software development and 7+ in web development, and I want to get a certification in another area, like DBA. I have 7+ years of experience like MySQL developer. Which is better for ...
6
votes
1answer
188 views

Is Oracle Configuration Manager HIPAA compliant?

Does Oracle's Configuration Manager which according to Oracle provides a "40% faster issue resolution", violate HIPAA or require specific configuration in order to comply? This question concerns ...
6
votes
1answer
1k views

Oracle Grid Control Notification for Data Guard failures

Grid Control 10.2.0.5. Does anyone know a way to configure a Grid Control notification rule for a failure of the SQL APPLY process on a logical standby database? I'd really like an email notification ...
6
votes
3answers
5k views

Add Oracle Label Security to an existing Oracle installation

We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g ...
6
votes
1answer
1k views

Using sys.diana and sys.pidl to parse PL/SQL

It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
6
votes
2answers
282 views

What is an Oracle ASSEMBLY?

What is an Oracle ASSEMBLY, in the context of Oracle SQL? It's listed in GV$SQLCOMMAND. Although GV$SQLCOMMAND is not a perfect guide to "real" SQL statements - for example it has UPSERT instead of ...
6
votes
1answer
997 views

What are the Oracle Dataguard Implications of Migrating to a PDB?

If I have an OLTP database with two physical standby instances (one remote) what are the implications of migrating the primary DB to being an Oracle 12c PDB? i.e. Does the process of migrating to ...
6
votes
1answer
469 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 ...
6
votes
1answer
561 views

How can I make the audit system create new audit files if they already exist?

I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the ...
6
votes
1answer
1k views

Global locking for multi-master Oracle GoldenGate replication

This is a very complex scenario, but I figured a state-of-the-art challenge might interest some of the many high-end users of dba.se. Problem I'm working on an intercontinental data replication ...