Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

6
votes
1answer
24k views

Why Oracle sessions are still alive after two weeks being Inactive

DB: Oracle 10g O/S: Windows Server 2003 64 bits I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe) select * from V$session where UPPER(program) LIKE '%W3%' ...
6
votes
4answers
2k views

How to document Oracle database?

I'm new to Oracle databases. I have used to SQL Server and the description fields (MS_Description) for tables and columns for documentation purposes. Is there an equivalent for Oracle? What is the ...
6
votes
3answers
680 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
6
votes
4answers
716 views

Can this business logic be enforced by a conditional database constraint?

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
6
votes
2answers
89 views

Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table?

Suppose I have the following view: CREATE VIEW dummy AS SELECT a.some_column, b.other_column FROM a LEFT JOIN b USING (key_column); When I do a SELECT some_column FROM dummy, is there a way to ...
6
votes
2answers
3k 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
1k 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
1answer
286 views

What is “SAP on Oracle”

OK, total newb question. What is "SAP on Oracle", exactly. This link is talking about how SAP can run on Oracle (I think). I'm not understanding how this is working. My understanding: SAP is a ...
6
votes
2answers
360 views

In Oracle, can we restore a database onto the same instance with a different name?

In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different ...
6
votes
3answers
77k views

How to make sqlplus output appear in one line?

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read. What I'd rather like is either a horizontal scroll bar to appear or somehow send the ...
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
1answer
3k views

Why does an Oracle database require both the SYSTEM and the SYSAUX tablespaces?

Quoting from Oracle documentation about logical storage structures: A database must have the SYSTEM and SYSAUX tablespaces: Why is it compulsory to have these tablespaces?
6
votes
1answer
63k views

“ORA-03113: end-of-file on communication channel” on startup

I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem... An Oracle database hung. Shutdown of the database sat for ...
6
votes
1answer
2k views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
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
1answer
6k views

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, ...
6
votes
2answers
629 views

Can Oracle be installed on Windows without being an administrator?

Can the oracle software be installed on windows without having administrator privileges?
6
votes
1answer
25k views

Importing schema to new or different tablespace

Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated? As an example, I have exported BLOG_DATA from OLDDB, where ...
6
votes
1answer
3k views

When is an application using too many sessions?

I'm working on a client-server application that uses OCI to communicate with Oracle. In the past our application often treated sessions as an infinite resource, quite possibly to the detriment of ...
6
votes
4answers
23k 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
2answers
32k 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
1answer
92 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
6k 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
476 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 ...
6
votes
2answers
4k views

Oracle's Merge v. Select->Update/Insert

What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value? I'm trying to optimize a database, but I haven't found any speed tests comparing the two. I found ...
6
votes
1answer
369 views

Can I use Oracle Standard or Enterprise edition for my own training purposes?

Question is about Oracle licensing terms. I would like to learn more about Oracle Database administration. Unfortunatelly I am using 32 bit verison of LInux, so I can't install Oracle 11 XE (I can't ...
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
18k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
6
votes
1answer
7k 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
1answer
744 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
349 views

Exporting database Oracle 7.3 on Windows NT 3.51

How to take dump files on Oracle7.3 running on Windows NT 3.51? Should you stand in the Orant/bin folder and run one of the programs there like> exp73 SYSTEM/password@[dbinstance] FULL=Y ...
6
votes
2answers
6k views

How to Install/Configure Oracle Instant Client on Ubuntu

Installing the Oracle Instant Client on Ubuntu/Debian based systems is awkward since Oracle is RPM centric regarding its Linux support. What's the best way to easily install and configure the Instant ...
6
votes
3answers
2k 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
5k 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
2answers
1k 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
57k 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
1answer
2k 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
167 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
3answers
4k 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
2answers
119 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
466 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
402 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
17k views

How to free up disk space? which logs/directories to clean?

I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size /u01/app/11.2.0/grid/cv/log ...
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 ...
6
votes
3answers
2k views

Why would one need multiple instances of same Oracle database?

I came across following statement while reading this short article - An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance ...
6
votes
1answer
809 views

Package state not re-intialized on raise_application_error

When a package has state and the header is changed, the first call gets an error stack something like this: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of ...
6
votes
2answers
3k views

SQL developer: Setup debugger for plsql

I'm trying to debug remotely pl/sql. But I can't - database returns me an error. What should I do to fix this and start debugging ? ORA-30683: failure establishing connection to debugger ...
6
votes
1answer
1k views

What is the meaning of “select * from relational(owner.table)”?

We recently got some errors in our alert log of the form 301598: Tue Jul 16 03:58:29 2013 301599: ORA-01555 caused by SQL statement below 301600: SELECT * FROM ...
5
votes
3answers
2k views

Alternatives to an INSERT Trigger

Looking for an example code in Oracle to do the same. i.e. Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like? ...