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
4answers
700 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
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
284 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
357 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
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
2k 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
2answers
25k views

What is the easiest way to move data from Oracle to SQL Server?

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition ...
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
603 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
53k views

How to transfer data using expdp and impdp commands?

I'm Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump expdp and impdp commands. I have ...
6
votes
1answer
24k 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
3answers
954 views

What methods are available to create a snapshot of an oracle database for testing purposes?

I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done. I'm a bit ...
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
2answers
31k 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
2answers
446 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
14k views

How do I find an object that doesn't seem to exist in the objects table?

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
6
votes
1answer
321 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
722 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
345 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
4k 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
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
165 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
105 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
392 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
397 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
16k 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
1answer
790 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 ...
6
votes
4answers
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 ...
5
votes
3answers
1k 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? ...
5
votes
6answers
2k views

Is Oracle free to download?

I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free? I found this link: ...
5
votes
3answers
18k views

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
5
votes
3answers
705 views

For Oracle, we mount the instance to the database or mount the database to the instance?

The following text is extract from oracle documentation. Start the instance, and mount and open the database. This can be done in unrestricted mode, allowing access to all users, or in ...
5
votes
2answers
192 views

How the indexes are managed?

I am trying to understand how the indexes are managed for below common data types: 1- Numeric (Integer, Decimals) 2- String (Varchar, Char) 3- DateTime I have few questions: 1- How the indexes are ...
5
votes
1answer
3k views

how to use dual foreign key in oracle 10g

I have created two tables create table ref1(id varchar2(3) primary key); create table ref2(id varchar2(3) primary key); Now I want to create a third table with a single column as foreign key for ...
5
votes
2answers
15k views

How to get a list of locked accounts / check that account is locked?

I can use following statement to unlock an account: ALTER USER username ACCOUNT UNLOCK But which statement can I use to verify that account is currently locked out?
5
votes
2answers
13k 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 ...
5
votes
2answers
8k views

Why do we need to backup Oracle archive logs along with datafiles

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that Archived redo logs are the key to successful media recovery. Back them up regularly. But, I wonder, why backups of ...