Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [oracle]

All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.

Oracle is a relational DBMS (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. When asking a question with the oracle tag, please be sure to include the full version number, (i.e., 10.2.0.5, 11.2.0.4), in addition the relevant version-related tags described below.

Oracle makes plethora of documentation available at http://docs.oracle.com.

Tagging

  • Questions about SQL should be tagged . If the question applies to an Oracle database it should probably also be tagged .

  • Questions about PL/SQL should be tagged as well as .

  • Questions specific to a particular database version may be tagged with a version tag such as , , , or , but they should also be tagged .

  • Questions applicable to 11.1.0.6 through 11.1.0.7 should be tagged both and .

  • Questions applicable to 11.2.0.1 through 11.2.0.4 should be tagged both and .

  • Questions applicable to other versions should be tagged as noted above.

  • Oracle TimesTen (in-memory database) should be tagged with as well as .

  • Oracle NoSQL (non-relational database) should be tagged with as well as .

7663 questions
102
votes
8 answers

Difference between database vs user vs schema

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)? If they are same then, what are the similarities between them? How do we use them? And how do we create them?
Ravi
  • 1,485
  • 4
  • 15
  • 20
101
votes
4 answers

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 = (PROTOCOL= TCP)(Host= hostname.network)(Port=…
Louis Rhys
  • 1,205
  • 3
  • 11
  • 8
78
votes
6 answers

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the first row for each Val, ordering by Kind. Sample…
BrunoLM
  • 3,313
  • 7
  • 26
  • 22
63
votes
7 answers

Connect to sql plus from command line using connection string

Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c. So how do I need to connect correctly? I used many options like: sqlplus…
Edvinas
  • 631
  • 1
  • 6
  • 3
63
votes
6 answers

Why not use a table instead of a materialized view?

I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some parameter. If view is saved as a physical table,…
jrara
  • 5,253
  • 19
  • 53
  • 65
62
votes
4 answers

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these two statements, if any?
Martin
  • 2,411
  • 4
  • 24
  • 34
60
votes
3 answers

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 output to less I run following statements in SQLPlus…
Kshitiz Sharma
  • 3,087
  • 8
  • 29
  • 35
55
votes
7 answers

EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

Whenever I need to check for the existence of some row in a table, I tend to write always a condition like: SELECT a, b, c FROM a_table WHERE EXISTS (SELECT * -- This is what I normally write FROM another_table WHERE…
joanolo
  • 12,949
  • 7
  • 35
  • 65
48
votes
15 answers

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to eliminate duplicates in the results and don't seem…
Leigh Riffel
  • 23,674
  • 16
  • 76
  • 148
46
votes
4 answers

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as a administrator using sqlplus: sqlplus sys as sysdba sqlplus system/manager These accounts should be uses for different purposes, I suppose. Which tasks are these two schemas meant for? When should I use…
Lazer
  • 3,281
  • 15
  • 40
  • 53
44
votes
8 answers

How to see list of databases in Oracle?

Is there an equivalent to MySQL SHOW DATABASES statement? Is it possible to find databases in a cluster? i.e. databases present on the network on some other system? Could I analyze the files present on an Oracle installation to find the same? Given…
Kshitiz Sharma
  • 3,087
  • 8
  • 29
  • 35
37
votes
3 answers

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is just as valid SUM(CASE WHEN invoice_amount > 100 THEN 1…
孔夫子
  • 4,300
  • 3
  • 27
  • 49
36
votes
2 answers

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 user_tables; Thanks, GC.
Clarkey
  • 1,043
  • 3
  • 12
  • 18
35
votes
3 answers

Why does this query work?

I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c. Why does this query not return an exception? select * from table_a where name in (select name from table_b); From what I understand, Oracle sees this as select * from…
eagerMoose
  • 523
  • 1
  • 5
  • 11
34
votes
2 answers

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.
Clarkey
  • 1,043
  • 3
  • 12
  • 18
1
2 3
…
99 100