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
5answers
9k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
6
votes
5answers
4k views

Auto-Commit in SQL Server and Oracle

When I worked on Oracle 8 many years before, I used to execute COMMIT command manually after each bulk INSERT. In SQL Server, Auto-Commit is ON by default which has advantages as well as hazards. I ...
6
votes
3answers
2k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
6
votes
4answers
521 views

Does an IOT guarantee order in a select?

We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1). The priority does not matter ...
6
votes
3answers
86k views

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database local_SID = (DESCRIPTION = (ADDRESS ...
6
votes
2answers
1k 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, ...
6
votes
3answers
5k views

Why use explicit cursors instead of regular loops?

I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data: for i in (select * from STUDENTS) ...
6
votes
3answers
785 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
9k views

How do I find my current SCN?

Given any version of Oracle: How do I find my current SCN? What is the maximum possible SCN?
6
votes
3answers
390 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
1answer
3k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
6
votes
1answer
2k views

When a record is locked in Oracle, can we know which record is locked?

When a record is locked ,can we know which one is locked? How can I get the record rowid or something else info? I can get some info by this sql SELECT ...
6
votes
2answers
1k 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
4answers
428 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
886 views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
6
votes
1answer
717 views

Materialized views in oracle

How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
6
votes
3answers
810 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
533 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
2answers
2k views

Oracle 10g - How to prevent one user from hogging all resources?

I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run ...
6
votes
2answers
321 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
227 views

How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?

I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( ...
6
votes
1answer
1k 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
7k views

What happens when you modify (reduce) a column's length?

Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and ...
6
votes
4answers
1k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
6
votes
1answer
968 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
2answers
3k views

How to extract Procedure, Function, etc. Source Code from an Oracle Export

I have generated an EXPDAT.DMP file of an Oracle database with the command: exp userid=usr/pass@db owner=own rows=n compress=n I then ran the following command to generate a file containing the ...
6
votes
2answers
366 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
3answers
30k views

How do I generate a tnsnames.ora file to connect to an Oracle Database?

I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find. Do I need to generate the tnsnames.ora file myself? If so, where do ...
6
votes
2answers
250 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
1answer
9k views

How can I Estimate Table Sizes within Schema (Oracle)

I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far: SELECT table_name, owner, last_analyzed FROM all_tables I'm fairly new to SQL so I have no idea how I ...
6
votes
3answers
2k 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 materialized view refresh schedule

Since I see that it is possible to specify an expression like sysdate + 1, here are my questions: Is it possible to specify the exact time of the refresh for materialized views in oracle? Is it ...
6
votes
1answer
3k 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
389 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
282 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
3answers
548 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
1k 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
883 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
3answers
3k 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

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
7k views

What makes an Oracle Applications DBA different from an Oracle DBA?

All Oracle databases support some type of application that the Oracle DBA would need to have some knowledge of. What makes an Oracle Applications DBA different than this? Does the term mean that ...
6
votes
1answer
424 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
974 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
3answers
8k views

Oracle returns an error ORA-01031: insufficient privileges

Oracle returns an error "ORA-01031: insufficient privileges" When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why? Oracle version: Oracle Database 10g Enterprise Edition ...
6
votes
0answers
136 views

9i streams capture doesn't evaluate rules

I have a streams replication set up between two oracle 9.2.0.8 instances as sources and an 11g instance as a destination, processing a small subset of data from each database (let it be one table ...
5
votes
3answers
950 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
278 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
2answers
172 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
3answers
341 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
4answers
3k views

PL/SQL: best way to count elements in an array?

Given this: DECLARE TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; MY_ARRAY T_ARRAY; V_COUNT INTEGER; I would like to do: BEGIN -- ... some code filling the MY_ARRAY ...